The astrick wildcard not working in a form to query

[Ellerd]

I have created a form (FrmCrit) that has three boxes in it. Two are combo boxes and the other is a text box. The first combo box, cboJobType, selects the type of job you want to sort by from the list. The second combo box, cboItemType, selects the item type from the list. The text box, search, searches for text from the item description field and has the wild card embedded in the query as you had shown. In a query (Query by Job & Group Type) I coded the criteria [Forms]![FrmCrit]![JobType] and etc. I have a command button box that opens another form (Search Output Form) made from the same query. This work fine except if I select an * in the first combo box (which also is the first column in the query). With a asterisk in all the boxes I should get everything from the table that the query is set up to find. When I select the * in the first box I get nothing but an empty row of data. If I select any item in the first box and asterisks in the others everything works fine. The other oddity is that on the same selection form (FrmCrit) I also have a command button to go to a report made from the same query as the Output form. On the report all the data is there when asterisks are used in all three boxes.
If I have selected * in the combo boxes with the form open and then open the query to the data it also shows all the data that I expect.
I'm using MS Access97
Do you have any suggestions?

 

[jdegeorge]

In this instance, "*" isn't a wildcard. What Access is doing is looking for records where the field tied to that CBO = the "*", which will never happen. You need to set the CBO values to a act as a filter, and if the "*" is selected in any one of the CBOs, ignore that CBO in building the fitler. Hope this helps.

Jim DeGeorge

 

[Ellerd]

I understand what you are saying but I'm not sure how to accomplish it. The 2nd form that is opened from the command button when I created it I selected on filter so it takes the selection from the first form or actually from the query. Is there some place to set what you are talking about?

 

[jdegeorge]

I re-read your post. Sounds like you're on the right track. First, the command button that opens the 2nd form should either HIDE the first form or just leave it open.

Then, the query that controls the 2nd form should have the following criteria for the related fields:

[forms]![FrmCrit]![cboJobType]

[forms]![FrmCrit]![cboItemType]

You were closed with "JobType" and "ItemType" but you need to refer to the value of the fields on the form not anything else, hence the "cbo" part.

I still don't understand what you're doing with an asterisk in the text box.

Jim DeGeorge

 

[Ellerd]

I missed typed the original message, the criteria I put in the query was as you suggested[Forms]![FrmCrit]![cboItemType] & [Forms]![FrmCrit]![cboJobType].
From your first response I felt that the query was taking the * literally and that was why it didn't work but when I opened the report instead of the form everything worked, I was confused.
I was using the * to search for all records. I don't need it in the text box (3rd criteria) because its line of criteria is: Like "*" & [Forms]![FrmCrit]![Search] & "*". With nothing filled it you still got everything because of the *'s in the like statement.
I tried using the % and it didn't work either. I don't understand why the * works in the second criteria but not the first, meaning if I select anything in the first criteria and a * in the second I get all the data matching the first criteria.
Is there other ways I should go about this?

 

[Ellerd]

I sent the db home where I have Access2000. You were correct none of the * querries work, it must of been some sort of querk in 97 that it works sometimes. I think I will have to make 3 differnet querries with no criteria in the field I wall "All" reported. thansks for the help/

 

[BillPower]

Hi,

I have a slightly different method of dealing with "wildcards" in queries which is briefly explained in thread181-620597

Might help!

Bill

 

[Ellerd]

Bill I went to Thread181-620597 and found the buttons file but I couldn't down load it as it was not found. I was able to down load some of the other files that looked interesting.

 

[BillPower]

Hi Ellerd,

Sorry about that and thanks for letting me know. I made a code change last night and seem to have somehow deleted the link to the new DB.

You should be able to download Buttons.zip now.

Let me know how you get on.

Bill

 

[Ellerd]

Bill I was able to down load it and your Find screen is what I'm trying to do, when field are blank all data is shown. I couldn't get to the code to see have it was done.

Thanks

Denny

 

[BillPower]

Hi Ellerd,

While in the "SwitchBoard" hit the F11 key to view the DB Window.

Right Click on any of the Forms and select Design View.

While in Design View, to view any code select from the Menu, View>> Code. Or Press Alt + F11 together.

Bill

 

[Ellerd]

Thanks Bill

I was able to get mine working. I used your AfterUpdate code on the cboBoxes. And changed the open form command button so it didn't apply any filters (this probably was the problem). With my three criteria field it opens a form that has 8 other field shown based on those selections.

Again thanks for the help

Denny