Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too Few Parameters, for an SQL String?? (Access 2K)

    Dear All

    I'm trying to use a simple SQL string in VBA to validate an option, here's the SQL and the line I get the error on. I'm thinking that maybe I need to define the strCurrentUser Variable? It is currently set to the CurrentUser from the Access Security log in.

    strCurrentUser = CurrentUser

    SQLInfo = "SELECT * FROM ConcessionNamesTbl WHERE ConcessionAwardee LIKE " & strCurrentUser
    Set RstUserInfo = DbS.OpenRecordset(SQLInfo)

    I've checked that the variable strCurrentUser has been assigned a value, I've modified the SQL string to contain the value and everything works fine then, so, how do I persuade Access that the variable is what it needs? I've also tried using the = symbol, switching to LIKE as I'd heard this might allow Access to match different types of variable more easily.

    Thanks

    Ian

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

    Re: Too Few Parameters, for an SQL String?? (Access 2K)

    strCurrentUser is a string; string values must be enclosed in quotes: "Admin". You can't just embed quotes within the quoted string SQLInfo, because that would confuse VBA. There are three possible workarounds:

    - Double the double quotes within the string
    - Use single quotes around the value
    - Concatenate with Chr(34)

    For this situation, I prefer the latter option:

    SQLInfo = "SELECT * FROM ConcessionNamesTbl WHERE ConcessionAwardee = " & Chr(34) & strCurrentUser & Chr(34)

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Few Parameters, for an SQL String?? (Access 2K)

    Hans

    Thanks, problem solved [img]/forums/images/smilies/smile.gif[/img]

    Now, while I'm learning something new...... I've looked up the Chr bit in the help file (I'd not come across them before) and it lists quite a few characters. Are these the things I can use to 'format' sections of VBA code that will be output as text?

    For example, I've got some Docmd.SendObject code and want to include a longish length of text in the body section. Could I use Chr 10 (a line feed characater) to make the body text look more presentable? Along with say Chr 13 (carriage return)?.

    I think you might have revealed yet another corner of the Pandora's Box that is Access [img]/forums/images/smilies/smile.gif[/img]

    Thanks again

    Ian

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

    Re: Too Few Parameters, for an SQL String?? (Access 2K)

    Hi Ian,

    Each 'character' has a numeric code, but in most cases you don't need to know it, you just use the character. But special characters, such as quotes, but also the 'tab' character and 'line feed', sometimes can't be entered directly. For some of those, there are built-in symbolic constants:

    <table border=1><td>Constant</td><td>Characters</td><td>vbTab</td><td>Chr(9)</td><td>vbLf</td><td>Chr(10)</td><td>vbCr</td><td>Chr(13)</td><td>vbCrLf</td><td>Chr(13)+Chr(10)</td><td>vbNewLine</td><td>idem</td></table>
    So if you want to insert a line break in a string, concatenate with vbCrLf:

    strBody = "Hello Ian," & vbCrLf & "Here is your file." & vbCrLf & "Cheers, Hans"

    If necessary, concatenate multiple vbCrLf's.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Few Parameters, for an SQL String?? (Access 2K)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> and the nearest I can find to an 'I worship at your feet' smilie <img src=/S/woops.gif border=0 alt=woops width=58 height=36> .

    Thanks Hans. You keep revealing those little tricks that make Access so much more friendly [thumbsup]

    Ian

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Too Few Parameters, for an SQL String?? (Access 2K)

    To make your like a little easier, you might want to create some public functions that you can stick in a module. Since the vb constants won't work within SQL, it gives you tools to use anywhere. For example, here are a couple I use:

    Public Function Qu( )
    'Returns the double quote character
    Qu = chr(34)
    End Function

    Public Function nl( )
    ' Returns a carriage return & linefeed
    nl = vbcrlf
    End Function
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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