Re: HELP PLEASE

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: DBarker (DBarker_at_discussions.microsoft.com)
Date: 03/09/04 

Date: Tue, 9 Mar 2004 07:31:05 -0800

The CaseID field in both tables is a numeric field.

Debbie

>-----Original Message-----
>Check the first query in your posted code more
carefully.
>
>Are you sure that the CaseID field is a numeric type?
>
>I don't have any idea what you mean about
a "parameter". I
>can't discern the use of any parameters.
>--
>Marsh
>MVP [MS Access]
>
>
>
>DBarker wrote:
>>I am confused, your syntax is the same as mine. I
don't
>>think so, it seems that there has to be an additional
>>parameter added but I am not sure where exactly.
>>
>>
>>Marshall Barton wrote:
>>>You forgot the brackets around Journal Date in at
least
>>>one place:
>>>
>>> strSQL = "SELECT [Journal Date], Journals, " _
>>>
>>>Is all this trouble caused by you're use of space
>>>characters in names?
>>>
>>>
>>>DBarker wrote:
>>>>This is the error message:
>>>>Run-Time Error 3075
>>>>Syntax Error (Missing operator) in query
>>>>expression "Journal Date"
>>>>
>>>>I have tried putting journal date in brackets and
just
>>>>with an underscore separating the two words.
>>>>
>>>>Below is the entire code
>>>>-------------------------------------
>>>>
>>>>Public Sub Combine()
>>>>
>>>> Dim strSQL As String
>>>> Dim dbs As DAO.Database
>>>> Dim rstFr As DAO.Recordset
>>>> Dim rstTo As DAO.Recordset
>>>> Dim varNoteBatch As Variant
>>>>
>>>> Set dbs = CurrentDb()
>>>> strSQL = "SELECT Journal Date, Journals, " _
>>>> & "FROM STi_Table " _
>>>> & "ORDER BY CaseID"
>>>> Set rstTo = dbs.OpenRecordset(strSQL, dbOpenDynaset)
>>>> strSQL = "SELECT [Journal Date], Journals, " _
>>>> & "FROM STi_Multiples " _
>>>> & "ORDER BY CaseID"
>>>> Set rstFr = dbs.OpenRecordset(strSQL,
dbOpenSnapshot)
>>>> Do Until rstTo.EOF
>>>> varNoteBatch = Null
>>>> strSQL = "[CaseID]=" & rstTo!CaseID
>>>> rstFr.FindFirst strSQL
>>>> Do Until rstFr.NoMatch
>>>> varNoteBatch = varNoteBatch & "; " _
>>>> & rstFr![Journal Date] & " " & rstFr!Journals
>>>> rstFr.FindNext strSQL
>>>> Loop
>>>> rstTo.Edit
>>>> rstTo!Journals = varNoteBatch
>>>> rstTo.Update
>>>> rstTo.MoveNext
>>>> Loop
>>>>
>>>> rstFr.Close
>>>> rstTo.Close
>>>> Set rstFr = Nothing
>>>> Set rstTo = Nothing
>>>>End Sub
>
>.
>