Problem with time format

[pcc]

I am fairly new to Access SQL and cannot understand this.
I have a a table called 'Status' including this record
Filename Date Time
R2035009.MEA 14-Nov-07 23:14:16

If I run this query
SELECT * FROM Status
WHERE (filename = "R2035009.MEA") and (Date = #2007/11/14# ) and (time = #23:14:16#);

I get no records returned, but if I run this query

SELECT * FROM Status WHERE (filename = "R2035009.MEA") and (Date = #2007/11/14# ) and (time <> #23:14:16#);

then the record is returned.

I guess it's some sort of formatting problem but I don't know where to start.
Can anyone assist, please? :wink:

 

[giacomo]

you shouldn't split up your date and time fields, it should be one column. If you're stuck with that format then I suggest you try to concatenate the two and query on that.

example:

SELECT * FROM Status 
WHERE (filename = "R2035009.MEA") and ([Date] + [time] =#2007/11/14 23:14:16#);

if you do that does it work?

PS you should avoid using DATE and TIME as field names.

hth,
Giacomo

 

[pcc]

Thanks for reply. In the meantime I have found that this works, so I will stick with it.

SELECT * from Status where filename = "R2035009.MEA" and Date = #2007/11/14# and cstr(time)="23:14:16";


Many thanks once again.
Cheers