Avoid Parameter Prompt

  • Thread starter Thread starter D
  • Start date Start date
D

D

Hi:

In the SQL below, can you please tell me ho can I avoid to be promted to
enter:[Further Gross up - 7 Sisters]?

Thanks,

Dan

****

SELECT DISTINCT HE.CTA_NIFO, HE.NIFO_Name, "" AS [Line-4],
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group") AS [CCY GROUP
(value= USD, Non-USD)], HE_Details.NIFO_Func_Ccy,
HE_Details.[OH-Priority-Inv], HE_Details.[OH-Priority-RE],
HE_Details.[Inv-Beg], HE_Details.[RE-Beg], HE_Details.[Grossup-Inv-Beg], 0 AS
[Further Gross up - 7 Sisters], HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters] AS [Inv-Beg after
Gross Up], [RE-Beg]+[grossup-re-beg] AS [R/E-Beg after Gross Up],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg] AS [Total Hedged Item after Gross Up],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax INTO [He+He Details]
FROM HE INNER JOIN HE_Details ON (HE.CTA_NIFO = HE_Details.CTA_NIFO) AND
(HE.NIFO_Func_Ccy = HE_Details.NIFO_Func_Ccy)
GROUP BY HE.CTA_NIFO, HE.NIFO_Name, "",
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group"),
HE_Details.NIFO_Func_Ccy, HE_Details.[OH-Priority-Inv],
HE_Details.[OH-Priority-RE], HE_Details.[Inv-Beg], HE_Details.[RE-Beg],
HE_Details.[Grossup-Inv-Beg], 0, HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters],
[RE-Beg]+[grossup-re-beg], [Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax
ORDER BY IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group");
 
Make sure it is spelled correctly. Most of the time, it's a spelling error.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

D said:
Hi:

In the SQL below, can you please tell me ho can I avoid to be promted to
enter:[Further Gross up - 7 Sisters]?

Thanks,

Dan

****

SELECT DISTINCT HE.CTA_NIFO, HE.NIFO_Name, "" AS [Line-4],
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group") AS [CCY
GROUP
(value= USD, Non-USD)], HE_Details.NIFO_Func_Ccy,
HE_Details.[OH-Priority-Inv], HE_Details.[OH-Priority-RE],
HE_Details.[Inv-Beg], HE_Details.[RE-Beg], HE_Details.[Grossup-Inv-Beg], 0
AS
[Further Gross up - 7 Sisters], HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters] AS [Inv-Beg
after
Gross Up], [RE-Beg]+[grossup-re-beg] AS [R/E-Beg after Gross Up],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg] AS [Total Hedged Item after Gross Up],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv],
HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax INTO [He+He Details]
FROM HE INNER JOIN HE_Details ON (HE.CTA_NIFO = HE_Details.CTA_NIFO) AND
(HE.NIFO_Func_Ccy = HE_Details.NIFO_Func_Ccy)
GROUP BY HE.CTA_NIFO, HE.NIFO_Name, "",
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group"),
HE_Details.NIFO_Func_Ccy, HE_Details.[OH-Priority-Inv],
HE_Details.[OH-Priority-RE], HE_Details.[Inv-Beg], HE_Details.[RE-Beg],
HE_Details.[Grossup-Inv-Beg], 0, HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters],
[RE-Beg]+[grossup-re-beg], [Inv-Beg]+[grossup-inv-beg]+[Further Gross up -
7
Sisters]+[RE-Beg]+[grossup-re-beg],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv],
HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax
ORDER BY IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group");
Click to expand...
 
It is trying to process [Further Gross up - 7 Sisters] several times before
it is declaried. Try moving '0 AS [Further Gross up - 7 Sisters]' as the
last part of your select statement.

I see what looks like another problem - HE.OH_Reduct_CTA_after_Tax INTO
[He+He Details]

--
KARL DEWEY
Build a little - Test a little


D said:
Hi:

In the SQL below, can you please tell me ho can I avoid to be promted to
enter:[Further Gross up - 7 Sisters]?

Thanks,

Dan

****

