Date comparison

[Peter Kinsman]

I have a query that provides the data to a monthly report. The start and
end dates for the month are stored in text boxes on a form and a WHERE
[Date] Between MonthStartDate and MonthEndDate2 extracts the correct
records.
At the same time, I try to place the data in week ending fields in the
query. I have text boxes for start and end dates for the four or five weeks
in the month, and fields in the query of the form
Wkn: IIf([Date] Between WeeknStartDate and WeeknEndDate, Qty, 0)
The results are bizarre, including the same quantity appearing in more than
one column, although it behaves once the Day is greater than 12.
I have formatted the dates in the text boxes as mm/dd/yy to please SQL, but
my native date format is dd/mm/yy and I am wondering if comparisons on the
Field row of a query need to be in dd/mm/yy format.

Any suggestions please?

Many thanks

Peter Kinsman

 

[Golfinray]

Datediff("d",[start],[end])

 

[Peter Kinsman]

I have added text boxes to hold the dates in dd/mm/yy format and it appears
to work perfectly.

Peter