Access 2007: "Enter Parameter Value '###.000'" ????

[(PeteCresswell)]

Porting a form from 2003 to 2007, it has started throwing "Enter
Parameter Value '###.000'" when opened.

I've searched the form and it's underlying query but, for the life of
me, I cannot find anything like "###.000" - which seems tb a format
string that 2007 is unable to interpret (??).

This is a subform and the same prompt is being thrown for the other two
subforms on the same parent.

OTOH, I've got other forms with many, many subforms - but no problems.

Anybody been here?
--
Pete Cresswell

[Phil]

Does the query run without asking for the parameters?
Have you checked that there the Query Parameters "Table thing" is blank?
What is the SQL?

Phil

[(PeteCresswell)]

Per Phil:

>Does the query run without asking for the parameters?
>Have you checked that there the Query Parameters "Table thing" is blank?
>What is the SQL?

One thing that maybe I left out in the OP: it works a-ok under 2003 and
has been for quite a few years.

I used a search utility to find all occurrences of "###.000" in the app,
but it only found two - in other forms.

OTOH, there's quite a bit of "#,###.00" formatting in the problem
form/subforms - and I tried removing all formatting, but the problem
persisted.

Also, I said that it was happening in multiple subforms. In retrospect
that was a careless statement. The dialog gets thrown four times and I
assumed it was once for each form involved.... but there is no reason to
assume that.

Can you say the "Query Parameters 'Table thing'" another way? I'm not
getting it.

Here's all the SQL that I can find associated with the form:
==========================================================================
SELECT tblTrade.TradeID, tblTradeFund.TradeFundID, tblTradeFund.FundID,
tlkpFund.FundName, [tblTradeFund].[Shares]*[ParValue] AS Amount,
tblTradeFund.Shares AS FundShares, tblTrade.Price,
tblTrade.SettlementDate, [tblTrade].[shares]*[ParValue] AS TotalAmount,
tblTrade.Shares AS TotalShares, tblTrade.TradeDate,
tblTrade.UnderwriterID
FROM tlkpFund RIGHT JOIN (tblTrade LEFT JOIN tblTradeFund ON
tblTrade.TradeID = tblTradeFund.TradeID) ON tlkpFund.FundID =
tblTradeFund.FundID
WHERE
(((tblTrade.TrancheID)=[forms]![frmTrancheTransactionEntry]![txtTrancheID])
AND ((tblTrade.TradeTypeID)=5))
ORDER BY tlkpFund.FundName;
=============================================
SELECT tlkpFund.FundName, tblTradeFund.Shares,
[tblTradeFund].[Shares]*[ParValue] AS Amount
FROM tlkpFund RIGHT JOIN (tblTrade LEFT JOIN tblTradeFund ON
tblTrade.TradeID = tblTradeFund.TradeID) ON tlkpFund.FundID =
tblTradeFund.FundID
WHERE (((tblTrade.TrancheID) Is Null) AND ((tblTrade.TradeTypeID)=5));
=============================================
UPDATE ttblTradeEntryFunds INNER JOIN
qryTrancheTransactionEntry_Committments_ForTranche ON
ttblTradeEntryFunds.FundID =
qryTrancheTransactionEntry_Committments_ForTranche.FundID SET
ttblTradeEntryFunds.Amount =
[qryTrancheTransactionEntry_Committments_ForTranche].[Amount],
ttblTradeEntryFunds.Shares =
[qryTrancheTransactionEntry_Committments_ForTranche].[FundShares];
=============================================
SELECT Sum(ttblTradeEntryFunds.Shares) AS SumOfShares,
Sum(ttblTradeEntryFunds.Amount) AS SumOfAmount,
Sum(ttblTradeEntryFunds.Percent) AS SumOfPercent
FROM ttblTradeEntryFunds;
=============================================
SELECT ttblTradeEntryFunds.FundName, Sum(ttblTradeEntryFunds.Shares) AS
SumOfShares, Sum(ttblTradeEntryFunds.Amount) AS SumOfAmount,
Sum(ttblTradeEntryFunds.Percent) AS SumOfPercent
FROM ttblTradeEntryFunds
GROUP BY ttblTradeEntryFunds.FundName
HAVING (((Sum(ttblTradeEntryFunds.Shares))>0)) OR
(((Sum(ttblTradeEntryFunds.Amount))>0)) OR
(((Sum(ttblTradeEntryFunds.Percent))>0));
=============================================
SELECT tlkpFund.FundName, tblTrade.ParValue, Sum(tblTradeFund.Shares) AS
SumOfShares
FROM tlkpFund RIGHT JOIN (tblTrade LEFT JOIN tblTradeFund ON
tblTrade.TradeID = tblTradeFund.TradeID) ON tlkpFund.FundID =
tblTradeFund.FundID
WHERE
(((tblTrade.TrancheID)=[forms]![frmTrancheTransactionEntry].[txtTrancheID])
AND ((tblTrade.TradeTypeID)<>4 And (tblTrade.TradeTypeID)<>5))
GROUP BY tlkpFund.FundName, tblTrade.ParValue
HAVING (((Sum(tblTradeFund.Shares))<>0 And (Sum(tblTradeFund.Shares)) Is
Not Null))
ORDER BY tlkpFund.FundName;
=============================================
--
Pete Cresswell

[Phil]

On 10/05/2013 20:35:48, "(PeteCresswell)" wrote:
> Per Phil:
>>Does the query run without asking for the parameters?
>>Have you checked that there the Query Parameters "Table thing" is blank?
>>What is the SQL?
>
> One thing that maybe I left out in the OP: it works a-ok under 2003 and
> has been for quite a few years.
>
> I used a search utility to find all occurrences of "###.000" in the app,
> but it only found two - in other forms.
>
> OTOH, there's quite a bit of "#,###.00" formatting in the problem
> form/subforms - and I tried removing all formatting, but the problem
> persisted.
>
> Also, I said that it was happening in multiple subforms. In retrospect
> that was a careless statement. The dialog gets thrown four times and I
> assumed it was once for each form involved.... but there is no reason to
> assume that.
>
> Can you say the "Query Parameters 'Table thing'" another way? I'm not
> getting it.
>
> Here's all the SQL that I can find associated with the form:

> =========================================================================SELECT

> tblTrade.TradeID, tblTradeFund.TradeFundID, tblTradeFund.FundID,
> tlkpFund.FundName, [tblTradeFund].[Shares]*[ParValue] AS Amount,
> tblTradeFund.Shares AS FundShares, tblTrade.Price,
> tblTrade.SettlementDate, [tblTrade].[shares]*[ParValue] AS TotalAmount,
> tblTrade.Shares AS TotalShares, tblTrade.TradeDate, tblTrade.UnderwriterID
> FROM tlkpFund RIGHT JOIN (tblTrade LEFT JOIN tblTradeFund ON
> tblTrade.TradeID = tblTradeFund.TradeID) ON tlkpFund.FundID

> tblTradeFund.FundID WHERE
> (((tblTrade.TrancheID)=[forms]![frmTrancheTransactionEntry]![txtTrancheID])
> AND ((tblTrade.TradeTypeID)=5)) ORDER BY tlkpFund.FundName;

> ============================================SELECT tlkpFund.FundName,

> tblTradeFund.Shares, [tblTradeFund].[Shares]*[ParValue] AS Amount
> FROM tlkpFund RIGHT JOIN (tblTrade LEFT JOIN tblTradeFund ON
> tblTrade.TradeID = tblTradeFund.TradeID) ON tlkpFund.FundID

> tblTradeFund.FundID WHERE (((tblTrade.TrancheID) Is Null) AND
> ((tblTrade.TradeTypeID)=5));

> ============================================UPDATE ttblTradeEntryFunds

> INNER JOIN qryTrancheTransactionEntry_Committments_ForTranche ON
> ttblTradeEntryFunds.FundID

> qryTrancheTransactionEntry_Committments_ForTranche.FundID SET
> ttblTradeEntryFunds.Amount

> [qryTrancheTransactionEntry_Committments_ForTranche].[Amount],
> ttblTradeEntryFunds.Shares

> [qryTrancheTransactionEntry_Committments_ForTranche].[FundShares];
> ============================================SELECT

> Sum(ttblTradeEntryFunds.Shares) AS SumOfShares,
> Sum(ttblTradeEntryFunds.Amount) AS SumOfAmount,
> Sum(ttblTradeEntryFunds.Percent) AS SumOfPercent FROM ttblTradeEntryFunds;

> ============================================SELECT

