Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2003
    Posts
    5

    Unanswered: Query for known record returns no results

    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

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150

    Re: Query for known record returns no results

    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

  3. #3
    Join Date
    Feb 2003
    Posts
    5
    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.

  4. #4
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150

    Lightbulb

    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

  5. #5
    Join Date
    Feb 2003
    Posts
    5
    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.

  6. #6
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    I wonder if it is not adding split seconds.

    It may be worth having a look at this.

  7. #7
    Join Date
    Feb 2003
    Posts
    5
    Hmm... How can I tell?

  8. #8
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    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

  9. #9
    Join Date
    Feb 2003
    Posts
    5
    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.

  10. #10
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •