Opening a multiple filtered form from another form

R

Ray

I have two forms (Form1 and Form2)> Form1 has 7
comboboxes and I want to open Form2 using any combination
of the comboboxes. The problem is that if any one of the
comboboxes has not been selected and its current value is
therefore "null", then I get a syntax error. Is there any
default value that I can assign to the comboboxes which
would be interpreted as meaning "no filter for this
column"? My Code is as follows. The individual parts work
fine. But, put them all together, like below, and leave
one combobox unselected and I get error messages

stLinkCriteria = "[Date]>" & "#" & Me![StartDate] & "#" _
& "And" & "[Date]<" & "#" & Me![EndDate] & "#" _
& "And" & "[ProducedShift]=" & "'" & Me![Shift] & "'" _
& "And" & "[TLPriKey]=" & Me![TeamLeaders] _
& "And" & "[PONum]=" & Me![PONum] _
& "And" & "[RoutingID]=" & Me![RoutingNum]
 
N

Nikos Yannacopoulos

Ray,

Try this instead:

If Not IsNull(Me![StartDate]) Then
vStr = "[Date]>#" & Me![StartDate] & "#"
End If
If Not IsNull(Me![EndDate]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Date]<#" & Me![EndDate] & "#"
End If
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If
If Not IsNull(Me![TLPriKey]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If
If Not IsNull(Me![PONum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PONum]=" & Me![PONum]
End If
If Not IsNull(Me![RoutingNum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[RoutingID]=" & Me![RoutingNum]
End If
stLinkCriteria = vStr

HTH,
Nikos
 
G

Guest

Thankyou. Had come to same idea just few minutes before
-----Original Message-----
Ray,

Try this instead:

If Not IsNull(Me![StartDate]) Then
vStr = "[Date]>#" & Me![StartDate] & "#"
End If
If Not IsNull(Me![EndDate]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[Date]<#" & Me![EndDate] & "#"
End If
If Not IsNull(Me![Shift]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[ProducedShift]='" & Me![Shift] & "'"
End If
If Not IsNull(Me![TLPriKey]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[TLPriKey]=" & Me![TeamLeaders]
End If
If Not IsNull(Me![PONum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[PONum]=" & Me![PONum]
End If
If Not IsNull(Me![RoutingNum]) Then
If Not IsNull(vStr) Then vStr = vStr & " And "
vStr = vStr & "[RoutingID]=" & Me![RoutingNum]
End If
stLinkCriteria = vStr

HTH,
Nikos
I have two forms (Form1 and Form2)> Form1 has 7
comboboxes and I want to open Form2 using any combination
of the comboboxes. The problem is that if any one of the
comboboxes has not been selected and its current value is
therefore "null", then I get a syntax error. Is there any
default value that I can assign to the comboboxes which
would be interpreted as meaning "no filter for this
column"? My Code is as follows. The individual parts work
fine. But, put them all together, like below, and leave
one combobox unselected and I get error messages

stLinkCriteria = "[Date]>" & "#" & Me![StartDate] & "#" _
& "And" & "[Date]<" & "#" & Me![EndDate] & "#" _
& "And" & "[ProducedShift]=" & "'" & Me![Shift] & "'" _
& "And" & "[TLPriKey]=" & Me![TeamLeaders] _
& "And" & "[PONum]=" & Me![PONum] _
& "And" & "[RoutingID]=" & Me![RoutingNum]
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transfer values from Form to Reports 1
Opening a form from another form 9
data from one form to another. 2
use form from one mdb in another mdb 2
Filtering Combo Box from another form 2
functionality of form decreasing 1
Go to a new record on another form? 6
Opening a new form from another form 12

Top