prompting query * in value 2

[mdlaugh1]

I have a prompt query shown below which runs fine for most values. My problem is that some values in the field are of the format *ADMIN, *ELECT, etc... When I enter one of these "*...." values I return NO records. How do I code the prompt or instruct the users to enter the value?

Like UCase([Enter the CLASS or * for all] & "*")

thanks!

 

[Remou]

How about using:
[tt]Like "*" & UCase([Enter the CLASS or * for all] & "*")[/tt]
And asking your users to enter just ADMIN no *.
Alternatively, strip the * in the query from the field to be searched:
[tt]SELECT Class.ID, Class.Class
FROM Class
WHERE (((Replace([Class],"* ","")) Like UCase([Enter the CLASS or * for all] & "*")));[/tt]

 

[mdlaugh1]

Hi Remou,

On your first suggestion, not all values in the field begin with an *, so appending the * to Class would fail for the non-astrik values I think??.
I am going to try the 2nd idea of stripping off the *. thanks!

 

[Remou]

I tested both ideas on some sample data and both seemed to work for me. The * at the beginning does not mean *, it is a wildcard:
Like AnyTextADMIN
Just the same as * at the end.

 

[Lilliabeth]

appending the * to Class would fail for the non-astrik values I think??.

You are mistaken.

A * can stand for one character, multiple characters, or NO CHARACTERS. For example: *ADMIN will return ADMIN.

 

[mdlaugh1]

From the Criteria Line in the Query Design window (I mis-typed before, field is "category", not "class"):

Now I get two prompts related to Category... 1 prompt for CATEGORY and a 2nd for "Enter the CATEGORY of * for All"
Here's the code i entered into the Criteria field:

(((Replace([CATEGORY],"* ","")) Like UCase([Enter the CATEGORY or * for all] & "*")))

 

[Remou]

Have you double-checked the spelling of the Category field in your query?

 

[mdlaugh1]

Thank you both! and, Yes, Remou... the reason it failed that last time was because I used the "alias" name of category, not the field name work_category... oops!

but it's working now using the Replace.... code. thanks again, Stars to you both!