Storing more than 255 chars in a string

[mdweezer]

This ties in with my other problem. Here we go.

I'm generating code for a report and storing it in one long string, conatanating each line with the next. Most of it is just a SQL query taken straight from the qry editor, the other stuff is parsing data from passed information from another form. Here is a small snip from the huge query:

qry = qry & "Issues.IssueNumber = TBL_ASU_FBM_FUNCTIONS.IssueNumber "
qry = qry & "WHERE (((Issues.[Status Code])<>""CL - Closed"")) "
qry = qry & "AND (((AutoCorrelation) = True)) "

' and then in the end using the following to execute it
Me.RecordSource = qry

However, my qry has become so long it no longer wants to fit in the string. I can imagine there has to be a way to work this out, just beyond the scope of my access smarts!

Thanks in advance

 

[mdweezer]

I may have answered my own question looking in the FAQ

I was aware of the memo field, just not sure how it would react in this scenario but i'll give it a try!

 

[mdweezer]

Just like I thought I was not able to dimension the variable as a memo, only a string.

Sorry for the quick responses by me, i'm bouncing back and forth between resources (which this is definitely the best one)

 

[lespaul]

Don't think that will work for you mdweezer! The memo field is a type of field that you can use in a table, I don't think you can use it in your code. You should be able to reduce your query string size by using alias names for the tables:

SELECT FirstTable.Field1, SecondTable.Field2, SecondTable.Field3, SomeReallyLongTableName.Field4
FROM FirstTable
INNER JOIN SecondTable ON SecondTable.Field1 = FirstTable.Field1
INNER JOIN SomeReallyLongTableName ON SomeReallyLongTableName.Field2 = FirstTable.Field2
WHERE SomeReallyLongTableName.Field4 = "Something"

by using alias names this can be reduced to

SELECT F.Field1, S.Field2, S.Field3, R.Field4
FROM FirstTable As F
INNER JOIN SecondTable As S ON S.Field1 = F.Field1
INNER JOIN SomeReallyLongTableName As R ON R.Field2 = F.Field2
WHERE R.Field4 = "Something"

Leslie

 

[mdweezer]

I think I've got it cut down as much as I can...

What I then did was the following.

I have 20 fields I need to use, if they're true then add the line "OR yada yadda yadda" if not just skip to the next field. So I have a bunch of the following:

qry = qry & "WHERE (((Issues.[Status Code])<>""CL - Closed"")) "
If Field1 = True Then
qry = qry & "OR (((Field1) = True)) "
End If
If Field2 = True Then
qry = qry & "OR (((Field2) = True)) "
End If

...

I did that to cut down on size as well but now what I want to do is show any records that are not closed (first where statement) AND has field 1 or field 2 or field 3 ... field 20.

So:

Not Closed
and
field1
or field2
or field3
...

But right now if field2 is checked but it's closed, it still shows so I need to have it pick up both clauses saying that it's not closed and does involve check 2, or any of the other checks that might be checked.

It's pretty confusing to explain, but i'm trying my best

 

[PHV]

To shorten your query string, use table aliases and get rid of all the spurious parens added by the query builder.

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

 

[cmmrfrds]

Depending on how you are going to use the string variable, you could make it a variant instead, or make several string variables and concatnate later.

For instance to use as the record source in a Form.

Me.RecordSource = var1 & var2

 

[PHV]

Try something like this:
qry = qry & "WHERE Issues.[Status Code]<>'CL - Closed' AND ("
If Field1 Then
qry = qry & "Field1 OR "
End If
...
If Field20 Then
qry = qry & "Field2 OR "
End If
qry = Left(qry, Len(qry) - 4) & ");"


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

 

[lespaul]

but if you still have:

Issues.[Status Codes]

you haven't used any alias names!

Somewhere you have the table Issues, either in a FROM clause or a JOIN

FROM Issues I
INNER JOIN Issues I


then anywhere in your string that you reference the table Issues you can replace that with the letter I:

I.[Status Codes]

Leslie