Limit on Variables?

[LQ]

Not sure if I'm using the correct terminology here...Basically, I am using a search form to build an SQL statement "on the fly". The resulting statement is a variable named Mysql. What I have discovered (by using the debug window) is that if I put in lots of different criteria in my search form, some of the SQL statement stored in mySQL looks like its been cut off, and the records returned are not what has been specified in the search form. I tried to look up storage size of a string in the help files and all it says is "length of string." So, is there a limit on how long a string a variable can hold, and if so, what can I do to get around this? This will turn into a big problem if I can't figure this out!

Thanks in advance for any input...

[This message has been edited by LQ (edited 10-17-2001).]

 

[Travis]

There is a limit in the Size of a Built SQL String (I think it is 2664 characters Don't quote me though). Check the Length (LEN) of your SQL String.

You might be able to get around this by building a QueryDef through Code.

 

[LQ]

Thanks for the reply, Travis. Let me make sure I am doing this correctly...I put a breakpoint in the code and then in the bottom of the debug window, typed in len(mysql) and when I let the mouse hover over it, it told me the length was 807. But in the top pane of the debug window, I saw that the sql statement was being truncated and when I removed the breakpoint and let the thing run, it did not return the correct records.

This is making me crazy...I have to figure this out. I was hoping not to have to use Querydef b/c I've never done it before. But in any case, if I am somehow reaching the limit of a built SQL string with my current statement, wouldn't I have the same problem with Querydef? I can't help thinking that there has to be something else wrong here!

Thanks for any suggestions.

[This message has been edited by LQ (edited 10-19-2001).]

 

[LQ]

OK, I think I figured out how to use Querydef to create a dynamic SQL statement, but I can't figure out if there is a way to open a form or report using that query as a criteria?

And if that can't be done, I *did* figure out my initial problem...the SQL statement is not being truncated, it is just that you can't view the entire thing in the debug window. My real problem is that the logic of my SQL statement is wrong (and it's wrong in both the query created by QueryDef and in my initial query). Is there a way to insert parenthesis between parts of the statement so that my ANDs and ORs are read the way they should be? Here is an example of part of my code:

Private Sub cmdrunrpt_Click()
Dim mySql As String
'this creates a dynamic SQL statement that changes with each new criteria
Dim Addand As String
Addand = ""
'This will be added after the first criterion
mySql = "SELECT * FROM [letter log] WHERE "
If Nz(Forms!frmnewrpt!txtdate1) <> "" And Nz(Forms!frmnewrpt!txtdate2) <> "" Then
mySql = mySql & Addand & "[Date of action] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2 "
Addand = "OR "
End If
If Nz(Forms!frmnewrpt!txtdate1) <> "" And Nz(Forms!frmnewrpt!txtdate2) <> "" Then
mySql = mySql & Addand & "[letter2dateentered] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2 "
Addand = "OR "
End If
If Nz(Forms!frmnewrpt!txtdate1) <> "" And Nz(Forms!frmnewrpt!txtdate2) <> "" Then
mySql = mySql & Addand & "[letter3dateentered] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2 "
Addand = "AND "
End If

The problem is, these three statements querying the date field need an "OR" between them and they need to be in parenthesis (because there are other criteria that will follow). I can't figure out how to do it! I keep getting "data mismatch" and other errors.

Sorry for the length of this post, but this has been driving me crazy for the past three days!



[This message has been edited by LQ (edited 10-19-2001).]

 

[Travis]

Make life a little easier on yourself:

Dim strWhere As String

If Nz(Forms!frmnewrpt!txtdate1) <> "" And Nz(Forms!frmnewrpt!txtdate2) <> "" Then
strWhere = strWhere & IIf(strWhere <> "", " Or ", " ") & "([Date of action] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2 )"
End If

If Nz(Forms!frmnewrpt!txtdate1) <> "" And Nz(Forms!frmnewrpt!txtdate2) <> "" Then
strWhere = strWhere & IIf(strWhere <> "", " Or ", " ") & "([letter2dateentered] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2 )"
End If

If Nz(Forms!frmnewrpt!txtdate1) <> "" And Nz(Forms!frmnewrpt!txtdate2) <> "" Then
strWhere = strWhere & IIf(strWhere <> "", " Or ", " ") & "([letter3dateentered] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2 )"
End If

mySQL = mySQL & " " & strWhere

 

[LQ]

Thanks for your help, Travis.

This is what I ended up doing in order to get my SQL statement to make logical sense...

Private Sub cmdrunrpt_Click()
'this creates a dynamic SQL statement that changes with each new criteria

Dim mySql As String
Dim myWhere As String

'search the [letter log] table
mySql = "SELECT * FROM [letter log]"
myWhere = ""

If Nz(Forms!frmnewrpt!txtdate1) <> "" And Nz(Forms!frmnewrpt!txtdate2) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([Date of action] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2"
myWhere = myWhere & " OR [letter2dateentered] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2"
myWhere = myWhere & " OR [letter3dateentered] between Forms!frmnewrpt!txtdate1 and Forms!frmnewrpt!txtdate2)"
End If
If Nz(Forms!frmnewrpt!cboIssuestatus) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([issuestatus] Like '*" & Forms!frmnewrpt!cboIssuestatus & "*')"
End If
If Nz(Forms!frmnewrpt!cbolettertype) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([Type of letter] Like '*" & Forms!frmnewrpt!cbolettertype & "*'"
myWhere = myWhere & " OR [letter2type] Like '*" & Forms!frmnewrpt!cbolettertype & "*'"
myWhere = myWhere & " OR [letter3type] Like '*" & Forms!frmnewrpt!cbolettertype & "*')"
End If
If Nz(Forms!frmnewrpt!txtletterdate1) <> "" And Nz(Forms!frmnewrpt!txtletterdate2) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([letter date] between Forms!frmnewrpt!txtletterdate1 and Forms!frmnewrpt!txtletterdate2"
myWhere = myWhere & " OR [letter2date] between Forms!frmnewrpt!txtletterdate1 and Forms!frmnewrpt!txtletterdate2"
myWhere = myWhere & " OR [letter3date] between Forms!frmnewrpt!txtletterdate1 and Forms!frmnewrpt!txtletterdate2)"
End If
If Nz(Forms!frmnewrpt!cboTOS1) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([TOS Billed 1] Like '*" & Forms!frmnewrpt!cboTOS1 & "*'"
myWhere = myWhere & " OR [TOS Billed 2] Like '*" & Forms!frmnewrpt!cboTOS1 & "*')"
End If
If Nz(Forms!frmnewrpt!cboTOS2) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([TOS Billed 1] Like '*" & Forms!frmnewrpt!cboTOS2 & "*'"
myWhere = myWhere & " OR [TOS Billed 2] Like '*" & Forms!frmnewrpt!cboTOS2 & "*')"
End If
If Nz(Forms!frmnewrpt!cboOutcome) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([Outcome] Like '*" & Forms!frmnewrpt!cboOutcome & "*')"
End If
If Nz(Forms!frmnewrpt!cboreason) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([Outcomereason] Like '*" & Forms!frmnewrpt!cboreason & "*')"
End If
If Nz(Forms!frmnewrpt!cboHISstatus) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([HISstatus] Like '*" & Forms!frmnewrpt!cboHISstatus & "*')"
End If
If Nz(Forms!frmnewrpt!CBOCMstatus) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([CMstatus] Like '*" & Forms!frmnewrpt!CBOCMstatus & "*')"
End If
If Nz(Forms!frmnewrpt!cboPFSstatus) <> "" Then
If myWhere <> "" Then
myWhere = myWhere & " AND"
End If
myWhere = myWhere & " ([PFSstatus] Like '*" & Forms!frmnewrpt!cboPFSstatus & "*')"
End If

'add WHERE statement if any search criteria are provided
If myWhere <> "" Then
mySql = mySql & " WHERE " & myWhere
End If

DoCmd.OpenReport "rptletterdate", acViewPreview, mySql
DoCmd.Close acForm, "frmnewrpt"
End Sub