Page 3 of 3 FirstFirst 123
Results 31 to 36 of 36

Display the last matching date on form

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

    Yes it does. Basically it means that Access doesn't like it if your not in the US!.

    I understand what the problem is, but I don't know how to go about resolving it.



    Seriously though, it got me thinking that maybe I've been trying to be too clever, and don't really need to test for the Service Date all.

    All I really need to do is search the xServices table for the last entry for a customer for their machine number as defined on the Machine Components form. If there is a value in the Follow Up Required field then display the text box, otherwise don't display the text box. It should work in cases where customers have more than one machine as well.

    So I think If I remove the date criteria from the statement, this will give me the result that I want. What do you think? Something like this.

    =DLookUp("[Follow Up Required]","xServices","[Machine No]='" & [MachineNo] & "' AND DMax("[Machine No]","xServices","[Machine No]='" & [MachineNo] & "'") & "#")

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

    Following on from my last thought, I've been playing with this code.

    Code:
    =DMax("[Next Service]","xServices","[Machine No]='" & [MachineNo] & "'")
    Which seems to work just fine.

    However, when I change it to
    Code:
    =DMax("[Follow Up Required]","xServices","[Machine No]='" & [MachineNo] & "'")
    I get an #Error in the text box.

    Why is that? What do I need to do differently when the field I'm querying is a text field and not a date field?

  3. #33
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,433
    The #Error is because it is a Memo type field.

    DMax will not always grab the 'last' entry, it gets the value that is maximum regardless of record order. Z is greater than A.

    The expression in post 31 really doesn't make sense. Neither expression in post 32 will get you what you want, even if change to a Text type field.

    As I understand, dates need to be formatted in expression to a structure that disambiguate the date.

    Alternatively, if the autonumber record identifier can be relied on to always be positive and increasing (I have read this is not a guarantee but have never seen otherwise), use it instead of date to grab the 'last' record.

    =DLookUp("[Follow Up Required]","xServices","[Machine No]='" & [MachineNo] & "' AND [ServiceNo] =" & DMax("[ServiceNo]","xServices","[Machine No]='" & [MachineNo] & "'"))
    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.

  4. #34
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    31
    Quote Originally Posted by June7 View Post
    =DLookUp("[Follow Up Required]","xServices","[Machine No]='" & [MachineNo] & "' AND [ServiceNo] =" & DMax("[ServiceNo]","xServices","[Machine No]='" & [MachineNo] & "'"))
    Hi June7
    Thanks for explanation.

    Your solution works perfectly except in the case where there is no service record at all for a machine. In that case the text box throws up the #Error, which I understand and would expect. Whilst this isn't a huge problem (it indicates that there are no service records at all for that machine) I think it looks bad.

    On my form that displays the text box I'm currently doing this to display the text box as required. I would have expected the IsNull to trap the #Error, Obviously not and I don't quite see why it doesn't. Is it because the error from the DLookup() is being passed as a value to the text box?

    Code:
    Private Sub Form_Current()
    If IsNull(Me.txtWorkOutstanding) Then
        Me.txtWorkOutstanding.Visible = False
        Else
    '    MsgBox "There is work outstanding for this machine arising from the last service.", vbInformation, "Confirmation"
        Me.txtWorkOutstanding.Visible = True
    '    DoCmd.Beep
    End I
    Is there another way I can trap the #Error?

    I am grateful for you time and explanations. Believe it or not, this old dog is learning new tricks.

  5. #35
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,433
    Actually don't need MachineNo in the DLookup any more because ServiceNo is a unique value.

    The error is because DMax() returns Null when there is no match. Use Nz() function to provide a parameter when DMax() returns Null.

    =DLookUp("[Follow Up Required]","xServices","[ServiceNo] =" & Nz(DMax("[ServiceNo]","xServices","[Machine No]='" & [MachineNo] & "'"),0))
    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.

  6. #36
    sheusz is offline Advanced Beginner
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    31
    Quote Originally Posted by June7 View Post
    Actually don't need MachineNo in the DLookup any more because ServiceNo is a unique value.

    The error is because DMax() returns Null when there is no match. Use Nz() function to provide a parameter when DMax() returns Null.

    =DLookUp("[Follow Up Required]","xServices","[ServiceNo] =" & Nz(DMax("[ServiceNo]","xServices","[Machine No]='" & [MachineNo] & "'"),0))
    BOOM. And there it is. Simple in the end. And I actually understand what is going on in the expression.

    Thank you for so much help and bouncing ideas around. It seems that sometimes it's easy to overthink things.

    I really appreciate your assistance with this and hope I can be helpful to someone in return in the future.

Page 3 of 3 FirstFirst 123
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