No Value Given for one or more required parameters

[OD]

Hi
I'm using an unbound field on my form called txtPRNum and taking the input
and doing a query against a table with a list of items.

"INSERT INTO tblPRNumbers ( PRNum ) VALUES (" & txtPRNum & ")"

This query works correctly with numeric info (even though it's a text box)
but throws the "No value given for one or more required parameters" when a
alpha or alphanumeric input is entered.

WOuld someone explain why this is/
Thanks

 

[OD]

Thanks Dale
It is a text field so how would i "wrap" the value? Haven't been able to
figure that out in the query correctly yet

Thanks again

 

[John Spencer MVP]

Because you need to add the text delimiters into the string. You can do that
in several ways.

Insert two quote marks for every single quote mark you need in the string
"INSERT INTO tblPRNumbers ( PRNum ) VALUES (""" & txtPRNum & """)"

Use the Chr function to add the quote marks.
"INSERT INTO tblPRNumbers ( PRNum ) VALUES (" & Chr(34) & txtPRNum & cChr(34)
& ")"

Use an apostrophe as the text delimiter. This works as long as you aren't
trying to insert data like "O'Connell"
"INSERT INTO tblPRNumbers ( PRNum ) VALUES ('" & txtPRNum & "')"

If you are trying to insert a date then you will need to use # as the
delimiter and format the date into yyyy-mm-dd format if you are not using the
US standard of mm/dd/yyyy.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[OD]

Thank you

I figured it out and it now works

Thanks again

 

[Marshall Barton]

I'm using an unbound field on my form called txtPRNum and taking the input
and doing a query against a table with a list of items.

"INSERT INTO tblPRNumbers ( PRNum ) VALUES (" & txtPRNum & ")"

This query works correctly with numeric info (even though it's a text box)
but throws the "No value given for one or more required parameters" when a
alpha or alphanumeric input is entered.

Text values need to be surrounded by quotes in the final SQL
statement. I.e. it should end up lookomg like
INSERT INTO tblPRNumbers (PRNum) VALUES ("X123")

The code to generate that result could be like:

strSQL = "INSERT INTO tblPRNumbers (PRNum) " _
& "VALUES (""" & txtPRNum & """)"