Answer needed ASAP

[Guest]

I am stumped!
I have 3 fields.
Reorder_qty
Maintain_qty
Physical_Count

This is what I have written so far.

Qty_to_order:
IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count].

I basically want the query to give me a total of how much to order when the
Physical count is less than or equal to the reorder level. But it needs to
generate the difference between the maintain qty and the physical count.

What am I doing wrong?

 

[ChrisM]

I am stumped!
I have 3 fields.
Reorder_qty
Maintain_qty
Physical_Count

This is what I have written so far.

Qty_to_order:
IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count].

I basically want the query to give me a total of how much to order when
the
Physical count is less than or equal to the reorder level. But it needs
to
generate the difference between the maintain qty and the physical count.

What am I doing wrong?

You don't actually say what the problem is, maybe you could give a few more
details.
In the meantime, may I just correct what I assume is a typo in your code, if
it isn't then that may be your problem:

IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count],0)

Cheers,

Chris.

 

[Guest]

Chris,
I apologize for being so vauge.
The Qty_to_order field is a caluclated field in the query. Right now it is
written simply as Qty_to_Order: [maintain_qty]-[Physical_Count]. This gives
me what I want. However thinking of exactly what I needthis query to do, I
discovered that I need to add a new field which is the ReOrder Level Field.
I added the field to my main table. So I thought I would write the code that
I listed below becuase I would like the Qty_to_order field to populate the
difference between the Maintaining filed and the Physical field only if the
physical count is less than or equal to the reorder level.
Now when I run the query...I get asked a "parameter" question pertaining to
the reorder level, and none of the calculation work any more. Confused?????
Me too!

I am stumped!
I have 3 fields.
Reorder_qty
Maintain_qty
Physical_Count

This is what I have written so far.

Qty_to_order:
IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count].

I basically want the query to give me a total of how much to order when
the
Physical count is less than or equal to the reorder level. But it needs
to
generate the difference between the maintain qty and the physical count.

What am I doing wrong?

You don't actually say what the problem is, maybe you could give a few more
details.
In the meantime, may I just correct what I assume is a typo in your code, if
it isn't then that may be your problem:

IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count],0)

Cheers,

Chris.

 

[ChrisM]

Hmm,

What you had in your OP (or at least my correction to it) seems perfectly
fine to me.
Double check your spelling, and make sure the field names in the calculated
field are EXACTLY the same as the astual field names (check
underscores/spaces and everything). If its prompting you for 'reorder level'
then I'm guessing that's most likely the one that is different?
I notice in your post, you refer to 'Reorder Level' but in your original
post, your field is 'Reorder_qty'...?

Cheers,

Chris.

Chris,
I apologize for being so vauge.
The Qty_to_order field is a caluclated field in the query. Right now it
is
written simply as Qty_to_Order: [maintain_qty]-[Physical_Count]. This
gives
me what I want. However thinking of exactly what I needthis query to do,
I
discovered that I need to add a new field which is the ReOrder Level
Field.
I added the field to my main table. So I thought I would write the code
that
I listed below becuase I would like the Qty_to_order field to populate the
difference between the Maintaining filed and the Physical field only if
the
physical count is less than or equal to the reorder level.
Now when I run the query...I get asked a "parameter" question pertaining
to
the reorder level, and none of the calculation work any more.
Confused?????
Me too!

I am stumped!
I have 3 fields.
Reorder_qty
Maintain_qty
Physical_Count

This is what I have written so far.

Qty_to_order:
IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count].

I basically want the query to give me a total of how much to order when
the
Physical count is less than or equal to the reorder level. But it
needs
to
generate the difference between the maintain qty and the physical
count.

What am I doing wrong?

You don't actually say what the problem is, maybe you could give a few
more
details.
In the meantime, may I just correct what I assume is a typo in your code,
if
it isn't then that may be your problem:

IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count],0)

Cheers,

Chris.

 

[Guest]

I have checked and double checked... I even created a whole new query. I
just don't know.....It should work, but it isn't///

Hmm,

What you had in your OP (or at least my correction to it) seems perfectly
fine to me.
Double check your spelling, and make sure the field names in the calculated
field are EXACTLY the same as the astual field names (check
underscores/spaces and everything). If its prompting you for 'reorder level'
then I'm guessing that's most likely the one that is different?
I notice in your post, you refer to 'Reorder Level' but in your original
post, your field is 'Reorder_qty'...?

Cheers,

Chris.

