[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Strange query error from form

I have created another form and really not much is different except I am referring to another query at the end of the code.  For some reason I get the

Run-time error '3142':

Characters found after end of SQL statement

The debug button takes me to

  CurrentDb.QueryDefs("TicketQuery").SQL = strSQL

Which is the second to last line in the code.  If I use the query that I was using with the other form it runs fine.

Any help is appreciated.
Private Sub Command9_Click()
Dim strSQL As String
Dim varSQL As String 'SQL is string not variant!

varSQL = "" 'explicitly set it to empty-string


  varSQL = "[TRADE_DATE] Between #" & [Forms]![TicketForm]![BeginDate] & "# And #" & [Forms]![TicketForm]![EndDate] & "#"

    'assumes [Field1] is a text field
    If Nz(Me.cboTicker, "") <> "" Then

        varSQL = (varSQL + " AND ") & "[TICKER] = " & Chr(34) & Me.cboTicker & Chr(34) 'this is a lot easier to maintain
    End If
      
    If Nz(Me.cboCUSIP, "") <> "" Then
       
        varSQL = (varSQL + " AND ") & "[CUSIP] = " & Chr(34) & Me.cboCUSIP & Chr(34) 'this is a lot easier to maintain
    End If
    
   If Nz(Me.cboSedol, "") <> "" Then
        
        varSQL = (varSQL + " AND ") & "[SEDOL] = " & Chr(34) & Me.cboSedol & Chr(34) 'this is a lot easier to maintain
    End If
    
   If Nz(Me.cboISIN, "") <> "" Then
      
        varSQL = (varSQL + " AND ") & "[ISIN_NO] = " & Chr(34) & Me.cboISIN & Chr(34) 'this is a lot easier to maintain
    End If

    
strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
    If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
    DoCmd.OpenQuery "TicketQuery", acViewNormal
End Sub

Open in new window

0
JohnMac328
Asked:
JohnMac328
  • 18
  • 11
  • 11
  • +3
1 Solution
 
Dale FyeCommented:
start out by putting a line right above that looks like:

debug.print strSQL

Then add a break point on the following line.

CurrentDb.QueryDefs("TicketQuery").SQL = strSQL

When the code breaks, copy the SQL string from the debug window and post it in a response here.

0
 
pdebaetsCommented:
If there is no "WHERE" clause, then you will be concatenating some text to the end of the SQL in the "TicketQuery" query. Try this:

strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
If InStr(strSQL, "WHERE") > 0 Then 
    strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
else
    strSQL = Left(strSQL, InStr(strSQL, ";") - 1)
endif
strSQL = strSQL & (" WHERE " + varSQL)
CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
DoCmd.OpenQuery "TicketQuery", acViewNormal

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Seems you would want this:

Dim strSQL As String
Dim varSQL As String 'SQL is string not variant!

varSQL = "" 'explicitly set it to empty-string


  varSQL = "[TRADE_DATE] Between #" & [Forms]![TicketForm]![BeginDate] & "# And #" & [Forms]![TicketForm]![EndDate] & "#"

    'assumes [Field1] is a text field
    If Nz(Me.cboTicker, "") <> "" Then

        varSQL = varSQL & " AND " & "[TICKER] = " & Chr(34) & Me.cboTicker & Chr(34) 'this is a lot easier to maintain
    End If
     
    If Nz(Me.cboCUSIP, "") <> "" Then
       
        varSQL = varSQL & " AND " & "[CUSIP] = " & Chr(34) & Me.cboCUSIP & Chr(34) 'this is a lot easier to maintain
    End If
   
   If Nz(Me.cboSedol, "") <> "" Then
       
        varSQL = varSQL & " AND " & "[SEDOL] = " & Chr(34) & Me.cboSedol & Chr(34)  'this is a lot easier to maintain
    End If
   
   If Nz(Me.cboISIN, "") <> "" Then
     
        varSQL = varSQL & " AND " & "[ISIN_NO] = " & Chr(34) & Me.cboISIN & Chr(34)  'this is a lot easier to maintain
    End If

   
strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
    If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
    DoCmd.OpenQuery "TicketQuery", acViewNormal
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Dale FyeCommented:
pdebaets,

I disagree, that line of code is designed to strip off the WHERE clause from the saved query, if there is one.

The following line, adds it back, if varSQL is not NULL

Dale
0
 
mbizupCommented:
Since you are appending to the SQL of an existing query, you may want to make sure that you remove any ending semicolon (which can define the 'end' of a query) .  You can do this by changing this line:

>> strSQL = CurrentDb.QueryDefs("TicketQuery").SQL

To this:

strSQL = Replace( CurrentDb.QueryDefs("TicketQuery").SQL,";","")
0
 
mbizupCommented:
As an aside to my last comment, you can also ensure that there is no semicolon at the end of your existing query ("TicketQuery").

If there is a semicolon at the end, remove it.  That will definitely cause this error if it is present.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And change this

strSQL = strSQL & (" WHERE " + varSQL)

to this:

strSQL = strSQL & (" WHERE " & varSQL)
0
 
Dale FyeCommented:

In the original code I provided for one of the OPs previous posts, varSQL was supposed to be a variant, because the critieria to be entered were not mandatory, and there was the possiblity that none of the criteria would have a value.

I'm guessing that the main problem was with a semi-colon at the end of the saved query that didn't contain a WHERE clause, as mbizup mentioned in one of the earlier posts.

0
 
Nick67Commented:
Your current code leaves out the part where I check to see if I am getting things right

'to test msgbox, comment it out after
'MsgBox varSQL
'is it right?


@fyed suggested debug.print strSQL

Both are the same idea.  Get that SQL to dsiplay someplace where you can read it!
You are also doing some funny stuff with your concatenation.
& is the string concatenator in VBA
+ also works, and can do some very neat things in conjunction with nulls, but some unexpected things in conjunction with numbers.
Read this article about that!
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2040-Concatenation-in-Access-Reports-and-Forms.html?sfQueryTermInfo=1+10+30+custom+format+harfang+nick67
As you are testing for null and empty-string, you shouldn't need those capabilities

Try this code:

Private Sub Command9_Click()
Dim strSQL As String
Dim varSQL As String

varSQL = "" 'explicitly set it to empty-string
varSQL = "[TRADE_DATE] Between #" & [Forms]![TicketForm]![BeginDate] & "# And #" & [Forms]![TicketForm]![EndDate] & "#"

'assumes the combo returns a string value, and the field is a text data type
If Nz(Me.cboTicker, "") <> "" Then
    varSQL = varSQL & " AND [TICKER] = " & Chr(34) & Me.cboTicker & Chr(34) 'this is a lot easier to maintain
End If
   
If Nz(Me.cboCUSIP, "") <> "" Then
    varSQL = varSQL & " AND [CUSIP] = " & Chr(34) & Me.cboCUSIP & Chr(34) 'this is a lot easier to maintain
End If
 
If Nz(Me.cboSedol, "") <> "" Then
     varSQL = varSQL + " AND [SEDOL] = " & Chr(34) & Me.cboSedol & Chr(34) 'this is a lot easier to maintain
End If
 
If Nz(Me.cboISIN, "") <> "" Then
     varSQL = varSQL + " AND [ISIN_NO] = " & Chr(34) & Me.cboISIN & Chr(34) 'this is a lot easier to maintain
End If

'to test msgbox, comment it out after
MsgBox varSQL
'is it right?
    
strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
If InStr(strSQL, "WHERE") > 0 Then
    strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
End If

'to test msgbox, comment it out after
MsgBox strSQL
'is it right?

DoCmd.OpenQuery "TicketQuery", acViewNormal
End Sub

Open in new window

0
 
pdebaetsCommented:
A "+" will work as well as an "&" when you are concatenating string data. In addition, a "+" is compatible with SQLServer.
0
 
JohnMac328Author Commented:
mbizup

I did this change

strSQL = Replace( CurrentDb.QueryDefs("TicketQuery").SQL,";","")

And it now gives "Syntax error in ORDER BY clause on this line


CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, there is this difference:

?"Peter" & Null
Peter

?"Peter" + Null
Null

But I think mbizup probably has the answer with the trailing semi colon.

mx
0
 
Nick67Commented:
<A "+" will work as well as an "&" when you are concatenating string data. In addition, a "+" is compatible with SQLServer. >
They do NOT work identically, and that can trip you up if you don't understand the differences in how they behave.

Go ahead and try it!
Function openit()
MsgBox "ampersand:" & Nz(Null & "3", "It is Null") 'Null or 3, which?
MsgBox "Plus:" & Nz(Null + "3", "it is Null")'Null or 3, which?
MsgBox "ampersand:" & "3" & "3" '33 or 6, which?
MsgBox "Plus:" & "3" + 3 '33 or 6, which?
End Function


Use & to concatenate strings in VBA unless you are specifically designing some null handling.
Otherwise, you may NOT get what you expected.
0
 
Nick67Commented:
The original code that @fyed and I helped the OP created deep-sixed the existing WHERE clause and anything to the right of it (like an order by clause) on a query and replaced it with the built up string.

strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
'deep-six the clause
strSQL = strSQL & (" WHERE " + varSQL)
'replace it

So it ain't a semi-colon that's the problem.
And the code doesn't touch a query with no WHERE clause
If InStr(strSQL, "WHERE") > 0 Then
so that isn't it either
0
 
pdebaetsCommented:
... when you are concatenating string data.
0
 
JohnMac328Author Commented:
Nick67

I changed it to this strSQL = strSQL & (" WHERE " + varSQL) and it still gives the same error
0
 
mbizupCommented:
JohnMac328,

Is there a semicolon at the end of your stored query ("TicketQuery")?
0
 
pdebaetsCommented:
After this line of code

strSQL = strSQL & (" WHERE " + varSQL)

Can you put a

Debug.Print strSQL

statement?

Then press Ctrl-G and copy and paste the SQL statement here.
0
 
JohnMac328Author Commented:
mbizup

Yes there is
0
 
mbizupCommented:
Okay -

Remove it, and try your original code again (revert the change I suggested earlier)
0
 
Nick67Commented:
My bad.

I didn't get all those + and (

Try
Private Sub Command9_Click()
Dim strSQL As String
Dim varSQL As String

varSQL = "" 'explicitly set it to empty-string
varSQL = "[TRADE_DATE] Between #" & [Forms]![TicketForm]![BeginDate] & "# And #" & [Forms]![TicketForm]![EndDate] & "#"

'assumes the combo returns a string value, and the field is a text data type
If Nz(Me.cboTicker, "") <> "" Then
    varSQL = varSQL & " AND [TICKER] = " & Chr(34) & Me.cboTicker & Chr(34) 'this is a lot easier to maintain
End If
   
If Nz(Me.cboCUSIP, "") <> "" Then
    varSQL = varSQL & " AND [CUSIP] = " & Chr(34) & Me.cboCUSIP & Chr(34) 'this is a lot easier to maintain
End If
 
If Nz(Me.cboSedol, "") <> "" Then
     varSQL = varSQL + " AND [SEDOL] = " & Chr(34) & Me.cboSedol & Chr(34) 'this is a lot easier to maintain
End If
 
If Nz(Me.cboISIN, "") <> "" Then
     varSQL = varSQL + " AND [ISIN_NO] = " & Chr(34) & Me.cboISIN & Chr(34) 'this is a lot easier to maintain
End If

'to test msgbox, comment it out after
MsgBox varSQL
'is it right?
    
strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
If InStr(strSQL, "WHERE") > 0 Then
    strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & " WHERE " & varSQL
    CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
End If

'to test msgbox, comment it out after
MsgBox strSQL
'is it right?

DoCmd.OpenQuery "TicketQuery", acViewNormal
End Sub

Open in new window

0
 
pdebaetsCommented:
the author's use of "+" in this statement

strSQL = strSQL & (" WHERE " + varSQL)

... is effective because if varSQL is null, then the " WHERE" string is eliminated, which is desirable.
0
 
JohnMac328Author Commented:
Nick67

It did not run at all
0
 
JohnMac328Author Commented:
When I remove the semicolon it just puts it back when I open it up again
0
 
Nick67Commented:
@pdebaets
<... when you are concatenating string data. >

You don't necessarily know if a control or variable is going to return null when you are concatenating.
If you use &, and you don't handle the null, you get an error-->which you then handle.
Use + and may be the code carries on in unexpected ways--and maybe very useful ways, too.
Which is the gist of the article.

But if you use + and not & routinely and unawarely, bad things will bite you!

<... is effective because if varSQL is null, then the " WHERE" string is eliminated, which is desirable. >

varSQL must NOT be null becuase the date parameters are NOT optional.
So, no, we don't want the WHERE clause to be eliminated
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

*****************
When I remove the semicolon it just puts it back when I open it up again
*************************

*********************
JohnMac328:  Please see mbizup's suggest @ http:#a36951763
***************************
0
 
JohnMac328Author Commented:
DatabaseMX

I did and she told me to put it back in ID: 36952070
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Not exactly.  She told you to remove it ... and you have to do that using the Replace Function on your SQL ... @ http:#a36951763

You can't do it in the query designer.  You need to do it On-The-Fly in code.

mx
0
 
JohnMac328Author Commented:
Ok, so what would the code be to replace it?
0
 
mbizupCommented:
< it just puts it back when I open it up again >

Do the criteria get added as you are expecting without error, with the added semicolon at the end of a new query?
0
 
JohnMac328Author Commented:
In the other form which is almost identical, I have a semicolon in the query and it does not error out
0
 
mbizupCommented:
Do all of these queries have WHERE clauses?

I'm guessing that this is erroring when you have a trailing semicolon and no WHERE clause.
0
 
JohnMac328Author Commented:
Yes - at the very minimum a begin date and end date
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
A single semi-colon as the very last character at the end of an SQL string - can be there or not. It doesn't matter.

But it seems you are first getting an existing SQL string from the QueryDef - which has the semi-colon by default (Access does this in the query designer), then concatenating addition SQL (WHERE) ... which is where the problem is ... there is now a semi-colon in the middle of the string.
0
 
pdebaetsCommented:
Try this:

Private Sub Command9_Click()
Dim strSQL As String
Dim varSQL As String 'SQL is string not variant!

varSQL = "" 'explicitly set it to empty-string

'pdebaets - Add one space here at the beginning:
  varSQL = " [TRADE_DATE] Between #" & [Forms]![TicketForm]![BeginDate] & "# And #" & [Forms]![TicketForm]![EndDate] & "#"

    'assumes [Field1] is a text field
    If Nz(Me.cboTicker, "") <> "" Then

        varSQL = (varSQL + " AND ") & "[TICKER] = " & Chr(34) & Me.cboTicker & Chr(34) 'this is a lot easier to maintain
    End If
      
    If Nz(Me.cboCUSIP, "") <> "" Then
       
        varSQL = (varSQL + " AND ") & "[CUSIP] = " & Chr(34) & Me.cboCUSIP & Chr(34) 'this is a lot easier to maintain
    End If
    
   If Nz(Me.cboSedol, "") <> "" Then
        
        varSQL = (varSQL + " AND ") & "[SEDOL] = " & Chr(34) & Me.cboSedol & Chr(34) 'this is a lot easier to maintain
    End If
    
   If Nz(Me.cboISIN, "") <> "" Then
      
        varSQL = (varSQL + " AND ") & "[ISIN_NO] = " & Chr(34) & Me.cboISIN & Chr(34) 'this is a lot easier to maintain
    End If

    
strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
If InStr(strSQL, "WHERE") > 0 Then 
    strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
else
    ' Check for semicolon
    strSQL = Left(strSQL, InStr(strSQL, ";") - 1)
endif
strSQL = strSQL & (" WHERE " + varSQL)
CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
DoCmd.OpenQuery "TicketQuery", acViewNormal

End Sub

Open in new window

0
 
JohnMac328Author Commented:
Then why does the original form work just fine?  It is almost identical
0
 
mbizupCommented:
Try revising the last few lines of code like this:

strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
    If InStr(strSQL, "WHERE") > 0 Then
           strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    Elseif Right(strSQL,1) = ";" Then
          strSQL = Left(strSQL,Len(strSQL)-1)
    end if
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
    DoCmd.OpenQuery "TicketQuery", acViewNormal
End Sub
0
 
JohnMac328Author Commented:
pdebaets:

Nothing happens
0
 
JohnMac328Author Commented:
Same error screen
0
 
pdebaetsCommented:
Oh! It just occurred to me that you might have an Order By clause in your SQL. Ignore that last posting of mine. Try this:

Private Sub Command9_Click()
Dim strSQL As String
Dim varSQL As String 'SQL is string not variant!

varSQL = "" 'explicitly set it to empty-string

'pdebaets - Add one space here at the beginning:
  varSQL = " [TRADE_DATE] Between #" & [Forms]![TicketForm]![BeginDate] & "# And #" & [Forms]![TicketForm]![EndDate] & "#"

    'assumes [Field1] is a text field
    If Nz(Me.cboTicker, "") <> "" Then

        varSQL = (varSQL + " AND ") & "[TICKER] = " & Chr(34) & Me.cboTicker & Chr(34) 'this is a lot easier to maintain
    End If
     
    If Nz(Me.cboCUSIP, "") <> "" Then
       
        varSQL = (varSQL + " AND ") & "[CUSIP] = " & Chr(34) & Me.cboCUSIP & Chr(34) 'this is a lot easier to maintain
    End If
   
   If Nz(Me.cboSedol, "") <> "" Then
       
        varSQL = (varSQL + " AND ") & "[SEDOL] = " & Chr(34) & Me.cboSedol & Chr(34) 'this is a lot easier to maintain
    End If
   
   If Nz(Me.cboISIN, "") <> "" Then
     
        varSQL = (varSQL + " AND ") & "[ISIN_NO] = " & Chr(34) & Me.cboISIN & Chr(34) 'this is a lot easier to maintain
    End If

   
strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
if InStr(strSQL, ";") > 0 then
    strSQL = Left(strSQL, InStr(strSQL, ";") - 1)
end if
If InStr(strSQL, "WHERE") > 0 Then
    strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
endif
if instr(strSQL,"ORDER BY") > 0 Then
    strSQL = Left(strSQL, InStr(strSQL,"ORDER BY") -1)
endif
'* (there might be more clauses to check for here!)
strSQL = strSQL & (" WHERE " + varSQL)
CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
DoCmd.OpenQuery "TicketQuery", acViewNormal

End Sub
0
 
pdebaetsCommented:
Please see my suggestion from ID:36952055 above.
0
 
JohnMac328Author Commented:
Still does not run pdebaets:
0
 
mbizupCommented:
When you click Debug on the error, go to the immediate pane in the vb editor and type

? strSQL

Post the text here.

Also if you are able to, upload a copy of your database.
0
 
JohnMac328Author Commented:
Here is the code
SELECT TS_ORDER.TRANS_TYPE, TS_ORDER.TRADE_DATE, TS_ORDER_ALLOC.TRADE_ID, TS_ORDER.SETTLE_DATE, TS_ORDER.EXEC_BROKER, CSM_SECURITY.TICKER, TS_ORDER.BROKER_REASON, TS_ORDER.ORDER_ID, TS_ORDER.TRADER, CSM_SECURITY.SEC_NAME, CSM_SECURITY.SEC_TYP_CD, CSM_SECURITY.CUSIP, CSM_SECURITY.SEDOL, CSM_SECURITY.ISIN_NO, CSM_SECURITY.VALORAN, TS_ORDER.DELIVERY_TYPE, CSM_SECURITY.LIST_EXCH_CD, CSM_SECURITY.EXT_SEC_ID, TS_ORDER.EXEC_PRICE, TS_ORDER.EXEC_QTY, TS_ORDER.EXEC_AMT, TS_ORDER_ALLOC.COMMISION_AMT, TS_ORDER_ALLOC.EXEC_QTY, TS_ORDER_ALLOC.EXEC_AMT, TS_ORDER_ALLOC.NET_PRIN_AMT, TS_ORDER.NET_PRIN_AMT, TS_ORDER_ALLOC.ACCT_CD, TS_ORDER_ALLOC.COMMISION_IND, TS_ORDER_ALLOC.COMMISION_RATE, TS_ORDER.PRIN_LOCAL_CRRNCY
FROM (TS_ORDER INNER JOIN CSM_SECURITY ON TS_ORDER.SEC_ID = CSM_SECURITY.SEC_ID) INNER JOIN TS_ORDER_ALLOC ON TS_ORDER.ORDER_ID = TS_ORDER_ALLOC.ORDER_ID
ORDER BY TS_ORDER_ALLOC.TRADE_ID DESC;
 WHERE [TRADE_DATE] Between #9/1/2010# And #12/1/2010# AND [TICKER] = "003670 KS" AND [CUSIP] = "003069119"
SELECT TS_ORDER.TRANS_TYPE, TS_ORDER.TRADE_DATE, TS_ORDER_ALLOC.TRADE_ID, TS_ORDER.SETTLE_DATE, TS_ORDER.EXEC_BROKER, CSM_SECURITY.TICKER, TS_ORDER.BROKER_REASON, TS_ORDER.ORDER_ID, TS_ORDER.TRADER, CSM_SECURITY.SEC_NAME, CSM_SECURITY.SEC_TYP_CD, CSM_SECURITY.CUSIP, CSM_SECURITY.SEDOL, CSM_SECURITY.ISIN_NO, CSM_SECURITY.VALORAN, TS_ORDER.DELIVERY_TYPE, CSM_SECURITY.LIST_EXCH_CD, CSM_SECURITY.EXT_SEC_ID, TS_ORDER.EXEC_PRICE, TS_ORDER.EXEC_QTY, TS_ORDER.EXEC_AMT, TS_ORDER_ALLOC.COMMISION_AMT, TS_ORDER_ALLOC.EXEC_QTY, TS_ORDER_ALLOC.EXEC_AMT, TS_ORDER_ALLOC.NET_PRIN_AMT, TS_ORDER.NET_PRIN_AMT, TS_ORDER_ALLOC.ACCT_CD, TS_ORDER_ALLOC.COMMISION_IND, TS_ORDER_ALLOC.COMMISION_RATE, TS_ORDER.PRIN_LOCAL_CRRNCY
FROM (TS_ORDER INNER JOIN CSM_SECURITY ON TS_ORDER.SEC_ID = CSM_SECURITY.SEC_ID) INNER JOIN TS_ORDER_ALLOC ON TS_ORDER.ORDER_ID = TS_ORDER_ALLOC.ORDER_ID
ORDER BY TS_ORDER_ALLOC.TRADE_ID DESC;
 WHERE [TRADE_DATE] Between ## And ##

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

**********  JohnMac328: ********************

Put a Breakpoint on this line of code ... so execution stop here

   CurrentDb.QueryDefs("TicketQuery").SQL = strSQL

Then, in the VBA Immediate Window, enter (including the question mark)

?strSQL  and hit return, such that we can see the SQL before it executes
0
 
JohnMac328Author Commented:
We better pick this up tomorrow - I have to go to a meeting.  Thanks for all the input from everyone
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Reformatted only

SELECT TS_ORDER.TRANS_TYPE,
    TS_ORDER.TRADE_DATE,
    TS_ORDER_ALLOC.TRADE_ID,
    TS_ORDER.SETTLE_DATE,
    TS_ORDER.EXEC_BROKER,
    CSM_SECURITY.TICKER,
    TS_ORDER.BROKER_REASON,
    TS_ORDER.ORDER_ID,
    TS_ORDER.TRADER,
    CSM_SECURITY.SEC_NAME,
    CSM_SECURITY.SEC_TYP_CD,
    CSM_SECURITY.CUSIP,
    CSM_SECURITY.SEDOL,
    CSM_SECURITY.ISIN_NO,
    CSM_SECURITY.VALORAN,
    TS_ORDER.DELIVERY_TYPE,
    CSM_SECURITY.LIST_EXCH_CD,
    CSM_SECURITY.EXT_SEC_ID,
    TS_ORDER.EXEC_PRICE,
    TS_ORDER.EXEC_QTY,
    TS_ORDER.EXEC_AMT,
    TS_ORDER_ALLOC.COMMISION_AMT,
    TS_ORDER_ALLOC.EXEC_QTY,
    TS_ORDER_ALLOC.EXEC_AMT,
    TS_ORDER_ALLOC.NET_PRIN_AMT,
    TS_ORDER.NET_PRIN_AMT,
    TS_ORDER_ALLOC.ACCT_CD,
    TS_ORDER_ALLOC.COMMISION_IND,
    TS_ORDER_ALLOC.COMMISION_RATE,
    TS_ORDER.PRIN_LOCAL_CRRNCY


FROM (TS_ORDER
    INNER JOIN CSM_SECURITY
        ON TS_ORDER.SEC_ID
                = CSM_SECURITY.SEC_ID)
    INNER JOIN TS_ORDER_ALLOC
        ON TS_ORDER.ORDER_ID
                = TS_ORDER_ALLOC.ORDER_ID


ORDER BY TS_ORDER_ALLOC.TRADE_ID DESC;
 

WHERE [TRADE_DATE] Between #9/1/2010#
        AND #12/1/2010#
        AND [TICKER]
                = "003670 KS"
        AND [CUSIP]
                = "003069119"


SELECT TS_ORDER.TRANS_TYPE,
    TS_ORDER.TRADE_DATE,
    TS_ORDER_ALLOC.TRADE_ID,
    TS_ORDER.SETTLE_DATE,
    TS_ORDER.EXEC_BROKER,
    CSM_SECURITY.TICKER,
    TS_ORDER.BROKER_REASON,
    TS_ORDER.ORDER_ID,
    TS_ORDER.TRADER,
    CSM_SECURITY.SEC_NAME,
    CSM_SECURITY.SEC_TYP_CD,
    CSM_SECURITY.CUSIP,
    CSM_SECURITY.SEDOL,
    CSM_SECURITY.ISIN_NO,
    CSM_SECURITY.VALORAN,
    TS_ORDER.DELIVERY_TYPE,
    CSM_SECURITY.LIST_EXCH_CD,
    CSM_SECURITY.EXT_SEC_ID,
    TS_ORDER.EXEC_PRICE,
    TS_ORDER.EXEC_QTY,
    TS_ORDER.EXEC_AMT,
    TS_ORDER_ALLOC.COMMISION_AMT,
    TS_ORDER_ALLOC.EXEC_QTY,
    TS_ORDER_ALLOC.EXEC_AMT,
    TS_ORDER_ALLOC.NET_PRIN_AMT,
    TS_ORDER.NET_PRIN_AMT,
    TS_ORDER_ALLOC.ACCT_CD,
    TS_ORDER_ALLOC.COMMISION_IND,
    TS_ORDER_ALLOC.COMMISION_RATE,
    TS_ORDER.PRIN_LOCAL_CRRNCY


FROM (TS_ORDER
    INNER JOIN CSM_SECURITY
        ON TS_ORDER.SEC_ID
                = CSM_SECURITY.SEC_ID)
    INNER JOIN TS_ORDER_ALLOC
        ON TS_ORDER.ORDER_ID
                = TS_ORDER_ALLOC.ORDER_ID


ORDER BY TS_ORDER_ALLOC.TRADE_ID DESC;  << Semi Colon here
 

WHERE [TRADE_DATE] Between ##    <<<< Extra Stuff her
        AND ##
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
In fact ... it's kind of a mess ....
0
 
pdebaetsCommented:
My last code example above throws away the Order By clause which isn't correct. The Order By clause should be preserved.

Still, it appears the Order By clause is not being discovered by the example code.

Can you post your database. I think that's the only way to get to the bottom of this one.
0
 
JohnMac328Author Commented:
Way too big to post - tomorrow I will post what the other form does and the code that it gives which is what I used to create this form
0
 
mbizupCommented:
JohnMac328,

Start out with known, good working SQL in "TicketQuery".  Make sure that the query works without error before testing the code.

Once you have working SQL, try the code again.

This is a sanity check... (If you add more SQL to existing bad SQL - this appears to be happening -, your results are not going to be good)
0
 
JohnMac328Author Commented:
TicketQuery works fine on it's own - just blows up from the form
0
 
Nick67Commented:
@mx

I suspect his new query doesn't have an existing WHERE clause or if it did, it's been bollixed up by the VBA code.
His first one did.
The code was predicated on finding, truncating and re-writing the WHERE clause
If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
What may have happened now is that the .SQL has been over-written by junk because of the failure of the code.

The damned + concatenation doesn't help matters, because he does want to create a WHERE clause, and if varSQL winds up null anywhere along the line, it ain't happening.

He'll FIRST need to rebuild his query in the editor to be correct, with a WHERE clause, and then debug.

@JohnMac328.
Your query USED to work fine.
Better open it.
When you use

CurrentDb.QueryDefs("TicketQuery").SQL = "Some String Value"

Everything that WAS in your query gets replaced.
And at the momemt, it's been replaced with junk
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, there SQL (reformatted) above is what it is ... just before execution ... and it is wrong.

mx
0
 
mbizupCommented:
<TicketQuery works fine on it's own>

I just wanted to verify that that the problem is not external to this function.   ie: if it is built and/or modified via code before being processed by this function, we want to isolate this function to test it.
0
 
pdebaetsCommented:
Please open the "TicketsQuery" query in design mode, switch to SQL view, then copy and paste that SQL here. That should serve as a good starting point.
0
 
Nick67Commented:
OK,

Since your original query is likely messed up by the failed attempts to tweak it in code, let's use @fyed'd suggestion from your original question and build the whole thing in code.
@mx has nicely cleaned up the SELECT, FROM and ORDER BY clauses, so this should work
Private Sub Command9_Click()
Dim strSQL As String
Dim varSQL As String

'now originally @fyed suggested building the SELECT statement out like it is shown here
strSQL = "SELECT TS_ORDER.TRANS_TYPE, TS_ORDER.TRADE_DATE, TS_ORDER_ALLOC.TRADE_ID, TS_ORDER.SETTLE_DATE,"
strSQL = strSQL & "TS_ORDER.EXEC_BROKER, CSM_SECURITY.TICKER, TS_ORDER.BROKER_REASON, TS_ORDER.ORDER_ID, TS_ORDER.TRADER,"
strSQL = strSQL & "CSM_SECURITY.SEC_NAME, CSM_SECURITY.SEC_TYP_CD, CSM_SECURITY.CUSIP, CSM_SECURITY.SEDOL, CSM_SECURITY.ISIN_NO,"
strSQL = strSQL & "CSM_SECURITY.VALORAN , TS_ORDER.DELIVERY_TYPE, CSM_SECURITY.LIST_EXCH_CD, CSM_SECURITY.EXT_SEC_ID, "
strSQL = strSQL & "TS_ORDER.EXEC_PRICE , TS_ORDER.EXEC_QTY, TS_ORDER.EXEC_AMT, TS_ORDER_ALLOC.COMMISION_AMT, TS_ORDER_ALLOC.EXEC_QTY, "
strSQL = strSQL & "TS_ORDER_ALLOC.EXEC_AMT , TS_ORDER_ALLOC.NET_PRIN_AMT, TS_ORDER.NET_PRIN_AMT, TS_ORDER_ALLOC.ACCT_CD, "
strSQL = strSQL & "TS_ORDER_ALLOC.COMMISION_IND , TS_ORDER_ALLOC.COMMISION_RATE, TS_ORDER.PRIN_LOCAL_CRRNCY"

'here's the FROM clause
strSQL = strSQL & " FROM (TS_ORDER INNER JOIN CSM_SECURITY ON TS_ORDER.SEC_ID = CSM_SECURITY.SEC_ID)"
strSQL = strSQL & " INNER JOIN TS_ORDER_ALLOC ON TS_ORDER.ORDER_ID = TS_ORDER_ALLOC.ORDER_ID"

'@Nick67 suggested that this is ugly, and if I want to change it, hard to do, since I HAVE to do it here.
'And NOT in the query editor
'But use of CurrentDb.QueryDefs("SomeQuery").SQL = "Some String" hose-bags the saved query if done wrong.
'And my first code did it wrong...
'So my first form uses @Nick67's idea.
'This one uses @fyed's

'now lets build the WHERE clause
varSQL = "" 'explicitly set it to empty-string
'These are manadatory date parameters
varSQL = "[TRADE_DATE] Between #" & [Forms]![TicketForm]![BeginDate] & "# And #" & [Forms]![TicketForm]![EndDate] & "#"

'assumes the combo returns a string value, and the field is a text data type
'skip if it is null or empty-string
If Nz(Me.cboTicker, "") <> "" Then
    varSQL = varSQL & " AND [TICKER] = " & Chr(34) & Me.cboTicker & Chr(34) 'this is a lot easier to maintain
End If
   
'skip if it is null or empty-string, add to what exists, if the control has a value
If Nz(Me.cboCUSIP, "") <> "" Then
    varSQL = varSQL & " AND [CUSIP] = " & Chr(34) & Me.cboCUSIP & Chr(34) 'this is a lot easier to maintain
End If
 
'skip if it is null or empty-string, add to what exists, if the control has a value
If Nz(Me.cboSedol, "") <> "" Then
     varSQL = varSQL & " AND [SEDOL] = " & Chr(34) & Me.cboSedol & Chr(34) 'this is a lot easier to maintain
End If
 
'skip if it is null or empty-string, add to what exists, if the control has a value
If Nz(Me.cboISIN, "") <> "" Then
     varSQL = varSQL & " AND [ISIN_NO] = " & Chr(34) & Me.cboISIN & Chr(34) 'this is a lot easier to maintain
End If

'Because we've built the whole query string in code, we are just going to replace everything
'so we don't need to test for a WHERE clause, or truncate it.
    
'strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
'If InStr(strSQL, "WHERE") > 0 Then
    'strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    'Just concatenate it altogether
    strSQL = strSQL & " WHERE " & varSQL & " ORDER BY TS_ORDER_ALLOC.TRADE_ID DESC;"

    'this replaces whatever was created in the Query Editor with the string created above
    'That is how this works!
    CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
'End If

'to test msgbox, comment it out after
MsgBox strSQL
'is it right?

DoCmd.OpenQuery "TicketQuery", acViewNormal
End Sub

Open in new window

0
 
pdebaetsCommented:
@Nick76
Yes, that's a much better approach. Hopefully that will end this thread.
0
 
Nick67Commented:
The SELECT and FROM clauses are a lot of ugliness in the VBA code--and they aren't meant to be dynamic in this case.
I prefer to keep them in the Query editor where they belongs.
It's much easier to view, tweak and maintain them there--but only if the code doesn't bollix them up.

Which in this case, it did.
The .SQL property of a QueryDef is a nice one to play with...if you're careful.
I use that technique with passthroughs to sprocs a lot

It makes for cleaner looking code.
This is uglier, but it should work.

It still doesn't have error handling for null dates, so and end-user could still build
WHERE [TRADE_DATE] Between ## And ##
but that will return nothing, so no harm, no foul
0
 
mbizupCommented:
I've been playing with this in a sample...

You should also refresh your TickerForm prior to using it's date values in your code (your last entered date  may be seen as null otherwise).

This code works with or without a where clause in your TicketQuery, as long as the TickerForm is open and as long as the query you start with is a good working query (not damaged by code either in this form or in others):

Dim strSQL As String
Dim varSQL As String 'SQL is string not variant!

varSQL = "" 'explicitly set it to empty-string

' Refresh the Ticket form
 Forms!TicketForm.Refresh
 
  '*** Add null handling here
  varSQL = "[TRADE_DATE] Between #" & Nz([Forms]![TicketForm]![BeginDate], #1/1/1900#) & "# And #" & Nz([Forms]![TicketForm]![EndDate], #1/31/2199#) & "#"

    'assumes [Field1] is a text field
    If Nz(Me.cboTicker, "") <> "" Then

        varSQL = (varSQL + " AND ") & "[TICKER] = " & Chr(34) & Me.cboTicker & Chr(34) 'this is a lot easier to maintain
    End If
      
    If Nz(Me.cboCusip, "") <> "" Then
       
        varSQL = (varSQL + " AND ") & "[CUSIP] = " & Chr(34) & Me.cboCusip & Chr(34) 'this is a lot easier to maintain
    End If
    
   If Nz(Me.cboSedol, "") <> "" Then
        
        varSQL = (varSQL + " AND ") & "[SEDOL] = " & Chr(34) & Me.cboSedol & Chr(34) 'this is a lot easier to maintain
    End If
    
   If Nz(Me.cboIsin, "") <> "" Then
      
        varSQL = (varSQL + " AND ") & "[ISIN_NO] = " & Chr(34) & Me.cboIsin & Chr(34) 'this is a lot easier to maintain
    End If

    
strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
    strSQL = Replace(strSQL, ";", "")  '***** Add this to get rid of trailing semicolons
    If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
    DoCmd.OpenQuery "TicketQuery", acViewNormal

Open in new window


Here's a sample, which is a much simplified version of your DB: Sample.mdb

The following line comits the data in the TicketForm, and can also be added to the code that Nick Posted, prior to any references to the date fields on that form:

' Refresh the Ticket form
 Forms!TicketForm.Refresh
0
 
Nick67Commented:
Here's a shim that demonstrates the use of the property (.SQL) that you are using.
Note that an attempt to use .SQL to create an unworkable query fails and leaves the existing SQL intact
SQL-Property.mdb
0
 
Dale FyeCommented:
Actually, I think the problem is that the stored query contains an ORDER BY clause, with out the WHERE clause.

So when you strip everything after the non-existent where you get:

SELECT yada, yada, yada FROM something Order BY  x, y, z

and when you append the where clause after that, you now have the WHERE and ORDER BY clauses out of sequence.

So, at the very end, try this:

    Dim intCharPos as integer
    Dim strOrderBy as string

    strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
    strSQL = Replace(strSQL, ";", "")  '***** Add this to get rid of trailing semicolons
    intCharPos = instr(strSQL, "ORDER BY")
    if intCharPos > 0 Then
        strOrderBy = Mid(strSQL, intCharPos)
        strSQL = Left(strSQL, intCharPos-1)
    End IF

    intCharPos = instr(strSQL, "WHERE")
    if intCharPos > 0 then strSQL = left(strSQL, intCharPos - 1)
    strSQL = strSQL & (" WHERE " + varSQL) & strOrderBy
    CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
    DoCmd.OpenQuery "TicketQuery", acViewNormal


0
 
Nick67Commented:
@fyed.
I think he messed the end of it up, too

strSQL = CurrentDb.QueryDefs("TicketQuery").SQL
    If InStr(strSQL, "WHERE") > 0 Then strSQL = Left(strSQL, InStr(strSQL, "WHERE") - 1)
    strSQL = strSQL & (" WHERE " + varSQL)
    CurrentDb.QueryDefs("TicketQuery").SQL = strSQL
    DoCmd.OpenQuery "TicketQuery", acViewNormal

That bolded line is a one line if then, which ain't good, and is probably part of the problem
If there is no WHERE clause then it'll boil down to

    strSQL = strSQL & (" WHERE " + varSQL)

Which is the entire SQL statement, Order By, semi-colon and all, with stuff added.
And that won't be good!

@JohnMac
Although the code I last posted should work, Does "TicketQuery" have at least one criteria in it to start with?
If it doesn't, your original code had no hope of working correctly.
It was designed to strip a pre-existing WHERE clause off and replace it

0
 
Dale FyeCommented:
Nick,

Don't know why you think the one line if statement is not good.  

That particular line is just fine, if you there is no ORDER BY clause, but if there is, then you need to parse that out of the SQL string before deleting the WHERE clause, thus my previous code.
0
 
Nick67Commented:
I think it's bad because one line if-then's are being deprecated and the whole block should have been in the THEN--so if there was no WHERE nothing got changed.  Which is how I wrote it originally to cover that possibility
0
 
JohnMac328Author Commented:
I was pretty frazzled two days ago.  I changed the TicketQuery to have at least one parameter and changed the code to your example and it worked.  Many thanks to all.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 18
  • 11
  • 11
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now