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]

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]

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]

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]

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]

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]