Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    17

    Unhappy Unanswered: RunSQL Quotes Help

    I'm trying the run a delete qry that removes specific records from a table via a RunSQL statement below;

    The period and data tyes are determined by variables in a loop.

    The problem I have is with the Data Type (All prior to that works - i.e I have run the SQL line prior to the "And" which works.

    The variable Led(i) = "A" (Dim as a string)

    SQL = "DELETE Data_Tbl.*, Data_Tbl.Period, Data_Tbl.[Data Type] FROM Data_Tbl WHERE (((Data_Tbl.Period)= 200" & Yr & "/0" & m & ") And ((Data_Tbl.[Data Type])=" & Led(i) & "));"

    DoCmd.RunSQL SQL

    Running this brings up a parameter box with the letter A and requests a variable. I have tried various forms of ' and " between =" & Led(i) & " but can't seem to get the right combination can anyone help?

    Note: Hovering over the Led(i) before running the DoCmd line shows Led(i) = "A" which I thought I needed as it's defined as a string?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if Led(i) is string you need to add a couple of single-quotes:
    ....And ((Data_Tbl.[Data Type])='" & Led(i) & "'));"


    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    17

    Red face

    Thanks Izy.

    I had tried that but in a combination with the first section - I now realise that I also needed ' before the 200 and after before the ) in the first part (shown below)...... obviously that hadn't worked as I first thought!

    (((Data_Tbl.Period)= '200" & Yr & "/0" & m & "')

Posting Permissions

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