Question on opening query to new recordset

[KC-Mass]

I have a module that walks down a file of unique claim numbers. Db is
Current db.

For each claim number I want it to retrieve, to a recordset all the
transactions related to that claim.

The Set process for the recordset is obviously wrong. I get an error of "
At least one parameter expected" Any ideas on fixing the query text.
strQuote = Chr$(34)

Set rsClaims = db.OpenRecordset("SELECT * from tblFromCSV WHERE
[rsclaims]![Claim_Number] = "_
& strQuote & [rsCNumb]![Claim_Number] & strQuote & strQuote)

Debug.Print rsClaims.EOF
Thx

Kevin

[rkc]

On Sep 11, 9:35*pm, "KC-Mass" <connearneyATcomcastDOTnetwrote:

I have a module that walks down a file of unique claim numbers. *Db is
Current db.

For each claim number I want it to retrieve, to a recordset all the
transactions related to that claim.

The Set process for the recordset is obviously wrong. *I get an error of "
At least one parameter expected" Any ideas on fixing the query text.
strQuote = Chr$(34)

*Set rsClaims = db.OpenRecordset("SELECT * from tblFromCSV WHERE
[rsclaims]![Claim_Number] = * "_
* * * & strQuote & [rsCNumb]![Claim_Number] & strQuote & strQuote)

* * * * Debug.Print rsClaims.EOF

If this kind of thing baffles you on a regular basis try writing a
test.

Sub wtf()
Dim strQuote As String
strQuote = Chr$(34)
Dim s
'stand in for [rsCNumb]![Claim_Number]
Dim p As String
p = "12321"

' shows stray quote at end of string
s = "SELECT * from tblFromCSV WHERE [rsclaims]![Claim_Number] =
" _
& strQuote & p & strQuote & strQuote

' looks better to me
's = "SELECT * from tblFromCSV WHERE [rsclaims]![Claim_Number] = "
& strQuote & p & strQuote

Debug.Print s
End Sub

[lyle fairfield]

rkc <rk*@rkcny.comwrote in
news:7f**********************************@x41g2000 hsb.googlegroups.com:

On Sep 11, 9:35*pm, "KC-Mass" <connearneyATcomcastDOTnetwrote:

>I have a module that walks down a file of unique claim numbers. *Db
is Current db.

For each claim number I want it to retrieve, to a recordset all the
transactions related to that claim.

The Set process for the recordset is obviously wrong. *I get an error
o

f "

>At least one parameter expected" Any ideas on fixing the query text.
strQuote = Chr$(34)

*Set rsClaims = db.OpenRecordset("SELECT * from tblFromCSV WHERE
[rsclaims]![Claim_Number] = * "_
* * * & strQuote & [rsCNumb]![Claim_Number] & strQuote & strQuote)

* * * * Debug.Print rsClaims.EOF

If this kind of thing baffles you on a regular basis try writing a
test.

Sub wtf()
Dim strQuote As String
strQuote = Chr$(34)
Dim s
'stand in for [rsCNumb]![Claim_Number]
Dim p As String
p = "12321"

' shows stray quote at end of string
s = "SELECT * from tblFromCSV WHERE [rsclaims]![Claim_Number] " _
& strQuote & p & strQuote & strQuote

' looks better to me
's = "SELECT * from tblFromCSV WHERE [rsclaims]![Claim_Number] = "
& strQuote & p & strQuote

Debug.Print s
End Sub

To further confuse things we might use single quotes in sql strings?

SELECT *
FROM Employees
WHERE [Last Name] = 'Cencini'
(north winds 2007)

so
s = "SELECT * from tblFromCSV
WHERE [rsclaims]![Claim_Number] = '" & p & "'"

but I'm puzzled by [rsclaims]![Claim_Number] in a query string.

Maybe I'm not getting the substance of this thread.

--
-
lyle fairfield

[rkc]

On Sep 12, 9:27*am, lyle fairfield <[e-mail]:

