sql statement length

[tmort]

I am putting together a long sql string from smaller sqlclaused from user supplied data as part of a search by form form.

Basically the sqlstring is:

SELECT strSQLClause1 & " " & strSQLClause2 & " " & strSQLClause3 & " " ... FROM...

It works with 12 strSQLClauses but if I exceed that (I have 36) I get an Error 2001 You canceled the previous operation error.

I've tried the code with and without line continuation characters and that doesn't matter. I get the same result.

I've read conflicting things about how long a SQL string can be.

Does anyone know how long the string can be and have a work around. The different claused are not static and can change with user input.

Thanks

 

[RuralGuy]

I would display the string with a MsgBox or Debug.Print statement to see what is going on. The error you describe is usually from not spelling something correctly but it could be from a poorly constructed string.

 

[tmort]

I'll check what the SQL statement actually works out to be. I'll also try leaving out the clause where I notice the problem starts and skipping ahead. Maybe there is some sort of field type mismatch

SELECT strSQLClause1 & " " & strSQLClause2 & " " strSQLClause3 & " " & strSQLClause(5)...FROM...

 

[RuralGuy]

Is your problem right at the beginning?
SELECT strSQLClause1
should be:
"SELECT " & strSQLClause1

 

[tmort]

I found the problem. I was using some old code and copying and pasting. I had forgotten to change one of the old variable names to the new one.

I also found I have another problem. I'm using Access to do a query by form. The actual data is in another database. I think it is in Oracle.

A clone of an existing database was cloned to create a new database to house some survey information. The structure of this database is not how I would set it up (then again I'm just beginning).

I would have set up a table with something like

question1
answer1
question2
answer2
question3
answer3
etc.

Then I would set say question one to a number format and the others to text.

This database is just setup with fields

Questionsequence
Question
Answer

All of the questions are mixed together in Question and all of the answers are mixed together in Answer.

I can massage things into a workable form except the format for Answers is set to text. Usually it is text but sometimes the answers are numbers.

One of the things I want to to is search by a number or > a number etc.

It looks like all I'll be able to do is search by a specific string.

I don't know much about Oracle but the structures and tables always seem to be put together in what in my limited experience appears to be awkward ways