Length of SQL

[Guest]

All,

I am trying to automate the generation of a 94 page report. The user has
already created all of the queries needed for the report and I am just
writing the code to tie everything together.

Everything has been going as expected with no problems until I got down to
the last 4 queries. I can run the query fine from design mode but when I
execute it from code I get the following error: "Data Type Mismatch in
Criteria Expression", error # 3464.

I execute by coping the sql to a string variable and then use it as part of
the recordset open command.

The sql statement returns a length of 1942. I am sure the problem is
associated with the length but I haven't found the answer yet.

thanks for your help all.

Terry

 

[Guest]

I thought I would provide the following code. I have used the same code for
the previous 60 queries and everyone of them ran fine. This one set runs
fine by itself but when ran from the code it errors out on the Set statement.

Dim QrySql As String
QrySql = db.QueryDefs(obj.Name).sql

Set rst = db.OpenRecordset(QrySql)

Hope this helps.

Terry

 

[Steve]

The problem is noy in the length of the SQL statement. The field you are
setting criteria for is a string and your criteria statement returns a
numeric value or vice versa; the field you are setting criteria for is a
numeric and your criteria statement returns a string value.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Ok, last post on this today since its Friday.

The string variable is passed to a sub routine if this matters at all.

Call UpdateExcel_Q19(QrySql, VideoComp)

Thanks and everyone enjoy their weekend.

Terry

 

[Douglas J. Steele]

If you're trying to create a recordset based on the query, why not just use:

Set rst = db.QueryDefs(obj.Name).OpenRecordset