Results 1 to 8 of 8
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    SQL String Not Working

    Hi Guy's, i have a query saved that runs OK when Running Query, (prompted to put date in), when i run it through VBA it won't run

    Too Few Parameters Expected 1 Run Time Error 3061

    sDelTo is a specific DelTo to avoid

    Is it when i am using Not In ? i know double quotes are needed but also trying exclude Delivery or Collection In the status ?

    So target is Exclude > sDelTo Exclude > Delivery > Exclude Collection

    It runs fine when trying Query25 with a prompt to add date (week start)


    Code:
    strSQL3 = "SELECT tblEdit.DelTo, tblEdit.Town, tblEdit.PostCode, tblEdit.ItemType, tblEditItemNo, tblEdit.SONumber, tblEdit.TotalBox, tblEdit.TotalItem, tblEdit.Status, tblEdit.ShipmentDate " _        & "From tblEdit " _
            & "WHERE (((tblEdit.DelTo)<> '" & sDelTo & "') AND ((tblEdit.Status) Not In (""Delivery"",""Collection"")) AND ((tblEdit.ShipmentDate)= [Forms]![frmPlanning]![cboWeek]));"


  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Debug.Print!!!

    And you would see your problem immediately. You have nearly 1000 posts you should know to examine your concatenated string by now.

    You need to put single quotes around your String list items, the double quotes will be screwing up the string.

    Code:
    Not In ('Delivery','Collection')
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try:
    Edited after reading Minty's post
    Code:
    strSQL3 = "SELECT tblEdit.DelTo, tblEdit.Town, tblEdit.PostCode, tblEdit.ItemType, tblEditItemNo, tblEdit.SONumber, tblEdit.TotalBox, tblEdit.TotalItem, tblEdit.Status, tblEdit.ShipmentDate " _        & "From tblEdit " _
            & "WHERE (((tblEdit.DelTo)<> '" & sDelTo & "') AND ((tblEdit.Status) Not In ('Delivery','Collection')) AND ((tblEdit.ShipmentDate)= #" & [Forms]![frmPlanning]![cboWeek] & "#));"
    If [Forms]![frmPlanning]![cboWeek] is a date I would expect it to need the use of # as a date delimiter.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Minty, sorry the "Delivery and Collection" quotes are added not via String, just typed in, I have always used double quotes when not a string and always worked, I have NOT Dimmed these 2 just added manually

    So i now know single quotes regardless of string or manually typed as per your reply and Bob's

    Thank you

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    No need to apologise, I still debug.print every string I create as I'm simply not clever enough to get them right first time every time unless it super simple.
    it's simple yet easy method to reduce your head scratching
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    @ Minty Can't believe you guy's on here don't get it right 1st time, you are all a genius as far as i am concerned, for what little spare time i do get which is very minimal, i do read some posts on a night time when settled down

    Sometimes i will do a 06:00 start so i am not side tracked to think of the logics, if it is beyond 07:30 when coding, i am a tennis ball, write some, come back to it 20 times in that day and 20 times lost where i am!!!!

    Thanks gain to you all

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Minty View Post
    No need to apologise, I still debug.print every string I create as I'm simply not clever enough to get them right first time every time unless it super simple.
    it's simple yet easy method to reduce your head scratching
    Will also take less time than posting here and waiting for a reply
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi WGM

    I don't know, Minty and Bob pointed out some errors fairly quickly, also me now knowing using single quotes which i always use as string delimiters but wasn't aware to use when typing the criteria, i always used double quotes

    I think was very quick replies as always

    Thank you

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 10-22-2021, 05:24 PM
  2. Replies: 3
    Last Post: 07-20-2020, 01:04 PM
  3. Replies: 5
    Last Post: 06-15-2018, 03:14 PM
  4. Replies: 2
    Last Post: 04-05-2015, 06:06 PM
  5. Replies: 5
    Last Post: 05-29-2012, 08:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums