Opening a multiple filtered form from another form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ray - 11 Mar 2005 05:38 GMT

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]

Reply to this Message

Nikos Yannacopoulos - 11 Mar 2005 08:46 GMT

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
[quoted text clipped - 13 lines]
> & "And" & "[PONum]=" & Me![PONum] _
> & "And" & "[RoutingID]=" & Me![RoutingNum]

Reply to this Message

anonymous@discussions.microsoft.com - 11 Mar 2005 10:05 GMT

Thankyou. Had come to same idea just few minutes before

>-----Original Message-----
>Ray,
[quoted text clipped - 47 lines]
>> & "And" & "[RoutingID]=" & Me![RoutingNum]
>.

Reply to this Message