Page 1 of 3 123 LastLast
Results 1 to 15 of 36

Display the last matching date on form

  1. #1
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    31

    Question Display the last matching date on form

    Hello Brains Trust

    I have a text field on a form that displays the last date a service was taken for a machine.

    Code:
    =DMax("[Service Date]","xServices","MachineNo='" & [MachineNo] & "' AND Catagory='Service'")
    This works fine except where a customer has more than one machine. In that case when I use the record navigation buttons to display the next record, the text field shows this date on all records.

    Each machine has a unique number, so as I cycle through the machine records, the text box should not display the date because "MachineNo='" & [MachineNo] is not true.

    Ultimately what I want to do is to have the last service record display when a button is pressed where another field WorkOutstanding is not blank. The button will only display when the machine currently displayed on the form has WorkOutstanding from the last service.

    Obviously I can't proceed until I get the first part working correctly.

    Can anyone point out where I am going wrong?



    Thank you on advance.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,723
    Sorry, not clear to me what the issue is. Because
    "MachineNo='" & [MachineNo] is true. This expression is pulling MachineNo from the current record and finding the Max date for that MachineNo. Why would this not be appropriate?

    If you want to provide db for analysis, follow instructions at bottom of my post.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    31
    Quote Originally Posted by June7 View Post
    Sorry, not clear to me what the issue is. Because
    "MachineNo='" & [MachineNo] is true. This expression is pulling MachineNo from the current record and finding the Max date for that MachineNo. Why would this not be appropriate?

    If you want to provide db for analysis, follow instructions at bottom of my post.

    Hi June7
    Sorry that I was not clear.

    In this part of the application there are 2 tables involved; (there are actually more, but they don't affect this)
    Customer Machine Components
    xServices


    They both have the field MachineNo, which is used to match the service history to the machine number.

    Customers can have more than one machine (each with a unique number), and the xServices table will have records for all services for all machines for all customers. I just filter on MachineNo when I need to present service history/reports/forms etc for each machine.

    The xServices table has a field WorkOutstanding. If there is work outstanding then details are entered in here.

    So, for a customer with multiple machines, they may have an outstanding issue on one or more than one machine.

    When I bring up the form Machine Components, it shows details for one machine only for that customer. You then use record navigation buttons to move to the next machine record.

    This is the form on which I want to display a work outstanding button if xServices.WorkOutstanding is not blank for the last service for that machine number, as in the screenshot below

    Click image for larger version. 

Name:	Capture1.JPG 
Views:	15 
Size:	12.1 KB 
ID:	38720

    The date below the button is the date of the last service for that machine. I'm using this while I'm developing as an aid to make sure I'm getting the right record. The idea being, when the operator clicks on this button, it will bring up the last service record that shows the work outstanding.

    What is happening is that if I move to the next customer machine record, where there is no work outstanding, it displays the work outstanding from the incorrect machine number, as shown here.

    Click image for larger version. 

Name:	Capture2.JPG 
Views:	15 
Size:	12.1 KB 
ID:	38721

    Thanks for your offer to look at the DB, I'll await your response before I upload it.

    I really appreciate your feedback.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    doesn't your description of the problem imply that you need to include a flag for outstanding work? However, if 1 customer has 2 units that have that situation, or 2 outstanding flags for one or more units (i.e. any situation that can return 2 or more records) then DLookup is no good. Seems to me you ought to be using 1 or more subforms.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  5. #5
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    31
    Quote Originally Posted by Micron View Post
    doesn't your description of the problem imply that you need to include a flag for outstanding work? However, if 1 customer has 2 units that have that situation, or 2 outstanding flags for one or more units (i.e. any situation that can return 2 or more records) then DLookup is no good. Seems to me you ought to be using 1 or more subforms.
    Hello Micron
    Thanks for your suggestion. I did initially think of using a flag, but decided against it as I thought that entering data in to the WorkOutstanding field could be used as a flag (just test for content).

    Also, when I polled the users after the request was made for this addition, they all agreed that having a button appear (in red) would more likely get their attention. If there is no button, there is no problem. If a big red button appears, there is a problem.

    The user can only view one machine information form at a time. It already has a lot of information on it, so a services subform is not really an option.

    I'm not sure I understand why DLookup is not right for this situation.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,723
    The DMax is pulling ServiceDate without considering whether work is outstanding. What data type is WorkOutstanding field?

    You need to know if the last ServiceDate record is outstanding? I.e., pull value of WorkOutstanding field of the last service record. Doing this with domain aggregate functions can get complicated.

    DLookup("WorkOutstanding", "xServices", "MachineNo='" & [MachineNo] & "' AND Category='Service' AND ServiceDate=#" & DMax("[Service Date]", "xServices", "MachineNo='" & [MachineNo] & "' AND Catagory='Service'") & "#")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    That's not what I meant. june7 said it better
    The DMax is pulling ServiceDate without considering whether work is outstanding.
    Your explanation indicates there are 3 pieces of criteria - machine, date and outstanding work but you're only using DMax on 2. Still don't see why this isn't a main form that filters on customer and machine number and a subform for all dates and work types
    OR
    same main form + work type and subform for dates for that work type, or something similar.

  8. #8
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    31
    Hi June7
    The WorkOutstanding is a LongText Field

    Micorn. You are correct, there are 3 pieces of criteria. The Machine Components form does actually filter on customer (its called as a popup from the Main Customer Record Form). Clicking a button on the Machine Components form brings up the Services Form that is filtered by CustomerNo and MachineNo. It only shows services associated with that MachineNo.

    Guys, I'm obviously attempting something that I'm not knowledgeable enough to achieve on my own. I appreciate your assistance. This DB started in in 2005 and has been growing as the needs and number of users has grown. 15years ago it was difficult to imagine the direction it would take, still I think it works well and the users have very little difficulty using it.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,723
    Can machine assocate with multiple customers?

    Did you try code in post 6?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    31
    Hi June7

    MachineNo is unique. There are no duplicates allowed.

    I tried the code you suggested, but I had to modify it with correct field names. (I used simpler field name in the post for clarity - may have been a mistake)

    Code:
    DLookup("Follow Up Required", "xServices", "MachineNo='" & [MachineNo] & "' AND Category='Service' AND "Service Date"=#" & DMax("[Service Date]", "xServices", "MachineNo='" & [MachineNo] & "' AND Catagory='Service'") & "#")
    WorkOutstanding - Follow Up Required
    ServiceDate = Service Date

    but I get an error in the textbox #Error

    Syntax?

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,723
    Field names with spaces require enclose in [ ]

    "[Follow Up Required]"

    "[Service Date]"

    Advise not to use spaces nor punctuation/special characters in naming convention.

    Spelling: Category or Catagory - which is correct, you have both.

    So each machine is associated with only one customer?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    31
    Quote Originally Posted by June7 View Post
    Field names with spaces require enclose in [ ]
    Advise not to use spaces nor punctuation/special characters in naming convention.

    Spelling: Category or Catagory - which is correct, you have both.

    So each machine is associated with only one customer?
    Hi June7
    Thanks. I have trouble getting my head around when to use what punctuation operator.

    Way back in the day I (erroneously) thought that using real English would make readability easier for whoever I pass this on to. I no longer use spaces.

    Thanks for spotting the spelling error.

    Yes, each machine is associated with only one customer (they are built to order)

    I corrected the code
    Code:
    =DLookUp("[Follow Up Required]","xServices","MachineNo='" & [MachineNo] & "' AND Catagory='Service' AND "[Service Date]"=#" & DMax("[Service Date]","xServices","MachineNo='" & [MachineNo] & "' AND Catagory='Service'") & "#")
    but I get the error
    Click image for larger version. 

Name:	Capture.JPG 
Views:	16 
Size:	12.4 KB 
ID:	38725

    When I try to save it.

    I fail to see where the problem lies.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,723
    Extra " marks "[Service Date]"=#"

    Sorry, I should have seen that last post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    31
    Hi June7
    No problem
    Though I don't quite know what you mean.

    Code:
    =DLookUp("[Follow Up Required]","xServices","MachineNo='" & [MachineNo] & "' AND Category='Service' AND [Service Date]=#" & DMax("[Service Date]","xServices","MachineNo='" & [MachineNo] & "' AND Catagory='Service'") & "#")
    If I remove the extra " marks I don't get the error, but the text field still shows an error. #Error flashes up quickly then the textbox is blank. If I click in it #Error is displayed.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,723
    I have used this technique before and it is always a challenge.

    You still have Category and Catagory spellings.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-05-2018, 01:59 AM
  2. matching date Values
    By George in forum Modules
    Replies: 2
    Last Post: 07-13-2016, 08:56 PM
  3. Matching Date Formats for Query in a form
    By WickidWe in forum Queries
    Replies: 1
    Last Post: 01-15-2014, 03:19 PM
  4. Replies: 6
    Last Post: 12-27-2012, 10:49 AM
  5. Display Non Matching Data
    By k1fox in forum Access
    Replies: 1
    Last Post: 04-03-2009, 07:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums