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

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

Hans

Thanks, problem solved

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

Thanks again

Ian

Reply | Quote

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:

Constant	Characters
vbTab	Chr(9)
vbLf	Chr(10)
vbCr	Chr(13)
vbCrLf	Chr(13)+Chr(10)
vbNewLine	idem

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.

Reply | Quote

and the nearest I can find to an ‘I worship at your feet’ smilie .

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

Ian

Reply | Quote

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

Reply | Quote

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

Reply | Quote

and the nearest I can find to an ‘I worship at your feet’ smilie .

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

Ian

Reply | Quote

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:

Constant	Characters
vbTab	Chr(9)
vbLf	Chr(10)
vbCr	Chr(13)
vbCrLf	Chr(13)+Chr(10)
vbNewLine	idem

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.

Reply | Quote

Hans

Thanks, problem solved

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

Thanks again

Ian

Reply | Quote

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)

Reply | Quote