Using a form to query between values

MCTampa

Board Regular
Joined
Apr 14, 2016
Messages
97
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
 
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.
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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?
 
Last edited:
Upvote 0
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])
 
Upvote 0
You could change the format of your text boxes to be General Number.

The change your criteria on the query grid - something like:
Code:
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.
 
Last edited:
Upvote 0
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".
 
Upvote 0
You could change the format of your text boxes to be General Number.

The change your criteria on the query grid - something like:
Code:
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!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top