Input dialog box criteria with wildcard added

[NitherWise]

I have a Department field that I'm using the Mid function to start with the 4th char to filter the results. I need the user to input a three character dept number which would be char 4,5, and 6. However, sometimes there is a char in the 7th position. Is there a way to add a wild card to the input criteria?

Example:

There are department numbers 332, 332A, 332B,
and 332C.

I need to pull all four when the user enters 332 in the input dialog box prompt. I've tried several ways using the * wild card but I either get no results or an error message. Is there another way I could get the results I want?

Thanks

 

[Parra]

If this is on a form, why not just create a drop down box that has the list of departments available?

Is the user inputing this from a form, or a query?

 

[NitherWise]

The dialog box pops up when the query is executed.

I have it in the criteria row as [Enter Dept Number:]

 

[Parra]

Its my understanding you can't use wildcards in that manner. I tried and it did'nt work.

Can you create a form. With a drop down box that has the deparment numbers. The department chosen will populate the criteria and give you the result you are looking for.

 

[NitherWise]

Maybe at a later date I could create a form. I was trying to do something quick because there are over 2000 department numbers and I'm just using a few as a test group.

Thanks for your help.

 

[Parra]

OK, let me think for a moment, I have to run to a meeting but hopefully I get you some info when I return.

2000 departments are a lot for a drop down box.

 

[NitherWise]

I found out how to get it to work. Using this as the criteria:

Like [Enter Dept Number:] & "*" and the field as:

Dept:Mid([Dept],4,4)

Thanks for your help.

 

[Parra]

Hey Wise I got this from Help.

Wildcard characters are meant to be used with text data types, although you can sometimes use them successfully with other data types.

Because if you use the wildcard in the design criteria view of the query it should pick it up. Try doing it from there instead of from the pop up window and see if it works.