Question about WHERE syntax, errors with "Type Mismatch".

[Mac]

I am trying to generate a report using two combo boxes to select the search
fields and two text boxes to be the search parameters. This search feature
previously had only one fiield selector and one search parameter textbox and
worked OK. Now I am adding a second search parameter to improve searching
results, hence the AND. I have modified the WHERE statement below to filter
the report contents but I am not having success with the syntax. Is there
anything obviously wrong in the strWhere string? B.T.W I have separate
strWhere search strings for the date and number data types and those have the
same error. Also, Any suggestions on how to handle multiple data types
(number, date, and string) for building the strWhere string with two
different search parameters each being able to be any of those three data
types? Thanks in advance for your help.


strWhere = "[" & Me!cboField & "] ='" & Me![tboSearchInformation] & "'" And
"[" & Me!cboField2 & "] ='" & Me![tboSearchInformation2] & "'"

strDocName = "Recycled Interface Report"

'Open the report and use the strWhere string to filter the contents
DoCmd.OpenReport strDocName, acPreview, , strWhere

 

[Mac]

After playing with it for a bit I found an error in the syntax. See below I
had to remove the double-quotes on each side of the AND.

strWhere = "[" & Me!cboField & "] ='" & Me![tboSearchInformation] & "' And
[" & Me!cboField2 & "] ='" & Me![tboSearchInformation2] & "'"

I could still use some suggestions for handling two inputs with three
different data types and build the strWhere string dynamically.

 

[Gary Walter]

I don't know if this will help, but...

here be typical "quick-and-dirty" code example of
method I always use to construct a WHERE clause from
multiple form controls:

{point being on very first parameter we start with "WHERE"
and use "(1=1)" if it does not have a value}

Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT t.Type, t.tag, t.Name, " _
& "t.version, t.Description, t.Owner, " _
& "t.Status, t.Priority, t.uda_name, " _
& "t.uda_value " _
& "FROM tblReqsDetail AS t"

If Len(Trim(Me!cboType & "")) > 0 then
strWhere = " WHERE (t.[Type] = '" & me!cboType & "')"
Else
strWhere = " WHERE (1=1)"
End If

If Len(Trim(Me!cboOwner & "")) > 0 then
strWhere = strWhere & " AND (t.Owner = '" & me!cboOwner & "')"
Else
'no parameter
End If

If Len(Trim(Me!cboStatus & "")) > 0 then
strWhere = strWhere & " AND (t.[Status] ='" & me!cboStatus & "')"
Else
'no parameter
End If

If Len(Trim(Me!cboOriginator & "")) > 0 then
strWhere = strWhere & " AND (t.uda_value = '" & me!cboOriginator & "')"
Else
'no parameter
End If

Debug.Print strWhere

strSQL = strSQL & strWhere
'view new sql in Immediate window
Debug.Print strSQL

'(must have reference set to DAO)
'redefine your query
CurrentDb.QueryDefs("nameofyourquery").SQL = strSQL

'now do something with your new query

Code above assumes all your WHERE fields were
type TEXT...if any were not, then delete the
single quotes surrounding me!cboxxxx
(and use "#" instead if Date type)

After playing with it for a bit I found an error in the syntax. See below
I
had to remove the double-quotes on each side of the AND.

strWhere = "[" & Me!cboField & "] ='" & Me![tboSearchInformation] & "' And
[" & Me!cboField2 & "] ='" & Me![tboSearchInformation2] & "'"

I could still use some suggestions for handling two inputs with three
different data types and build the strWhere string dynamically.
--
Regards, Michael

I am trying to generate a report using two combo boxes to select the
search
fields and two text boxes to be the search parameters. This search
feature
previously had only one fiield selector and one search parameter textbox
and
worked OK. Now I am adding a second search parameter to improve searching
results, hence the AND. I have modified the WHERE statement below to
filter
the report contents but I am not having success with the syntax. Is there
anything obviously wrong in the strWhere string? B.T.W I have separate
strWhere search strings for the date and number data types and those have
the
same error. Also, Any suggestions on how to handle multiple data types
(number, date, and string) for building the strWhere string with two
different search parameters each being able to be any of those three data
types? Thanks in advance for your help.


strWhere = "[" & Me!cboField & "] ='" & Me![tboSearchInformation] & "'"
And
"[" & Me!cboField2 & "] ='" & Me![tboSearchInformation2] & "'"

strDocName = "Recycled Interface Report"

'Open the report and use the strWhere string to filter the contents
DoCmd.OpenReport strDocName, acPreview, , strWhere