Results 1 to 3 of 3

Thread: SQL Statement

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good Afternoon,

    I'm struggling to find out what is wrong with my SQL syntax ( ). Can someone help find my error?

    [codebox]
    dCLD = DMax("LoadDate", "tbLoadDate")

    strData = "SELECT tbl_CompleteList.fkUserID " & _
    "FROM tbl_CompleteList " & _
    "WHERE (tbl_CompleteList.fkUserID Is Null AND tbl_CompleteList.nClosed=0 AND (tbl_CompleteList.Load_Date < dcld));"[/codebox]

    I've also tried:

    [codebox]
    strData = "SELECT tbl_CompleteList.fkUserID " & _
    "FROM tbl_CompleteList " & _
    "WHERE (((tbl_CompleteList.fkUserID) Is Null) AND ((tbl_CompleteList.nClosed)=0) AND ((tbl_CompleteList.Load_Date) < dcld));"[/codebox]

    When I run either one, the dreaded "Run Time-Erro 3061: Too few parameters. Expected 1", error message is returned.
    I appreciate any help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You cannot use a VBA variable within an SQL string - you must concatenate the SQL string with its value. Also, date values must be enclosed in # characters:

    strData = "SELECT fkUserID FROM tbl_CompleteList " & _
    "WHERE fkUserID Is Null AND nClosed=0 AND " & _
    "Load_Date < #" & dCLD & "#"

    If there is a chance that your database will be used by someone with non-US date settings, you must convert the date to mm/dd/yyyy format:

    strData = "SELECT fkUserID FROM tbl_CompleteList " & _
    "WHERE fkUserID Is Null AND nClosed=0 AND " & _
    "Load_Date < #" & Format(dCLD, "mm/dd/yyyy") & "#"

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans,

    That works....I don't know why I insist on overlooking the obvious and making my work harder than it needs to be.

    Much appreciated.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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