> ttblTradeEntryFunds.FundName, Sum(ttblTradeEntryFunds.Shares) AS
> SumOfShares, Sum(ttblTradeEntryFunds.Amount) AS SumOfAmount,
> Sum(ttblTradeEntryFunds.Percent) AS SumOfPercent FROM ttblTradeEntryFunds
> GROUP BY ttblTradeEntryFunds.FundName HAVING
> (((Sum(ttblTradeEntryFunds.Shares))>0)) OR
> (((Sum(ttblTradeEntryFunds.Amount))>0)) OR
> (((Sum(ttblTradeEntryFunds.Percent))>0));

> ============================================SELECT tlkpFund.FundName,

> tblTrade.ParValue, Sum(tblTradeFund.Shares) AS SumOfShares
> FROM tlkpFund RIGHT JOIN (tblTrade LEFT JOIN tblTradeFund ON
> tblTrade.TradeID = tblTradeFund.TradeID) ON tlkpFund.FundID

> tblTradeFund.FundID WHERE
> (((tblTrade.TrancheID)=[forms]![frmTrancheTransactionEntry].[txtTrancheID])
> AND ((tblTrade.TradeTypeID)<>4 And (tblTrade.TradeTypeID)<>5)) GROUP BY
> tlkpFund.FundName, tblTrade.ParValue HAVING
> (((Sum(tblTradeFund.Shares))<>0 And (Sum(tblTradeFund.Shares)) Is Not
> Null)) ORDER BY tlkpFund.FundName;

> ============================================--
> Pete Cresswell
>

Nothing obvious in the SQLs

The "Query Parameters 'Table thing'" is when you build a query in Access
2010, there is an option on the ribbon next to the Totals (Sigma, if your
Greek is OK) which says "Parameters". If you press that, there are 2 columns
labled "Parameter" and "Data Type". Something similat has existed in all
versions of Access, but I am not sure where you find it an Access 2007.

Have you had a look at all the queries for Combo boxes, List boxes and
subforms.

You didn't answer the question whether all - I stress ALL associated queries
ran without asking for the parameters.

Phil

[(PeteCresswell)]

Per Phil:

>You didn't answer the question whether all - I stress ALL associated queries
>ran without asking for the parameters.

In 2007, I *think* opened each one from the query list and there were no
problems - which seems consistent with the observation that the SQL
looks OK.

"Think" because maybe I'm missing something.

Next thing I'll go back and start removing things from the problem form
and see when the prompts stop...

--
Pete Cresswell

[(PeteCresswell)]

Per (PeteCresswell):

>"Think" because maybe I'm missing something.
>
>Next thing I'll go back and start removing things from the problem form
>and see when the prompts stop...

Yeah... my assumption about multiple forms was totally off-base.

Started deleting subforms and testing. First subform I deleted turned
out to have the problem in it. Multiple prompts because it was getting
re-queried multiple times.

The SQL for that one is:
======================================================================

SELECT tlkpFund.FundName, tblTrade.ParValue, Sum(tblTradeFund.Shares) AS
SumOfShares
FROM tlkpFund RIGHT JOIN (tblTrade LEFT JOIN tblTradeFund ON

tblTrade.TradeID = tblTradeFund.TradeID) ON tlkpFund.FundID =

tblTradeFund.FundID
WHERE
(((tblTrade.TrancheID)=[forms]![frmTrancheTransactionEntry].[txtTrancheID])
AND ((tblTrade.TradeTypeID)<>4 And (tblTrade.TradeTypeID)<>5))
GROUP BY tlkpFund.FundName, tblTrade.ParValue
HAVING (((Sum(tblTradeFund.Shares))<>0 And (Sum(tblTradeFund.Shares)) Is
Not Null))
ORDER BY tlkpFund.FundName;

=======================================================================

Remove the form's .RecordSource (i.e. the SQL above) and the problem
goes away.

--
Pete Cresswell

[(PeteCresswell)]

Per (PeteCresswell):

>Next thing I'll go back and start removing things from the problem form
>and see when the prompts stop...

Got it.

The problem form had "#,###.000" in OrderBy.

My guess is it's been there for a long time, but Access 2003 gave it a
pass and 2007 did not. Certainly when I went back a couple of
releases, it was there...

Interesting, though, that the prompt referred to "###.000" instead of
the actual value "#,###.000".
--
Pete Cresswell

[Phil]

Don't you kick yourself when you find it. Glad it's sorted

Phil