Time criteria in queries

  • Thread starter Thread starter Ian Bayly
  • Start date Start date
I

Ian Bayly

I have a table where I need to keep dates and times in
separate fields.
If the table is an attached ACCESS table then a SELECT
query will return the correct number of records using a
time criteria passed in as a value.
If the table is an ODBC attached SQL 2000 table it returns
no records.
The data type in SQL 2000 is DateTime.
The datatype in Access is data/Time with a format of Long
Time.
If I hard code the time value in the query as a criteria
all is well.
I'm into my 24th hour on this problem so I'm starting to
go in circles - any ideas?

Thanks in anticipation.
 
You stated "If I hard code the time value in the query as a criteria all is
well." but didn't state how you are applying a criteria that isn't working
well. Are you using a parameter query or control on a form?
 
Sorry Duane

I really am going in circles.
On refreshing my memory, the only way I can get any values
returned from SQL 2000 is to have a criteria in a query of
TimeValue("13:00").
If I then transfer this to a SQL statement .... WHERE
tblTest.Time = #" & TimeValue('13:00') & "#" I get a
syntax error.
I think one resolution is to get the syntax right on my
SQL statement then I'm away

Any help appreciated

Ian B
 
Probably an inelegant solution BUT it works:
Dim myStr As String
myStr = TimeValue("17:00")
sSQL = "SELECT tblTest FROM tblTest WHERE tblTest.NATime =
TimeValue('" & myStr & "')"
Returb=ns the correct number of rows.
Wish I knew why!

Ian B
 
Have you tried something like:
sSQL = "SELECT tblTest FROM tblTest WHERE tblTest.NATime = #" & myStr & "#"
 
Pardon me, but are you using an .mdb or an .adb as the backend?

If the latter, then I think you need to use an apostrophe as the delimiter for
date and times vice the # mark. So that is why
'09:00' works vice #09:00#

This is of course a guess on my part.
 
Back
Top