How to fix syntax error on iff statement

[Stevestan1]

I have a column labelled "date_st". I am trying to assign a period to the record based on the "date_st". I wrote an iff statement code, but I am getting syntax errors. Can anyone point out where the errors might be? Thanks.

Iif([date_st]=>12/26/2009 and [date_st]<=1/23/2010, “P1 2010”, iif([date_st]=>1/24/2010 and [date_st]<=2/20/2010, “P2 2010”, iif([date_st]=>2/21/2010 and [date_st]<=3/20/2010, “P3 2010”, iif([date_st]=>3/21/2010 and [date_st]<=4/17/2010, “P4 2010”, iif([date_st]=>4/18/2010 and [date_st]<=5/15/2010, “P5 2010”, iif([date_st]=>5/16/2010 and [date_st]<=6/12/2010, “P6 2010”, iif([date_st]=>6/13/2010 and [date_st]<=7/10/2010, “P7 2010”, iif([date_st]=>7/11/2010 and [date_st]<=8/7/2010, “P8 2010”, iif([date_st]=>8/8/2010 and [date_st]<=9/4/2010, “P9 2010”, iif([date_st]=>9/5/2010 and [date_st]<=10/2/2010, “P10 2010”, null)

[Mariostg]

There are a few things here.
First, Greater or equal than is >= not =>
Second, dates must ben enclosed between #
Third, nesting IIf can get extremely confusing. You need a bunch of closing parenthesis which can be hard to debug.

This is my guess

SELECT Iif([date_st]>=#12/26/2009# and [date_st]<=#1/23/2010#, "P1 2010", iif([date_st]>=#1/24/2010# and [date_st]<=#2/20/2010#, "P2 2010", iif([date_st]>=#2/21/2010# and [date_st]<=#3/20/2010#, "P3 2010", iif([date_st]>=#3/21/2010# and [date_st]<=#4/17/2010#, "P4 2010", iif([date_st]>=#4/18/2010# and [date_st]<=#5/15/2010#, "P5 2010", iif([date_st]>=#5/16/2010# and [date_st]<=#6/12/2010#, "P6 2010", iif([date_st]>=#6/13/2010# and [date_st]<=#7/10/2010#, "P7 2010", iif([date_st]>=#7/11/2010# and [date_st]<=#8/7/2010#, "P8 2010", iif([date_st]>=#8/8/2010# and [date_st]<=#9/4/2010#, "P9 2010", iif([date_st]>=#9/5/2010# and [date_st]<=#10/2/2010#, "P10 2010", null))))))))))

[ADezii]

1. Why not let a Public Function do the work? It is more readable, efficient, and versatile. Simply pass to the Function the appropriate Date.
   

   Public Function fReturnInfo(dteDate As Date) As Variant
   If dteDate >= #12/26/2009# And dteDate <= #1/23/2010# Then
     fReturnInfo = "P1 2010"
   ElseIf dteDate >= #1/24/2010# And dteDate <= #2/20/2010# Then
     fReturnInfo = "P2 2010"
   ElseIf dteDate >= #2/21/2010# And dteDate <= #3/20/2010# Then
     fReturnInfo = "P3 2010"
   ElseIf dteDate >= #3/21/2010# And dteDate <= #4/17/2010# Then
     fReturnInfo = "P4 2010"
   ElseIf dteDate >= #4/18/2010# And dteDate <= #5/15/2010# Then
     fReturnInfo = "P5 2010"
   ElseIf dteDate >= #5/16/2010# And dteDate <= #6/12/2010# Then
     fReturnInfo = "P6 2010"
   ElseIf dteDate >= #6/13/2010# And dteDate <= #7/10/2010# Then
     fReturnInfo = "P7 2010"
   ElseIf dteDate >= #7/11/2010# And dteDate <= #8/7/2010# Then
     fReturnInfo = "P8 2010"
   ElseIf dteDate >= #8/8/2010# And dteDate <= #9/4/2010# Then
     fReturnInfo = "P9 2010"
   ElseIf dteDate >= #9/5/2010# And dteDate <= #10/2/2010# Then
     fReturnInfo = "P10 2010"
   Else
     fReturnInfo = Null
   End If
   End Function

2. Sample Function Calls:
   

   Dim varRet As Variant
   
   varRet = fReturnInfo(#4/27/2010#)
   
   Debug.Print varRet

   SELECT [SomeDate], fReturnInfo([SomeDate]) As Period FROM Table1;

3. OUTPUTs:
   

   P5 2010

   SomeDate	Period
   1/2/2010	P1 2010
   10/27/2010	
   7/29/2010	P8 2010
   5/1/2010	P5 2010

[Oralloy]

@Stevestan1,

If the answer @Mariostg posted works, great. If not, start simple and build up one step at a time....

SELECT Iif([date_st]>=#12/26/2009# and [date_st]<=#1/23/2010#, "P1 2010", "<insert alternate here>")

And then, step by step, extend the alternate conditions until you get what you need.

A better solution would be to put the code into a function (in a module), that can then be called like this:

SELECT MyP([date_st])

Where the function looks something like this:

public function MyP(theDate byVal As Variant) As Variant
  If (isNull(theDate)) Then            MyP = null
  ElsIf (theDate < #12/26/2009#) Then  MyP = "error"
  ElsIf (theDate < #1/24/2010#) Then   MyP = "P1 2010"
 ...
  Else
    MyP = null
  End If
End Function

[Mariostg]

Or set yourself some arrays in a function like the following. It is a lot easier to maintain a function as ADezzi said.

Function GetPeriod(myDate As Date) As String
Dim arrayDate1 As Variant
Dim arrayDate2 As Variant
Dim arrayPeriod As Variant
Dim i As Integer
Dim myPeriod As String
arrayDate1 = Array(#12/26/2009#, #1/24/2010#, #2/21/2010#, #3/21/2010# _
    , #4/18/2010#, #5/16/2010#, #6/13/2010#, #7/11/2010#, #8/8/2010#, #9/5/2010#)
arrayDate2 = Array(#1/23/2010#, #2/20/2010#, #3/20/2010#, #4/17/2010# _
    , #5/15/2010#, #6/12/2010#, #7/10/2010#, #8/7/2010#, #9/4/2010#, #10/2/2010#)
arrayPeriod = Array("P1 2010", "P2 2010", "P3 2010", "P4 2010", "P5 2010" _
    , "P6 2010", "P7 2010", "P8 2010", "P9 2010", "P10 2010")

For i = 0 To UBound(arrayDate1)
    If myDate >= arrayDate1(i) And myDate <= arrayDate2(i) Then
        myPeriod = arrayPeriod(i)
        Exit For
    End If
Next i
If myPeriod = "" Then myPeriod = "No Period Assigned."
Debug.Print myPeriod
End Function

[OldBirdman]

If [date_st] contains a time element also, then be very careful with <= comparisons. If the date is #1/23/2010 8:00:00AM# and the SELECT statement is:

SELECT Iif([date_st]>=#12/26/2009# and [date_st]<=#1/23/2010#, "P1 2010", Iif([date_st]>=#1/24/2010# and [date_st]<=#2/20/2010#, "P2 2010",  .....

then this date is greater than #1/23/2010# so the value cannot be 'P1 2010', but this date is less than #1/24/2010# so the value cannot be 'P2 2010'.
If this condition can occur, for instance if [date_st] = Now() All of the suggestions above do not consider this.
Perhaps if you started each period with that period's first (lowest) date, then

Public Function fReturnInfo(dteDate As Date) As Variant 
Select Case dteDate
Case < #1/23/2010#
	fReturnInfo = "P1 2010"
Case < #2/20/2010#
	fReturnInfo = "P2 2010"
Case < #3/20/2010#
	fReturnInfo = "P3 2010"
. . . . .
Case Else
	fReturnInfo = Null
End Select
End Function