Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Problem (Access 2000)

    The following code is giving me an error on some dates. Most of the time the code works but I will then get a no record found. Any ideas.

    Private Sub lstPalletNumber_Click()

    On Error GoTo Err_lstPalletNumber_Click

    Dim db As DAO.Database
    Dim smp As DAO.Recordset
    Dim PalletNumber, PalletID As String

    Set db = CurrentDb
    PalletNumber = "#" & [Forms]![frmNew]![lstPalletNumber].ItemData([Forms]![frmNew]![lstPalletNumber].ListIndex) & "#"
    sqlStr = "SELECT * FROM TBLPALLETS WHERE [DATE] = " & PalletNumber

    Set smp = db.OpenRecordset(sqlStr)

    PalletID = smp![PalletID]

    lstBoxNumber.RowSource = "SELECT tblBoxes.[BoxNumber] FROM tblBoxes WHERE [PALLETID] = " & PalletID

    Exit_lstPalletNumber_Click:
    Exit Sub

    Err_lstPalletNumber_Click:
    MsgBox Err.Description
    Resume Exit_lstPalletNumber_Click

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Problem (Access 2000)

    If you (or anyone else using the database) has Windows set to a non-US date format, the code will not work correctly. SQL only understands dates in m/d/y format. To get around this, use

    PalletNumber = "#" & Format([Forms]![frmNew]![lstPalletNumber].ItemData([Forms]![frmNew]![lstPalletNumber].ListIndex), "mm/dd/yyyy") & "#"

    Otherwise, we'd have to know more about the situations in which the code fails.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Problem (Access 2000)

    Thanks, that did it.

Posting Permissions

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