Chris,
I apologize for being so vauge.
The Qty_to_order field is a caluclated field in the query. Right now it
is
written simply as Qty_to_Order: [maintain_qty]-[Physical_Count]. This
gives
me what I want. However thinking of exactly what I needthis query to do,
I
discovered that I need to add a new field which is the ReOrder Level
Field.
I added the field to my main table. So I thought I would write the code
that
I listed below becuase I would like the Qty_to_order field to populate the
difference between the Maintaining filed and the Physical field only if
the
physical count is less than or equal to the reorder level.
Now when I run the query...I get asked a "parameter" question pertaining
to
the reorder level, and none of the calculation work any more.
Confused?????
Me too!

I am stumped!
I have 3 fields.
Reorder_qty
Maintain_qty
Physical_Count

This is what I have written so far.

Qty_to_order:
IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count].

I basically want the query to give me a total of how much to order when
the
Physical count is less than or equal to the reorder level. But it
needs
to
generate the difference between the maintain qty and the physical
count.

What am I doing wrong?

You don't actually say what the problem is, maybe you could give a few
more
details.
In the meantime, may I just correct what I assume is a typo in your code,
if
it isn't then that may be your problem:

IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count],0)

Cheers,

Chris.

 

[ChrisM]

If you can,

In the query design screen, select the SQL view, and post the SQL from the
query here

Cheers,

Chris.

I have checked and double checked... I even created a whole new query. I
just don't know.....It should work, but it isn't///

Hmm,

What you had in your OP (or at least my correction to it) seems perfectly
fine to me.
Double check your spelling, and make sure the field names in the
calculated
field are EXACTLY the same as the astual field names (check
underscores/spaces and everything). If its prompting you for 'reorder
level'
then I'm guessing that's most likely the one that is different?
I notice in your post, you refer to 'Reorder Level' but in your original
post, your field is 'Reorder_qty'...?

Cheers,

Chris.

Chris,
I apologize for being so vauge.
The Qty_to_order field is a caluclated field in the query. Right now
it
is
written simply as Qty_to_Order: [maintain_qty]-[Physical_Count]. This
gives
me what I want. However thinking of exactly what I needthis query to
do,
I
discovered that I need to add a new field which is the ReOrder Level
Field.
I added the field to my main table. So I thought I would write the
code
that
I listed below becuase I would like the Qty_to_order field to populate
the
difference between the Maintaining filed and the Physical field only if
the
physical count is less than or equal to the reorder level.
Now when I run the query...I get asked a "parameter" question
pertaining
to
the reorder level, and none of the calculation work any more.
Confused?????
Me too!


:


I am stumped!
I have 3 fields.
Reorder_qty
Maintain_qty
Physical_Count

This is what I have written so far.

Qty_to_order:
IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count].

I basically want the query to give me a total of how much to order
when
the
Physical count is less than or equal to the reorder level. But it
needs
to
generate the difference between the maintain qty and the physical
count.

What am I doing wrong?

You don't actually say what the problem is, maybe you could give a few
more
details.
In the meantime, may I just correct what I assume is a typo in your
code,
if
it isn't then that may be your problem:

IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count],0)

Cheers,

Chris.

 

[Guest]

Here you go Chris

SELECT Supply_Table.*,
IIf([physical_count]<=[ReOrder_level],[qty_to_maintain]-[physical_count]) AS
Qty_to_Order
FROM Supply_Table
WHERE (((Supply_Table.Show) Like Yes))
ORDER BY Supply_Table.Product_Description;

If you can,

In the query design screen, select the SQL view, and post the SQL from the
query here

Cheers,

Chris.

I have checked and double checked... I even created a whole new query. I
just don't know.....It should work, but it isn't///

Hmm,

What you had in your OP (or at least my correction to it) seems perfectly
fine to me.
Double check your spelling, and make sure the field names in the
calculated
field are EXACTLY the same as the astual field names (check
underscores/spaces and everything). If its prompting you for 'reorder
level'
then I'm guessing that's most likely the one that is different?
I notice in your post, you refer to 'Reorder Level' but in your original
post, your field is 'Reorder_qty'...?

Cheers,

Chris.

