strSQL

[Guest]

Hello,

I'm trying to build a query using strsql. The problem is in the where
clause. I have a field called identifiedstype and what I want is to add into
the where clause where identifiedstype = 'Person at scene' and [last name] =
somevalue. I'm prompted for an identifiedstype as if it's a parameter. My
code is below. Any assistance is greatly appreciated.


If Me.txtCrit1 > "" Then
If Me.txtOper1 = "like" Then
strSQL = "[" & Me.txtCrit1 & "]" & " " & (Me.txtOper1) & " '*" &
Trim(Me.txtVal1) & "*'" '
ElseIf Me.txtCrit1 = "Person At Scene Last Name" Then
strSQL = "[IdentifiedsType] = 'Person At Scene' And [last name] = " & "
'" & Trim(Me.txtVal1) & "'" '
ElseIf Me.txtCrit1 = "eventid" Or Me.txtCrit1 = "booknumber" Or
Me.txtCrit1 = "pagenumber" Or Me.txtCrit1 = "EVENTDATE" Or Me.txtCrit1 =
"ENTRYDATE" Then
strSQL = "[" & Me.txtCrit1 & "]" & " " & (Me.txtOper1) & "" &
(Me.txtVal1) & "" '
ElseIf Me.txtCrit1 > "" And Me.txtCrit1 <> "eventid" Or Me.txtCrit1 <>
"booknumber" Or Me.txtCrit1 <> "pagenumber" Or Me.txtCrit1 = "EVENTDATE" Or
Me.txtCrit1 = "ENTRYDATE" Then
strSQL = "[" & Me.txtCrit1 & "]" & " " & (Me.txtOper1) & "'" &
(Me.txtVal1) & "'" '
End If
End If

 

[George Nicholson]

Any assistance is greatly appreciated.

OK, lets have a fishing lesson. ("...teach a person how to fish...")

- Put a breakpoint on your last End If
- Run the code (form, etc.)
- When the breakpoint is reached, go to the VB editor
- Make sure the Immediate window is showing (View>Immediate)
- in the Immediate window, type: ? strSQL
- the result will be what you are telling Jet to do. Does it make sense?
right number of quotes, parentheses, etc.?
You should be able to take the resulting string, paste it into the query
designer's SQL view and have it run successfully.

I suspect your problems is in the number or placement of quotes.

HTH,