Using a form to query between values

[MCTampa]

Hello,

I have a table of data where one column is called week number (from 1 to 52).
I created form with "Starting Week" and "Ending Week" as individual unbound text boxes.
I simply want the user to be able to filter their data between the Starting and Ending weeks.

When I use the following query criteria, it works:

>=[Forms]![Inputs]![StartingWeek].[Text]

and I get everything greater than the starting week.
Same deal for anything less than the ending week.

However, I cannot get the following to work:

>=[Forms]![Inputs]![StartingWeek].[Text] and <=[Forms]![Inputs]![StartingEnding].[Text]

Using Between.....And doesn't work either.

Any help would be appreciated.

Mike

 

[Micron]

Probably doesn't matter in this case (at least not yet) but are you sure you want to use the Text property of the control? To help any further, I think we need to see what the expression is being compare to. You've left that off at the beginning (what comes before >=) which can be just an oversight. However, it cannot be omitted from the right side of the expression.

SOMETHING >=[Forms]![Inputs]![StartingWeek] And SOMETHING <=[Forms]![Inputs]![StartingEnding].[Text]
You cannot expect Access to interpret criteria the way your mind can/does. Each segment for AND or OR or BETWEEN has to be able to stand on its own. If you had

SOMETHING >=[Forms]![Inputs]![StartingWeek] And <=[Forms]![Inputs]![StartingEnding].[Text] then the rightmost part

<=[Forms]![Inputs]![StartingEnding].[Text]
makes no sense (at least to Access).

 

[stumac]

<=[Forms]![Inputs]![StartingEnding].[Text]
makes no sense (at least to Access).

It does make sense to Access - in the criteria of a query.



Is the form control called [StartingEnding] - you note above that it is called "Ending Week" - could it be you have just used the wrong name?

 

[xenou]

also be careful of your unbound textboxes which might hold text values. If they are, then a text comparison of for instance 2 and 13 would be no results:

i.e.
between 2 and 13 --> (Text Comparison): no results because 13 is less than 2.
between 2 and 13 --> (Integer Comparison): fine, returns values from 2 to 13.

For what it's worth, you can use Access inbuilt filters by right-clicking on bound fields. There might be a between filter for numbers. Seems like nobody uses this kind of filtering but it's much easier than writing code to do filtering.

 

[Micron]

It does make sense to Access - in the criteria of a query.

Can you show an example of how it can work when it is on the right side of the AND operator, and show the entire expression, including both sides?

 

[stumac]

The Op says: When I use the following query criteria

There was no mention of Expression until your post.

An example would be:

SELECT *
FROM MyTable
WHERE ([My Value]>=[Forms]![MyForm]![Number1] And [My Value]<=[Forms]![MyForm]![Number2])

 

[stumac]

To clarify - this is based on a query in Design view - not SQL:

 

[Micron]

The Op says: When I use the following query criteria

There was no mention of Expression until your post.

When you quoted me in post 3 you left off the most critical part (in bold)

then the rightmost part
<=[Forms]![Inputs]![StartingEnding].[Text]
makes no sense (at least to Access).

Expression or sql statement, it doesn't matter which I referred to, the reference has to be on both sides of the AND/OR/BETWEEN operator. Your example supports what I originally said:
([My Value]>=[Forms]![MyForm]![Number1] And [My Value]<=[Forms]![MyForm]![Number2])
Take away the red one, which is representative of how the original statement was posted by the OP and then what? I expect you'd agree that it won't work anymore. So I still don't see how your statement "It does make sense to Access - in the criteria of a query." applies to my incompletely quoted statement.

 

[stumac]

The question is clearly about the criteria section in query design screen - your response is clearly not.

Look at the image in post #7 you will see my example does not support what your continuing to say is incorrect. Your comments are misleading - the issue has nothing to do with what you have responded, if the form control names are correct then >=[Forms]![Inputs]![StartingWeek].[Text] and <=[Forms]![Inputs]![StartingEnding].[Text] will work!

 

[Micron]

Whatever. Who cares what's in the query design grid? It's a gui representation of what's behind in the sql statement. Take a look there and see if it makes sense to you. If not, so be it.
Signing off on this thread.