Chris,
I apologize for being so vauge.
The Qty_to_order field is a caluclated field in the query. Right now
it
is
written simply as Qty_to_Order: [maintain_qty]-[Physical_Count]. This
gives
me what I want. However thinking of exactly what I needthis query to
do,
I
discovered that I need to add a new field which is the ReOrder Level
Field.
I added the field to my main table. So I thought I would write the
code
that
I listed below becuase I would like the Qty_to_order field to populate
the
difference between the Maintaining filed and the Physical field only if
the
physical count is less than or equal to the reorder level.
Now when I run the query...I get asked a "parameter" question
pertaining
to
the reorder level, and none of the calculation work any more.
Confused?????
Me too!


:


I am stumped!
I have 3 fields.
Reorder_qty
Maintain_qty
Physical_Count

This is what I have written so far.

Qty_to_order:
IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count].

I basically want the query to give me a total of how much to order
when
the
Physical count is less than or equal to the reorder level. But it
needs
to
generate the difference between the maintain qty and the physical
count.

What am I doing wrong?

You don't actually say what the problem is, maybe you could give a few
more
details.
In the meantime, may I just correct what I assume is a typo in your
code,
if
it isn't then that may be your problem:

IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count],0)

Cheers,

Chris.

 

[Douglas J Steele]

WHERE (((Supply_Table.Show) Like Yes))

is incorrect

If Show is a boolean field (Yes/No), that should be:

WHERE (((Supply_Table.Show) = True))

If Show is a text field with Yes as a value in it, use

WHERE (((Supply_Table.Show) = "Yes"))

If Show is a text field and you want all rows where Show starts with the
word Yes, use

WHERE (((Supply_Table.Show) Like "Yes*"))

If Show is a text field and you want all rows where Show contains the word
Yes anywhere in the string, use

WHERE (((Supply_Table.Show) Like "*Yes*"))

If you're using ADO to run the query, rather than Access's normal DAO,
replace * with % in the last two examples.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here you go Chris

SELECT Supply_Table.*,
IIf([physical_count]<=[ReOrder_level],[qty_to_maintain]-[physical_count]) AS
Qty_to_Order
FROM Supply_Table
WHERE (((Supply_Table.Show) Like Yes))
ORDER BY Supply_Table.Product_Description;

If you can,

In the query design screen, select the SQL view, and post the SQL from the
query here

Cheers,

Chris.

I have checked and double checked... I even created a whole new query. I
just don't know.....It should work, but it isn't///

:

Hmm,

What you had in your OP (or at least my correction to it) seems perfectly
fine to me.
Double check your spelling, and make sure the field names in the
calculated
field are EXACTLY the same as the astual field names (check
underscores/spaces and everything). If its prompting you for 'reorder
level'
then I'm guessing that's most likely the one that is different?
I notice in your post, you refer to 'Reorder Level' but in your original
post, your field is 'Reorder_qty'...?

Cheers,

Chris.

Chris,
I apologize for being so vauge.
The Qty_to_order field is a caluclated field in the query. Right now
it
is
written simply as Qty_to_Order: [maintain_qty]-[Physical_Count]. This
gives
me what I want. However thinking of exactly what I needthis query to
do,
I
discovered that I need to add a new field which is the ReOrder Level
Field.
I added the field to my main table. So I thought I would write the
code
that
I listed below becuase I would like the Qty_to_order field to populate
the
difference between the Maintaining filed and the Physical field only if
the
physical count is less than or equal to the reorder level.
Now when I run the query...I get asked a "parameter" question
pertaining
to
the reorder level, and none of the calculation work any more.
Confused?????
Me too!


:


I am stumped!
I have 3 fields.
Reorder_qty
Maintain_qty
Physical_Count

This is what I have written so far.

Qty_to_order:
IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count].

I basically want the query to give me a total of how much to order
when
the
Physical count is less than or equal to the reorder level. But it
needs
to
generate the difference between the maintain qty and the physical
count.

What am I doing wrong?

You don't actually say what the problem is, maybe you could give a few
more
details.
In the meantime, may I just correct what I assume is a typo in your
code,
if
it isn't then that may be your problem:

IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count],0)

Cheers,

Chris.

 

[Guest]

Douglas,
I am not trying to sound ignorant, but I don't write in the SQL area. I
normally just create the queries in design view. The only thing I did was a
chose the * to choose all the fields from the main talbe "Supply_Table" to
create me a query called "ShowYes" In this query is where I have created the
calulated field as I mentioned below. I do not want anything to show the
word Yes anywhere. forgive me because I don't understand what your are
telling me to try. I however would love to. I have been learning access
going on 2 years now, and have never really dove into this language.
Anything you can do to help will be greatly appreciated

