Invalid Syntax

[Guest]

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)?

 

[Guest]

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?

 

[John W. Vinson]

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]

 

[Guest]

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

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]

 

[John W. Vinson]

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]

 

[Guest]

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

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]

 

[John W. Vinson]

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]

 

[Guest]

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!

 

[Douglas J. Steele]

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

(no e-mails, please!)

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!

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]

 

[Guest]

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

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

(no e-mails, please!)

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!

On Thu, 24 May 2007 10:34:02 -0700, Andre Adams

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]

 

[Douglas J. Steele]

What happens when you simply open the query, as opposed to the report?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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

(no e-mails, please!)

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!

:

On Thu, 24 May 2007 10:34:02 -0700, Andre Adams

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]

 

[Guest]

I'm in the query now. That's the message it gives me within the query.

What happens when you simply open the query, as opposed to the report?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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

(no e-mails, please!)


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!

:

On Thu, 24 May 2007 10:34:02 -0700, Andre Adams

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]

 

[John W. Vinson]

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]

 

[Guest]

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

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]

 

[John W. Vinson]

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]

 

[Guest]

YES!!!!!!!!!!

Great sir. Thank you very much for your help. Works perfectly!

Andre Adams

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]