Date stored as a number 1

[melaniecarr23]

Someone wrote a program for us using access as the backend database. I have linked to one of their tables so I can run reports off it. The problem is pulling records by date.

7/14/05 is stored as 38547 for example.

How am I supposed to do queries using the date? I can format it as mm/dd/yyyy, but when using now() in the criteria I get no records and I know there are records for today's date.

WHY does this not work?

Thanks,
Melanie

 

[SkipVought]

Hi,

Microsoft Dates are just numbers! 38547 is july 14, 2005.

When you compare convert Now() to an integral number like this

....
Where MyDate < CLng(Now())

Skip,
[sub]
[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie![/sub]

 

[RoyVidar]

Now doesn't return an integer, but a double, so if you are comparing a date (long integer) with now (double), you won't get a match, try either Date() or Int(Date)

Roy-Vidar

 

[jsteph]

Roy,
But Skip was casting Now() with Clng, so that would work.

I'm not sure about the rounding...if Now() is 11:59 PM if it rounds to the next day, so Fix() I believe takes care of that.
--Jim

 

[RoyVidar]

jsteph,
I think I tried to answer the OP's question about why Now() didn't work in the criteria row of the query, and suggested the Date() function in stead, which doesn't need any conversion to match the numbers...

If you're suggesting that the Int function I mentioned may have some rounding problems, and to use the Fix function in stead, I think you'll need negative values for that to make any difference on the rounding (dates earlier than 30/12/1899) - but, as stated, if using the Date() function as I suggested, there's really no need for any of them, just use Date()...

Roy-Vidar