Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Query expression problem (access 2003 xpsp2)

    The code below takes passed info from a listbox click event (childID) and checks the attendance table for the the same id with the date it is being entered already existing. A simple check to stop double entry

    strSQL = "SELECT tblAttendance.ChID, tblAttendance.AttendDate FROM tblAttendance WHERE (((tblAttendance.AttendDate) = Forms![frmMain]![txtAttDate]));"

    Set rst = CurrentDb.OpenRecordset(strSQL)
    With rst
    .FindFirst "[ChID] = " & who
    If .NoMatch Then
    Set rst2 = CurrentDb.OpenRecordset("tblAttendance")
    With rst2
    .AddNew
    !ChID = who
    !AttendDate = txtAttDate.Value
    .Update
    End With
    rst2.Close
    Set rst2 = Nothing
    End If
    End With
    rst.Close
    Set rst = Nothing

    in the above strSQL [txtAttDate] is a text box on the same form that this code is from. The text box is in short date format

    the code dies at the Set rst = CurrentDb.OpenRecordset(strSQL) line with a not enough parameters error expected 1

    but........

    If I take the SQL code and paste it in to a query, the query returns nothing while the above code is paused but returns the correct records when the code is stopped!

    what is happening here and how can I get around it?
    "Heading for the deep end"

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query expression problem (access 2003 xpsp2)

    Just a guess, but try this:

    strSQL = "SELECT tblAttendance.ChID, tblAttendance.AttendDate FROM tblAttendance WHERE (((tblAttendance.AttendDate) = cdate(Forms![frmMain]![txtAttDate])));"
    Charlotte

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

    Re: Query expression problem (access 2003 xpsp2)

    If Charlotte's suggestion doesn't help:

    strSQL = "SELECT ChID, AttendDate FROM tblAttendance WHERE AttendDate = #" & Format(Forms![frmMain]![txtAttDate], "mm/dd/yyyy") & "#"

    If this code is called from frmMain itself, you can use Me.txtAttDate instead of Forms![frmMain]![txtAttDate].

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Query expression problem (access 2003 xpsp2)

    Thanks Hans, your version did the trick. I had tried along those lines by including the hash marks but it obviously needed the format command as well. As well as all the quote marks in thr right places!
    "Heading for the deep end"

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

    Re: Query expression problem (access 2003 xpsp2)

    The format command is to force the date into USA date format; SQL is strictly US oriented.

Posting Permissions

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