I'm having a problem with retrieving existing records for an acess 2000 database in VB using DAO 3.6. I wrote the following to debug and to my surprise the msgbox does come up on some machines and certian records, always the same records each time it runs. Any ideas?

Set eqry = masterDB.OpenRecordset("select * from email", dbOpenSnapshot)
Do While Not eqry.EOF
keyu = eqry("keyu")
keyd = Str(eqry("keyd"))
Set emailRS = masterDB.OpenRecordset("select * from email where keyu = '" & keyu & "' and keyd = #" & keyd & "#", dbOpenDynaset)
If emailRS.EOF Then MsgBox "-" & keyu & "-" & keyd & "-"
eqry.MoveNext
Loop

I assume 'keyd' datatype is a Date

try formatting 'keyd' as US Date Format

keyd = Format(keyd, "mm/dd/yy")
Or
keyd = Format(keyd, "mm/dd/yy HH:MMS")

depending on if exact time is a factor


Microsoft Jet is icky with dates.

Regards,
Machado

Thanks but that didn't work either. I'm wondering if something really strange is going on, It only fails to get two or three out of 2000+ records? It only seems to happen on certian machine.

If it is just on a certain machine. Make sure that the regional settings are the same as the others.

I am not sure if this will work but give it a crack

Machado

Thanks, I checked that already

Check this out

SELECT * FROM email WHERE keyu='KNorris' AND keyd Between #2/27/2003 7:03:29# And #2/27/2003 7:03:31#

returns 1 record

SELECT * FROM email WHERE keyu='KNorris' AND keyd=#2/27/2003 7:03:30#

Doesn't return anything?????!?!?!

I get the same results from within Access.
If I change the record then change it back from access on my machine the record is then accessable on all machines. Repair/Compact doesn't fix it.

I wonder if it is not adding split seconds.

It may be worth having a look at this.

Hmm... How can I tell?

Because you are using jet you can add a VB Function in the SQL Query

Try this


WHERE Format(keyd, "mm/dd/yyyy HH:MMS") = #" & keyd & "#

So now you will be formatting the data you are searching before you search it. You will have to use CHR(34) for the quotation marks in the format function

Try it in Access before trying it in VB

Machado

You are the MAN.

Ok one last thing, is there a way in access to show the datetime with milliseconds?

Thanks for your help, I really appreciate it.

I Think Microsoft Jet is limited in this regard. I know SQL Server does give the option of milliseconds with a bit of fiddling.

Just search the net I am sure there is someone with the same problem.

Machado