Enter Parameter Value

[ngweixiong]

Hi,

I have a query to check for condition. but when i execute, a message box apprear. "Enter Parameter Value"

below is my code.

Stat: IIf([AvgOfTL PO and Inv]<7,[StatA],IIf([AvgOfTL PO and Inv]<10,[StatB]))

[puppydogbuddy]

Hi,

I have a query to check for condition. but when i execute, a message box apprear. "Enter Parameter Value"

below is my code.

Stat: IIf([AvgOfTL PO and Inv]<7,[StatA],IIf([AvgOfTL PO and Inv]<10,[StatB]))

If [StatA] and [StatB] are textboxes on your form, you need to provide a full reference as shown. Replace yourForm with the actual name of your form.

Stat: IIf([AvgOfTL PO and Inv]<7,Forms!YourForm![StatA],IIf([AvgOfTL PO and Inv]<10,Forms!YourForm![StatB]))

[NeoPa]

What's the prompt in the Message Box?

[ngweixiong]

If [StatA] and [StatB] are textboxes on your form, you need to provide a full reference as shown. Replace yourForm with the actual name of your form.

Stat: IIf([AvgOfTL PO and Inv]<7,Forms!YourForm![StatA],IIf([AvgOfTL PO and Inv]<10,Forms!YourForm![StatB]))

Hi,

I don't have any form in my Ms Access.

All i have is tables and query.

[ngweixiong]

What's the prompt in the Message Box?

Hi,

the prompt is " Enter Parameter Value"

[NeoPa]

You should have a Title (Enter Parameter Value), then a Prompt (?), then a box to enter your value in.
What is the Prompt?

[ngweixiong]

You should have a Title (Enter Parameter Value), then a Prompt (?), then a box to enter your value in.
What is the Prompt?

Hi,

the prompt is Stat A and Stat B

[ngweixiong]

Hi,

the prompt is Stat A and Stat B

SOrry,

what i mean is, firstly the prompt is Stat A, then after pressing enter,
it come out another prompt which is Stat B.

hope it will be clearer.

[NeoPa]

Right, in that case you are missing both of those fields from your query input.
As you have spelled them differently even here between two of your own posts, I think it very likely that you have done the same in your query. Check the spellings. Are they [StatA] or are they [Stat A]?
Do the same for [StatB] of course.

[ngweixiong]

Right, in that case you are missing both of those fields from your query input.
As you have spelled them differently even here between two of your own posts, I think it very likely that you have done the same in your query. Check the spellings. Are they [StatA] or are they [Stat A]?
Do the same for [StatB] of course.

Hi,

WHat do you mean by...
"missing both of those fields from your query input"

Please help..

[NeoPa]

I do TRY to help ;)
Your query must be taking its data from somewhere. This can be a single or multiple data sources. In the SQL view of your query you will see this (these) defined in the FROM clause. In the design view you will see it in the Tables area (above the definition of the fields you want the query to return).

It may be easier to help you if you posted the SQL string of the query that you are working on. That way we can help you more easily.

[ngweixiong]

I do TRY to help ;)
Your query must be taking its data from somewhere. This can be a single or multiple data sources. In the SQL view of your query you will see this (these) defined in the FROM clause. In the design view you will see it in the Tables area (above the definition of the fields you want the query to return).

It may be easier to help you if you posted the SQL string of the query that you are working on. That way we can help you more easily.

Hi,

below is my query codes:

SELECT [Average time lead between PO date and Inv Date].[AvgOfTL PO and Inv], IIf([AvgOfTL PO and Inv]<7,[StatA],IIf([AvgOfTL PO and Inv]<10,[StatB])) AS Stat
FROM [Average time lead between PO date and Inv Date];
 

Thanks alot

[NeoPa]

I'll have to look at this when I return on Thursday.
Please bump the thread if I skip it.

[ngweixiong]

I'll have to look at this when I return on Thursday.
Please bump the thread if I skip it.

*knock knock*

=)

kiNdly please help..

[NeoPa]

"Bump" received and understood. I'll do what I can when I can :)

[NeoPa]

Hi,

below is my query codes:

