Using Text Box for Between Criteria

[jereece]

I have a report query (qry_Data) that contains a field called "StartDate". I have a report form (frm_Data) where I want to put 2 text boxes (Text101 and Text102). I want to create the Query Criteria code for the StartDate so that it will look for start dates between the dates listed in Text101 and Text102 and if the user does not put anything into Text101 or Text102 it just searches for all dates.

I tried to use the code below

Between ([Forms]![frm_Data]![text101] Or Like [Forms]![frm_Data]![text101] & "*") and ([Forms]![frm_Data]![text102] Or Like [Forms]![frm_Data]![text102] & "*")

However, Access modified my code to this

Between (([tbl_Data].[Start])=[Forms]![frm_Data]![text101] Or ([tbl_Data].[Start]) Like [Forms]![frm_Data]![text101] & "*") And (([tbl_Data].[Start])=[Forms]![frm_Data]![text102] Or ([tbl_Data].[Start]) Like [Forms]![frm_Data]![text102] & "*")

And it does not work. Any suggestions on how to do this?

Thanks,
Jim

 

[Jon K]

I have just answered a similar question.

Take a look at my sample database in post #4 in this thread:=
http://www.access-programmers.co.uk/forums/showthread.php?t=133472
.

 

[jereece]

That worked great! Thanks. If you don't mind, I have a followup question similar to this one.

In this same query, I have the following field code.

DAYS_OPEN: IIf([Status]="Open",(Date()-[Start]),([Complete]-[Start]))

This calculates the days open. On the same form mentioned above, I want to have a text box so that the user can enter a number and the query will return records greater than the number (days) entered into the field, but if the field is empty it looks for any number. I tried the code below, but it does not seem to work.

>([Forms]![frm_Data]![text105] Or Like [Forms]![frm_Data]![text105] & "*")

I can't use the example you sent me because that code is also put into the field section of the query. Any suggestions?

I really appreciate your help.
Jim

 

[Jon K]

You can put the DAYS_OPEN expression in one column and its criteria in another column.

-------------------------------------
Field: DAYS_OPEN: IIf([Status]="Open",(Date()-[Start]),([Complete]-[Start]))

Show: check
-------------------------------------

-------------------------------------
Field: IIf([Status]="Open",(Date()-[Start]),([Complete]-[Start]))>[Forms]![frm_Data]![text105] Or [Forms]![frm_Data]![text105] Is Null

Show: uncheck

Criteria: True
-------------------------------------
.

 

[jereece]

I thought this may work, but when I try it the query returns all the data. I have several test records and one is showing 223 days open. When I run the query with 100 in Text105 on the form, I still get all records returned. I do not get any error messages.

Any suggestions?

Thanks,
Jim

 

[ajetrumpet]

I want to have a text box so that the user can enter a number and the query will return records greater than the number (days) entered into the field, but if the field is empty it looks for any number. I tried the code below, but it does not seem to work.

>([Forms]![frm_Data]![text105] Or Like [Forms]![frm_Data]![text105] & "*")

Try it without the IFF function....

>(Forms![frm_Data]![text105] OR Forms![frm_Data]![text105] Is Null)

If the value is Null with this code it returns all values because any value is greater than NULL (or so that's how I read it).

 

[Jon K]

Originally posted by jereece

I thought this may work, but when I try it the query returns all the data. I have several test records and one is showing 223 days open. When I run the query with 100 in Text105 on the form, I still get all records returned. I do not get any error messages.

Access is treating the number 100 in Text105 as text.

You need to declare the data type of the parameter Text105. In query Design View, select menu Query, Parameters... In the dialog box,

type [Forms]![frm_Data]![text105] in the left column
and select either Integer or Long Integer in the right column.
Click OK to close the dialog.

This should fix it.
.

 

[jereece]

That worked....I appreciate the help. Thank You!

Jim