Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Control Source (Office XP)

    Hi

    I am trying to build a new database to keep a record of purchase orders sent to suppliers.

    I have fields called Date Now, Date Sent, Date Received, Status, Days Elapsed.
    I would like Status to show Overdue if, if Date sent is 14 days< than Date now else "" ,but if there is an entry in Date Received I would like it to show Received, and in the Elapsed field to show the days different from Date Sent and Date Received.

    I hope this is not too much to ask

    Novice User

    Thanks

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    If you are a fool at forty, you will always be a fool

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Control Source (Office XP)

    Hi Hans

    Thanks for your reply, being a novice I don't fully understand what you mean calculated fields I took the status and days elapsed field out of the table

    and tried to do what you suggested I added a text box and entered the formula you gave me, but I get a name error.

    Sorry if I seem a bit dumb!
    If you are a fool at forty, you will always be a fool

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Control Source (Office XP)

    Hi Hans

    Typical Novice error I forgot to put the = sign in, However from the screenshot you can see the the status should say overdue and not recieved.

    Thanks
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Control Source (Office XP)

    <P ID="edit" class=small>(Edited by HansV on 18-Feb-03 14:22. Corrected Typo)</P>You shouln't put the Status and Days Elapsed fields in the table; they should be calculated fields in a query. The expressions for these fields in the query design grid could be:

    Status:IIf(IsNull([Date Received]),IIf([Date Sent]<Date()-14,"Overdue",""),"Received")

    DaysElapsed:[Date Received]-[Date Sent]

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Control Source (Office XP)

    Hi Braddy,

    1. I got the order of the arguments wrong in Status - sorry about that. It should have been

    IIf(IsNull([Date Received]),IIf([Date Sent]<Date()-14,"Overdue",""),"Received")

    (If you use this as control souce of a text box, precede it with =). I have corrected my previous reply.

    2. You can use these expressions as control source of text boxes on a form - in that case, you should put = before them, as you found out. However, I would suggest including the calculated fields in a query based on your table, and use the query as record source for forms, reports etc.

    When you are designing a query, you can drag fields from the fields list of the table to the design grid, or select a field from the dropdown list. But it is also possible to add fields to the design grid that are calculated each time the query is opened. You do this by typing the caption you want the calculated field to have, followed by a colon and then the expression to be calculated. The expressions in my previous reply were meant this way, for example, after correcting my stupid error:

    Status:IIf(IsNull([Date Received]),IIf([Date Sent]<Date()-14,"Overdue",""),"Received")

    This will add a column named Status to the query that calculates the IIf(...) expression.

    When you use the query as record source for a form or report, you can add Status just as if it were a field in the underlying table; the only difference is that the user can't edit the value, because it's calculated.

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Control Source (Office XP)

    Apologies for butting in, but I've included a visual representation of Hans' point about setting up a query.
    It may help, depending on your experience with the QBE grid.
    Attached Images Attached Images
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Control Source (Office XP)

    Hi Hans

    Thanks very much that works just fine, Once again thank you.

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Control Source (Office XP)

    Hi
    Bandido

    No apoligies neeeded, I can use all the help I can get!!

    Thanks
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •