Problems concatenating records that contain wildcards in data

[mrule9]

I'm having trouble attempting to concatenate records that contain wildcard caracters in the data. When the module is run, it usually hangs when it comes to a record that contains data with quotes or pound symbol. Below is the code for the module:

Public Sub Belts2()
On Error Resume Next
Dim db As Database
Dim model, Make, ACDPartnbr As String
Dim Parse As DAO.Recordset
Dim Parse2 As DAO.Recordset
Dim criteria As String
Dim firstyear, lastyear As String
Dim cnt As Long

Set db = CurrentDb()
Set Parse = db.OpenRecordset("tblbeltsGroupedByPMM", dbOpenDynaset)
Set Parse2 = db.OpenRecordset("tblMakeModelGrouping", dbOpenDynaset)
' This version modified by composition
'inputs: numericalindex
'outputs: tblMakeModelGrouping
'Date Written: 1/16/01
'date revised: 10/25/01
'reason:
'first loop grabs the first record in the index table sets the criteria to allow looping thru
'the rest of the table concatinating the models by make
'the record is then added to the output table
'the records are then removed from the input table and the loop contiues until eof
cnt = 0
DoEvents
If Parse.RecordCount > 0 Then
Do While Not Parse.EOF
model = ""
Parse.MoveFirst
ACDPartnbr = Parse!ACDPartnbr
Make = Parse!Make
'firstyear = Parse!firstyr
'lastyear = Parse!lastyr
criteria = ("Acdpartnbr = '" & ACDPartnbr & "' And make = '" & Make & "' &quot
' Parse.FindFirst (criteria)
Do Until Parse.NoMatch
If model = "" Then
model = Parse!model
Else
model = model & ", " & Parse!model
End If
Parse.FindNext (criteria)
Loop
Parse2.AddNew
Parse2!ACDPartnbr = ACDPartnbr
Parse2!Make = Make
Parse2!MODELs = model
'Parse2!firstyear = firstyear
'Parse2!lastyear = lastyear
Parse2.Update
'below code removes all the records from the input table
'that have been concatinated and inserted into the output table
Parse.FindFirst (criteria)
Do Until Parse.NoMatch
Parse.Delete
Parse.FindNext (criteria)
Loop
Loop
Beep
MsgBox "Make and Model Compile Complete", , "Consolidate Models"
End If
End Sub

Is there any way around this problem, other than removing the wildcard characters????

 

[jebry]

Hi!

Go to a module an define this constant in the General Declarations section:

Public Const Quote = """"

Use the constant as in the following example:

criteria = "Acdpartnbr = " & Quote & ACDPartnbr & Quote & " And make = " & Quote & Make & Quote & " "

This should allow any character as part of your criteria.

hth
Jeff Bridgham
bridgham@purdue.edu