More than one value needed

  • Thread starter Thread starter Gail
  • Start date Start date
G

Gail

I need to pull more than one answer from a field in a query.
I have tried: , : ; and &
For an example- tax types: 1040, 1065, 1120, 1041
I want to pull all but the 1040.
Any ideas on how I do that?
 
I need to pull more than one answer from a field in a query.
I have tried: , : ; and &
For an example- tax types: 1040, 1065, 1120, 1041
I want to pull all but the 1040.
Any ideas on how I do that?
Click to expand...


TaxType is a Number datatype?
Where YourTable.TaxType in (1065,1120,1041)

Of it there are even more tax types and you wish all except 1040:
Where YouTable.TaxType <> 1040

If the datatype is Text, surround each value with quotes, i.e.
In ("1065","1120","1041")
 
Gail

I can't tell if the data includes a field that holds multiple values within
a single record, or multiple records, each one with a single value in that
field.

If the former, it's time to stop and redesign the table! One fact - one
field is a basic rule for good database design.

If the latter, you can add selection criteria into a query that excludes
values. For example, with the example you gave, I'd add:

Not "1040"

as a selection criterion (if the field were text ... if the field were
"number", I'd leave off the quotes).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I want to pull all but the 1040.
In design view of your select query scroll to the right of the grid to a
blank Field row column and enter this --
[Enter criteria separated by spaces]

Then in the Criteria row below enter this --
Like "*" & [tax types] & "*"
assuming your field is named 'tax types'.
 
Fred,
Under Tax Type Combo box - just numbers 1040 1065 1120 etc.
So in the criteria section, I would typr "1065","1120" etc?
Thank you so much, I knew it would be easy to do but I could not put the
question into words for the help menu
 
Fred,
Under Tax Type Combo box - just numbers 1040 1065 1120 etc.
So in the criteria section, I would typr "1065","1120" etc?
Thank you so much, I knew it would be easy to do but I could not put the
question into words for the help menu
Click to expand...

No......
As criteria in the TaxType column you would write:
In (1065,1120,1041)

The word In and the parenthesis are important here.
And if the values are numbers you do not need the quotes; you need
them only if the values are Text datatype.
By Text I mean the underlying datatype of the TaxType field. The
fields datatype could be Text or Number, even though each entry was a
number. Look in your table properties. It will say Text or Number.
 
Back
Top