Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to rewrite in sql (Access 2000)

    In the ControlSource of a control i have the following statement which works
    =[grade] & " : " & [branch1] & "" & IIf([size]<19;" cartons ";" drums ")


    I am trying now to rewrite it as follows

    Dim StrSQL AS String

    StrSQL = " [grade] & " : " & [branch1] & "" & IIf([size]<19;" cartons ";" drums ") "



    But Acces does accept it, and i have the line red highlighted.

    Could somebody show my where my mistake lies? I have heard that sometimes i must write comma instead of semicolon
    Is it true?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to rewrite in sql (Access 2000)

    Try changing the literal double-quotes to single quotes. Access can't tell the difference between the ones that you want to appear in the control source, and the ones that bound the expression.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to rewrite in sql (Access 2000)

    i regret changing to single quotes brought no effect.Could i receive some fruther help?

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: how to rewrite in sql (Access 2000)

    .. As already stated, the strsql expression defined as a string will evaluate the first to "". However, could you explain what it is you are trying to do with the strSql expression. If you plan on running a docmd.runsql strSql then your expression won't work. You would need to re-write your entire expression as an Select... From... Where to get the strSql to evaluate.

    Please explain what it is you are trying to do with the strSql.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to rewrite in sql (Access 2000)

    Did you change all the quote marks? You should just change the inners to singles and leave the outermost doubles as they are at each end.
    Peter

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to rewrite in sql (Access 2000)

    Is it this that you what you want ?
    StrSQL = [grade] " : " & [branch1] & IIf([size]<19," cartons "," drums ")
    Francois

  7. #7
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to rewrite in sql (Access 2000)

    try this:

    strSQL = "[grade] & "" : "" & [branch1] & """" & IIf([size]<19;"" cartons "";"" drums "") "

    value of strSQL will be:
    <pre>[grade] & " : " & [branch1] & "" & IIf([size]<19;" cartons ";" drums ") </pre>


    If you want to include a " INSIDE of a string, you need to double it up "".

    What exactly are you trying to do? This will still NOT be a valid SQL string to use for recordset operations.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

Posting Permissions

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