rkc <[e-mail] innews:7f**********************************@x41g20 00hsb.googlegroups.com:

On Sep 11, 9:35*pm, "KC-Mass" <connearneyATcomcastDOTnetwrote:

I have a module that walks down a file of unique claim numbers. *Db
is Current db.

For each claim number I want it to retrieve, to a recordset all the
transactions related to that claim.

The Set process for the recordset is obviously wrong. *I get an error
o

f "

At least one parameter expected" Any ideas on fixing the query text.
strQuote = Chr$(34)

*Set rsClaims = db.OpenRecordset("SELECT * from tblFromCSV WHERE
[rsclaims]![Claim_Number] = * "_
* * * & strQuote & [rsCNumb]![Claim_Number] & strQuote & strQuote)

* * * * Debug.Print rsClaims.EOF

If this kind of thing baffles you on a regular basis try writing a
test.

Sub wtf()
* * Dim strQuote As String
* * strQuote = Chr$(34)
* * Dim s
* * 'stand in for [rsCNumb]![Claim_Number]
* * Dim p As String
* * p = "12321"

* *' shows stray quote at end of string
* *s = "SELECT * from tblFromCSV WHERE [rsclaims]![Claim_Number] " _
* * * * *& strQuote & p & strQuote & strQuote

* ' looks better to me
* *'s = "SELECT * from tblFromCSV WHERE [rsclaims]![Claim_Number]= "
& strQuote & p & strQuote

* Debug.Print s
End Sub

To further confuse things we might use single quotes in sql strings?

SELECT *
FROM Employees
WHERE [Last Name] = 'Cencini'
(north winds 2007)

so
s = "SELECT * from tblFromCSV
WHERE [rsclaims]![Claim_Number] = '" & p & "'"

but I'm puzzled by [rsclaims]![Claim_Number] in a query string.

Maybe I'm not getting the substance of this thread.

--
-
lyle fairfield

I interpreted [rsclaims]![Claim_Number] as a field from a second
recordset.

[lyle fairfield]

rkc <rk*@rkcny.comwrote in
news:e8**********************************@d1g2000h sg.googlegroups.com:

I interpreted [rsclaims]![Claim_Number] as a field from a second
recordset.

Now I'm more puzzled.

A query string can reference a recordset!field?
--
-
lyle fairfield

[rkc]

On Sep 12, 11:12*am, lyle fairfield <[e-mail]:

rkc <[e-mail] innews:e8**********************************@d1g200 0hsg.googlegroups.com:

I interpreted *[rsclaims]![Claim_Number] as a field from a second
recordset.

Now I'm more puzzled.

A query string can reference a recordset!field?

Are you seriously puzzled?

The op was building a string using the value of a field from a
recordset.

[connearney]

On Sep 12, 11:12*am, lyle fairfield <[e-mail]:

rkc <[e-mail] innews:e8**********************************@d1g200 0hsg.googlegroups.com:

I interpreted *[rsclaims]![Claim_Number] as a field from a second
recordset.

Now I'm more puzzled.

Aquerystring can reference a recordset!field?
--
-
lyle fairfield

The reference to the "Claim_Number" should be just that. The
"rsclaim!" piece got there through some inartful copy and paste.

I now have it working as Set rsClaims = db.OpenRecordset("SELECT *
from tblFromCSV WHERE Claim_Number = " & strQuote & rsCNumb!
Claim_Number & strQuote)

Many thanks

[lyle fairfield]

rkc <rk*@rkcny.comwrote in
news:34**********************************@m3g2000h sc.googlegroups.com:

Are you seriously puzzled?

Not with

Set rsClaims = db.OpenRecordset("SELECT *
from tblFromCSV WHERE Claim_Number = " & strQuote & rsCNumb!
Claim_Number & strQuote)

which the op (maybe ... he used a different nick but I think the op)

just posted.

--
-
lyle fairfield