Problem retrieving value from Recordset 1

[RonMcNatt]

I'm trying to use a value from a Recordset as part of a string statement. When the code executes, I get the error message, too few parameters.

Dim db As Database
Dim strSQL As String
Dim rsinfo As Recordset
Dim slic As String

Set db = CurrentDb

strSQL = "SELECT * FROM zCenterList;"
Set rsinfo = db.OpenRecordset(strSQL)

rsinfo.MoveFirst

Do While Not rsinfo.EOF
slic = Trim(rsinfo.Fields("SLIC&quot)
'slic = "3040"
strSQL = "INSERT INTO zMaster_CBX ( SLIC, DEL_DATE )SELECT tim1" & slic & ".SLIC, tim13040.DEL_DATE, cbx3040.* FROM cbx3040 INNER JOIN tim13040 ON cbx3040.DISP_ID = tim13040.DISP_ID;"
CurrentDb.Execute (strSQL)

rsinfo.MoveNext
Loop

I can manually set the variable slic equal to a value (Ex. 3040) and it runs fine, I can set other variables in the string and it runs fine. It just will not work when using a value from the recordset. Any thoughts would be appreciated.

Ron

 

[ETID]

try this

slic = str$(Trim(rsinfo.Fields("SLIC&quot))

 

[RonMcNatt]

Thanks for the suggestion, but it didn't work. The error statement shows a space in the string in front of the "slic" value. Example it shows "tim1 3204" instead of "tim13204" where 3204 is the value of slic.

I've tried the statement without the trim, but get a space on the other side "tim13204 ".

Any other suggestion?

 

[ETID]

Hmmm...maybe I was dislexic, try this

slic = trim(str$(rsinfo.Fields("SLIC&quot))

 

[RonMcNatt]

Thanks for all your help.

Ron

 

[ETID]

It doesn't sound as though you were successful?!?

 

[RonMcNatt]

Well, I was, kinda.

You statement worked great, so thank you. Now my problem is that I have exceeded 2GB for my database, not exactly sure why, but it has corrupted my database.

Also, when the database is completely built, it will probably hold 3GB of data, apparently too large for Access. So I am searching for a way to handle this much data. Migrating to SQL is not an option.

Thanks again for your time.

 

[strongm]

Is MSDE an option?