Date to Number

[Cosmos75]

I have a table that has a date field. What I need to do is to make a query of the the table and include the date column as well as a duplicate column that shows that date field as a number instead. How do I do this?

THANKS IN ADVANCE!

 

[Sohaila Taravati]

Do this in your query:
NewDate: IIf([YourDateField]=[YourDateField],1,2).
This will put number 1 in your new field and if there is no date it will put number 2. I just don't know why you want to put numbers for your date! Post back and elaborate.

Sohaila

 

[The_Doc_Man]

I think Sohaila misunderstood your question.

In the first column of your query, include the date. The default will be short date format. In the duplicate column, use

Format( [MyDate], "c" )

which will display the date as the uniform day number that underlies the current date manipulation scheme. If [MyDate] actually includes timestamps, you will also see the time as a fraction for which midnight = 0.0 and Noon = 0.5

 

[raskew]

What kind of a number do you want to see?

Not to be argumentive but in my time zone

(from the debug window)
endDte = now()
? format(endDte, "c")

returns: 6/8/02 5:19:35 AM

If you wanted to use Access' native date storage scheme you could try:

? cdbl(endDte)
37415.2219328704
or
? int(cdbl(endDte))
37415

Please provide a little more detail.

 

[Cosmos75]

Sorry for not giving enough detail.

There are two things I'd like to do, is to extract from a Long Date format (12/31/2002 3:15:00 PM)
1) the date (12/31/2002) into it's number format - i.e. 37621

2) the time (3:15:00 PM) into it's number format - 0.64

and put them into a new table.

 

[Jon K]

Try this make-table query:

SELECT Int(CDbl(DateTimeField)) AS [The Date],
Format(CDbl(DateTimeField)-Int(CDbl(DateTimeField)),"Standard") AS [The Time]
INTO NewTable
FROM yourTable

Hope this helps.