SELECT DISTINCT HE.CTA_NIFO, HE.NIFO_Name, "" AS [Line-4],
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group") AS [CCY GROUP
(value= USD, Non-USD)], HE_Details.NIFO_Func_Ccy,
HE_Details.[OH-Priority-Inv], HE_Details.[OH-Priority-RE],
HE_Details.[Inv-Beg], HE_Details.[RE-Beg], HE_Details.[Grossup-Inv-Beg], 0 AS
[Further Gross up - 7 Sisters], HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters] AS [Inv-Beg after
Gross Up], [RE-Beg]+[grossup-re-beg] AS [R/E-Beg after Gross Up],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg] AS [Total Hedged Item after Gross Up],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax INTO [He+He Details]
FROM HE INNER JOIN HE_Details ON (HE.CTA_NIFO = HE_Details.CTA_NIFO) AND
(HE.NIFO_Func_Ccy = HE_Details.NIFO_Func_Ccy)
GROUP BY HE.CTA_NIFO, HE.NIFO_Name, "",
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group"),
HE_Details.NIFO_Func_Ccy, HE_Details.[OH-Priority-Inv],
HE_Details.[OH-Priority-RE], HE_Details.[Inv-Beg], HE_Details.[RE-Beg],
HE_Details.[Grossup-Inv-Beg], 0, HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters],
[RE-Beg]+[grossup-re-beg], [Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax
ORDER BY IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group");
Click to expand...
 
Thanks Karl/Roger:

Still there...!??

Dan

KARL DEWEY said:
It is trying to process [Further Gross up - 7 Sisters] several times before
it is declaried. Try moving '0 AS [Further Gross up - 7 Sisters]' as the
last part of your select statement.

I see what looks like another problem - HE.OH_Reduct_CTA_after_Tax INTO
[He+He Details]

--
KARL DEWEY
Build a little - Test a little


D said:
Hi:

In the SQL below, can you please tell me ho can I avoid to be promted to
enter:[Further Gross up - 7 Sisters]?

Thanks,

Dan

****

SELECT DISTINCT HE.CTA_NIFO, HE.NIFO_Name, "" AS [Line-4],
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group") AS [CCY GROUP
(value= USD, Non-USD)], HE_Details.NIFO_Func_Ccy,
HE_Details.[OH-Priority-Inv], HE_Details.[OH-Priority-RE],
HE_Details.[Inv-Beg], HE_Details.[RE-Beg], HE_Details.[Grossup-Inv-Beg], 0 AS
[Further Gross up - 7 Sisters], HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters] AS [Inv-Beg after
Gross Up], [RE-Beg]+[grossup-re-beg] AS [R/E-Beg after Gross Up],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg] AS [Total Hedged Item after Gross Up],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax INTO [He+He Details]
FROM HE INNER JOIN HE_Details ON (HE.CTA_NIFO = HE_Details.CTA_NIFO) AND
(HE.NIFO_Func_Ccy = HE_Details.NIFO_Func_Ccy)
GROUP BY HE.CTA_NIFO, HE.NIFO_Name, "",
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group"),
HE_Details.NIFO_Func_Ccy, HE_Details.[OH-Priority-Inv],
HE_Details.[OH-Priority-RE], HE_Details.[Inv-Beg], HE_Details.[RE-Beg],
HE_Details.[Grossup-Inv-Beg], 0, HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters],
[RE-Beg]+[grossup-re-beg], [Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax
ORDER BY IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group");
Click to expand...
Click to expand...
 
Still there...!??
I do not understand your comment.
--
KARL DEWEY
Build a little - Test a little


D said:
Thanks Karl/Roger:

Still there...!??

Dan

KARL DEWEY said:
It is trying to process [Further Gross up - 7 Sisters] several times before
it is declaried. Try moving '0 AS [Further Gross up - 7 Sisters]' as the
last part of your select statement.

I see what looks like another problem - HE.OH_Reduct_CTA_after_Tax INTO
[He+He Details]

--
KARL DEWEY
Build a little - Test a little


D said:
Hi:

In the SQL below, can you please tell me ho can I avoid to be promted to
enter:[Further Gross up - 7 Sisters]?

Thanks,

Dan

****

SELECT DISTINCT HE.CTA_NIFO, HE.NIFO_Name, "" AS [Line-4],
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group") AS [CCY GROUP
(value= USD, Non-USD)], HE_Details.NIFO_Func_Ccy,
HE_Details.[OH-Priority-Inv], HE_Details.[OH-Priority-RE],
HE_Details.[Inv-Beg], HE_Details.[RE-Beg], HE_Details.[Grossup-Inv-Beg], 0 AS
[Further Gross up - 7 Sisters], HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters] AS [Inv-Beg after
Gross Up], [RE-Beg]+[grossup-re-beg] AS [R/E-Beg after Gross Up],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg] AS [Total Hedged Item after Gross Up],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax INTO [He+He Details]
FROM HE INNER JOIN HE_Details ON (HE.CTA_NIFO = HE_Details.CTA_NIFO) AND
(HE.NIFO_Func_Ccy = HE_Details.NIFO_Func_Ccy)
GROUP BY HE.CTA_NIFO, HE.NIFO_Name, "",
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group"),
HE_Details.NIFO_Func_Ccy, HE_Details.[OH-Priority-Inv],
HE_Details.[OH-Priority-RE], HE_Details.[Inv-Beg], HE_Details.[RE-Beg],
HE_Details.[Grossup-Inv-Beg], 0, HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters],
[RE-Beg]+[grossup-re-beg], [Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax
ORDER BY IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group");
Click to expand...
Click to expand...
Click to expand...
 
Hi:

In the SQL below, can you please tell me ho can I avoid to be promted to
enter:[Further Gross up - 7 Sisters]?
Click to expand...
The simplest way is to replace it by its value, 0, in all the expressions
where it occurs. You usually cannot reuse a calculated field in a further
calculation, and this *is* a calculated field (albeit a very trivial
"calculation", just the constant 0).
 
Hi Karl:

In other words it is still 'prompting' to enter a value.

Thanks,

Dan

KARL DEWEY said:
I do not understand your comment.
--
KARL DEWEY
Build a little - Test a little


D said:
Thanks Karl/Roger:

Still there...!??

Dan

KARL DEWEY said:
It is trying to process [Further Gross up - 7 Sisters] several times before
it is declaried. Try moving '0 AS [Further Gross up - 7 Sisters]' as the
last part of your select statement.

I see what looks like another problem - HE.OH_Reduct_CTA_after_Tax INTO
[He+He Details]

--
KARL DEWEY
Build a little - Test a little


:

Hi:

In the SQL below, can you please tell me ho can I avoid to be promted to
enter:[Further Gross up - 7 Sisters]?

Thanks,

Dan

****

SELECT DISTINCT HE.CTA_NIFO, HE.NIFO_Name, "" AS [Line-4],
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group") AS [CCY GROUP
(value= USD, Non-USD)], HE_Details.NIFO_Func_Ccy,
HE_Details.[OH-Priority-Inv], HE_Details.[OH-Priority-RE],
HE_Details.[Inv-Beg], HE_Details.[RE-Beg], HE_Details.[Grossup-Inv-Beg], 0 AS
[Further Gross up - 7 Sisters], HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters] AS [Inv-Beg after
Gross Up], [RE-Beg]+[grossup-re-beg] AS [R/E-Beg after Gross Up],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg] AS [Total Hedged Item after Gross Up],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax INTO [He+He Details]
FROM HE INNER JOIN HE_Details ON (HE.CTA_NIFO = HE_Details.CTA_NIFO) AND
(HE.NIFO_Func_Ccy = HE_Details.NIFO_Func_Ccy)
GROUP BY HE.CTA_NIFO, HE.NIFO_Name, "",
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group"),
HE_Details.NIFO_Func_Ccy, HE_Details.[OH-Priority-Inv],
HE_Details.[OH-Priority-RE], HE_Details.[Inv-Beg], HE_Details.[RE-Beg],
HE_Details.[Grossup-Inv-Beg], 0, HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters],
[RE-Beg]+[grossup-re-beg], [Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax
ORDER BY IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group");
Click to expand...
Click to expand...
Click to expand...
 
I suggest you follow the advice given by John W. Vinson.
--
KARL DEWEY
Build a little - Test a little


D said:
Hi Karl:

In other words it is still 'prompting' to enter a value.

Thanks,

Dan

KARL DEWEY said:
Still there...!??
Click to expand...
I do not understand your comment.
--
KARL DEWEY
Build a little - Test a little


D said:
Thanks Karl/Roger:

Still there...!??

Dan

:

It is trying to process [Further Gross up - 7 Sisters] several times before
it is declaried. Try moving '0 AS [Further Gross up - 7 Sisters]' as the
last part of your select statement.

I see what looks like another problem - HE.OH_Reduct_CTA_after_Tax INTO
[He+He Details]

--
KARL DEWEY
Build a little - Test a little


:

Hi:

In the SQL below, can you please tell me ho can I avoid to be promted to
enter:[Further Gross up - 7 Sisters]?

Thanks,

Dan

****

SELECT DISTINCT HE.CTA_NIFO, HE.NIFO_Name, "" AS [Line-4],
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group") AS [CCY GROUP
(value= USD, Non-USD)], HE_Details.NIFO_Func_Ccy,
HE_Details.[OH-Priority-Inv], HE_Details.[OH-Priority-RE],
HE_Details.[Inv-Beg], HE_Details.[RE-Beg], HE_Details.[Grossup-Inv-Beg], 0 AS
[Further Gross up - 7 Sisters], HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters] AS [Inv-Beg after
Gross Up], [RE-Beg]+[grossup-re-beg] AS [R/E-Beg after Gross Up],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg] AS [Total Hedged Item after Gross Up],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax INTO [He+He Details]
FROM HE INNER JOIN HE_Details ON (HE.CTA_NIFO = HE_Details.CTA_NIFO) AND
(HE.NIFO_Func_Ccy = HE_Details.NIFO_Func_Ccy)
GROUP BY HE.CTA_NIFO, HE.NIFO_Name, "",
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group"),
HE_Details.NIFO_Func_Ccy, HE_Details.[OH-Priority-Inv],
HE_Details.[OH-Priority-RE], HE_Details.[Inv-Beg], HE_Details.[RE-Beg],
HE_Details.[Grossup-Inv-Beg], 0, HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters],
[RE-Beg]+[grossup-re-beg], [Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax
ORDER BY IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group");
Click to expand...
Click to expand...
Click to expand...
 
Thanks Karl:

Yes, I did.

Dan

KARL DEWEY said:
I suggest you follow the advice given by John W. Vinson.
--
KARL DEWEY
Build a little - Test a little


D said:
Hi Karl:

In other words it is still 'prompting' to enter a value.

Thanks,

Dan

KARL DEWEY said:
Still there...!??
I do not understand your comment.
--
KARL DEWEY
Build a little - Test a little


:

Thanks Karl/Roger:

Still there...!??

Dan

:

It is trying to process [Further Gross up - 7 Sisters] several times before
it is declaried. Try moving '0 AS [Further Gross up - 7 Sisters]' as the
last part of your select statement.

I see what looks like another problem - HE.OH_Reduct_CTA_after_Tax INTO
[He+He Details]

--
KARL DEWEY
Build a little - Test a little


:

Hi:

In the SQL below, can you please tell me ho can I avoid to be promted to
enter:[Further Gross up - 7 Sisters]?

Thanks,

Dan

****

SELECT DISTINCT HE.CTA_NIFO, HE.NIFO_Name, "" AS [Line-4],
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group") AS [CCY GROUP
(value= USD, Non-USD)], HE_Details.NIFO_Func_Ccy,
HE_Details.[OH-Priority-Inv], HE_Details.[OH-Priority-RE],
HE_Details.[Inv-Beg], HE_Details.[RE-Beg], HE_Details.[Grossup-Inv-Beg], 0 AS
[Further Gross up - 7 Sisters], HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters] AS [Inv-Beg after
Gross Up], [RE-Beg]+[grossup-re-beg] AS [R/E-Beg after Gross Up],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg] AS [Total Hedged Item after Gross Up],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax INTO [He+He Details]
FROM HE INNER JOIN HE_Details ON (HE.CTA_NIFO = HE_Details.CTA_NIFO) AND
(HE.NIFO_Func_Ccy = HE_Details.NIFO_Func_Ccy)
GROUP BY HE.CTA_NIFO, HE.NIFO_Name, "",
IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group"),
HE_Details.NIFO_Func_Ccy, HE_Details.[OH-Priority-Inv],
HE_Details.[OH-Priority-RE], HE_Details.[Inv-Beg], HE_Details.[RE-Beg],
HE_Details.[Grossup-Inv-Beg], 0, HE_Details.[Grossup-RE-Beg],
[Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7 Sisters],
[RE-Beg]+[grossup-re-beg], [Inv-Beg]+[grossup-inv-beg]+[Further Gross up - 7
Sisters]+[RE-Beg]+[grossup-re-beg],
HE_Details.[Total-Hedges-before-Reduction],
HE_Details.[7sisters-Hedge-Reduction], HE_Details.[OH-Red-Inv],
HE_Details.[OH-Red-RE], HE_Details.[Total-Grossed-up-Hedge-Pos-Beg],
HE_Details.[Total-Month-Movement-Inv], HE_Details.[Total-Month-Movement-RE],
HE_Details.[Total-Month-Movement-Hedge], HE_Details.[Total-Expo-for-HE],
HE_Details.[Total-Hedge-for-HE], HE_Details.[Hedge-over-Expo-%],
HE.Expo_CTA_after_Tax, HE.Prorated_Expo_CTA_after_Tax,
HE.Hedge_CTA_after_Tax, HE.OH_Reduct_CTA_after_Tax
ORDER BY IIf([he_details].[nifo_func_ccy]<>"USD","Non-USD","USD-Group");
Click to expand...
Click to expand...
Click to expand...
 
Back
Top