Include range in query

[kirkm]

I'd like my select query to include Field1 where it's 1960 to 1961 and Field2 is any number 15 to19.
But also work if the Year is only 1960 and my number only 16.
Been playing around with > and < and having some success but maybe there's an Access method to include a number in a certain range?

 

[arnelgp]

Depands on what Output you like.
Exact match, you use equal sign (=) as operator.
Between range, use >= or <=.

 

[theDBguy]

I usually just use Between/And for a value range.

 

[kirkm]

I am using <= and>=. It's just getting a bit unweildy.
I'll google for a Between/And example.
Thanks.

 

[plog]

But also work if the Year is only 1960 and my number only 16.

Why did you write that? I mean you explicitly stated that 1 case like it was in addition to the general criteria sentence you already wrote, but it's not. As written that specific case falls within the criteria you laid out.

Not criticizing, just wondering if you communicated your criteria correctly to us so we don't miss anything.

 

[kirkm]

Is there a limit to how many BETWEENS you can have ?
I'm running into trouble with

SELECT Year([Date Peaked]) AS Expr1, tblMain4.Prefix, tblMain4.High, tblMain4.SheetSort
FROM tblMain4
WHERE (((Year([Date Peaked])) Between 1960 And 1960) AND ((tblMain4.SheetSort) Between 2 And 3) AND ((Val([High])) Between 14 And 16));

In design mode Data type mismatch in criteria expression.
If I remove the Between 2 And 3 and use just 2 (or just 3) it is ok. (But I need both)
You can open a recordset and loop through until .MoveNext on last record. Then:
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
That also happens with .MoveLast
Any thoughts ?

 

[kirkm]

You're right plog, I was probably over thinking that.

 

[kirkm]

I think I've solved it. Needed Val(nz([High]))

 

[plog]

There's no limit in betweens, but there is a 255 character limit on the query itself.

Also tblMain4 is a red flag name. Do you have 3 other tblMains?