Popup form for filtering form

[Nathan Bloomfield]

Hi All,

I am hoping someone out there will be kind enough to find out where my
code is going wrong. The current code is inefficiant but hopefully it
will convey the data I require to be filtered.

Basically I have a popup form which has 6 optional controls to filter
records in another form. The code below does not work. Can anyone
suggest some correctons or alternatives.

Thanks,

Nathan

Private Sub Search_Click()
Dim varFilter1 As String
Dim varFilter2 As String
Dim varFilter3 As String
Dim varFilter4 As String
Dim varFilter5 As String
Dim varFilter6 As String

varFilter1 = "[LoadsheetNo] = #" & Forms!frmSearch ![Filter1] & "#"
varFilter2 = "[CarrierConnote] = #" & Forms!frmSearch ![Filter2] & "#"
varFilter3 = "[Date] = #" & Forms!frmSearch ![Filter3] & "#"
varFilter4 = "[CarrierName] = Forms!frmSearch ![Filter4]"
varFilter5 = "[SendingStoreNo] = #" & Forms!frmSearch ![Filter5] & "#"
varFilter6 = "[ReceivingStoreN o] = #" & Forms!frmSearch ![Filter6] &
"#"

Dim strSQL As String, intCounter As Integer
'Build SQL String

If Application.Cur rentProject.All Forms("frmLoads heetsSearch").I sLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsS earch"
DoCmd.OpenForm "frmLoadsheetsS earch", acViewNormal
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) &
" And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSe arch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch ![Filter1] & "#"
Forms![frmLoadsheetsSe arch].FilterOn = True
GoTo Endcode
End If
Else

DoCmd.OpenForm "frmLoadsheetsS earch", acViewNormal

For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) & " And "

End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSe arch].Filter = strSQL
Forms![frmLoadsheetsSe arch].FilterOn = True
End If
End If
Endcode:
End Sub

[Allen Browne]

Hi Nathan

To filter Date/Time fields, you need the # as the delimiter.
To filter Text fields, use " as the delimiter.
To filter Number fields, use no delimiter.

Test if each of the controls is Null before including them in the filter
string.

(Note that if you do have a field called "Date", Access may misunderstand
it, since Date in VBA refers to the system date.)

