Help with a search form's VBA

[RickDB]

I am using the attached sample to build my search form on.

I have a problem though. The sample searches multiple fields based on unbound entries on the form, but each only searches individual results from a query.

Example:

There may be an address saved to the customer table, and an address saved in the order table for that customer.

Here is the serach sample's VBA:

Private Sub cmdSearch_Click()
On Error Resume Next

Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "

'tblSubject qrySearchCriteriaSub
If Me![Index] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Index = """ & Index & """"
End If

If Me![Title] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Title like """ & Title & "*"""
End If

If Me![AreaCode] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.AreaCode = """ & AreaCode & """"
End If

If Me![NewsPaper] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.NewsPaper = """ & NewsPaper & """"
End If

If Me![StartDate] <> "" And EndDate <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.DateOfPaper between #" & Format(StartDate, "dd-mmm-yyyy") & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
End If

If Me![Subject] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Subject like """ & Subject & "*"""
End If

sSql = "SELECT DISTINCT [NewsPaperID], [Index],[Title],[AreaCode],[NewsPaper],[DateofPaper],[Subject] from qrySearchCriteriaSub " & sCriteria
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
',[Title],[AreaCode],[NewsPaper],[StartDate],[EndDate],[Subject]

'frmSearchCriteriaSub.Requery
End Sub

If Me![NewsPaper] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.NewsPaper = """ & NewsPaper & """"
End If

will only search the 'NewsPaper' field in the qrySearchCriteriaSub query. What if there was a second field in the query 'Newspaper2' (as a generic example). How can I alter this VBA to search both 'NewsPaper' and 'NewsPaper2' based on what is entered into [NewsPaper] on the search form?

Am I looking at this right? Or should I be asking myself how to concatenate the 'NewsPaper' & 'Newspaper2' fields into one query result that can then be searched?

Thanks in advance for your responses!

 

[Bodisathva]

If I'm understanding you correctly, you want to use:

If Me![NewsPaper] <> "" Then
  sCriteria = sCriteria & " AND (qrySearchCriteriaSub.NewsPaper = '" &  NewsPaper & _
                       "' OR qrySearchCriteriaSub.NewsPaper2 = '" & NewsPaper & "') "
End If

 

[RickDB]

Thanks,

I noticed you added an underscore after the third ampersand in the statement, why? (I'm just curious).

Also, can you wrap single quotes around double quotes instead of using 2 sets of double quotes? (Another change I noticed). Is the difference simply semantics, or should you always go single quote to double quote, instead of usig multiple sets of double quotes?

Thanks!

 

[Bodisathva]

The underscore represents a line continuation in VBA. It's significant only to the complier and doesn't really have any operational value, but it makes it possible to structure your logic switches and SQL statements in a more visually appealing manner

I have always found it easier to signify strings within SQL statements using single quotes. Again, there's no real operational difference, you could have used "", ', or Chr(34)...but it makes it easier to read

 

[RickDB]

Thank you Bodisathva,

Getting this into my db is proving to be entirely too difficult at this point, hopefully I'll be able to see some working results soon before I start pulling out hair!

Thanks for the help!

 

[RickDB]

One more thing...

What if in my query, there are fields with the same name out of different tables, like tblOrders.CompanyName and tblCustomers.CompanyName.

How would I refer to each of those seperate fields in the VBA? Do I simply add in "tblOrders." before the field name and after the query name (example: qrySearchCriteriaSub.tblCustomers.CompanyName) or does that not work?

Thanks again!

 

[Bodisathva]

open your query in the query painter, then go to the SQL view to see the query in SQL text. Most of the time it will add a lot of unnecessary prefixes, but you'll get a better idea of how Access "thinks" when it comes to SQL statement interpretation.

The easiest way to solve the problem is to create an alias for the field from within the SQL statement. if you have 3 tables, each with an identically named field, you can achieve the goal by using:

SELECT table1.field AS Field1, 
       table2.field AS Field2, 
       table3.field AS Field3...

now references can be made to the alias