Filter Data - Multiple Options

G

Gary Dolliver

Hi all,
I have a search form used to look up records based on a number of entry
fields. I have, with the help of this forum, created successful searches for
all of the text like fields, but I also wanted to have a "# of weeks back"
filter as well. My crrent code is:
Dim strWhere As String

strWhere = "True"

If Not IsNull(Me.LastName) Then
strWhere = strWhere & " AND ShipTo_LName like """ & Me.LastName & "*"""
End If
If Not IsNull(Me.Organization) Then
strWhere = strWhere & " AND ShipTo_Organization like """ & Me.Organization &
"*"""
End If
If Not IsNull(Me.ZipCode) Then
strWhere = strWhere & " And ShipTo_ZIP5='" & Me.ZipCode & "'"
End If
If Not IsNull(Me.PhoneNum) Then
strWhere = strWhere & " And ShipTo_Phone='" & Me.PhoneNum & "'"
End If
If Not IsNull(Me.Email) Then
strWhere = strWhere & " And ShipTo_Email like '" & Me.Email & "*'"
End If
If Not IsNull(Me.OrderNum) Then
strWhere = strWhere & " And Order_ID=" & Me.OrderNum
End If

DoCmd.OpenForm "INQUIRY_MAIN_View", , , strWhere
DoCmd.Close acForm, "INQUIRY_MAIN"

