How do I get the number of a date.....

[Guest]

How do I convert a Date in the Format DD/MM/YYYY to a number?

The Variable that the data is read into is a date variable, e.g. DATE_ENTERED

I have tried VAR(DATE_ENTERED) INT(DATE_ENTERED), but none of these work

Anybody any Ideas

 

[Stefan Hoffmann]

hi,

How do I convert a Date in the Format DD/MM/YYYY to a number?

Why would you do that?

I have tried VAR(DATE_ENTERED) INT(DATE_ENTERED), but none of these work

What kind of number?

You can use
- CDbl(CDate(yourDate))
- CLng(CDate(yourDate))
- CLng(Format(CDate(yourDate), "DDMMYYYY"))


mfG
--> stefan <--

 

[Guest]

I am trying to use Date a part of a unique key required to build a Vlookup,
so I need the Date converted to a value, e.g in Excel if you use
INT(dd/mm/yyyy) it returns a number

Thank you

 

[Stefan Hoffmann]

hi,

I am trying to use Date a part of a unique key required to build a Vlookup,
so I need the Date converted to a value, e.g in Excel if you use
INT(dd/mm/yyyy) it returns a number

If this is really necessary, then use

CLng(Format(CDate(yourDate), "DDMMYYYY"))

Take a look at the help for the Format() function.

But in such cases the better solution is to take YYYYMMDD, because this
is sortable as number and as string.


mfG
--> stefan <--

 

[Guest]

Thank you Stefan, works exactly as I wanted it......

Thank you

 

[John W. Vinson]

I am trying to use Date a part of a unique key required to build a Vlookup,
so I need the Date converted to a value, e.g in Excel if you use
INT(dd/mm/yyyy) it returns a number

Are you working in Access, or in Excel? VLookUp doesn't exist in Access; and
if you're trying to use the date as a criterion or to join two tables, it is
not necessary to convert it to a number in order to do so.

What's the context? What are you working with, and what are you trying to
accomplish? It's quite likely that there is a simpler way with no conversion
at all.

Remember: Access is NOT "Excel on steroids". It's not a flawed implementation
of Excel. It is a *different program* and does things differently; some of the
ways you're used to doing things in Excel may not apply at all, or may have
much simpler alternatives!

John W. Vinson [MVP]