Using LIKE SQL syntax with a Form Value

[TZyk2003]

Hey guys! Great board!

I have a form with only have one field that loads a query based off that one field. Simple.

However, here is what I have:
SELECT BTN.BTN, BTN.HIERARCHY_ID
FROM Customer
WHERE BTN.BTN LIKE '[Forms]![BTN Search]![Text0]';

Now, in the form I want to be able to use a %. I.E. '12355%'. However, when the SQL command runs nothing is returned since it sees [Forms]![BTN Search]![Text0] as a text string instead of a variable. Make sense? A BTN is like '123457627' NOT '[Forms]![BTN Search]![Text0]'. So I need to get access to put a value in for that instead of taking it as a text string.

Anyone know the syntax to fix this??

THANKS!!

-Tim

 

[Tranman]

Tim,
Why not use the String of BTN?

Where trim(str(BTN.BTN)) Like '" & [Forms]![BTN Search]![Text0].text & "*';"

Also in Access, I think you'll need to use * instead of % unless you're passing the query to a SQL or Oracle database.

Tranman

The string function has a nasty habit of leaving a blank space (for the sign) in the first character position of its result.

 

[TZyk2003]

THANKS SO MUCH for the reply. However, it's not quite working.

It says "Characters Found At End of SQL Statement" since there is a " after the semi-colon. If I take out the extra " after the semi-colon then it says Data Type Mismatch.

So I tried this:

Where BTN.BTN Like '" & [Forms]![BTN Search]![Text0].text & "*';

No results were returned. I also tried it with %.

CLARIFICATION: The user is going to put the % in with the BTN. Like "123453%" so I don't need to concatanate it in the SQL expression.

Any help on this syntax would be wonderful! THANKS!

-Tim

 

[Norris68]

Put your closing double-quote before the semi-colon. In fact, the semi-colon is completely superfluous (and is not part of any ANSI SQL spec) - Access will work perfectly well without it.

If you are using an ADP to a SQL/MDSE backend, use '%' as a wildcard. In pure Access, as Tranman says, use a '*'.

 

[TZyk2003]

Hmmm...no luck. Access still returns no results. It doesn't even ask me to insert a value for [Forms]![BTN Search]![Text0].text when I run the SQL query in Access. I took out the .text and it still doesn't prompt me for a value.

Any thoughts?? THANKS!

-Tim

 

[Tranman]

Tim,
The term: [Forms]![BTN Search]![Text0] with or without the .text (which is the default property, and may be omitted), refers to a field on a form, which may or may not be open at the time you run the query. In any case, the query will not ask you for input when you use the contents of a field on a form as criteria.

If you want the query to ask you for a value each time it's run, you need to say something like: Where BTN.BTN Like [Enter a BTN Value]

Also, I go back to what I said to start with--the percent sign is a masking character in SQL Server and Oracle databases (plus many others), but not in Access. In Access, if you want to select every record that starts with the letter P, you have to say "like 'P*'". The single quotes inside the double quotes tell the query that you are looking for a string literal instead of a number. If the user enters a percent sign at the end of the field, it will tell the database to return all rows where BTN.BTN contains a value similar to "12345%" while I suspect that what you want to return is any row where BTN.BTN contains values like 12345??? where the ?'s can be any character.

At any rate, it sounds like we're getting close to the solution. Hang in there, and we'll sort it out. It's a shame there is no facility for talking to each other over this site. Two minutes on the phone would probably clear up everything.

Tranman

 

[TZyk2003]

Nevermind guys...figured it out.

FYI, it was like this:

WHERE BTN.BTN Like '' & [Forms]![BTN Search]![Text0] & '';

Thanks!

-Tim