If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Invalid Syntax
What happens when you simply open the query, as opposed to the report?
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Andre Adams" wrote in message ... Thank you so much Mr. Douglas, I no longer recieve the parameter check, but when the report comes up it shows and #Error Message in all of the columns. Any reason why it would do that? Andre Adams "Douglas J. Steele" wrote: (([Execution])=IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null))); won't work. You cannot use = Null when checking whether a value is Null or not, you must use Is Null. If what you're trying to do is add a computed field Execution to your query, replace the [Execution] AS Expr1 with IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null) AS Execution (and remove the reference to Execution from the WHERE clause) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Andre Adams" wrote in message ... Here it is John, SELECT tblTradeListingGroup1.RepID, tblTradeListingGroup1.[Acct #], tblTradeListingGroup1.[Account Name], tblTradeListingGroup1.Symbol, tblTradeListingGroup1.SettleDate, tblTradeListingGroup1.Tran, tblTradeListingGroup1.[Shrs/Contr], tblTradeListingGroup1.TradePrice, tblTradeListingGroup1.[Commission Amount], tblTradeListingGroup1.[Clearing Charge], tblTradeListingGroup1.Bltr, [Execution] AS Expr1 FROM tblTradeListingGroup1 WHERE (((tblTradeListingGroup1.SettleDate)=[What Settlement Date do you wish to view?]) AND (([Execution])=IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null))); Also, the parameter value, it's asking me for one for the Execution. I have no idea what you say when asking for the recordsource. Let me know where I can find this information! "John W. Vinson" wrote: On Thu, 24 May 2007 10:34:02 -0700, Andre Adams wrote: Hey John, Thanks for you prompt response! I've tried your suggestion and it's prompting and "Enter parameter value" into the fold. I check the equation and it's entered exactly the way that you sent it to me, does this error mean that it can't be done within my report? No; it means you're making a mistake. Easy to do, you're working at two or three different levels here! Please open the Query. Select View... SQL. Copy and paste the SQL text to a message here. Also post the parameter value that it's asking for, and the Recordsource property of your Report. John W. Vinson [MVP] |
#12
|
|||
|
|||
Invalid Syntax
I'm in the query now. That's the message it gives me within the query.
"Douglas J. Steele" wrote: What happens when you simply open the query, as opposed to the report? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Andre Adams" wrote in message ... Thank you so much Mr. Douglas, I no longer recieve the parameter check, but when the report comes up it shows and #Error Message in all of the columns. Any reason why it would do that? Andre Adams "Douglas J. Steele" wrote: (([Execution])=IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null))); won't work. You cannot use = Null when checking whether a value is Null or not, you must use Is Null. If what you're trying to do is add a computed field Execution to your query, replace the [Execution] AS Expr1 with IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null) AS Execution (and remove the reference to Execution from the WHERE clause) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Andre Adams" wrote in message ... Here it is John, SELECT tblTradeListingGroup1.RepID, tblTradeListingGroup1.[Acct #], tblTradeListingGroup1.[Account Name], tblTradeListingGroup1.Symbol, tblTradeListingGroup1.SettleDate, tblTradeListingGroup1.Tran, tblTradeListingGroup1.[Shrs/Contr], tblTradeListingGroup1.TradePrice, tblTradeListingGroup1.[Commission Amount], tblTradeListingGroup1.[Clearing Charge], tblTradeListingGroup1.Bltr, [Execution] AS Expr1 FROM tblTradeListingGroup1 WHERE (((tblTradeListingGroup1.SettleDate)=[What Settlement Date do you wish to view?]) AND (([Execution])=IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null))); Also, the parameter value, it's asking me for one for the Execution. I have no idea what you say when asking for the recordsource. Let me know where I can find this information! "John W. Vinson" wrote: On Thu, 24 May 2007 10:34:02 -0700, Andre Adams wrote: Hey John, Thanks for you prompt response! I've tried your suggestion and it's prompting and "Enter parameter value" into the fold. I check the equation and it's entered exactly the way that you sent it to me, does this error mean that it can't be done within my report? No; it means you're making a mistake. Easy to do, you're working at two or three different levels here! Please open the Query. Select View... SQL. Copy and paste the SQL text to a message here. Also post the parameter value that it's asking for, and the Recordsource property of your Report. John W. Vinson [MVP] |
#13
|
|||
|
|||
Invalid Syntax
On Thu, 24 May 2007 11:48:00 -0700, Andre Adams
wrote: Here it is John, SELECT tblTradeListingGroup1.RepID, tblTradeListingGroup1.[Acct #], tblTradeListingGroup1.[Account Name], tblTradeListingGroup1.Symbol, tblTradeListingGroup1.SettleDate, tblTradeListingGroup1.Tran, tblTradeListingGroup1.[Shrs/Contr], tblTradeListingGroup1.TradePrice, tblTradeListingGroup1.[Commission Amount], tblTradeListingGroup1.[Clearing Charge], tblTradeListingGroup1.Bltr, [Execution] AS Expr1 FROM tblTradeListingGroup1 WHERE (((tblTradeListingGroup1.SettleDate)=[What Settlement Date do you wish to view?]) AND (([Execution])=IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null))); I think what Douglas is suggesting is: SELECT tblTradeListingGroup1.RepID, tblTradeListingGroup1.[Acct #], tblTradeListingGroup1.[Account Name], tblTradeListingGroup1.Symbol, tblTradeListingGroup1.SettleDate, tblTradeListingGroup1.Tran, tblTradeListingGroup1.[Shrs/Contr], tblTradeListingGroup1.TradePrice, tblTradeListingGroup1.[Commission Amount], tblTradeListingGroup1.[Clearing Charge], tblTradeListingGroup1.Bltr, IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null))) AS Execution FROM tblTradeListingGroup1 WHERE (((tblTradeListingGroup1.SettleDate)=[What Settlement Date do you wish to view?]) though it might be necessary to use a different syntax - I'm not sure whether IIF handles an IN clause: SELECT tblTradeListingGroup1.RepID, tblTradeListingGroup1.[Acct #], tblTradeListingGroup1.[Account Name], tblTradeListingGroup1.Symbol, tblTradeListingGroup1.SettleDate, tblTradeListingGroup1.Tran, tblTradeListingGroup1.[Shrs/Contr], tblTradeListingGroup1.TradePrice, tblTradeListingGroup1.[Commission Amount], tblTradeListingGroup1.[Clearing Charge], tblTradeListingGroup1.Bltr, IIf([Bltr]=16 OR [Bltr] = 66, [Shrs/Contr]*-0.005425,Null))) AS Execution FROM tblTradeListingGroup1 WHERE (((tblTradeListingGroup1.SettleDate)=[What Settlement Date do you wish to view?]) John W. Vinson [MVP] |
#14
|
|||
|
|||
Invalid Syntax
Thanks John.
Sorry to put you guys through this, I tried both ways to no avail. It stil shows me the #Error in the Execution field. The good thing is, I'm more comfortable in the SQL view! I did check my Table for the query and the Blotter is set to text for the 16 and 1V's that will populate the column. Are there anymore suggestions? Andre Adams "John W. Vinson" wrote: On Thu, 24 May 2007 11:48:00 -0700, Andre Adams wrote: Here it is John, SELECT tblTradeListingGroup1.RepID, tblTradeListingGroup1.[Acct #], tblTradeListingGroup1.[Account Name], tblTradeListingGroup1.Symbol, tblTradeListingGroup1.SettleDate, tblTradeListingGroup1.Tran, tblTradeListingGroup1.[Shrs/Contr], tblTradeListingGroup1.TradePrice, tblTradeListingGroup1.[Commission Amount], tblTradeListingGroup1.[Clearing Charge], tblTradeListingGroup1.Bltr, [Execution] AS Expr1 FROM tblTradeListingGroup1 WHERE (((tblTradeListingGroup1.SettleDate)=[What Settlement Date do you wish to view?]) AND (([Execution])=IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null))); I think what Douglas is suggesting is: SELECT tblTradeListingGroup1.RepID, tblTradeListingGroup1.[Acct #], tblTradeListingGroup1.[Account Name], tblTradeListingGroup1.Symbol, tblTradeListingGroup1.SettleDate, tblTradeListingGroup1.Tran, tblTradeListingGroup1.[Shrs/Contr], tblTradeListingGroup1.TradePrice, tblTradeListingGroup1.[Commission Amount], tblTradeListingGroup1.[Clearing Charge], tblTradeListingGroup1.Bltr, IIf([Bltr] In (16,66),[Shrs/Contr]*-0.005425,Null))) AS Execution FROM tblTradeListingGroup1 WHERE (((tblTradeListingGroup1.SettleDate)=[What Settlement Date do you wish to view?]) though it might be necessary to use a different syntax - I'm not sure whether IIF handles an IN clause: SELECT tblTradeListingGroup1.RepID, tblTradeListingGroup1.[Acct #], tblTradeListingGroup1.[Account Name], tblTradeListingGroup1.Symbol, tblTradeListingGroup1.SettleDate, tblTradeListingGroup1.Tran, tblTradeListingGroup1.[Shrs/Contr], tblTradeListingGroup1.TradePrice, tblTradeListingGroup1.[Commission Amount], tblTradeListingGroup1.[Clearing Charge], tblTradeListingGroup1.Bltr, IIf([Bltr]=16 OR [Bltr] = 66, [Shrs/Contr]*-0.005425,Null))) AS Execution FROM tblTradeListingGroup1 WHERE (((tblTradeListingGroup1.SettleDate)=[What Settlement Date do you wish to view?]) John W. Vinson [MVP] |
#15
|
|||
|
|||
Invalid Syntax
On Fri, 25 May 2007 06:38:03 -0700, Andre Adams
wrote: Thanks John. Sorry to put you guys through this, I tried both ways to no avail. It stil shows me the #Error in the Execution field. The good thing is, I'm more comfortable in the SQL view! I did check my Table for the query and the Blotter is set to text for the 16 and 1V's that will populate the column. Are there anymore suggestions? aha! If Blotter is a Text field then the criteria must be delimited with quotes, either ' or ". Try IIf([Bltr] In ("16","66") John W. Vinson [MVP] |
#16
|
|||
|
|||
Invalid Syntax
YES!!!!!!!!!!
Great sir. Thank you very much for your help. Works perfectly! Andre Adams "John W. Vinson" wrote: On Fri, 25 May 2007 06:38:03 -0700, Andre Adams wrote: Thanks John. Sorry to put you guys through this, I tried both ways to no avail. It stil shows me the #Error in the Execution field. The good thing is, I'm more comfortable in the SQL view! I did check my Table for the query and the Blotter is set to text for the 16 and 1V's that will populate the column. Are there anymore suggestions? aha! If Blotter is a Text field then the criteria must be delimited with quotes, either ' or ". Try IIf([Bltr] In ("16","66") John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|