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

 

[stumac]

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.

Ehhh the person who is asking for help cares!

If you going to give an answer that's totally irrelevant to them, why answer?

Of course it makes sense to me I have been writing SQL in access for 20 years... Doesn't mean the people asking questions here will understand it.

 

[MCTampa]

My apologies for not replying sooner, I was out of the office yesterday.
In terms of the question about an item being before the >= sign, I was referring to what I had typed in my query design grid and this appears to be working just fine when I have one argument (either greater than or equal to a week, or less than or equal to a week), but not when I try to use two weeks.

Yes, I had a typo when I wrote [StartingEnding]. The actual name was correct in the query and it was [EndingWeek]

The comment about using an integer comparison makes more sense to me than using a text comparison, I'm just not sure how to write that. Should I be using a different type of text box?

 

[xenou]

to make sure the values from the (unbound) textboxes are numbers/numeric then just use a function CLng() which converts text to long integers:


>=CLng([Forms]![Inputs]![StartingWeek])

 

[stumac]

You could change the format of your text boxes to be General Number.

The change your criteria on the query grid - something like:

Between NZ([Forms]![Inputs]![StartingWeek],[COLOR=#ff0000]0[/COLOR]) AND NZ(Forms]![Inputs]![EndingWeek],[COLOR=#ff0000]52[/COLOR])

Alternatively you could use combo boxes with the values 1-52 and default them to 1 and 52 respectively.

 

[MCTampa]

to make sure the values from the (unbound) textboxes are numbers/numeric then just use a function CLng() which converts text to long integers:


>=CLng([Forms]![Inputs]![StartingWeek])

Tried that and received the error "This expression is typed incorrectly or is too complex to be evaluated".

 

[MCTampa]

You could change the format of your text boxes to be General Number.

The change your criteria on the query grid - something like:

Between NZ([Forms]![Inputs]![StartingWeek],[COLOR=#ff0000]0[/COLOR]) AND NZ(Forms]![Inputs]![EndingWeek],[COLOR=#ff0000]52[/COLOR])

Alternatively you could use combo boxes with the values 1-52 and default them to 1 and 52 respectively.

BOOM! Combo boxes worked!
Thank you!