Need to compare two "complete date"

[Conner]

Need to compare two "complete date" (m/d/yyyy) in a query.I can use the DatePart function to extract a month, or a day or a year, but apparently not all three at the same time. The fields in the query are populated with the Now() function, so I get something like this: 1/22/2004 8:39:07 a.m. What I need to do is compare the full date,i.e 1/22/2004 with another date, like 1/23/2004.

This can be done if you use about six new query fields, the DatePart function half-a-dozen times and then create a function that compares first year to year, then month to month and finally, say, day to day, but this does not seem like a elegant approach.

Is there a better way? And if I can extract the full day, have I failed to anticipate problems with trying to compare a complete date with a compete date? Why is there not a better way in Access to do this? Am I missing something here?

 

[SteveR77]

Format the field that uses the Now function to be

FormattedDate:Format("m/d/yyyy",[NowDateField])

Once you do that you'll be able to do the comparison that you are attempting to do.



Steve

 

[Golom]

Although I dislike doing this, you can rely on the fact that dates are stored as doubles with the date before the decimal and the time after. A comparison like this compares the date parts and ignores the times.

   If cDate(Int(DateField1)) = cDate(Int(DateField2)) Then ...

 

[r937]

any time you apply a function to a column, as in

... where XXXX(datefield) = something

then you are not going to get the benefit of any index on the column

it is always better to do this instead:

... WHERE datefield = YYYY(something)

or (as in this particular case)

... WHERE datefield >= ZZZ1(something)
AND datefield < ZZZ2(something)

allowing the database engine to come up with two datetime values that it can use to search the index on is much, much faster

does this help? do you need the sometings elaborated?

rudy

http://r937.com/

SQL Consulting

 

[Conner]

Steve,Golom,Rudy: Suggestions as to what follows?

Thanks for the quick responses. I think the real problem may be that I'm trying to use the Now() function to do more than it was really designed to do...it may be better for me to go back,redesign the tables and queries, and use the Date function to collect the date and then figure out a way to get the times in at the instant the &quot;SignIn&quot; or &quot;SignOut&quot; buttons are clicked.Use two date/time fields to collect the date and time instead of one with Now(). All I really need to do is make sure that when I compare two times (signIn) and (SignOut) that I'm comparing the same date...The real problem is that the user may click sign-out before they click sign-in, forget to sign-in but sign-out,or even wait until the next day to sign-out (&quot;I forgot.&quot

And the major spec given to me by our payroll department is that the time a button is clicked be the &quot;real&quot; time, like in Now(). I'm still prototyping, and learning lots, with this thing...But I really do like the way this little database works up to this point.

 

[r937]

&quot;Use two date/time fields to collect the date and time instead of one with Now().&quot;

no, no, no

that would be awful

as in, you will be overcome with awe, when you see the sql logic you need to write to get two fields to do what one was designed to do

rudy

http://r937.com/

SQL Consulting