By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Question on opening query to new recordset

P: n/a
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
Sep 12 '08 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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


Sep 12 '08 #2

P: n/a
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
Sep 12 '08 #3

P: n/a
rkc
On Sep 12, 9:27*am, lyle fairfield <lylef...@yah00.cawrote:
rkc <r...@rkcny.comwrote 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.
Sep 12 '08 #4

P: n/a
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
Sep 12 '08 #5

P: n/a
rkc
On Sep 12, 11:12*am, lyle fairfield <lylef...@yah00.cawrote:
rkc <r...@rkcny.comwrote 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.
Sep 12 '08 #6

P: n/a
On Sep 12, 11:12*am, lyle fairfield <lylef...@yah00.cawrote:
rkc <r...@rkcny.comwrote 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
Sep 12 '08 #7

P: n/a
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
Sep 12 '08 #8

This discussion thread is closed

Replies have been disabled for this discussion.