Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1116
  • Last Modified:

Access 97 - General Date Format

Good Afternoon,

I have an Audit table that stores the date and time that a record was changed using the General Date Format (in the table). I am creating a report that will show all the records changed between dates (the user chooses on a form with unbound fields for entering the date). In my query I am using the following criteria:

Between [Forms]![frmChangeReports]![txtStartDate] And [Forms]![frmChangeReports]![txtEndDate]

Problem is that if a record was changed on 8/23/2004 9:17:05 AM and on my search form I put 08/01/04 to 08/23/04 I don't get the record referenced above.

Is there an easy solution to this (other than changing the format of the date in the table)?

Let me know if you need more information.

Lena
0
LenaWood
Asked:
LenaWood
1 Solution
 
ornicarCommented:
Simply add 1 day to the user's input using DateAdd function:

Between [Forms]![frmChangeReports]![txtStartDate] And DateAdd("d",1,[Forms]![frmChangeReports]![txtEndDate])

To be very precise we should add 23h 59m only, but noone is working at 00:00 :)

0
 
Data-ManCOOCommented:
You could do it like this if you like

Between [Forms]![frmChangeReports]![txtStartDate] And Cdate(Clng([Forms]![frmChangeReports]![txtEndDate]) + .999988425925926)


The value .999988425925926 is really 23:59:59

Mike
0
 
LenaWoodAuthor Commented:
Mike - I get the same message that the calculation is too complex :-(

Ornicar - your suggestion seems to do just want I wanted too.  Thanks!

Lena
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now