Query By Form; Range of Data

cbkebasen

Registered User.
Local time
Yesterday, 16:53
Joined
Jul 28, 2013
Messages
18
Dear All,

I've seen a QbF query that is active but the TS has a different question so I don't want to derail his thread, so apologies if I make another thread regarding QbF.

Anyway, here's my problem.

I have a query with a column that returns number of days per record. Using a form, I can query the records which has a specific number of days as based on my control field (i.e, all records that have "6" days). However, What I want to do now is to call records that fall within a range of number of days (i.e. all records that have "6 to 14" days).

I can do such in a query using >=6 AND <=14 in the query design view, but I tried using this in the form control and it does not work (or probably I missed something out).

Any thoughts or even best practice in calling data ranges using form control?
 
Show some sample data and the query-string you use.
I don't know how you've set it up, but normally to query in a range you need 2 controls on your form, one for the lower limit and one for the higher limit. For me it sounds like you have only one control.
 
Show some sample data and the query-string you use.
I don't know how you've set it up, but normally to query in a range you need 2 controls on your form, one for the lower limit and one for the higher limit. For me it sounds like you have only one control.

I'm not in the office right now so I'm sorry I can't post the query string, so I'll post on memory.

The form has a more than 5 criteria fields; but it was designed to show all records even if none of them are filled. I'm not sure if I remember correctly, but here's the general code for a field
(Like "*" & [Forms]![frmReportFilter]![cboSpecialty] & "*").
Same code applies (and works) in the "Days" column however it only returns specific number of days (i.e. records with "6" days only, and etc).

I tried a query criteria having minimum and maximum values a
(Between [Forms]![frmReportFilter]![txtDaysMin] AND [Forms]![frmReportFilter]![txtDaysMax]);

and

>=[Forms]![frmReportFilter]![txtDaysMin] AND <=[Forms]![frmReportFilter]![txtDaysMax]

but the trouble is that, the search form now only returns records if both the [txtDaysMin] and [txtDaysMax] are filled. Worse, they include values that are not in the range but does have a digit placed either on the two control fields (i.e. 4 to 5 query would return records with 478).

So I ditched both method because I know I made an error somewhere.
 
Last edited:
I'm not in the office right now so I'm sorry I can't post the query string, so I'll post on memory.
Then post it when you get to the office, + some sample data.
What is the field type where you store the days, (text or number)?
 
Then post it when you get to the office, + some sample data.
What is the field type where you store the days, (text or number)?

Yes I will. Regarding the field type, the column is a calculated field (datediff) and format in general number.
 

Users who are viewing this thread

Back
Top Bottom