I would like to have weeks back included in this code, and then I could use
the "Date_Received" field within the orders table (which controls
INQUIRY_MAIN_View") to be compared to Date() - ([weeks_back]*7)

Any sample code would be most appreciated, thanks!

-gary
 
M

Marshall Barton

Gary said:
I have a search form used to look up records based on a number of entry
fields. I have, with the help of this forum, created successful searches for
all of the text like fields, but I also wanted to have a "# of weeks back"
filter as well. My crrent code is:
Dim strWhere As String

strWhere = "True"

If Not IsNull(Me.LastName) Then
strWhere = strWhere & " AND ShipTo_LName like """ & Me.LastName & "*"""
End If
If Not IsNull(Me.Organization) Then
strWhere = strWhere & " AND ShipTo_Organization like """ & Me.Organization &
"*"""
End If
If Not IsNull(Me.ZipCode) Then
strWhere = strWhere & " And ShipTo_ZIP5='" & Me.ZipCode & "'"
End If
If Not IsNull(Me.PhoneNum) Then
strWhere = strWhere & " And ShipTo_Phone='" & Me.PhoneNum & "'"
End If
If Not IsNull(Me.Email) Then
strWhere = strWhere & " And ShipTo_Email like '" & Me.Email & "*'"
End If
If Not IsNull(Me.OrderNum) Then
strWhere = strWhere & " And Order_ID=" & Me.OrderNum
End If

DoCmd.OpenForm "INQUIRY_MAIN_View", , , strWhere
DoCmd.Close acForm, "INQUIRY_MAIN"

I would like to have weeks back included in this code, and then I could use
the "Date_Received" field within the orders table (which controls
INQUIRY_MAIN_View") to be compared to Date() - ([weeks_back]*7)
Click to expand...


This should be fairly close:

If Not IsNull(Me.[weeks_back]) Then
strWhere = strWhere & " And [Date_Received]>=" _
& Format(DateAdd("ww", -Me.[weeks_back], Date) _
, "\#m\/d\/yyyy\#")
End If
 
G

Gary Dolliver

Awesome!! Thank you so much!
I am trying to put in an error check so that if there is no matching data, I
no longer get a 2427 error - how would I set this up so that it would just
pop a message that there is no data, and then not open the new form and stay
on the search form? I have the following:

On Error GoTo Err_Command6_Click

SEARCH CODE

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
If Err.Number = 2427 Then
MsgBox "No Matching Records"
Resume Next
Else
MsgBox "Error: " & Err.Number & vbCr & "Desc: " & Err.Description
End If
Resume Exit_Command6_Click

I still get the 2427 message, so I am assuming it is an issue within the
error handling code... but not sure?
Thanks again!
-gary

Marshall Barton said:
Gary said:
I have a search form used to look up records based on a number of entry
fields. I have, with the help of this forum, created successful searches for
all of the text like fields, but I also wanted to have a "# of weeks back"
filter as well. My crrent code is:
Dim strWhere As String

strWhere = "True"

If Not IsNull(Me.LastName) Then
strWhere = strWhere & " AND ShipTo_LName like """ & Me.LastName & "*"""
End If
If Not IsNull(Me.Organization) Then
strWhere = strWhere & " AND ShipTo_Organization like """ & Me.Organization &
"*"""
End If
If Not IsNull(Me.ZipCode) Then
strWhere = strWhere & " And ShipTo_ZIP5='" & Me.ZipCode & "'"
End If
If Not IsNull(Me.PhoneNum) Then
strWhere = strWhere & " And ShipTo_Phone='" & Me.PhoneNum & "'"
End If
If Not IsNull(Me.Email) Then
strWhere = strWhere & " And ShipTo_Email like '" & Me.Email & "*'"
End If
If Not IsNull(Me.OrderNum) Then
strWhere = strWhere & " And Order_ID=" & Me.OrderNum
End If

DoCmd.OpenForm "INQUIRY_MAIN_View", , , strWhere
DoCmd.Close acForm, "INQUIRY_MAIN"

I would like to have weeks back included in this code, and then I could use
the "Date_Received" field within the orders table (which controls
INQUIRY_MAIN_View") to be compared to Date() - ([weeks_back]*7)
Click to expand...


This should be fairly close:

If Not IsNull(Me.[weeks_back]) Then
strWhere = strWhere & " And [Date_Received]>=" _
& Format(DateAdd("ww", -Me.[weeks_back], Date) _
, "\#m\/d\/yyyy\#")
End If
Click to expand...
 
M

Marshall Barton

Gary said:
I am trying to put in an error check so that if there is no matching data, I
no longer get a 2427 error - how would I set this up so that it would just
pop a message that there is no data, and then not open the new form and stay
on the search form? I have the following:

On Error GoTo Err_Command6_Click

SEARCH CODE

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
If Err.Number = 2427 Then
MsgBox "No Matching Records"
Resume Next
Else
MsgBox "Error: " & Err.Number & vbCr & "Desc: " & Err.Description
End If
Resume Exit_Command6_Click

I still get the 2427 message, so I am assuming it is an issue within the
error handling code... but not sure?
Click to expand...


That error handling code looks ok to me. Maybe the error is
in the INQUIRY_MAIN_View form? I'm not familiar with that
error code, are you sure the error is caused by the lack of
matching revords?

The easiest way to check for no records is to use code in
the INQUIRY_MAIN_View form's Load event:

If Me.Recordset.RecordCount = 0 Then
MsgBox "No Matching Records"
DoCmd.Close acForm, Me.Name, AcSaveNo
End If
 
G

Gary Dolliver

Marshall Barton said:
That error handling code looks ok to me. Maybe the error is
in the INQUIRY_MAIN_View form? I'm not familiar with that
error code, are you sure the error is caused by the lack of
matching revords?

The easiest way to check for no records is to use code in
the INQUIRY_MAIN_View form's Load event:

If Me.Recordset.RecordCount = 0 Then
MsgBox "No Matching Records"
DoCmd.Close acForm, Me.Name, AcSaveNo
End If
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

Wild Card Search Question 4
Filtering a multiple item form 1
Multiple Search Criteria with AND/OR??? 4
Filter for years and keywords 6
Send Records from Form, Method or Data Error not Found" 0
Filter for words at the beginning or end using a Form Command Butt 5
Check Box as filter 7
Filter coding stopped working 4

Top