report Where criteria 1

[knownote]

A query results form's listbox with selected items opens these selected records in
either a form or reports when press spacebar. Problem is, sometimes both form and
report open to selected records; sometimes both result in:

Run-time error '3075':
Syntax error (missing operator) in query expression 'tblClients.Subgect
IN ('1Sample1-Bank','1Sample2-Credit Card')'.

Primary key is text (Subgect); CustomerID is defined as AutoNumber in tables and queries.
1Sample1-Bank and 1Sample2-Credit Card are text primary key Subgect of sample records.

I just copied the form Where criteria syntax as the report Where criteria syntax.
Could this cause the error? (starting at If strList <> "" Then)

---- CODE

Private Sub lstResult_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case KeyCode

Case 32 ' vbKeySpace key
KeyCode = 32 ' this "revaluation" kills escape key action
'Open frmCustomer based on Subgect(s) from lstResult listbox

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strList As String

strList = ""
Set frm = Forms!frmAndOr
Set ctl = frm!lstResult
With Forms!frmAndOr!lstResult
For Each varItm In ctl.ItemsSelected
strList = strList & ",'" & ctl.ItemData(varItm) & "'"
'Debug.Print ctl.ItemData(varItm)
Next varItm
End With
' strList = Left(strList, Len(strList) - 2)
If strList <> "" Then
If MsgBox("View results in form (Yes) or reports (No) view?", vbYesNo, "Format of Results") = vbYes Then
DoCmd.OpenForm "frmCustomer", acNormal, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
Else
DoCmd.OpenReport "rptSubjects2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
DoCmd.OpenReport "rptSubtopics2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
End If
End If

Case Else ' see key code generated by other keystrokes
'MsgBox KeyCode ' comment this out when it annoys you
End Select

End Sub

 

[grtammi]

sounds to me like where you're populating the values for 1Sample1-Bank and 1Sample2-Credit Card ... one of those 2 pieces of data has an apostrophe in it ... would explain why sometimes you get errors and sometimes not.

hth

Greg

"for me, the action is the juice.

 

[knownote]

Thanks, I missed your response this morning.

How could I change the Where portion
"tblClients.Subgect IN (" & Mid(strList, 2) & ")"
to include a variation of this from my

main form (to allow apostrophe initially)?
Private Sub Subject__BeforeUpdate(Cancel As Integer)
' Primary key is text (Subgect)

Dim strCriteria As String

strCriteria = "Subgect = """ & Me!Subgect &

 

[grtammi]

Change the following code:

For Each varItm In ctl.ItemsSelected
      strList = strList & ",'" & ctl.ItemData(varItm) & "'"
     'Debug.Print ctl.ItemData(varItm)
    Next varItm

to:
vba replace

For Each varItm In ctl.ItemsSelected
      strList = strList & ",'" & Replace(ctl.ItemData(varItm), "'", "''") & "'"
     'Debug.Print ctl.ItemData(varItm)
    Next varItm

and you should be ok. Basically you have to escape the single quotes out ... in Access you can do this by doubling them up.

Greg

"for me, the action is the juice.

 

[knownote]

Thanks, that looks like the solution. But, I'm using Access 97 - how can you create a Replace function to do the same thing?

 

[grtammi]

Have a look at this FAQ:

http://www.tek-tips.com/faqs.cfm?fid=711

Greg

"for me, the action is the juice.

 

[knownote]

Thanks for pointing me towards that faq. Looked at several concerning apostrophes/ single quotes, but missed this one.


---- code portion resolved

For Each varItm In ctl.ItemsSelected
' strList = strList & ",'" & ctl.ItemData(varItm) & "'"
strList = strList & ",'" & ReplaceString(ctl.ItemData(varItm), "'", "''") & "'"
'Debug.Print ctl.ItemData(varItm)
Next varItm

---- along with
Microsoft: Access Modules (VBA Coding) FAQ

How do I replace some text in as string with some other text string?
faq705-711 Posted: 7 May 01