SELECT [Average time lead between PO date and Inv Date].[AvgOfTL PO and Inv], IIf([AvgOfTL PO and Inv]<7,[StatA],IIf([AvgOfTL PO and Inv]<10,[StatB])) AS Stat
FROM [Average time lead between PO date and Inv Date];
 

Thanks alot

In post #7 you say :

Hi,

the prompt is Stat A and Stat B

Looking at your SQL that cannot be true. Did you mean StatA & StatB instead?
Where do you expect the query to get StatA & StatB from?
Please answer both questions accurately and clearly.

[NeoPa]

In the mean time, I just want to redo your SQL in a form which is clearer to read and understand. It will be essentially the same as your version and is only done so that we can work with it more easily.

SELECT [AvgOfTL PO and Inv],
       IIf([AvgOfTL PO and Inv]<7,
           [StatA],
           IIf([AvgOfTL PO and Inv]<10,
               [StatB])) AS Stat
FROM [Average time lead between PO date and Inv Date];

Now, do you want to return the strings 'StatA' & 'StatB' instead of some strange undefined variables ([StatA] & [StatB])?

NB. You haven't provided a value for Stat in the case where [AvgOfTL PO and Inv]>=10. Perhaps you want the value "StatC"?

Don't forget to answer the questions in post #16 first.

[ngweixiong]

In post #7 you say :

Looking at your SQL that cannot be true. Did you mean StatA & StatB instead?
Where do you expect the query to get StatA & StatB from?
Please answer both questions accurately and clearly.

yeap. What i want is StatA and StatB.

eh? i though i could juz assign a "StatA" for the query.

THis is because, What i have is a condition query. iF tat query is less than 7, it will assign StatA.

[ngweixiong]

In the mean time, I just want to redo your SQL in a form which is clearer to read and understand. It will be essentially the same as your version and is only done so that we can work with it more easily.

SELECT [AvgOfTL PO and Inv],
       IIf([AvgOfTL PO and Inv]<7,
           [StatA],
           IIf([AvgOfTL PO and Inv]<10,
               [StatB])) AS Stat
FROM [Average time lead between PO date and Inv Date];

Now, do you want to return the strings 'StatA' & 'StatB' instead of some strange undefined variables ([StatA] & [StatB])?

NB. You haven't provided a value for Stat in the case where [AvgOfTL PO and Inv]>=10. Perhaps you want the value "StatC"?

Don't forget to answer the questions in post #16 first.

hmmm... for [AvgOfTL PO and Inv]>=10 i already define it as StatB.
i'm confused now..

[NeoPa]

hmmm... for [AvgOfTL PO and Inv]>=10 i already define it as StatB.
i'm confused now..

Your code assigns [StatB] (still unclear whether this should be a string 'StatB', or a field [StatB]) when [AvgOfTL PO and Inv] is >= 7 and < 10. A value of 10 or greater is unspecified.

[NeoPa]

yeap. What i want is StatA and StatB.

eh? i though i could juz assign a "StatA" for the query.

THis is because, What i have is a condition query. iF tat query is less than 7, it will assign StatA.

"StatA" you CAN assign. [StatA] you can NOT. At least not without specifying where it should come from.
"StatA" = 'StatA' but within SQL 'StatA' is the more correct version.

[ngweixiong]

"StatA" you CAN assign. [StatA] you can NOT. At least not without specifying where it should come from.
"StatA" = 'StatA' but within SQL 'StatA' is the more correct version.

YEA!!

so [StatA] = field
while
'StatA' = Variable (can be assign)

now, it solve my problem.

thanks alot!!

=)

[NeoPa]

Nearly.
[StatA] is a field (correct), and will show the CONTENTS of [StatA] - whatever they are.
'StatA', like 'Hello World', is a literal string and will always come out as :

StatA

I'm very pleased you've solved your problem anyway.

[ngweixiong]

Nearly.
[StatA] is a field (correct), and will show the CONTENTS of [StatA] - whatever they are.
'StatA', like 'Hello World', is a literal string and will always come out as :

StatA

I'm very pleased you've solved your problem anyway.

i see i see..

lesson learnt!

=)