How to convert Text values in Text Boxes to Integers and use them as parameters?

[lookingforK]

Hi,

I am using Query Design with MS Access 2007 to create a query for selecting/extracting a data set.

I make 2 text boxes named "Week From" & "Week To" on a form so that when I enter 2 numbers (but with Text type) in the text boxes and click a button running the query, I would get a selected data set for the specific period.

For example, when I enter "1" in "Week From" and 8 in "Week To", I should get the data set about the period from Week 1 to Week 8.

The problem is:
The data type of the corresponding fields in the table for "Week From" & "Week To" is Integer. So, I need to convert the 2 text boxes from Text to Integer or Number.

I try to use the function Cint (please see below) in the query, but get error message.
* <=CInt([Forms]![Extract Data]![Week To])
* >=CInt([Forms]![Extract Data]![Week From])

Why? How to convert the Text values in Text Boxes and use them as parameters in the query? :banghead:


Thank you in advance.

 

[John Big Booty]

Have a look at the CInt() function or one of the other Type Conversion functions available here

 

[John Big Booty]

Sorry my bad missed that bit about CInt()

 

[John Big Booty]

What is the error message you are receiving?

 

[boblarson]

Your criteria should all be on one line (and you should not need to use CINT at all)

>=[Forms]![Extract Data]![Week From] And <=[Forms]![Extract Data]![Week To]

 

[lookingforK]

Thank you.

I tried. But they could not work. I even tried Forms]![Extract Data]![Week From].[text] or Forms]![Extract Data]![Week From].[value]. Can't work too.

Looks like only the parameters from combo boxes can work.

 

[boblarson]

Looks like only the parameters from combo boxes can work.

And that would be an INCORRECT assumption. I use text boxes all the time and have been doing so for 15 years.

So, how about posting a copy of the database with fake data so we can play with it? I can almost guarantee that there is something we don't know that you aren't telling us but that you don't know you should be telling us (quite a frequent occurance so don't feel bad).

 

[lookingforK]

Thank you boblarson.

Still testing ...

 

[gemma-the-husky]

make sure the controls are set as numbers of some sort - say "fixed" rather than left blank.

then you should not need to manipulate them


in your query try this


between forms!someform!weekfrom and forms!someform!weekto

 

[lookingforK]

Thank you gemma-the-husky and boblarson,

I find the problem.

It works when using the values in Text Boxes as parameters for the query under 1 condition: after I input the values into the 2 Text Boxes, I should left-click the mouse on whatever other place except the 2 Text Boxes.

It is interesting. I think it is more convenient to use the Combo Boxes ...