Search form syntax problem 2

[bluegnu]

I've created a search form. It has 5 searchable fields, only the "SetRef" field below is a text field, the rest are combos. All fields unbound.

The search button I created has the following code:

Private Sub Search_Click()

Dim SearchCriteria As String


'Set Case Status Criteria
'-------------------------
Dim StatusCriteria

If Not IsNull([CaseStatus]) Then
    StatusCriteria = "(" + "[CaseComplete]=" & Me![CaseStatus] + ")" + " And "
    Else
    StatusCriteria = ""
End If


'Set Set Reference Search Criteria
'---------------------------
Dim SetRefCriteria
If Not IsNull([SetRef]) Then
    SetRefCriteria = "(" + "[Test Set Reference]=" & Me![SetRef] + ")" + " And "
    Else
    SetRefCriteria = ""
End If

'Set Prepared by Search Criteria
'---------------------------
Dim PrepCriteria

If Not IsNull([Prep]) Then
    PrepCriteria = "(" + "[Prepared by]=" & Me![Prep] + ")" + " And "
    Else
    PrepCriteria = ""
End If


'Set Case Priority Search Criteria
'---------------------------
Dim CasePriorityCriteria

If Not IsNull([CasePriority]) Then
    CasePriorityCriteria = "(" + "[Priority code]=" & Me![CasePrority] + ")" + " And "
    Else
    CasePriorityCriteria = ""
End If

'Set Case Tester Search Criteria
'---------------------------
Dim CaseTesterCriteria

If Not IsNull([CaseTester]) Then
   CaseTesterCriteria = "(" + "[Tester]=" & Me![CaseTester] + ")"
   Else
   CaseTesterCriteria = ""
   
End If


SearchCriteria = CasePriorityCriteria + StatusCriteria + SetRefCriteria + PrepCriteria + CaseTesterCriteria
DoCmd.OpenForm "Test Cases all", , , SearchCriteria
End Sub

but it doesn't work, when I search it tells me I have "Syntax Error (missing operator) inquery expression".

I've tried playing around with it, but I just can't get it to work. Any help is greatly appreciated.

 

[Golom]

I see two potential problems in this. The first is that you are creating conditions of the form
[tt]
[FieldName] = Value
[/tt]
Different data types need slightly different formats in this kind of specification. For example
[tt]
Numerics : [FieldName] = 6 No Delimiters
Text : [FieldName] = 'ABC' Single Quote Delimiters
Dates : [FieldName] = #05/15/2004# # Delimiters
[/tt]
Your formats are all set up using the numeric style and, if any of them are text or date types then the required delimiters are missing.

The second problem is that you have concatenated " And " to the end of your clauses (except the last one.) If the last clause (CaseTesterCriteria) is empty then you will have an "And" at the end without anything following it ... a syntax error. I suggest that you test the statement doing a
Debug.Print SearchCriteria before the DoCmd and look at the Immediate Window to see the code you are actually generating.

 

[bluegnu]

Thanks for the response.

I've played around a bit more with it. I've changed it to be similar to a search form that works, yet it mine still doesn't. How do you specify an empty numeric field? i.e. if text would be '' and date would be ## ?

 

[Golom]

> How do you specify an empty numeric field?

Depends somewhat on how your tables are defined. If the field does not allow nulls then a default value (usually zero) will appear. If it does allow NULLs then you need to test with
[tt]
[FieldName] IS NULL
[/tt]
or
[tt]
[FieldName] IS NOT NULL
[/tt]

 

[BobAtHome]

What Golom mentions about formats for different data types in the search string is correct.

I would approach building the search string differently. I would add to it as I tested each control for null - as follows.

Private Sub Search_Click()

Dim SearchCriteria As String
'set criteria to an empty string
SearchCriteria = ""


If Not IsNull([CaseStatus]) Then
'assuming that case complete is a string - need to put single quotes around it
SearchCriteria = "[CaseComplete]='" & Me![CaseStatus] + "'"
End If

If Not IsNull([SetRef]) Then
'check if criteria string is empty,if it is not then add AND
'assuming that set reference is a string - need single quotes
if SearchCriteria = "" then
SearchCriteria = "[Test Set Reference]='" & Me![SetRef] + "'"
Else
SearchCriteria = " and [Test Set Reference]='" & Me![SetRef] + "'"
end If
End If

If Not IsNull([Prep]) Then
'check if criteria string is empty,if it is not then add AND
'assuming that Prepared by is a string - need single quotes
if SearchCriteria = "" then
SearchCriteria = "[Prepared by]='" & Me![Prep] + "'"
Else
SearchCriteria = " and [Prepared by]='" & Me![Prep] + "'"
end If
End If


