SQL Statement Over 255 Chars 2

[jmeadows7]

I want to retrieve data from two tables using a join; however, the sql statement is over 255 characters long. I can break it into two different statements, but I wanted to know if there is a method to execute the statement without breaking it into two different queries.

Thanks,

 

[Savil]

You can shorten the SQL statement, If you have used the query design grid to create your code you will notice that it uses the format tablename.fieldname for everything as long as the name is unique from the all the tables in the query you can just specify the field name. There is a very slight hit in performance but in 99% of cases is not noticeable. I am intrigued about 255 though a string variable can be up to 2 billion characters?

 

[jsteph]

I think the SQL statement limit is 64K. I too wonder why the 255. jmeadows, are you storing this in a table of text-255?. You could use memo if you need to store this in a table.
--Jim

 

[jmeadows7]

I'm pretty sure a string can be longer than 255 characters, but when you try to execute the string as a SQL statement through VBA, it displays the error

Set rst = dbs.OpenRecordset(strSQL)

"Too few parameters. Expected 1"

I wrote the SQL from scratch and have taken out all the nice formatting spaces I like to use. It's a bare boned SQL expression.

But thanks for the help.

 

[PHV]

"Too few parameters. Expected 1"
Double check the spelling of the field names in your SQL string.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jmeadows7]

Hey PHV - thanks for the suggestion. The DBA spelled a field tax_paid_fl in one table I had already used and tax_pd_fl in the table I am using now.

So, I had the wrong spelling in my qry. I'll track him down and have him change the database to make it consistent if it's not too far past the point of no return!!

Thanks for everyone's suggestions!