Format date and time

[John Baker]

I am trying to use DAO and findfirst with a time and date. The system is
set to Australian Time zone and thus uses dd-mm-yyyy
I am using:

RSAncillory.FindFirst "[flddate] > " &
Format(RSSightings("sight_time"), "\#mm\/dd\/yyyy\#")

which I think works, but I want to include the time in the format as well.
How can I do this? I did try using "General Date" but that doesn't seem to
work.

Also is it possible to use the FindFirst to find the next highest value
using the ">" as I have above?

Thanks in advance
John B

 

[Allen Browne]

The format string to include date and time would be:
"\#mm\/dd\/yyyy hh\:nn\:ss\#"

The FindFirst should return the first date/time value after this one,
provided the recordset is sorted by flddate. But you always have struggles
with exactly matching floating point values, so it might also be helpful to
ensure that the record it finds is not the same record. Assuming a primary
key field named ID, you would have:
Dim strWhere As String
strWhere = "(ID <> " & RSSightings!ID & ") AND (flddate > " & _
Format(RSSightings!sight_time, "\#mm\/dd\/yyyy hh\:nn\:ss\#") & ")"
RSAncillory.FindFirst strWhere

 

[John Vinson]

I am trying to use DAO and findfirst with a time and date. The system is
set to Australian Time zone and thus uses dd-mm-yyyy

As you have seen, you must convert this to American m/d/y to use a
date literal in searches.

I am using:

RSAncillory.FindFirst "[flddate] > " &
Format(RSSightings("sight_time"), "\#mm\/dd\/yyyy\#")

which I think works, but I want to include the time in the format as well.
How can I do this? I did try using "General Date" but that doesn't seem to
work.

Try "\#mm\/dd\/yyyy hh\:nn\:ss\#"

Also is it possible to use the FindFirst to find the next highest value
using the ">" as I have above?

Use FindNext, if the recordset is sorted in reverse chronological
order.

John W. Vinson[MVP]

 

[Guest]

If you want to be independent of the Australian/US time formats, you could
use something similar to the following:

RSAncillory.FindFirst "[flddate] > CDate('" &
Format(RSSightings("sight_time"), "dd mmm yyyy hh:nn") & "')"

It should find the first record where flddate is higher than the date you
specified. To ensure that this is the lowest date/time from the whole
recordset, you will need to order the records first.

HTH