Query Starting and Ending Dates

[gtroiano]

i have a form that archives old records and it prompts the user to enter the dates which to archive. the records are added to a seperate table with an append query. the criteria i use in the query is:
Between [Forms]![frmAuditArchive]![txtBeginningDate] And [Forms]![frmAuditArchive]![txtEndingDate].
the problem is that when a user enters the ending date it does not include entries from that date. i can see that because i use "Between" it is only getting records "between" the dates entered by the user. so, my question is how else can i enter the criteria in order to get the records on and between the dates the user enters?

 

[cmmrfrds]

Two solutions.
1. change the between to yourdate >= bdate and yourdate <= enddate.
2. format yourdate to use the short date format since it doesn't retrieve end date because of the time part of the date. The short date format will ignore the time part.

 

[gtroiano]

thanks for the quick reply.

on the form i have two text boxes where the user enters the beginning date and the ending date. what is the &quot;yourdate&quot; that you mention?

jerry.

 

[cmmrfrds]

where yourdate >= bdate and yourdate < (enddate + 1)

yourdate is the date you are comparing to in the database. probably better to add one to the ending date and use less than. It is different than between since you need to repeat the name for each comparision.

 

[gtroiano]

changing the field to short date doesn't seem to work and i can't use the &quot;+1&quot; in the query b/c i keep getting a data type mismatch error. any other ideas?

jerry.

 

[CosmoKramer]

Jerry,

Your source data is probably in &quot;General Date&quot; format. If so, try this, it's worked for me:

Between ([Forms]![frmAuditArchive]![txtBeginningDate] & &quot; 00:01 &quot And ([Forms]![frmAuditArchive]![txtEndingDate] & &quot; 23:59&quot

 

[gtroiano]

thanks for suggestion but it returned a data type mismatch error. indeed, i am using the general date format. i don't know if it matters but the user is only entering the date not the time, when archiving (example: &quot;4/01/02&quot; and &quot;4/23/02&quot. this is probably so easy i will kick myself when i get it!

jerry.

 

[CosmoKramer]

I have the users only entering the date too. Try specifying this as the format for your two date text boxes instead of Short Date:

mm/dd/yy

When I added the above time criteria, the query worked......

 

[gtroiano]

it works now! the problem was that there wasn't a space before the (&quot;00:01&quot in the criteria. thanks a lot for your help.

jerry.