filter between 2 numbers

[Mbakker71]

Hello,

i've a query and a form and on the form i've 2 listboxes where people can select numbers. now i want that the query shows only the records between the 2 records and shows all records when the listboxes are empty. i've tried this code but is not working

IIf([Forms]![Search File]![cmbArea1]="";"";>=[Forms]![Search File]![cmbArea1] And <[Forms]![Search File]![cmbArea2])

the name of the query is "files query " and the name of the collumn = area



does someone the code??

bey,

Martijn

[ItsMe]

perhaps an expression using the BETWEEN operator

[Mbakker71]

the problem is that when i used the code :
IIf([Forms]![Search File]![cmbArea1]=Not Null;Between [Forms]![Search File]![cmbArea1] And [Forms]![Search File]![cmbArea2])then the code is not working

but when i do only the BETWEEN code then it works

[June7]

Can't use = Null or = Not Null. Nothing is ever = Null

See error 5 http://allenbrowne.com/casu-12.html

The correct syntax is

IIf(IsNull([something]), do this, else this)

or

IIf(Nz([something],"") = "", do this, else this)

[Mbakker71]

i i've nowthis

Like IIf(IsNull([Forms]![Search File]![cmbArea1]);"*";Between [Forms]![Search File]![cmbArea1] And [Forms]![Search File]![cmbArea2])

its not working

[June7]

Try:

BETWEEN Nz([Forms]![Search File]![cmbArea1], 0) AND Nz([Forms]![Search File]![cmbArea2], 99999999999)

However, that won't allow a search for exact value.

Try:

BETWEEN Nz([Forms]![Search File]![cmbArea1], 0) AND Nz([Forms]![Search File]![cmbArea2], Nz([Forms]![Search File]![cmbArea1],0))

[Mbakker71]

i want when the user set in cmberea1 1010 and in cmbarea2 1010 then that the query show the records with 1010 are showing and when the user set 1010 - 2000 the records are showing between that 2 numbers

[Mbakker71]

i've solved the problem thank you June 7 you solved the problem