quick help... date format issue in query

[travismp]

>=[FORMS]![frm_DATE_DIALOG]![Beginning Date] And <=[FORMS]![frm_DATE_DIALOG]![End Date]

My user has a form which they enter a begin date and then an end date. This is the criteria in the backend query in a field called [DateOrdered] The problem is that the field also has a time stamp so when my user queries this it will drop off the tests on the final day entered because if they enter

01/01/2008 to 06/25/2008 and there were 25 orders placed on 06/25/2008 they do not show up because technically they are listed as 06/25/2008 08:25:33 AM

The records placed on the final day do not show up due to they are taking place after 06/25/2008 in the eyes of the DB.

Please let me know what I can change to make this work for me. Thanks.

 

[GaryPanic]

add 1 day to your qry so it looks at the date in your form
Zdate then have the qry = zdate+1 see how that works

 

[raskew]

Hi -

The DateValue() function will return just the portion of a date/time field.

Here's a small example from the debug (immediate) window:

x = now()
? x
10/7/2008 5:03:19 PM 
? datevalue(x)
10/7/2008

HTH - Bob

 

[travismp]

the +1 function will not work the way my system is set up.

I would like to try the DateValue() option however I am not sure how to set it up in my query. Is that added into the criteria or in the fieldname section?

 

[Brianwarnock]

the +1 function will not work the way my system is set up.

why not? It is a fairly standard approach, provided nothing happens at midnight.

Brian

 

[Brianwarnock]

I would like to try the DateValue() option however I am not sure how to set it up in my query. Is that added into the criteria or in the fieldname section?

In the fieldname section.

Brian

 

[gemma-the-husky]

>=[FORMS]![frm_DATE_DIALOG]![Beginning Date] And <=[FORMS]![frm_DATE_DIALOG]![End Date]

irrespective of the time bit, surely this needs to be wrapped to include # characters
around the dates?

 

[travismp]

the +1 feature will not work because I have another form which adds the date without the time stamp into the same field. So I might have 10 total records 4 with times, 6 without. If I use the +1 feature it will grab the 6 without time stamps if they are entered the following date of my [enddate]

The #'s are not needed. I copied the criteria exactly as is from my query which works perfect EXCEPT for dropping any records with a timestamp included with the enddate.

 

[travismp]

Fieldname:

Date: DateValue([TestDate])

criteria:

>=[FORMS]![frm_DATE_DIALOG]![Beginning Date] And <=[FORMS]![frm_DATE_DIALOG]![End Date]

Error:
The expression is typed incorrectly, or it is too complex to be evaluted. For example, a numberic expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

 

[neileg]

Use <[FORMS]![frm_DATE_DIALOG]![End Date]+1
Note I have deleted the equals sign

 

[Brianwarnock]

If I use the +1 feature it will grab the 6 without time stamps if they are entered the following date of my [enddate]

Yes this is the equivalent of midnight as they take a default time of 00:00:00,
you really need to add 23:59:59, but using datevalue on the field should work.
However as ever Neil has the simple solution.

Brian

 

[boblarson]

Fieldname:

Date: DateValue([TestDate])

criteria:

>=[FORMS]![frm_DATE_DIALOG]![Beginning Date] And <=[FORMS]![frm_DATE_DIALOG]![End Date]

Error:
The expression is typed incorrectly, or it is too complex to be evaluted. For example, a numberic expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

For one, you shouldn't name a field DATE (which you are doing by saying Date: DateValue...) because DATE is a reserved word in Access.

 

[travismp]

OK so by dropping the = sign we are telling it to show me anything less than 6/30/2008 plus one more day, which would be anything done on 6/30/2008.

I did not get to drop the = sign on the second half. It looks like that will work perfect.

THANK YOU ALL FOR THE HELP!!!