VBA range criteria

[SamLis]

Hello,

I have a form with textboxes and comboboxes and a Button,
when button is clicked,
a new query is made, with criteria based on boxes on form.

For example this one :

If Not IsNull(Me.cmbType) Then Where = Where & " AND [TypeID] = " & Me.cmbType

What would be the statement for a range between to comboboxes.

BoxID


I have 2 comboboxes (cmbBox1 and cmbbox2), show only records equal and between the ones selected in cmbBox1 and 2.

What happens if only one is filled?

thanks,
Sam

 

[pr2-eugin]

Hello SamLis,

Try something like.

If Not IsNull(Me.cmbBox1) And Not IsNull(Me.cmbbox2) Then _
    WhereStr = WhereStr & " AND [yourColumnName] BETWEEN " & Me.cmbBox1 & " AND " & Me.cmbBox2

What happens if only one is filled?

That is a question you have to ask yourself/answer. The above code I gave would include that criteria only if both combo is used/filled.

 

[namliam]

if only one is filled you should basicaly revert to a < or > statement

If isnull(field1) and isnull(field2) then
'nothing filled
elseif isnull(field1) then
'< 
elseif isnull(field2) then
'>
else
    Where = Where & " AND [TypeID] Between " & Me.cmbType1 & " and " & Me.cmbType1
endif

This is assuming numbers
For dates use something like:
Where = Where & " AND [TypeID] Between #" & format(Me.cmbType1,"MM/DD/YYYY") & "# and #" & Format(Me.cmbType,"MM/DD/YYYY") & "#"

 

[CJ_London]

What would be the statement for a range between to comboboxes.

something like this (assumes the values are numeric, otherwise you need to add single quotes)

Where [BoxID] BETWEEN " & cmbBox1 & " AND " & cmbbox2

What happens if only one is filled?

Depends on what you want to happen - as it stands the query will fail so you need to control what you want to happen.

you might want to stop the query running by using a similar if statement as you posted or you can use nz to populate with a default value

e.g. if only one box is completed and you want to return everything before box2 or after box 1 you could use

Where [BoxID] BETWEEN " & nz(cmbBox1,0)  & " AND " & nz(cmbbox2,100000)

This will also retun all records (subject to 100000 being big enough) if both boxes are left blank

or this will return just the one record if either box is completed or nothing if neither is completed (unless you have a boxid=0)

Where [BoxID] BETWEEN " & nz(cmbBox1,nz(cmbbox2,0))  & " AND " & nz(cmbbox2,nz(cmbbox1,0))

 

[SamLis]

Hello,
Thanks for the quick response,

one more problem,
if only one is filled in,

it gives me from or to, without the value?

Search "1" result 2,3,4
I want result 1,2,3,4

If Not IsNull(Me.cmbBoxBegin) Then Where = Where & " AND [BoxID] >= " & Me.cmbBoxBegin

 

[namliam]

>= should give you 1,2,3,4 when you search for 1

 

[CJ_London]

suggest look at my post

 

[SamLis]

Hello,

Thanks a lot,

now it's working.

 

[pr2-eugin]

Glad you found a solution ! It would be great if you could share what you have used, so others may be benefited by this.

 

[SamLis]

Hi,

yes of course!
I thought, since my criteria is only build from boxes containig data, I don't need a "Between"

If Not IsNull(Me.cmbBoxBegin) Then Where = Where & " AND [Boxnmbr] >= " & Me.cmbBoxBegin
If Not IsNull(Me.cmbBoxEnd) Then Where = Where & " AND [Boxnmbr] <= " & Me.cmbBoxEnd

This covers all :

If 2 boxes are filled, is automatically ranges between the 2 values,
if only one is filled, the other one is not taking into consideration, I just label one combo "from", the other "to"

thanks for the help,
Sam