Date range using IIf help

[jmccullough]

I have a form with a combo box containing the names of the Months. What I want to do is then pass the Month name to a query as a date range. For example, select "May" from cboMonth and the query will check the date field for "between 05/01/2008 And 05/31/2008"

Here is what I have in my query but it does not show any records when I run it:

IIf([Forms]![Form1]![cboMonth]="May",Between #5/1/2008# And #5/31/2008#)

Is it even possible to use this type of an expression in the query?

Thanks......

 

[boblarson]

No you don't use it this way. Use the DateSerial function to get what you need:

I would put this function in a standard module:

Public Function ReturnLastDateOfMonth(datDate As Date)
    ReturnLastDateOfMonth = DateSerial(Year(datDate), Month(datDate) + 1, 0)
End Function

and then use this in your query:

Between DateSerial(Year(Date()),Month([Forms]![Form1]![cboMonth]),1) And DateSerial(Year(Date()),Month([Forms]![Form1]![cboMonth]),ReturnLastDateOfMonth(DateSerial(Year(Date()),Month([Forms]![Form1]![cboMonth]),1)))

Then, it will always give you the date range of the selected month for the current year with the range being the first of the month to whatever the last date is.

 

[raskew]

Here's a sample query (based on Northwind's Employees table) that will prompt user for [mm/yyyy] and return those records falling within that range.

SELECT
    Employees.EmployeeID
  , Employees.LastName
  , Employees.FirstName
  , Employees.HireDate
FROM
   Employees
WHERE
   (((Employees.HireDate) Between DateValue([enter mm/yyyy]) 
AND
   DateValue(DateAdd("m",1,DateValue([enter mm/yyyy]))-1)));

HTH - Bob

 

[namliam]

How about using something like
cdate("01-" & [Forms]![Form1]![cboMonth] & "-" & year(date))

This should convert into 01-may-2008. Now to get the last of this month...
Dateadd("m",1,<above Cdate function>) - 1

Should return 31-May-2008

 

[jmccullough]

Thanks to all for the quick replies. Bob, I tried the code you supplied and am getting "Ambiguous name in query expression". Any ideas?

Thanks for your help.

 

[boblarson]

Do you, by chance, have a date field named DATE? If so, rename it as DATE is a reserved Access word.

 

[jmccullough]

No fields named Date in my query, field name is SchedDate.

I'll keep plugging away. Thanks for your help

 

[Rabbie]

I have just used the SQL posted by Bob in #3 and it worked perfectly. How did you use it. I created a query in SQL view using it and it produced the correct results.

 

[jmccullough]

I used it in the criteria of my field "SchedDate".