Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Answer needed ASAP

Discussion in 'Information Technology' started by ktfrubel, Jul 28, 2006.

  1. ktfrubel

    ktfrubel
    Expand Collapse
    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?
     
  2. Loading...


  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    news:B989E791-1CEF-4F38-86C8-199318DCD359@microsoft.com...
    >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.
     
  4. ktfrubel

    ktfrubel
    Expand Collapse
    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!


    "ChrisM" wrote:

    >
    > "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > news:B989E791-1CEF-4F38-86C8-199318DCD359@microsoft.com...
    > >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.
    >
    >
    >
     
  5. ChrisM

    ChrisM
    Expand Collapse
    Guest

    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.

    "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    news:357BCF36-1E3C-4DB5-B9CC-377E29A8708E@microsoft.com...
    > 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!
    >
    >
    > "ChrisM" wrote:
    >
    >>
    >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    >> news:B989E791-1CEF-4F38-86C8-199318DCD359@microsoft.com...
    >> >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.
    >>
    >>
    >>
     
  6. ktfrubel

    ktfrubel
    Expand Collapse
    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///

    "ChrisM" wrote:

    > 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.
    >
    > "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > news:357BCF36-1E3C-4DB5-B9CC-377E29A8708E@microsoft.com...
    > > 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!
    > >
    > >
    > > "ChrisM" wrote:
    > >
    > >>
    > >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > >> news:B989E791-1CEF-4F38-86C8-199318DCD359@microsoft.com...
    > >> >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.
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. ChrisM

    ChrisM
    Expand Collapse
    Guest

    If you can,

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

    Cheers,

    Chris.

    "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    news:1F901AE9-21DC-49AA-8500-AF085ACF4419@microsoft.com...
    >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///
    >
    > "ChrisM" wrote:
    >
    >> 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.
    >>
    >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    >> news:357BCF36-1E3C-4DB5-B9CC-377E29A8708E@microsoft.com...
    >> > 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!
    >> >
    >> >
    >> > "ChrisM" wrote:
    >> >
    >> >>
    >> >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    >> >> news:B989E791-1CEF-4F38-86C8-199318DCD359@microsoft.com...
    >> >> >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.
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  8. ktfrubel

    ktfrubel
    Expand Collapse
    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;


    "ChrisM" wrote:

    > If you can,
    >
    > In the query design screen, select the SQL view, and post the SQL from the
    > query here
    >
    > Cheers,
    >
    > Chris.
    >
    > "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > news:1F901AE9-21DC-49AA-8500-AF085ACF4419@microsoft.com...
    > >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///
    > >
    > > "ChrisM" wrote:
    > >
    > >> 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.
    > >>
    > >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > >> news:357BCF36-1E3C-4DB5-B9CC-377E29A8708E@microsoft.com...
    > >> > 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!
    > >> >
    > >> >
    > >> > "ChrisM" wrote:
    > >> >
    > >> >>
    > >> >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > >> >> news:B989E791-1CEF-4F38-86C8-199318DCD359@microsoft.com...
    > >> >> >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.
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     
  9. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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
    http://I.Am/DougSteele
    (no e-mails, please!)


    "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    news:9AEBE6DB-0D2B-42CE-B6F9-E82801DE2A89@microsoft.com...
    > 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;
    >
    >
    > "ChrisM" wrote:
    >
    > > If you can,
    > >
    > > In the query design screen, select the SQL view, and post the SQL from

    the
    > > query here
    > >
    > > Cheers,
    > >
    > > Chris.
    > >
    > > "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > > news:1F901AE9-21DC-49AA-8500-AF085ACF4419@microsoft.com...
    > > >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///
    > > >
    > > > "ChrisM" wrote:
    > > >
    > > >> 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.
    > > >>
    > > >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > > >> news:357BCF36-1E3C-4DB5-B9CC-377E29A8708E@microsoft.com...
    > > >> > 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!
    > > >> >
    > > >> >
    > > >> > "ChrisM" wrote:
    > > >> >
    > > >> >>
    > > >> >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > > >> >> news:B989E791-1CEF-4F38-86C8-199318DCD359@microsoft.com...
    > > >> >> >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.
    > > >> >>
    > > >> >>
    > > >> >>
    > > >>
    > > >>
    > > >>

    > >
    > >
    > >
     
  10. ktfrubel

    ktfrubel
    Expand Collapse
    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

    "Douglas J Steele" wrote:

    > 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
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > news:9AEBE6DB-0D2B-42CE-B6F9-E82801DE2A89@microsoft.com...
    > > 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;
    > >
    > >
    > > "ChrisM" wrote:
    > >
    > > > If you can,
    > > >
    > > > In the query design screen, select the SQL view, and post the SQL from

    > the
    > > > query here
    > > >
    > > > Cheers,
    > > >
    > > > Chris.
    > > >
    > > > "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > > > news:1F901AE9-21DC-49AA-8500-AF085ACF4419@microsoft.com...
    > > > >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///
    > > > >
    > > > > "ChrisM" wrote:
    > > > >
    > > > >> 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.
    > > > >>
    > > > >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > > > >> news:357BCF36-1E3C-4DB5-B9CC-377E29A8708E@microsoft.com...
    > > > >> > 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!
    > > > >> >
    > > > >> >
    > > > >> > "ChrisM" wrote:
    > > > >> >
    > > > >> >>
    > > > >> >> "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    > > > >> >> news:B989E791-1CEF-4F38-86C8-199318DCD359@microsoft.com...
    > > > >> >> >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.
    > > > >> >>
    > > > >> >>
    > > > >> >>
    > > > >>
    > > > >>
    > > > >>
    > > >
    > > >
    > > >

    >
    >
    >
     
  11. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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?

    --
    Steve Schapel, Microsoft Access MVP

    ktfrubel wrote:
    > 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
     
  12. ktfrubel

    ktfrubel
    Expand Collapse
    Guest

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

    "Steve Schapel" wrote:

    > 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?
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > ktfrubel wrote:
    > > 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

    >
     
  13. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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?

    --
    Steve Schapel, Microsoft Access MVP

    ktfrubel wrote:
    > OK...I understand that! I will fix that right away. Would this help with my
    > calculation question?
    >
     
  14. ChrisM

    ChrisM
    Expand Collapse
    Guest

    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.

    "ktfrubel" <ktfrubel@discussions.microsoft.com> wrote in message
    news:B68AA6CD-3B0C-49AF-8167-91BEB31676AD@microsoft.com...
    > OK...I understand that! I will fix that right away. Would this help with
    > my
    > calculation question?
    >
    > "Steve Schapel" wrote:
    >
    >> 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?
    >>
    >> --
    >> Steve Schapel, Microsoft Access MVP
    >>
    >> ktfrubel wrote:
    >> > 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

    >>
     

Share This Page