Date problems

[Guest]

I have a form that has a start date field and an end date field. This is
linked to a report that runs on a query for start date and end date. I have
made the query pick up the dates on a between(form1!start date)and(form1!end
date) for some reason if I put the date in as 01/01/06 and end 15/01/06 I
still get all the 2005 information. What can I be doing wrong. Here is the
query code
SELECT DISTINCTROW [STATS CAMP].EMPLOYEE, [STATS CAMP].NAME, Format$([STATS
CAMP].DATE,'Short Date') AS [DATE By Day], [STATS CAMP].DIVISION, Sum([STATS
CAMP].[Count Of STATS]) AS [Sum Of Count Of STATS]
FROM [STATS CAMP]
GROUP BY [STATS CAMP].EMPLOYEE, [STATS CAMP].NAME, Format$([STATS
CAMP].DATE,'Short Date'), [STATS CAMP].DIVISION
HAVING (((Format$([STATS CAMP].[DATE],'Short Date')) Between ([Forms]![CAMP
DAY COUNT]![START DATE]) And ([Forms]![CAMP DAY COUNT]![END DATE])))
ORDER BY Format$([STATS CAMP].DATE,'Short Date') DESC;
Thanks my form has the fields as short dates also.

 

[John Vinson]

I have a form that has a start date field and an end date field. This is
linked to a report that runs on a query for start date and end date. I have
made the query pick up the dates on a between(form1!start date)and(form1!end
date) for some reason if I put the date in as 01/01/06 and end 15/01/06 I
still get all the 2005 information. What can I be doing wrong. Here is the
query code
SELECT DISTINCTROW [STATS CAMP].EMPLOYEE, [STATS CAMP].NAME, Format$([STATS
CAMP].DATE,'Short Date') AS [DATE By Day], [STATS CAMP].DIVISION, Sum([STATS
CAMP].[Count Of STATS]) AS [Sum Of Count Of STATS]
FROM [STATS CAMP]
GROUP BY [STATS CAMP].EMPLOYEE, [STATS CAMP].NAME, Format$([STATS
CAMP].DATE,'Short Date'), [STATS CAMP].DIVISION
HAVING (((Format$([STATS CAMP].[DATE],'Short Date')) Between ([Forms]![CAMP
DAY COUNT]![START DATE]) And ([Forms]![CAMP DAY COUNT]![END DATE])))
ORDER BY Format$([STATS CAMP].DATE,'Short Date') DESC;
Thanks my form has the fields as short dates also.

The Format() function returns a TEXT STRING - not a date. The text
string "01/01/1944" is in fact greater than the string "01/01/06"
since 1 is greater than 0 (the first difference betweenthe two
strings).

Lose the Format$ functions; instead, set the Format of the textbox on
the Form in which you'll be displaying this information. Bear in mind
that Access stores date information as a number - a count of days and
fractions of a day; the format is NOT stored in the table. A search
criterion MUST be in American mm/dd/yyyy format (or an unambiguous
format such as yyyy-mmm-dd). I'd use the WHERE clause rather than
HAVING because it filters the records *BEFORE* they're summed, rather
than summing them and discarding a lot of the results.


Try

SELECT DISTINCTROW [STATS CAMP].EMPLOYEE,
[STATS CAMP].NAME, [STATS CAMP].[DATE],
[STATS CAMP].DIVISION,
Sum([STATS CAMP].[Count Of STATS]) AS [Sum Of Count Of STATS]
FROM [STATS CAMP]
GROUP BY [STATS CAMP].EMPLOYEE, [STATS CAMP].NAME
Datevalue([STATS CAMP].DATE),
[STATS CAMP].DIVISION
WHERE [STATS CAMP].[DATE] Between
Format(DateValue([Forms]![CAMP DAY COUNT]![START DATE])),
"mm/dd/yyyy") And
Format(DateValue([Forms]![CAMP DAY COUNT]![END DATE])),
"mm/dd/yyyy")
ORDER BY [STATS CAMP].[DATE] DESC;

Note also that DATE and NAME are both reserved words, and therefore
BAD choices as fieldnames. I'd recommend changing these to CamperName
and StatsDate or something unambiguous!

John W. Vinson[MVP]

 

[Guest]

Thanks, worked well!

I have a form that has a start date field and an end date field. This is
linked to a report that runs on a query for start date and end date. I have
made the query pick up the dates on a between(form1!start date)and(form1!end
date) for some reason if I put the date in as 01/01/06 and end 15/01/06 I
still get all the 2005 information. What can I be doing wrong. Here is the
query code
SELECT DISTINCTROW [STATS CAMP].EMPLOYEE, [STATS CAMP].NAME, Format$([STATS
CAMP].DATE,'Short Date') AS [DATE By Day], [STATS CAMP].DIVISION, Sum([STATS
CAMP].[Count Of STATS]) AS [Sum Of Count Of STATS]
FROM [STATS CAMP]
GROUP BY [STATS CAMP].EMPLOYEE, [STATS CAMP].NAME, Format$([STATS
CAMP].DATE,'Short Date'), [STATS CAMP].DIVISION
HAVING (((Format$([STATS CAMP].[DATE],'Short Date')) Between ([Forms]![CAMP
DAY COUNT]![START DATE]) And ([Forms]![CAMP DAY COUNT]![END DATE])))
ORDER BY Format$([STATS CAMP].DATE,'Short Date') DESC;
Thanks my form has the fields as short dates also.

The Format() function returns a TEXT STRING - not a date. The text
string "01/01/1944" is in fact greater than the string "01/01/06"
since 1 is greater than 0 (the first difference betweenthe two
strings).

Lose the Format$ functions; instead, set the Format of the textbox on
the Form in which you'll be displaying this information. Bear in mind
that Access stores date information as a number - a count of days and
fractions of a day; the format is NOT stored in the table. A search
criterion MUST be in American mm/dd/yyyy format (or an unambiguous
format such as yyyy-mmm-dd). I'd use the WHERE clause rather than
HAVING because it filters the records *BEFORE* they're summed, rather
than summing them and discarding a lot of the results.


Try

SELECT DISTINCTROW [STATS CAMP].EMPLOYEE,
[STATS CAMP].NAME, [STATS CAMP].[DATE],
[STATS CAMP].DIVISION,
Sum([STATS CAMP].[Count Of STATS]) AS [Sum Of Count Of STATS]
FROM [STATS CAMP]
GROUP BY [STATS CAMP].EMPLOYEE, [STATS CAMP].NAME
Datevalue([STATS CAMP].DATE),
[STATS CAMP].DIVISION
WHERE [STATS CAMP].[DATE] Between
Format(DateValue([Forms]![CAMP DAY COUNT]![START DATE])),
"mm/dd/yyyy") And
Format(DateValue([Forms]![CAMP DAY COUNT]![END DATE])),
"mm/dd/yyyy")
ORDER BY [STATS CAMP].[DATE] DESC;

Note also that DATE and NAME are both reserved words, and therefore
BAD choices as fieldnames. I'd recommend changing these to CamperName
and StatsDate or something unambiguous!

John W. Vinson[MVP]