Private Sub Search_Click()
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filte r1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ") AND "
End If
If Not IsNull(Me.Filte r2) Then 'Text-field example
strFilter = strFilter & "[CarrierConnote] = """ & Me.Filter2 & """)
AND "
End If
If Not IsNull(Me.Filte r3) Then 'Date/Time example
strFilter = strFilter & "[Date] = #" & Format(Me.Filte r3,
"mm/dd/yyyy") & "#) AND "
End If
'etc.

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter , lngLen)
strFormName = "frmLoadsheetsS earch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormNa me)
.Filter = strFilter
.FilterOn = True
End With
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nathan Bloomfield" <na************ **@hotmail.com> wrote in message
news:4b******** *************** ***@posting.goo gle.com...

I am hoping someone out there will be kind enough to find out where my
code is going wrong. The current code is inefficiant but hopefully it
will convey the data I require to be filtered.

Basically I have a popup form which has 6 optional controls to filter
records in another form. The code below does not work. Can anyone
suggest some correctons or alternatives.

Thanks,

Nathan

Private Sub Search_Click()
Dim varFilter1 As String
Dim varFilter2 As String
Dim varFilter3 As String
Dim varFilter4 As String
Dim varFilter5 As String
Dim varFilter6 As String

varFilter1 = "[LoadsheetNo] = #" & Forms!frmSearch ![Filter1] & "#"
varFilter2 = "[CarrierConnote] = #" & Forms!frmSearch ![Filter2] & "#"
varFilter3 = "[Date] = #" & Forms!frmSearch ![Filter3] & "#"
varFilter4 = "[CarrierName] = Forms!frmSearch ![Filter4]"
varFilter5 = "[SendingStoreNo] = #" & Forms!frmSearch ![Filter5] & "#"
varFilter6 = "[ReceivingStoreN o] = #" & Forms!frmSearch ![Filter6] &
"#"

Dim strSQL As String, intCounter As Integer
'Build SQL String

If Application.Cur rentProject.All Forms("frmLoads heetsSearch").I sLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsS earch"
DoCmd.OpenForm "frmLoadsheetsS earch", acViewNormal
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) &
" And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSe arch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch ![Filter1] & "#"
Forms![frmLoadsheetsSe arch].FilterOn = True
GoTo Endcode
End If
Else

DoCmd.OpenForm "frmLoadsheetsS earch", acViewNormal

For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) & " And "

End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSe arch].Filter = strSQL
Forms![frmLoadsheetsSe arch].FilterOn = True
End If
End If
Endcode:
End Sub

[Mike Storr]

See comments/questions throughout...

"Nathan Bloomfield" <na************ **@hotmail.com> wrote in message
news:4b******** *************** ***@posting.goo gle.com...

Hi All, Private Sub Search_Click()
Dim varFilter1 As String
Dim varFilter2 As String
Dim varFilter3 As String
Dim varFilter4 As String
Dim varFilter5 As String
Dim varFilter6 As String

varFilter1 = "[LoadsheetNo] = #" & Forms!frmSearch ![Filter1] & "#"
varFilter2 = "[CarrierConnote] = #" & Forms!frmSearch ![Filter2] & "#"
varFilter3 = "[Date] = #" & Forms!frmSearch ![Filter3] & "#"
varFilter4 = "[CarrierName] = Forms!frmSearch ![Filter4]"
varFilter5 = "[SendingStoreNo] = #" & Forms!frmSearch ![Filter5] & "#"
varFilter6 = "[ReceivingStoreN o] = #" & Forms!frmSearch ![Filter6] &
"#"

You seem to be treating all but one of the above as dates, although only one
field name indicates it would be a date.
Only surround dates with #. Strings need to be enclosed with ' or ". Numbers
are not enclosed.
Dim strSQL As String, intCounter As Integer
'Build SQL String

If Application.Cur rentProject.All Forms("frmLoads heetsSearch").I sLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsS earch"
DoCmd.OpenForm "frmLoadsheetsS earch", acViewNormal
Why close the form, then re-open it?


For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) &
" And "
End If
Next

If you are refering to a control name when using Me("Filter" & intCounter)
then you should use
Me.Controls("Fi lter" & intCounter). Also you should (I feel it's good
practise even if it's not required)
to include the counter or object variable in the Next statement ie. Next
intCounter
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSe arch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch ![Filter1] & "#"
Forms![frmLoadsheetsSe arch].FilterOn = True
GoTo Endcode
End If
Else

Why start over? At the begining you tested to see if the form was open, then
you closed it then
reopened it. Instead of enclosing all of the above within the first If
statement, only deal with
wether or not the form is open. Once open, all the rest need only be writen
once.

DoCmd.OpenForm "frmLoadsheetsS earch", acViewNormal

For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) & " And "

End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSe arch].Filter = strSQL
Forms![frmLoadsheetsSe arch].FilterOn = True
End If
End If
Endcode:
End Sub

What are all the varFilters for? You haven't used them anywhere.

Mike Storr
www.veraccess.com

[Nathan Bloomfield]

Hi Allen,

Thanks very much for the help. The code below looks promising but I
have come across a minor(hopefully ) hiccup.

The number fields work but the text fields come up with "type mismatch"
error". I have checked all the fields on the table and they are
definately text.

The date field comes up with a "you cant assign a value to this object"
error.

Thanks again for your assistance,

Nathan

Private Sub Search_Click()
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filte r1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ")
AND "
End If
If Not IsNull(Me.Filte r2) Then 'Number-field example
strFilter = strFilter & "([CarrierConnote] = " & Me.Filter2 & ")
AND "
End If
If Not IsNull(Me.Filte r3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filte r3, "dd/mm/yyyy") & "#) AND "
End If
If Not IsNull(Me.Filte r4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] = """ & Me.Filter4 &
""")" And ""
End If
If Not IsNull(Me.Filte r5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 & ")
AND "
End If
If Not IsNull(Me.Filte r6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreN o] = " & Me.Filter6 &
") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter , lngLen)
strFormName = "frmLoadsheetsS earch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormNa me)
.Filter = strFilter
.FilterOn = True
End With
End If

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Nathan Bloomfield]

Just to add:

The LoadsheetNo & CarrierConnote are both text fields in the
tblLoadsheets which is the source of qryLoadsheets which is the record
source of frmLoadsheetsSe arch
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Andrew]

Hi Nathan,

In your test field assignments, you either need to use four
double-apostrophes at each end, or use a single apostrophe within two
doubles - e.g.
strFilter = strFilter & "[CarrierName] = " & """" & Me.Filter4 & """" & "
And "
or
strFilter = strFilter & "[CarrierName] = '" & Me.Filter4 & "' And "

In either case once Jet has a squizz at """" or "'" it'll know you mean a
string delimiter.
"""" translates into ", and "'" into ' either of which works fine with Jet.

BTW look out for redundant or extra brackets creeping in.

Try setting a breakpoint after the final clean-up of the string, then check
the variable using the Immediate Window (CTRL+G, then type "?strFilter " -
without the apostrophes - and press ENTER. You'll soon see it it's OK. You
can always open up a new query in design mode, change to SQL view, the copy
and paste the Immediate Window output into it, add "SELECT * FROM
<yourformrecord source> WHERE " in front of the filter string and see what
happens when you change to regular design or preview view. It'll bitch at
you if you've forgotten a space or left a bracket in by mistake.

Enough apostrophes already! You'll soon get the hang of it - some of the
rest of us have, and we're not all scary gurus!

By the way, you could also stick your filter string into the DoCmd command
as the fourth argument:
DoCmd.OpenForm "frmWhateve r", , , strFilter
or
DoCmd.OpenForm FormName:= "frmWhateve r", WhereCondition: =strFilter
if you prefer, so your filter string works just like an extra WHERE clause
on the forms RecordSource. Easy, huh?

Good luck

Andrew
"Nathan Bloomfield" <na************ ***@hortmail.co rm> wrote in message
news:40******** *************@n ews.frii.net...

Hi Allen,

Thanks very much for the help. The code below looks promising but I
have come across a minor(hopefully ) hiccup.

The number fields work but the text fields come up with "type mismatch"
error". I have checked all the fields on the table and they are
definately text.

The date field comes up with a "you cant assign a value to this object"
error.

Thanks again for your assistance,

Nathan

Private Sub Search_Click()
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filte r1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ")
AND "
End If
If Not IsNull(Me.Filte r2) Then 'Number-field example
strFilter = strFilter & "([CarrierConnote] = " & Me.Filter2 & ")
AND "
End If
If Not IsNull(Me.Filte r3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filte r3, "dd/mm/yyyy") & "#) AND "
End If
If Not IsNull(Me.Filte r4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] = """ & Me.Filter4 &
""")" And ""
End If
If Not IsNull(Me.Filte r5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 & ")
AND "
End If
If Not IsNull(Me.Filte r6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreN o] = " & Me.Filter6 &
") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter , lngLen)
strFormName = "frmLoadsheetsS earch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormNa me)
.Filter = strFilter
.FilterOn = True
End With
End If

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

Nathan, nothing is being assigned to any object until the string is assigned
to the Filter property of the form. Is that where the problem occurs? If so,
add:
Debug.Print strFilter
so you can see what's wrong in the Immediate Window (Ctrl+G).

If things are going wrong before that, you must have a naming conflict, or
invalid syntax.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nathan Bloomfield" <na************ ***@hortmail.co rm> wrote in message
news:40******** *************@n ews.frii.net...

Thanks very much for the help. The code below looks promising but I
have come across a minor(hopefully ) hiccup.

The number fields work but the text fields come up with "type mismatch"
error". I have checked all the fields on the table and they are
definately text.

The date field comes up with a "you cant assign a value to this object"
error.

Thanks again for your assistance,

Nathan

Private Sub Search_Click()
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filte r1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ")
AND "
End If
If Not IsNull(Me.Filte r2) Then 'Number-field example
strFilter = strFilter & "([CarrierConnote] = " & Me.Filter2 & ")
AND "
End If
If Not IsNull(Me.Filte r3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filte r3, "dd/mm/yyyy") & "#) AND "
End If
If Not IsNull(Me.Filte r4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] = """ & Me.Filter4 &
""")" And ""
End If
If Not IsNull(Me.Filte r5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 & ")
AND "
End If
If Not IsNull(Me.Filte r6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreN o] = " & Me.Filter6 &
") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter , lngLen)
strFormName = "frmLoadsheetsS earch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormNa me)
.Filter = strFilter
.FilterOn = True
End With
End If

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Nathan Bloomfield]

Hello Allen, Andrew & Mike:

Thanks for your help. By piecing together your suggestions I have
come away with more than originally intended. I have in the past
neglected to utilise the Immediate Window which is an invaluable tool.
And I now understand the use of apostrophes for the varying field
types. As an amateur programmer I would be lost without this kind of
assistance.

Regards,

Nathan
Melbourne, Australia
The final code:

Private Sub Search_Click()
'Thanks to Allen Browne for the code below
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filte r1) Then 'Text-field example
strFilter = strFilter & "[LoadsheetNo] =" & """" & Me.Filter1
& """" & " And "
End If
If Not IsNull(Me.Filte r2) Then 'Text-field example
strFilter = strFilter & "[CarrierConnote] =" & """" &
Me.Filter2 & """" & " And "
End If
If Not IsNull(Me.Filte r3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filte r3, "mm/dd/yyyy") & "# AND "
End If
If Not IsNull(Me.Filte r4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] =" & """" & Me.Filter4
& """" & " And "
End If
If Not IsNull(Me.Filte r5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 &
") AND "
End If
If Not IsNull(Me.Filte r6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreN o] = " & Me.Filter6
& ") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter , lngLen)
strFormName = "frmLoadsheetsS earch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormNa me)
Debug.Print strFilter
.Filter = strFilter
.FilterOn = True
End With
End If

End Sub