Month Function Data Type Problem

[bhujanga]

I have a report in which I am searching for records based on whether the month of a particular date in the record is less than or equal to a month that is stored in a one record table (this is to get YTD totals as of the end of a specified month).
This is an example of the control source I am using (I have many of these on the report that are using varied criteria, but they all are using the same date test):

="Level II - " & DCount("[PermitNumber]","AccessPermits","month([IssueDate]) <= '" & [ReportMonth] & "' and year([DateTransmit]) = '" & [ReportYear] & "'and [ComplexityLevel] = 'II'")

The part that is of relevance is this:
...month([IssueDate]) <= '" & [ReportMonth]...

In this case, [IssueDate] is the date being compared to an invisible field on the report called [ReportMonth] which reflects the month number that has been extracted from the single-record "Month-Year" table.

This works fine until we get to Oct, Nov and Dec. What seems to be happening is that the comparison is treating the data as text, so months 2 through 9 are not considered to be less than months 10 through 12. I know that the data in the Month-Year table is numeric and the description for the "Month" function says it returns an integer, so I don't see why this is happening and I can't figure out how to rectify it.

Any suggestions?

 

[PWise]

how about

    ...month([IssueDate]) <= '" & val([ReportMonth])...

 

[dhookom]

Don't convert the numbers to text by adding in the single quotes.

Try:

="Level II -  " & DCount("[PermitNumber]","AccessPermits","month([IssueDate]) <= " & [ReportMonth] & " and year([DateTransmit]) = " & [ReportYear] & " and [ComplexityLevel] = 'II'")

I'm not sure this logic will work as the year changes.


Duane
Hook'D on Access
MS Access MVP

 

[PWise]

how about

    ...month([IssueDate]) <= " & val([ReportMonth])...

 

[bhujanga]

I tried taking the single quotes out and that did the trick. I still have trouble sorting some of the syntax out and I tend to follow past examples a little too closely I guess. Thanks for your help.