Re: Why do I receive no value error message, when parameters do have v

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

"WoollyBear" <WoollyBear@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:06DFD1CE-4472-4347-8E4C-98961AF87879@xxxxxxxxxxxxxxxx
I have constructed the following piece of code, triggered from a command
button. Despite several attempts the following error message is displayed,
"No value given for one or more required parameters". I have debugged the
SELECT statement and used the Immediate window to verify that the two
parameter fields do contain values. Does anybody have any ideas please?

' Declare the ADO variable for the tbl_Referral_Data recordset
Dim rstbl_RefSEN As New ADODB.Recordset
' Declare the tbl_Referral_Data SQL statement variable
Dim RefSENSQLStmt As String
' Declare variable for closed referral indicator and set to "Y"
Dim ref_ClosedInd As String
ref_ClosedInd = "Y"
' Select the required data from tbl_Referral_Data and display the SEN data
on the form
RefSENSQLStmt = "SELECT * FROM tbl_Referral_Data WHERE
Child_ID = " & temp_ChildID & " AND Ref_Completed_Ind =" & ref_ClosedInd
rstbl_RefSEN.Open RefSENSQLStmt,
CurrentProject.Connection, adOpenDynamic, adLockReadOnly
If Not rstbl_RefSEN.EOF Then



I don't know what data type Child_ID is, but Ref_Completed_Ind appears to be a text field -- at least, you're trying to supply a text value for it. Therefore, that value has to be enclosed in quotes within the SQL statement. Try this:

RefSENSQLStmt = _
"SELECT * FROM tbl_Referral_Data WHERE Child_ID = " & _
temp_ChildID & _
" AND Ref_Completed_Ind ='" & ref_ClosedInd & "'"

It may not be obvious, but I've included single-quotes (') around the value from ref_ClosedInd. If Child_ID also happens to be a text field, you'd need to do something similar for temp_ChildID, too.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

.

Quantcast