Simple SELECT sql query, Enter Parameter Value error

TheVoiceInYourHead

New member
Local time
Today, 13:19
Joined
Mar 11, 2013
Messages
4
Hey guys I am trying to do a pretty simple SELECT query but I keep getting the Enter Parameter Value pop up box, where am I going wrong in my statement?

Private Sub btnSearchMembers_Click()
sql = "SELECT * FROM Member WHERE MemberID=" & txtSearchMembers.Value & " or Surname='" & txtSearchMembers.Value & "' or Forenames='" & txtSearchMembers.Value & "';"
Me.Combo30.RowSource = sql
Me.Combo30.Requery
End Sub

I've checked all the table column names and they are correct, MemberID is an integer hence no single quotation marks.

Anyhelp would be great cheers!
 
Hey thanks for the Reply,

What value is the parameter prompt looking for? That will tell you what Access can't find.

If I search the table for someone with the Surname Potter, by typing Potter into the txtSearchMembers textbox then the Enter Parameter Value box will look like this: (see attachment)

The Immediate window shows this
SELECT * FROM Member WHERE MemberID=Potter or Surname='Potter' or Forenames='Potter';

Is this an issue of trying to search for both integers and strings and it gets held up because MemberID=Potter should be MemberID='Potter' but that is not how an integer works?
 

Attachments

  • Useful.png
    Useful.png
    12.3 KB · Views: 110
You said the member ID is numeric, but you have the text "Potter" as the value (... WHERE MemberID=Potter or...). Presumably that should point to some other text/combo box for the appropriate numeric value.
 
What I am trying to achieve is having one box which will allow the user to search by either MemberID, Forenames or Surname all in the same textbox, I was hoping that if text was used WHERE MemberID=Potter would be ignored at it would search for the other two?

For example a user might not put Potter but 2 and it would find the user with the MemberID 2.

Must I have a second text box for MemberID search?

Thank you for the help so far pbaldy!
 
Access isn't going to ignore the input like that. If you want a single textbox, and since you're in code anyway, I'd do something like:

Code:
If IsNumeric(txtSearchMembers) Then
  sql = "SELECT * FROM Member WHERE MemberID=" & txtSearchMembers.Value
Else
  sql = "SELECT * FROM Member WHERE Surname='" & txtSearchMembers.Value & "' or Forenames='" & txtSearchMembers.Value & "'"
End If
 
Happy to help, and welcome to the site by the way!
 

Users who are viewing this thread

Back
Top Bottom