Invalid Syntax

Hello again...

This is my third question in 2 days and I've not gotten a response to them,
so what'll I do? I'll just keep on trying! Really want to learn this system!

I'm trying to finish a report that I've worked so hard on. I have a field
called "Execution" within my report that is only calculated when the
"Blotter" field shows with 16 or 66. Here's the problem. I do not have the
Execution field in my table, it's generated only in the report. How can I
tell the report to only calculate (Quantity * -.005625) only when those two
"Blotter" fields show ion a record in the table (But relating to the report)?

Part of the problem is your question is not clear, you talk about different
blotter fields and different values. Can you reword your question and add
more detail?
--
Dave Hargis, Microsoft Access MVP


"Andre Adams" wrote:

Hello again...

This is my third question in 2 days and I've not gotten a response to them,
so what'll I do? I'll just keep on trying! Really want to learn this system!

I'm trying to finish a report that I've worked so hard on. I have a field
called "Execution" within my report that is only calculated when the
"Blotter" field shows with 16 or 66. Here's the problem. I do not have the
Execution field in my table, it's generated only in the report. How can I
tell the report to only calculate (Quantity * -.005625) only when those two
"Blotter" fields show ion a record in the table (But relating to the report)?

On Wed, 23 May 2007 13:53:00 -0700, Andre Adams
wrote:

Hello again...

This is my third question in 2 days and I've not gotten a response to them,
so what'll I do? I'll just keep on trying! Really want to learn this system!

I'm trying to finish a report that I've worked so hard on. I have a field
called "Execution" within my report that is only calculated when the
"Blotter" field shows with 16 or 66. Here's the problem. I do not have the
Execution field in my table, it's generated only in the report. How can I
tell the report to only calculate (Quantity * -.005625) only when those two
"Blotter" fields show ion a record in the table (But relating to the report)?

Andre, we cannot see your computer. We have NO way to know what your table
structure is, what "Execution" might be, what Blotter is, or what the
recordsource of your Report might be.

At a wild guess in the dark, you might be able to base the Report on a query
with a calculated field. In a vacant Field cell type

Execution: IIF([Blotter] IN (16, 66), [Quantity] * -.005625, Null)

and include a textbox bound to this field in your report.

John W. Vinson [MVP]

Hello John,

Thanks for the response.

My table Structure is as follows:

Shares
Trade Price
Commission Amount
Blotter

The "Execution" field isn't listed in my Table. It's one of the fields that
will not come over in my import from Excel. Thus, I need to calculate this
directly in Access. I do understand now that I cannot create the formula
directly into the report, I have to go back to the query to create it. I
guess my question is, what is the formula to create the Execution fee (Shares
* -.005425, if Blotter = 16 or 66)? What's given is the shares and the
blotter is shown on all trades. Hope this helps!

Andre Adams


"John W. Vinson" wrote:

On Wed, 23 May 2007 13:53:00 -0700, Andre Adams
wrote:

Hello again...

This is my third question in 2 days and I've not gotten a response to them,
so what'll I do? I'll just keep on trying! Really want to learn this system!

I'm trying to finish a report that I've worked so hard on. I have a field
called "Execution" within my report that is only calculated when the
"Blotter" field shows with 16 or 66. Here's the problem. I do not have the
Execution field in my table, it's generated only in the report. How can I
tell the report to only calculate (Quantity * -.005625) only when those two
"Blotter" fields show ion a record in the table (But relating to the report)?

Andre, we cannot see your computer. We have NO way to know what your table
structure is, what "Execution" might be, what Blotter is, or what the
recordsource of your Report might be.

At a wild guess in the dark, you might be able to base the Report on a query
with a calculated field. In a vacant Field cell type

Execution: IIF([Blotter] IN (16, 66), [Quantity] * -.005625, Null)

and include a textbox bound to this field in your report.

John W. Vinson [MVP]

On Thu, 24 May 2007 07:38:03 -0700, Andre Adams
wrote:

Hello John,

Thanks for the response.

My table Structure is as follows:

Shares
Trade Price
Commission Amount
Blotter

The "Execution" field isn't listed in my Table. It's one of the fields that
will not come over in my import from Excel. Thus, I need to calculate this
directly in Access. I do understand now that I cannot create the formula
directly into the report, I have to go back to the query to create it. I
guess my question is, what is the formula to create the Execution fee (Shares
* -.005425, if Blotter = 16 or 66)? What's given is the shares and the
blotter is shown on all trades. Hope this helps!

Create a new Query based on your table.

In a vacant Field cell type what I suggested (rather too briefly!) in my
previous post:

Execution: IIF([Blotter] IN (16, 66), [Shares] * -.005625, Null)

This will create a calculated field named Execution; if the value of the field
named Blotter is 16 or 66, it will return the calculated value. If Blotter has
any other value (or no value at all) it will return a null (blank) result.

You can then base your Report on this query.

John W. Vinson [MVP]

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?

Andre Adams

"John W. Vinson" wrote:

On Thu, 24 May 2007 07:38:03 -0700, Andre Adams
wrote:

Hello John,

Thanks for the response.

My table Structure is as follows:

Shares
Trade Price
Commission Amount
Blotter

The "Execution" field isn't listed in my Table. It's one of the fields that
will not come over in my import from Excel. Thus, I need to calculate this
directly in Access. I do understand now that I cannot create the formula
directly into the report, I have to go back to the query to create it. I
guess my question is, what is the formula to create the Execution fee (Shares
* -.005425, if Blotter = 16 or 66)? What's given is the shares and the
blotter is shown on all trades. Hope this helps!

Create a new Query based on your table.

In a vacant Field cell type what I suggested (rather too briefly!) in my
previous post:

Execution: IIF([Blotter] IN (16, 66), [Shares] * -.005625, Null)

This will create a calculated field named Execution; if the value of the field
named Blotter is 16 or 66, it will return the calculated value. If Blotter has
any other value (or no value at all) it will return a null (blank) result.

You can then base your Report on this query.

John W. Vinson [MVP]

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]

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]

(([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]

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]