query using a form

[Shepps]

Currently i have a table with 4 types of training, and a date that the member of staff needs to go on re-training.

CPR
Fire Lectures
Manual Handling
Blood Protocol

im going to use a combo box which will contain those items, and a text box will be used to input a date. I need to make my query select which field to search depending on which training is selected.

Ne ideas?

 

[dcx693]

Base your query on the two fields on the form as criteria. Reference them like:
Forms!myformname!cboTrainingType
and
Forms!myformname!txtDate

 

[Shepps]

im not sure i understand what your saying. My query for just text string looks like this

Like "*" & [Forms]![search]![Text14] & "*"

how wud i adapt it to work for the combo + text idea?

 

[dcx693]

That's your critieria for the text field. Just add a column to your query for the training and use an expression like: Forms![search]!cboTrainingType, replacing cboTrainingType with the actual name of the combo box on your search form holding the choice for the training type.

 

[Shepps]

That's your critieria for the text field. Just add a column to your query for the training and use an expression like: Forms![search]!cboTrainingType, replacing cboTrainingType with the actual name of the combo box on your search form holding the choice for the training type.

right ok, in the query where it says field, i have a blank column with [Forms]![search]![List19] in. then in criteria i have Like "*" & [Forms]![search]![Text2] & "*" . im guessing its the like thats messing it up, but i dont want to loose the ability to ignore the field if its left blank, which i think the Like has something to do with..

when i select CPR and put a date in (copied from the table, so it should fine it) It brings up all the records in the table, i dont select anything in the combo box, then it comes up blank.

Whats going wrong?

 

[Shepps]

Been fiddling with this for 2 hours this morning, still cant get it working. im getting desperate now

Heres a link to my database, its too large to attatch.

http://shepz.pwp.blueyonder.co.uk/staffdatabase.rar

 

[dcx693]

right ok, in the query where it says field, i have a blank column with [Forms]![search]![List19] in. then in criteria i have Like "*" & [Forms]![search]![Text2] & "*" .

No, no, no, no. If I understand you correctly, it sounds like you were cutting class when they were teaching how to design queries. When you design a select query, you need to select a field in the "Field:" line of a column in the query grid that corresponds to a field from one of your query tables. OK, that sounded a bit complex, but basically, you add tables to your query and drag them to the grid. The name of the field that you've dragged to the query grid will show up in the "Field:" line.

Expressions like [Forms]![search]![List19] are used as Critieria. As such, they need to appear in the "Criteria" line of the query grid.

The field is what you want to see, the critieria limits the stuff that you see coming from the "field".

 

[Shepps]

i know how to design a query, just not one like this

i was trying to get [Forms]![search]![List19] so that the field wud b what ever the choice in the listbox was...

 

[o1110010]

dcx693 hit it on the head.

Expressions like [Forms]![search]![List19] are used as Critieria. As such, they need to appear in the "Criteria" line of the query grid.

if you put "Like [Forms]![search]![List19]" in the related field's criteria it should work.