Filter Data - Multiple Options

G

Guest

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

Guest

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

Guest

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
Filter for years and keywords 6
Filter for words at the beginning or end using a Form Command Butt 5
Filter coding stopped working 4
Filter data by using a form and open a report based on this data 1
Using strwhere and Like to filter form 8
Check Box as filter 7
Date By Month Filter 3

Top