Solved Missing Operator error in VBA Query (1 Viewer)

[bconner]

Below is the query I have built and it runs fine if I just filter Where MCID= txtMCID but fails when I add the OR Member_Id = txtMemberId.
txtMCID is a integer and txtMemberId is a string.

strSQLMember = "SELECT * FROM dbo_vw_RAA_Member_Information WHERE [MCID]=" & txtMCID & " or [Member_Id]='" & txtMemberId & "'"""
Set rs = db.OpenRecordset(strSQLMember, dbOpenDynaset, dbSeeChanges)

Any help is greatly appreciated.

 

[theDBguy]

Looks like maybe you have two extra quotes at the end there.

 

[bconner]

I tried the below but still get the same error.
The way the form is set up the user can enter either a MCID or Member_Id which means one field or the other can be null, does that matter?

strSQLMember = "SELECT * FROM dbo_vw_RAA_Member_Information WHERE [MCID]=" & txtMCID & " or [Member_Id]= '" & txtMemberId & "'"

 

[theDBguy]

I tried the below but still get the same error.
The way the form is set up the user can enter either a MCID or Member_Id which means one field or the other can be null, does that matter?

strSQLMember = "SELECT * FROM dbo_vw_RAA_Member_Information WHERE [MCID]=" & txtMCID & " or [Member_Id]= '" & txtMemberId & "'"

Do a Debug.Print strSQLMember and copy and paste the result from the Immediate Window into the Query Designer to get an idea of what is wrong with it.

 

[Pat Hartman]

It is more efficient to qualify form/report control names with Me. Plus, doing so gives you intellisense.

strSQLMember = "SELECT * FROM dbo_vw_RAA_Member_Information WHERE [MCID]=" & Me.txtMCID & " or [Member_Id]= '" & Me.txtMemberId & "'"

 

[bconner]

theDBguy, thank you for pointing me in the right direction I appreciate your help.

I edited the code to the below and it worked.

InttxtMCID = Nz(txtMCID, 0)
strtxtMemberId = Nz(txtMemberId, Null)

Set db = CurrentDb
'Fetch and populate the Member information on the form
strSQLMember = "SELECT * FROM dbo_vw_RAA_Member_Information WHERE [MCID]= " & InttxtMCID & " or [Member_Id]= '" & strtxtMemberId & "'"

 

[theDBguy]

theDBguy, thank you for pointing me in the right direction I appreciate your help.

I edited the code to the below and it worked.

InttxtMCID = Nz(txtMCID, 0)
strtxtMemberId = Nz(txtMemberId, Null)

Set db = CurrentDb
'Fetch and populate the Member information on the form
strSQLMember = "SELECT * FROM dbo_vw_RAA_Member_Information WHERE [MCID]= " & InttxtMCID & " or [Member_Id]= '" & strtxtMemberId & "'"

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.