Query by datevalue()

[Tyork]

The following query is used to populate a combobox control source. The problem is that I cant seem to get it to work properly.

The form controls in the query are as follows:

Forms!LA_Monthly![CF_Month] is a month value like January or February
Forms!LA_Monthly![CF_Year] is a year value like 2010 or 2011

The goal is to get a distinct set of 'ScoreBin' for a given month

 
SELECT DISTINCT ScoreBin
FROM dbo_VLetterSent
WHERE LetterSentDt <= Datevalue('Forms!LA_Monthly![CF_Month] & " 31, " &  Forms!LA_Monthly![CF_Year]')  AND LetterSentDt >= Datevalue('Forms!LA_Monthly![CF_Month] & " 1, " &  Forms!LA_Monthly![CF_Year]')

I've tried it multiple ways and can't seem to ge the syntax correct. Any help would be appreciated.

[jzwp11]

I would get rid of the single quote at the beginning and end of each date value (see where I have highlighted in red below, you'll have to do each expression).

Datevalue('Forms!LA_Monthly![CF_Month] & " 31, " &  Forms!LA_Monthly![CF_Year]')

Outside of that, what happens if the month in question does not have 31 days? I think you will get an error. For the ending date, you could actually set the ending date to the first of the next month and then subtract 1. You will need the dateadd() function. I'm am thinking that it might be a little be better to use a list/combo box instead of having a text box for your user to enter the month name. The list/combo box's bound column would be the month # but you could still display the month name. Your user could then select the month they want.

You can then use the dateserial() function to construct the date, add 1 to the month # and then subtract 1 to get the ending date

So for the ending date:
dateadd("d",-1,dateserial( Forms!LA_Monthly![CF_Year],comboboxwithmonthnumber+1,1) )

For the beginning date:
dateserial( Forms!LA_Monthly![CF_Year],comboboxwithmonthnumber,1)