How to convert text field to integer in query criteria

  • Thread starter Thread starter mjbruesch
  • Start date Start date
M

mjbruesch

I have a text box in a form, text box name is txtDaysOld, form name is
frmQueries. The user enters a number in the text box then clicks a
button to run a query. The query needs to pull all records older than
the number of days they entered in the text box. I tried setting the
criteria for the date of the record like this:

<Date() - CInt(Forms.frmQueries.txtDaysOld)

But it tells me the expression is too complex. I am familiar with VBA
so I could try something there if it would be easier. Any suggestions
on how to get this done?

Thanks.
 
I tried it as <Date() - Forms.frmQueries.txtDaysOld
and it worked fine. Just because it's called a text box, doesn't mean it
doesn't recognize a number when it sees it.
 
Yes, Access reports it is "too complex" if it does not understand the data
type, so we have to help it.

Rather than CInt() I suggest these 2 steps:
1. Set the Format property of the text box on your form to:
General Number
Now Access knows it's a number, even though it is unbound.
(If it's bound to a Number field, it knows anyway.)

2. Declare the parameter in your query.
In query design view, choose Parameters on the Query menu.
Access opens a dialog. Enter:
[Forms].[frmQueries].[txtDaysOld] Integer
Now the query engine also knows this is a number.

For your reference:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
 
Back
Top