hi!!!!!

have an access front end with from with ms sql server 7.0 as the
backend. the tables are linked to the ms access frontend (which has
the forms too and some local table).

when trying to execute a query with time in the where clause (the time
alone is stored in a column with datetime datatype) it fails to
retrieve the coressponding records

eg. of where clause used.

select * from testtable where timefield = format(<string containing
time value>,"hh:ss:mm ampm")

when the record is seen in the linked table the time value is seen as
5:00:00 PM

would highly appreciate if someone can guide me in the right direction

regards
bala

hi scott

first and foremost thanx for replying.

well infact i did enclose the time value within pound sign (#).

though the backend is ms sql server, since it is linked (linked
tables) to the ms access database, if i am right, it will have the
general characteristics of ms access tables.

well i found the solution. use the TimeValue (which returns the time)
builtin function of ms access on both the column of the table
containing time value and also on the variable holding the time value

using the same example i had used to describe the problem

instead of the following query
select * from testtable where timefield = format(<string containing
time value>,"hh:ss:mm ampm")

it is
select * from testtable where TimeValue(timefield) = TimeValue(<string
containing time value>)

here timefield is the field or column containing time value in the
linked table and "<string containing time value>" is a string variable
containing the time value from the form and used in the where clause.

thanx and hope u find this useful. incase u get any other solution
please do let me know, i am always of learning and updating my
knowledge base.

take care and have a great day

regards
bala

correction of the syntax inthe example used above

the correct syntax with time in where clause is

select * from testtable where TimeValue(timefield) = TimeValue(#" &
<string
containing time value> & "#)

thanx and hope this helps

regards
bala

note: again this is an reminder, this query is issued on a linked ms
sql server table so have to enclose time or date value with pound sign
(#). even otherwise the date and time should be enlosed with pound
sign (#) when the query is issued from visual basic or ms access on a
table in sql server database.