user3216034 February 2016

Using a variable in where clause

Using the following code:

Dim RCMSql As String
    RCMSql = "SELECT [Range Card Master Mailer].Master_ID," & _
    "[Range Card Master Mailer].MaxOfDate_of_Transaction," & _
    "[Range Card Master Mailer].FirstName," & _
    "[Range Card Master Mailer].LastName," & _
    "[Range Card Master Mailer].Email_Address," & _
    "[Range Card Master Mailer].Address_Line_1," & _
    "[Range Card Master Mailer].Phone_Number_1," & _
    "[Range Card Master Mailer].Phone_Number_2," & _
    "[Range Card Master Mailer].Date_Sent," & _
    "[Range Card Master Mailer].RedeemFlag " & _
    "FROM [Range Card Master Mailer] " & _
    "WHERE ((([Range Card Master Mailer].Master_ID) = '002366'))"
     RCMRs.Open RCMSql  

This execute just fine, but if I change the where clause to use the value in a text box on the open form I get an error "No value give for one or more perameters"

"WHERE ((([Range Card Master Mailer].Master_ID) = Me!scanTxtBox))"

I have tried at least a dozen variations, but can't get it to works

Thanks

jpl

Answers


Ralph February 2016

I believe this is what you're looking for:

Dim RCMRs As ADODB.Recordset
Dim RCMSql As String
RCMSql = "SELECT [Range Card Master Mailer].Master_ID," & _
    "[Range Card Master Mailer].MaxOfDate_of_Transaction," & _
    "[Range Card Master Mailer].FirstName," & _
    "[Range Card Master Mailer].LastName," & _
    "[Range Card Master Mailer].Email_Address," & _
    "[Range Card Master Mailer].Address_Line_1," & _
    "[Range Card Master Mailer].Phone_Number_1," & _
    "[Range Card Master Mailer].Phone_Number_2," & _
    "[Range Card Master Mailer].Date_Sent," & _
    "[Range Card Master Mailer].RedeemFlag " & _
    "FROM [Range Card Master Mailer] " & _
    "WHERE ((([Range Card Master Mailer].Master_ID) = '" & _
    Replace(Me!scanTxtBox, "'", "''") & "'))"
RCMRs.Open RCMSql


Cisco February 2016

In your code define a local variable as string

If the first code you posted runs, Master_ID is defined as string, so, define a local variable with

Dim localScanTxtBox as string

and set it with (if in your scanTxtBox you have a 6-char string):

localScanTxtBox = Me!scanTxtBox

or, convert it to a 6-digit string with:

localScanTxtBox = format$(Me!scanTxtBox,"000000")

Then write your where statement as:

WHERE ((([Range Card Master Mailer].Master_ID) = '" & localscanTxtBox & "'))

Hope this helps

Post Status

Asked in February 2016
Viewed 1,933 times
Voted 8
Answered 2 times

Search




Leave an answer