If Not IsNull([CasePriority]) Then
'check if criteria string is empty,if it is not then add AND
'assuming that Priority code is numeric - single quotes not needed
if SearchCriteria = "" then
SearchCriteria = "[Priority code]=" & Me![CasePrority]
Else
SearchCriteria = " and [Priority code]=" & Me![Prep]
end If
End If

If Not IsNull([CaseTester]) Then
'check if criteria string is empty,if it is not then add AND
'assuming that Tester is a string - need single quotes
if SearchCriteria = "" then
SearchCriteria = "[Tester]='" & Me![CaseTester] + "'"
Else
SearchCriteria = " and [Tester]='" & Me![CaseTester] + "'"
end If
End If


DoCmd.OpenForm "Test Cases all", , , SearchCriteria
End Sub

If none of the criteria fields were filled in, you would see all the records as the search string would be empty.

This method allows you to build the search string using only criteria that was entered.

Of course the whole thing gets more complicated if users do want to only records where a specific field is null.

 

[bluegnu]

Thanks for the help guys, but I'm really struggling here!

Bob, I've tried your code and it works fine for an empty search, or for the SetRef field. Other than that I either get told I have invalid syntax or that he "OpenForm action was canceled"

This leads me to think it has to be the type of fields I'm using:

SetRef is a text field
Priority Code, Tester and Prepared by are all lookup fields, with the stored value being numeric.
CaseComplete is a yes/no field. In the search I use a dropdown and pass -1 or 0 as the values.

I don't know if this sheds some light on my problem, but I really am grateful for you help

 

[bluegnu]

Sorry, just to elaborate a bit more. i've changed the code to reflect the field types and now it works fine on individual search items. However, if you have more than one search item there's a syntax error.

I believe this is because for each populated search field the code states ([tester] is the example used below]:

 Else
    SearchCriteria = " and [Tester]='" & Me![CaseTester] + "'"
  end If

So in the code is effectively over-riding the last search criteria. So if I search for SetRef 1000 and Tester 4 the string than gets passed is "and [Tester] = '4'" which means it ignores the previous search item. What I don't know is how to get it to store the preivous items?

thanks

 

[BobAtHome]

Sorry bluegnu - I did this on the rush and forgot to have the search string append to itself when it finds a criteria that has a value.

All you have to do is append the new part of the criteria to the current search string as shown below:

SearchCriteria = SearchCriteria & " and

in each of the 'else....end if' statements as shown in the correct code below.


Private Sub Search_Click()

Dim SearchCriteria As String
'set criteria to an empty string
SearchCriteria = ""


If Not IsNull([CaseStatus]) Then
'assuming that case complete is a string - need to put single quotes around it
SearchCriteria = "[CaseComplete]='" & Me![CaseStatus] + "'"
End If

If Not IsNull([SetRef]) Then
'check if criteria string is empty,if it is not then add AND
'assuming that set reference is a string - need single quotes
if SearchCriteria = "" then
SearchCriteria = "[Test Set Reference]='" & Me![SetRef] + "'"
Else
SearchCriteria = SearchCriteria & " and [Test Set Reference]='" & Me![SetRef] + "'"
end If
End If

If Not IsNull([Prep]) Then
'check if criteria string is empty,if it is not then add AND
'assuming that Prepared by is a string - need single quotes
if SearchCriteria = "" then
SearchCriteria = "[Prepared by]='" & Me![Prep] + "'"
Else
SearchCriteria = SearchCriteria & " and [Prepared by]='" & Me![Prep] + "'"
end If
End If


If Not IsNull([CasePriority]) Then
'check if criteria string is empty,if it is not then add AND
'assuming that Priority code is numeric - single quotes not needed
if SearchCriteria = "" then
SearchCriteria = "[Priority code]=" & Me![CasePrority]
Else
SearchCriteria = SearchCriteria & " and [Priority code]=" & Me![Prep]
end If
End If

If Not IsNull([CaseTester]) Then
'check if criteria string is empty,if it is not then add AND
'assuming that Tester is a string - need single quotes
if SearchCriteria = "" then
SearchCriteria = "[Tester]='" & Me![CaseTester] + "'"
Else
SearchCriteria = SearchCriteria & " and [Tester]='" & Me![CaseTester] + "'"
end If
End If


DoCmd.OpenForm "Test Cases all", , , SearchCriteria
End Sub

 

[bluegnu]

Cheers bob, that appears to have worked a gem.