Can I use standard Functions with MS Query?

[Guest]

I have a field that contains date and time, I would like to use the LEFT
function to trim off the data I do not want, as the output is to be used in a
pivot table (yikes). I am hooked to a FOXPRO database. Any suggestions?

Many thanks!
CJ

 

[Tom Ogilvy]

Date and time are stored as the elapsed number of days from a base date
(usually midnight preceding the start of 01/01/1900). So

? cdbl(now())
39068.6315162037
? now()
12/17/2006 3:09:32 PM

---------------------------------
so to separate time and date:


=Trunc(A1)
gives the date

=A1-trunc(A1)

gives the time.

in VBA

dt = int(Range("A1"))

dt1 = Range("A1")-int(Range("A1"))

to demo from the immediate window:

Range("A1") = now()
? range("A1").value
12/17/2006 3:05:17 PM
? int(Range("A1"))
12/17/2006
? cdate(Range("A1")-int(Range("A1")))
3:05:17 PM

of course, in Fox Pro, your value may very well be stored as a string - so
then it would depend on where you are getting the value from in your code.