Specific Criteria required

[NatKaa]

I have a table with one column (Gender/Age). Within this column there are 4 types of results: male child, female child, male adult, female adult. They can be use in various combinations, eg. [Male Child] or [Female Child, Male Child, Female Adult] all together.
What I would like to do is have a query select rows based on a specific data eg. all rows with [Male Child] in it. The data is coming from form so I tried 'Like "*[...]![...]![...]*" ' however this did not work.
How can I create a query which will look at each field and select the ones with the specific phrase in it.

 

[Jack Cowley]

You should make a separate field for each of the four possibilities. A field for Male Child, Male Adult, etc. Then you can make these Yes/No fields and check them to indicate that that particular field has been selected. I do not know how you plan to use the data and my suggestion of a Yes/No field may be wrong, but you do want to have a field for each of the 'categories'. A bit more detail on what you are trying to accomplish and I am sure that someone here will have a solution or suggestion for you.

 

[Pat Hartman]

I hate to disagree with you Jack but if we're going to redesign the table, it should be with two columns - Gender and AgeGroup.

The following syntax should work:

Where Gender = [Forms]![Yourform]![txtGender] AND AgeGroup = [Forms]![YourForm]![txtAgeGroup];

You do not need the LIKE operator since you are providing exact values for matching. If the criteria is coming from a subform, the reference is different:
[Forms]![YourMainForm]![YourSubForm].[Form]![YourSubFormField]

By the way - the original syntax you used was incorrect. You cannot include the form field reference inside the quotes or Access will treat it as a literal rather than substitute the field's value.

You said: Like "*[...]![...]![...]*"
It should be: Like "*" & [...]![...]![...] & "*"


[This message has been edited by Pat Hartman (edited 07-16-2001).]

 

[NatKaa]

I realise having all the information in one column is quite a hassle, however the reason for this is that the data is being imported from an excel sheet, 120 of them to be exact. Therefore I am trying to import them with out having to retype to much of the information.

The data are about books, on of the fields (Gender/Age) describes the different age and gender groups represented in the book. Hence the reason that all four may pop up in the same field.

Does this help shed any light on how to fix my problem?

 

[Pat Hartman]

Define a table that matches the columns of the spreadsheet you are importing. Go into design view and add two new columns. One for Age and one for gender. You can then append the spreadsheets to this existing table. Next, write an update query that takes the value of the combined field and splits it into two parts to update the discrete fields.

I also corrected your syntax in my previous post. That should fix your immediate problem.