"Enter Paramter Value" Prompt - how do I stop it?

[Guest]

I have a calculation in a query that is as follows

M3: [Width]*[Thick]*[Lineal]/1000000

The "Lineal" feild refers to a calculated feild in the same queary as this
new "M3" calculation but everytime I run it in datasheet view I get the
following promt box:

"Enter Parameter Value"
Lineal

If I just hit OK it returns the correct result but how do I stop this prompt
appearing?

"Show" is checked in the "Lineal" calculated field as per the help
instructions.

Thanks

JohnG

 

[Guest]

You can either use the calcuatin again
Instead of
[Width]*[Thick]*[Lineal]/1000000

Use
[Width]*[Thick]*(Use here the calculation for Lineal)/1000000

Or, create another query based on the first one that doesnt include this
calculation -([Width]*[Thick]*[Lineal]/1000000), and there you can use the
Lineal field as a field.

 

[Guest]

Thanks Ofer,

Heres the whole expression that calcualtes "Lineal"

I can't seem to get this into the M3 calculation without some bracket
problems.

[Width]*[Thick]*(Use here the calculation for Lineal)/1000000

Would prefer not to write another queary and have it all happening in this
one:

Maybe you can look at it and let me know what's wrong?

Lineal: NZ([Production Data]![900]*0.9)+NZ([Production
Data]![1200]*1.2)+NZ([Production Data]![1500]*1.5)+NZ([Production
Data]![1800]*1.8)+NZ([Production Data]![2100]*2.1)+NZ([Production
Data]![2400]*2.4)+NZ([Production Data]![2700]*2.7)+NZ([Production
Data]![3000]*3)+NZ([Production Data]![3300]*3.3)+NZ([Production
Data]![3600]*3.6)+NZ([Production Data]![3900]*3.9)+NZ([Production
Data]![4200]*4.2)+NZ([Production Data]![4500]*4.5)+NZ([Production
Data]![4800]*4.8)+NZ([Production Data]![5100]*5.1)+NZ([Production
Data]![5200]*5.2)+NZ([Production Data]![5400]*5.4)+NZ([Production
Data]![5700]*5.7)+NZ([Production Data]![6000]*6)+NZ([Production
Data]![6600]*6.6)+NZ([Production Data]![7200]*7.2)

The lineal calculation works well, I just need to multiply it's result it by
the width and thick and then divide by 1000000 to get M3 result.

Thanks

JohnG from New Zealand






--
JohnG

You can either use the calcuatin again
Instead of
[Width]*[Thick]*[Lineal]/1000000

Use
[Width]*[Thick]*(Use here the calculation for Lineal)/1000000

Or, create another query based on the first one that doesnt include this
calculation -([Width]*[Thick]*[Lineal]/1000000), and there you can use the
Lineal field as a field.

--
Good Luck
BS"D

I have a calculation in a query that is as follows

M3: [Width]*[Thick]*[Lineal]/1000000

The "Lineal" feild refers to a calculated feild in the same queary as this
new "M3" calculation but everytime I run it in datasheet view I get the
following promt box:

"Enter Parameter Value"
Lineal

If I just hit OK it returns the correct result but how do I stop this prompt
appearing?

"Show" is checked in the "Lineal" calculated field as per the help
instructions.

Thanks

JohnG

 

[John W. Vinson]

Thanks Ofer,

Heres the whole expression that calcualtes "Lineal"

I can't seem to get this into the M3 calculation without some bracket
problems.

[Width]*[Thick]*(Use here the calculation for Lineal)/1000000

Would prefer not to write another queary and have it all happening in this
one:

Maybe you can look at it and let me know what's wrong?

Lineal: NZ([Production Data]![900]*0.9)+NZ([Production
Data]![1200]*1.2)+NZ([Production Data]![1500]*1.5)+NZ([Production
Data]![1800]*1.8)+NZ([Production Data]![2100]*2.1)+NZ([Production
Data]![2400]*2.4)+NZ([Production Data]![2700]*2.7)+NZ([Production
Data]![3000]*3)+NZ([Production Data]![3300]*3.3)+NZ([Production
Data]![3600]*3.6)+NZ([Production Data]![3900]*3.9)+NZ([Production
Data]![4200]*4.2)+NZ([Production Data]![4500]*4.5)+NZ([Production
Data]![4800]*4.8)+NZ([Production Data]![5100]*5.1)+NZ([Production
Data]![5200]*5.2)+NZ([Production Data]![5400]*5.4)+NZ([Production
Data]![5700]*5.7)+NZ([Production Data]![6000]*6)+NZ([Production
Data]![6600]*6.6)+NZ([Production Data]![7200]*7.2)