WHERE (((Supply_Table.Show) Like Yes))

is incorrect

If Show is a boolean field (Yes/No), that should be:

WHERE (((Supply_Table.Show) = True))

If Show is a text field with Yes as a value in it, use

WHERE (((Supply_Table.Show) = "Yes"))

If Show is a text field and you want all rows where Show starts with the
word Yes, use

WHERE (((Supply_Table.Show) Like "Yes*"))

If Show is a text field and you want all rows where Show contains the word
Yes anywhere in the string, use

WHERE (((Supply_Table.Show) Like "*Yes*"))

If you're using ADO to run the query, rather than Access's normal DAO,
replace * with % in the last two examples.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Here you go Chris

SELECT Supply_Table.*,
IIf([physical_count]<=[ReOrder_level],[qty_to_maintain]-[physical_count]) AS
Qty_to_Order
FROM Supply_Table
WHERE (((Supply_Table.Show) Like Yes))
ORDER BY Supply_Table.Product_Description;

If you can,

In the query design screen, select the SQL view, and post the SQL from the
query here

Cheers,

Chris.

I have checked and double checked... I even created a whole new query. I
just don't know.....It should work, but it isn't///

:

Hmm,

What you had in your OP (or at least my correction to it) seems perfectly
fine to me.
Double check your spelling, and make sure the field names in the
calculated
field are EXACTLY the same as the astual field names (check
underscores/spaces and everything). If its prompting you for 'reorder
level'
then I'm guessing that's most likely the one that is different?
I notice in your post, you refer to 'Reorder Level' but in your original
post, your field is 'Reorder_qty'...?

Cheers,

Chris.

Chris,
I apologize for being so vauge.
The Qty_to_order field is a caluclated field in the query. Right now
it
is
written simply as Qty_to_Order: [maintain_qty]-[Physical_Count]. This
gives
me what I want. However thinking of exactly what I needthis query to
do,
I
discovered that I need to add a new field which is the ReOrder Level
Field.
I added the field to my main table. So I thought I would write the
code
that
I listed below becuase I would like the Qty_to_order field to populate
the
difference between the Maintaining filed and the Physical field only if
the
physical count is less than or equal to the reorder level.
Now when I run the query...I get asked a "parameter" question
pertaining
to
the reorder level, and none of the calculation work any more.
Confused?????
Me too!


:


I am stumped!
I have 3 fields.
Reorder_qty
Maintain_qty
Physical_Count

This is what I have written so far.

Qty_to_order:
IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count].

I basically want the query to give me a total of how much to order
when
the
Physical count is less than or equal to the reorder level. But it
needs
to
generate the difference between the maintain qty and the physical
count.

What am I doing wrong?

You don't actually say what the problem is, maybe you could give a few
more
details.
In the meantime, may I just correct what I assume is a typo in your
code,
if
it isn't then that may be your problem:

IIf([physical_Count]<=[Reorder_qty],[maintain_qty]-[Physical_Count],0)

Cheers,

Chris.

 

[Steve Schapel]

ktfrubel,

I think Doug was picking up on the part of yur posted SQL which says...
WHERE (((Supply_Table.Show) Like Yes))
This would indicate that if you look at the query in design view, you
will have a column in the query design grid for a field named Show, and
in the Criteria of that column is written Like Yes. Is that right? To
re-interpret Doug's comments, it depends whether the Show field is a
Yes/No data type, or a Text data type, or something else. If it's a
Yes/No data type, then the criteria should be simple Yes (or you could
also put True, or you could put -1). If it's a text field, then the
criteria should be in ""s, e.g. "Yes". In neither case should it have
the word Like.

But you are getting a parameter prompt. So here's my question related
to that... Does your Supply_Table table contain a field named
ReOrder_level spelt exactly like that?

 

[Guest]

OK...I understand that! I will fix that right away. Would this help with my
calculation question?

 

[Steve Schapel]

Ktfrubel,

As I understand it, your "calculation question" relates to the fact that
you are receiving a Parameter prompt, which means there is something in
the query that Access can't evaluate. So, as per my earlier question...
Does your Supply_Table table contain a field named ReOrder_level spelt
exactly like that?

 

[ChrisM]

How's it going?
If you're still having problems, you could email me an (empty?) copy of your
database if you like, and I'll have a quick look. This is starting to
intruige me now...

Remove Elvis's Shoes (Blue Suede) from my email address to reply.

Cheers,

Chris.