Problems with query builder in VB, error return

[Sevyt]

I was quickly creating a program to search through a access database.
The idea is to filter the names and figure out the file location in the physical archive.
Though my query wizard is being rather annoying right now.

I did not use the manual connection like a lot of people do, but just used the add data sources option.
The access database fields are all text, except the id field.

Hopefully somone can give me a wakeup call and tell me what is going on because i am confused.

Img of query builder and error when trying to finish.
Click Here

[Reverend Jim]

Could you possibly just post the table structure and tell us what you are trying to retrieve?

[Sevyt]

The table structure from the accdb is like this

Click Here

Sorry got access in dutch on this PC.
The 4 in the red box are supposed to be used to filter.(name, firstname, familynumber, Filing cabinet)
But in the first post i only had the Name, since i was trying to figure out why i was getting the error.

[Eternal Newbie]

Not an expert (yes, a newbie!), but, are you using Access database? Then 'LIKE @' was wrong, use 'LIKE yourString' instead will do the trick (or not?).

[Sevyt]

Yes thats from a access database.
Like Name(which i used in the above post) would not really do alot, except just show everything in the database that has data in the Name column.
But since that always has one it would just show everything.
When i use Like @Name (have to put @ prefix characters) then you can filter.
For example if do Ae% it would show every name starting with Ae in the Name column.

The filter basicaly works when testing in the query builder, but when saving it you get the error i showed in the screenshot.
Going back to the query after ignoring the error will show the error again but with a empty query builder screen.

[Eternal Newbie]

Yuck! I did meant:

LIKE *Name*

Or:

LIKE *@Name*

But the code was auto-corrected later :P
If I'm still wrong, I might need a sample and trying doing this my own :D

[Sevyt]

Never used it like that, but does not work the way i tried it.
Though you might do a few things different.

[Reverend Jim]

Tell me in English (not code or pictures) what you want your query to do.

[Sevyt]

The query is supposed to filter the database.
It is supposed to filter on Name, Firstname, FamilyNumber, Filling cabinet.
Got a textbox for each of those fields and of course a button to perform the filter, if one or all textboxes have text.

I have done it before like that for something else i once made.
But for whatever reason the @ in "Like @Name" is no longer accepted.
Even though the query works when tested in the builder.

[Reverend Jim]

It is supposed to filter on Name, Firstname, FamilyNumber, Filling cabinet.

A wildcard in access is "?" to match a single character or "*" to match any sequence of characters. An example query using a wildcard on the name would be

SELECT * FROM mytable 
 WHERE Name LIKE 'M*'

which would select all names starting with M. Because you want to use multiple fields it would look like

Dim qry As String = "SELECT * FROM mytable " _
                  & " WHERE Name          LIKE '" & txtName.text & "'" _
                  & "   AND FirstName     LIKE '" & txtFirstName.Text & "'" _
                  & "   AND FamilyNumber  LIKE '" & txtFamilyNumber.Text & "'" _
                  & "   AND FilingCabinet LIKE '" & txtFilingCabinet.Text & "'"

I suggest using parameterized queries if you are going to allow the user to specify the search strings. See here for how to do that.

[Sevyt]

alright thanks Rev, gonna have a look through all of that.