Query Inconsistency

[smaumau]

I have a report that I am trying to create that is returning some strange numbers. I am trying to count sales from an entered time frame. The first query counts the number of sales between the start date and end date that the user enters into a form (see below). The second query counts the sales month to date (see below). The [date] field in the second query simply returns the first day of the month of the end date. I was running the reports for the entire month (e.g. start date of 3/1/2006 and end date of 3/31/2006) in order to check for accuracy and the current period numbers did not match the month to date numbers. To make it even more confusing, the numbers tied out for January and February, but not March and April.

SumofSales1: Sum(IIf([salesdate1] Between forms!frmflash!startdate And forms!frmflash!enddate,IIf([Transfer?1]=No,1,0),0))

MTDSale1: Sum(IIf([salesdate1] Between [date] And forms!frmflash!enddate,IIf([transfer?1]=No,1,0),0))

I am struggling with this because no matter how much I look at these queries they are looking at the same records and should provide the same answer.

 

[Pat Hartman]

If salesdate1 contains a time component, that will throw the selection off. For example 5/5/6 11:30 AM is not between 5/5/6 1:00 PM and 5/7/6 3:33 PM. but it is between 5/5/6 and and 5/7/6 3:33 PM.

To get rid of time of day when trying to find a date in a range, use the DateValue() function.

SumofSales1: Sum(IIf(DateValue([salesdate1]) Between forms!frmflash!startdate And forms!frmflash!enddate,IIf([Transfer?1]=No,1,0),0))

 

[smaumau]

Continued....

That makes perfect sense, but when I run the query now I get the error message "This expression is typed incorrectly, or is too complex to be evaluated...." I checked the spelling and that is not the error. What can I do to fix this?

Thanks.

 

[Pat Hartman]

Break the expression apart so you can test the components individually.