Search for value between form parameters

[stepone]

Hello all,

I have a query which pulls its criteria from a form. There are about a dozen combo/text boxes on the form, and each one has a corresponding column in the query definition which goes something like this ;

[weeklyCaseSales]=[forms]![frmRetailerQuery]![txtWeeklyCaseSales] Or [forms]![frmRetailerQuery]![txtWeeklyCaseSales] Is Null

This is great for searching for single values, e.g. "weeklyCaseSales = 10", but the user also wants to search for ranges, e.g. "weeklyCaseSales > 5 and weekly Case Sales < 20". This means I will need two text boxes on the form e.g. txtMinCaseSales and txtMaxCaseSales. But how do I then use these in the query def (and also allow for the fact that one or both of them could be Null ?).

Thanks for any help,
StepOne

 

[Big Pat]

Yes - you will need two input boxes on your form and the criteria line in your query should go something like this:

BETWEEN [forms]![frmRetailerQuery]![txtMinCaseSales] AND [forms]![frmRetailerQuery]![txtMaxCaseSales]

I hope this helps!

Pat.

 

[stepone]

Thanks Pat,

Yes - you will need two input boxes on your form and the criteria line in your query should go something like this:

BETWEEN [forms]![frmRetailerQuery]![txtMinCaseSales] AND [forms]![frmRetailerQuery]![txtMaxCaseSales]

I hope this helps!

It does help - but I still hit a problem when one (or both) of the text boxes are blank. If they are both blank I want to select all records - if txtMaxCaseSales is blank I want to select all those where case sales is greater than txtMinCaseSales, and if txtMinCaseSales is blank, then I want to select all those where case sales is less than txtMaxCaseSales.

It seems like it will just end up as a complicated OR statement ;

(BETWEEN txtMin AND txtMax) OR
(txtMin IS NULL AND txtMax IS NULL) OR
(LESS THAN txtMax and txtMin IS NULL) OR
(GREATER THAN txtMin and txtMax IS NULL)

Will that work ?

Thanks,
StepOne

 

[Milothicus]

try this:

Between fValSrchL([forms]![frmRetailerQuery]![txtMinCaseSales]) And fValSrchU([forms]![frmRetailerQuery]![txtMaxCaseSales])

and copy:

Public Function fValSrchL(vValSrchL As Variant)

If IsNull(vValSrchL) Or vValSrchL = "" Then
fValSrchL = 0
Else
fValSrchL = vValSrchL
End If
End Function

and:

Public Function fValSrchU(vValSrchU As Variant)

If IsNull(vValSrchU) Or vValSrchU = "" Then
fValSrchU = 9999999999
Else
fValSrchU = vValSrchU
End If
End Function

into a module.

there may be a prettier way, but it works, unless your upper might be more than 9999999999, in which case you can add another 9 or two.