The lineal calculation works well, I just need to multiply it's result it by
the width and thick and then divide by 1000000 to get M3 result.

Your table design (as noted in my post from last week) *is wrong*.

You will have no end of complications if you insist on storing the length in
fieldnames.

Normalize your data and this expression becomes trivially easy: [Width] *
[Thick] * [Length].

John W. Vinson [MVP]

 

[Guest]

Hi John,
I would love to take your advice but I am a real novice at this stuff and to
be honest I have no idea what you are talking about or how to do what you are
asking. I don't even know where to start. I tried the manual and help but
still came up blank.Maybe I could email you the file so you could see what I
am trying to acheive and maybe very quickly help me out?
Thanks
JohnG
--
JohnG

Thanks Ofer,

Heres the whole expression that calcualtes "Lineal"

I can't seem to get this into the M3 calculation without some bracket
problems.

[Width]*[Thick]*(Use here the calculation for Lineal)/1000000

Would prefer not to write another queary and have it all happening in this
one:

Maybe you can look at it and let me know what's wrong?

Lineal: NZ([Production Data]![900]*0.9)+NZ([Production
Data]![1200]*1.2)+NZ([Production Data]![1500]*1.5)+NZ([Production
Data]![1800]*1.8)+NZ([Production Data]![2100]*2.1)+NZ([Production
Data]![2400]*2.4)+NZ([Production Data]![2700]*2.7)+NZ([Production
Data]![3000]*3)+NZ([Production Data]![3300]*3.3)+NZ([Production
Data]![3600]*3.6)+NZ([Production Data]![3900]*3.9)+NZ([Production
Data]![4200]*4.2)+NZ([Production Data]![4500]*4.5)+NZ([Production
Data]![4800]*4.8)+NZ([Production Data]![5100]*5.1)+NZ([Production
Data]![5200]*5.2)+NZ([Production Data]![5400]*5.4)+NZ([Production
Data]![5700]*5.7)+NZ([Production Data]![6000]*6)+NZ([Production
Data]![6600]*6.6)+NZ([Production Data]![7200]*7.2)

The lineal calculation works well, I just need to multiply it's result it by
the width and thick and then divide by 1000000 to get M3 result.

Your table design (as noted in my post from last week) *is wrong*.

You will have no end of complications if you insist on storing the length in
fieldnames.

Normalize your data and this expression becomes trivially easy: [Width] *
[Thick] * [Length].

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,
I would love to take your advice but I am a real novice at this stuff and to
be honest I have no idea what you are talking about or how to do what you are
asking. I don't even know where to start. I tried the manual and help but
still came up blank.Maybe I could email you the file so you could see what I
am trying to acheive and maybe very quickly help me out?
Thanks
JohnG

Well... I'm a self-employed database design consultant. What you're suggesting
is what I do for a living; it's a bit beyond what I'm comfortable offering as
a free service. If you're interested in hiring me, let me know by email and
I'll send you my terms.

My suggestion was that you restructure your table. A "Pack" of lumber consists
of multiple Boards, each board with a length, a width, and a thickness...
right? You're correctly storing the width and the thickness in fields with
those names, storing numeric values. The fact that you have a separate FIELD
for each length is the source of your problem!

You have a One (pack) to Many (board) relationship. Rather than handling that
one to many relationship as a spreadsheet - many fields within a single record
- consider using TWO TABLES in a one to many relationship: Packs and Boards.
The Boards table would have a PackID (as a link to the pack), and fields for
Length, Width, Thickness and Quantity. So if you have a pack containing 15
boards, 5cm thick, 25cm wide, 2m long, you would have a record with those
values; if the pack also contains boards of other sizes, you'ld have
additional records for THOSE boards. You can then easily use a Totals query to
calculate the lineal measurement (what my carpenter friends here in the
benighted US would call "board feed" I presume).

It will be possible (a bit tedious but not too bad) to use a "Normalizing
Union Query" to migrate the existing data from your wide-flat table into the
new table; and you can create a Crosstab query to *present* the data in
spreadsheet form if you wish.

John W. Vinson [MVP]