Using Date and time in YYYYMMDDHHMMSS format automatically

[simon255]

I have a question about using date and time in the following format:

yyyymmddhhmmss, i.e.

20070328144801

I have a column in a table called ScLoadTime. I am trying to do an
query which will automatically show me everything between yesterday at
17:00:01 and Today at 17:00:00

I am using the following:

And ((ScLoadTime) Between Format(Date(),"yyyymmdd""170000""") And
Format(DateAdd("d",-1,Date()),"yyyymmdd""170001"""))

The problem is the query comes straight back with no results. If I
changed the query to

And ScLoadTime between '20070327170000' and '20070627170001'

this would show me the information I require (but it is no longer an
automatic process).

Any idea why my use of Format(Date() and Format(DateAdd are not
working correctly? I have spent 4 days trying, reading "Help" files
for FormatDate() and FormatDate, but still I have had no luck.

Many thanks,

Si.

 

[KARL DEWEY]

It seems to me your problem is that your field is not a datatype of DateTime
but is either a number field or text field.

What kind of field is it?

 

[John Spencer]

Between Format(Date()-1,"yyyymmdd" & "170001") and Format(Date(),"yyyymmdd"
& "170000")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Si]

Between Format(Date()-1,"yyyymmdd" & "170001") and Format(Date(),"yyyymmdd"
& "170000")

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.















- Show quoted text -

Thank you so much. That worked straight away!

Si.