By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,475 Members | 954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,475 IT Pros & Developers. It's quick & easy.

Popup form for filtering form

P: n/a
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 = "[ReceivingStoreNo] = #" & Forms!frmSearch![Filter6] &
"#"

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

If Application.CurrentProject.AllForms("frmLoadsheets Search").IsLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsSearch"
DoCmd.OpenForm "frmLoadsheetsSearch", 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![frmLoadsheetsSearch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch![Filter1] & "#"
Forms![frmLoadsheetsSearch].FilterOn = True
GoTo Endcode
End If
Else

DoCmd.OpenForm "frmLoadsheetsSearch", 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![frmLoadsheetsSearch].Filter = strSQL
Forms![frmLoadsheetsSearch].FilterOn = True
End If
End If
Endcode:
End Sub
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.Filter1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ") AND "
End If
If Not IsNull(Me.Filter2) Then 'Text-field example
strFilter = strFilter & "[CarrierConnote] = """ & Me.Filter2 & """)
AND "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[Date] = #" & Format(Me.Filter3,
"mm/dd/yyyy") & "#) AND "
End If
'etc.

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter, lngLen)
strFormName = "frmLoadsheetsSearch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormName)
.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.google.c om...

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 = "[ReceivingStoreNo] = #" & Forms!frmSearch![Filter6] &
"#"

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

If Application.CurrentProject.AllForms("frmLoadsheets Search").IsLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsSearch"
DoCmd.OpenForm "frmLoadsheetsSearch", 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![frmLoadsheetsSearch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch![Filter1] & "#"
Forms![frmLoadsheetsSearch].FilterOn = True
GoTo Endcode
End If
Else

DoCmd.OpenForm "frmLoadsheetsSearch", 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![frmLoadsheetsSearch].Filter = strSQL
Forms![frmLoadsheetsSearch].FilterOn = True
End If
End If
Endcode:
End Sub

Nov 12 '05 #2

P: n/a
See comments/questions throughout...

"Nathan Bloomfield" <na**************@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
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 = "[ReceivingStoreNo] = #" & 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.CurrentProject.AllForms("frmLoadsheets Search").IsLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsSearch"
DoCmd.OpenForm "frmLoadsheetsSearch", 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("Filter" & 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![frmLoadsheetsSearch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch![Filter1] & "#"
Forms![frmLoadsheetsSearch].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 "frmLoadsheetsSearch", 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![frmLoadsheetsSearch].Filter = strSQL
Forms![frmLoadsheetsSearch].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

Nov 12 '05 #3

P: n/a
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.Filter1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ")
AND "
End If
If Not IsNull(Me.Filter2) Then 'Number-field example
strFilter = strFilter & "([CarrierConnote] = " & Me.Filter2 & ")
AND "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filter3, "dd/mm/yyyy") & "#) AND "
End If
If Not IsNull(Me.Filter4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] = """ & Me.Filter4 &
""")" And ""
End If
If Not IsNull(Me.Filter5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 & ")
AND "
End If
If Not IsNull(Me.Filter6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreNo] = " & Me.Filter6 &
") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter, lngLen)
strFormName = "frmLoadsheetsSearch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormName)
.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!
Nov 12 '05 #4

P: n/a
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 frmLoadsheetsSearch
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

P: n/a
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
<yourformrecordsource> 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 "frmWhatever", , , strFilter
or
DoCmd.OpenForm FormName:= "frmWhatever", 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.corm> wrote in message
news:40*********************@news.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.Filter1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ")
AND "
End If
If Not IsNull(Me.Filter2) Then 'Number-field example
strFilter = strFilter & "([CarrierConnote] = " & Me.Filter2 & ")
AND "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filter3, "dd/mm/yyyy") & "#) AND "
End If
If Not IsNull(Me.Filter4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] = """ & Me.Filter4 &
""")" And ""
End If
If Not IsNull(Me.Filter5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 & ")
AND "
End If
If Not IsNull(Me.Filter6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreNo] = " & Me.Filter6 &
") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter, lngLen)
strFormName = "frmLoadsheetsSearch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormName)
.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!

Nov 12 '05 #6

P: n/a
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.corm> wrote in message
news:40*********************@news.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.Filter1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ")
AND "
End If
If Not IsNull(Me.Filter2) Then 'Number-field example
strFilter = strFilter & "([CarrierConnote] = " & Me.Filter2 & ")
AND "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filter3, "dd/mm/yyyy") & "#) AND "
End If
If Not IsNull(Me.Filter4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] = """ & Me.Filter4 &
""")" And ""
End If
If Not IsNull(Me.Filter5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 & ")
AND "
End If
If Not IsNull(Me.Filter6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreNo] = " & Me.Filter6 &
") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter, lngLen)
strFormName = "frmLoadsheetsSearch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormName)
.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!

Nov 12 '05 #7

P: n/a
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.Filter1) Then 'Text-field example
strFilter = strFilter & "[LoadsheetNo] =" & """" & Me.Filter1
& """" & " And "
End If
If Not IsNull(Me.Filter2) Then 'Text-field example
strFilter = strFilter & "[CarrierConnote] =" & """" &
Me.Filter2 & """" & " And "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filter3, "mm/dd/yyyy") & "# AND "
End If
If Not IsNull(Me.Filter4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] =" & """" & Me.Filter4
& """" & " And "
End If
If Not IsNull(Me.Filter5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 &
") AND "
End If
If Not IsNull(Me.Filter6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreNo] = " & Me.Filter6
& ") AND "
End If

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

End Sub
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.