HELP PLEASE

  • Thread starter Thread starter DBarker
  • Start date Start date
D

DBarker

I originally posted this and had no takers so now I am
reposting. Any help would be appreciated.


Below is the code that Elwin helped me with and it works
GREAT. Now I have an additional need. I have a date
field that I want to put before each Note instead of
the ; that now goes before. I know somewhat of what this
code is doing, but I am not sure where I would place
this. Any help would be appreciated.

Debbie
----------------------------------------------------------


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 STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID"
Set rstTo = dbs.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT STi_Multiples.* " _
& "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!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




..
 
Change one line to something like:

varNoteBatch = varNoteBatch & "; " _
& rdtFr!thedatfield & " " & rstFr!Journals
 
I originally posted this and had no takers so now I am
reposting. Any help would be appreciated.


Below is the code that Elwin helped me with and it works
GREAT. Now I have an additional need. I have a date
field that I want to put before each Note instead of
the ; that now goes before. I know somewhat of what this
code is doing, but I am not sure where I would place
this. Any help would be appreciated.

Just use the Format() function to convert the datefield to a text
string and include it:
Debbie
...
Do Until rstFr.NoMatch
varNoteBatch = varNoteBatch & Format(rstFr![datefield], "mm/dd/yyyy;") _
& rstFr!Journals
rstFr.FindNext strSQL
Loop
 
When I put that line in then it tells me that I do not
have enough parameters.

????????????????

Debbie
-----Original Message-----
Change one line to something like:

varNoteBatch = varNoteBatch & "; " _
& rdtFr!thedatfield & " " & rstFr!Journals
--
Marsh
MVP [MS Access]



I originally posted this and had no takers so now I am
reposting. Any help would be appreciated.


Below is the code that Elwin helped me with and it works
GREAT. Now I have an additional need. I have a date
field that I want to put before each Note instead of
the ; that now goes before. I know somewhat of what this
code is doing, but I am not sure where I would place
this. Any help would be appreciated.

Debbie
-------------------------------------------------------- --


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 STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID"
Set rstTo = dbs.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT STi_Multiples.* " _
& "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!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




.

.
 
DBarker said:
When I put that line in then it tells me that I do not
have enough parameters.

Where is thedatefield coming from?

Please do not paraphrase error messages. Their exact
wording is important to trying figure out what's going on.
What you said above might(?) mean that the recordset's query
is not running correctly and has nothing to do the line of
code I suggested you change.
--
Marsh
MVP [MS Access]



 
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




-----Original Message-----
DBarker said:
When I put that line in then it tells me that I do not
have enough parameters.

Where is thedatefield coming from?

Please do not paraphrase error messages. Their exact
wording is important to trying figure out what's going on.
What you said above might(?) mean that the recordset's query
is not running correctly and has nothing to do the line of
code I suggested you change.
--
Marsh
MVP [MS Access]



.
 
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




-----Original Message-----
DBarker said:
When I put that line in then it tells me that I do not
have enough parameters.

Where is thedatefield coming from?

Please do not paraphrase error messages. Their exact
wording is important to trying figure out what's going on.
What you said above might(?) mean that the recordset's query
is not running correctly and has nothing to do the line of
code I suggested you change.
--
Marsh
MVP [MS Access]



.
 
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?
--
Marsh
MVP [MS Access]


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




-----Original Message-----
DBarker said:
When I put that line in then it tells me that I do not
have enough parameters.

Where is thedatefield coming from?

Please do not paraphrase error messages. Their exact
wording is important to trying figure out what's going on.
What you said above might(?) mean that the recordset's query
is not running correctly and has nothing to do the line of
code I suggested you change.
--
Marsh
MVP [MS Access]



-----Original Message-----
Change one line to something like:

varNoteBatch = varNoteBatch & "; " _
& rdtFr!thedatfield & " " & rstFr!Journals


DBarker wrote:

I originally posted this and had no takers so now I am
reposting. Any help would be appreciated.


Below is the code that Elwin helped me with and it
works
GREAT. Now I have an additional need. I have a date
field that I want to put before each Note instead of
the ; that now goes before. I know somewhat of what
this
code is doing, but I am not sure where I would place
this.
.
 
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.

Sorry so confused.

Debbie

-----Original Message-----
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?
--
Marsh
MVP [MS Access]


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




-----Original Message-----
DBarker wrote:

When I put that line in then it tells me that I do not
have enough parameters.

Where is thedatefield coming from?

Please do not paraphrase error messages. Their exact
wording is important to trying figure out what's going on.
What you said above might(?) mean that the recordset's query
is not running correctly and has nothing to do the line of
code I suggested you change.
--
Marsh
MVP [MS Access]




-----Original Message-----
Change one line to something like:

varNoteBatch = varNoteBatch & "; " _
& rdtFr!thedatfield & " " & rstFr!Journals


DBarker wrote:

I originally posted this and had no takers so now I am
reposting. Any help would be appreciated.


Below is the code that Elwin helped me with and it
works
GREAT. Now I have an additional need. I have a date
field that I want to put before each Note instead of
the ; that now goes before. I know somewhat of what
this
code is doing, but I am not sure where I would place
this.
.

.
 
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]


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 said:
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?

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
 
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]


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 said:
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

.
 
Then the only thing I can see wrong is the missing brackets
(see below) in the first query.
 
Back
Top