Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26

Display the last matching date on form

  1. #16
    sheusz is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    17
    Thanks June7



    You show remarkable patience.

    Not sure how the misspelling crept back in. OK, so this code doesn't throw any errors.

    Code:
    =DLookUp("[Follow Up Required]","xServices","MachineNo='" & [MachineNo] & "' AND Catagory='Service' AND [Service Date] =#" & DMax("[Service Date]","xServices","MachineNo='" & [MachineNo] & "' AND Catagory='Service'") & "#")
    but it also doesn't show the service date result in the text box to which this code is tied.

    I have checked and the record I am testing with does have an entry in the "[Follow Up Required]" field for this customer and this machine.

    Also, I don't understand why there are on " marks around [Service Date] =#" in this part of the statement.

  2. #17
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,165
    If you want to provide db for analysis, follow instructions at bottom of my post. That may be the only way I can help further.

    Because that is part of string already within quote marks

    & "' AND Catagory='Service' AND [Service Date] =#" &
    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. #18
    sheusz is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    17
    Hi June7.
    Ok Thanks, It will take me a bit of time to get it ready to send.

    I appreciate your offer.

  4. #19
    sheusz is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    17
    Hi June7


    Quote Originally Posted by June7 View Post
    If you want to provide db for analysis, follow instructions at bottom of my post. That may be the only way I can help further.
    Please see attachment. Some explanatory notes are in the zip folder.

    sheusz.zip

    Once again, I appreciate your help.

  5. #20
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,165
    I am actually surprised anything showed. Field name in xServices table is [Machine No] - with a space. So add a space (and the [ ]) for both references to that field in the expression.

    "[Machine No]='"

    As I said, should not use spaces nor punctuation/special characters in naming convention. I see " and - in fieldnames ([4" Pickup] and [Pop-Up Pins]). Can get away with _ but I don't use.

    Also, best not to use exact same name in multiple tables.
    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. #21
    sheusz is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    17
    Hi June7
    That's embarrassing. Thank you for spotting that. I'll give that a go tomorrow when I get back to work.

    Sent from my SM-G950F using Tapatalk

  7. #22
    sheusz is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    17
    Quote Originally Posted by June7 View Post
    I am actually surprised anything showed. Field name in xServices table is [Machine No] - with a space. So add a space (and the [ ]) for both references to that field in the expression.

    "[Machine No]='"

    As I said, should not use spaces nor punctuation/special characters in naming convention. I see " and - in fieldnames ([4" Pickup] and [Pop-Up Pins]). Can get away with _ but I don't use.

    Also, best not to use exact same name in multiple tables.
    Hi June7

    Thank you for all your help and suggestions. I've made changes as suggested and that seems to have fixed the problem, with the exception that if the last record for that customer for that machine has Follow Up Required and the catagory is not service then the text box throws up #Error. This isn't a real issue because it gives me a good result (accidentally)

    I'm now thinking that removing the AND catagory = service might be better anyway. Looking through the service table, I can see that users aren't always diligent in which catagory they assign follow up to, so maybe removing the test all together would be best.

    I've tried this code
    Code:
    =DLookUp("[Follow Up Required]","xServices","[Machine No]=' " & [MachineNo] & " AND '[Service Date] =#" & DMax("[Service Date]","xServices","[Machine No]='" & [MachineNo] & ") & "#")
    But get an error The expression you entered has an invalid date value

    Any ideas? BTW I resolved the issue with the button not working.

  8. #23
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,165
    Misplaced space and misplaced apostrophe and missing apostrophe and quote.

    This

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


    should be

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

    Make sure parens, brackets, apostrophes, quotes are always in pairs.
    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.

  9. #24
    sheusz is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    17
    Hi June7

    Thank you.

    Trouble is I can't see stuff that small, even when I zoom up the code window!. Is there a way that Access can display pairs of parens, brackets, apostrophes, quotes etc. I've used code builders in the past that would show matching parens, brackets, apostrophes, quotes etc in green, and those without a pair in red. Was a real help.

  10. #25
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,165
    Access can't but I think there are 3rd-party tools can copy text into and it will show pairs. Notepad++ will see parens, brackets, braces but apostrophes and quotes are just text.
    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.

  11. #26
    sheusz is offline Novice
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    17
    Quote Originally Posted by June7 View Post
    Access can't but I think there are 3rd-party tools can copy text into and it will show pairs. Notepad++ will see parens, brackets, braces but apostrophes and quotes are just text.
    I might look in to that. Will save a lot of time.

    I've noticed something really odd about the code we just sorted, or maybe it's the data that I'm working with.

    Seems some results don't show when they should. They meet all the criteria. (last service record for that machine for that customer with Follow Up Required), but don't show up in the text block.

    If I create a new service record for that customer with exactly the same information in it. It does show up. If I delete the original record and replace it with the new record, it does show up.

    If I look at the records in the xServices table I can't see any difference.

    I'll play around over the weekend to see if I can figure out what is going on. At this time I can't seem to trust this to show me all the results all of the time.

    With your indulgence, I'll update you next week.

    BTW, everything else I wanted to achieve with this project is working (Big red button appearing as required and presenting the service record when pressed.)

    I'm thinking of taking your advice and going through the whole DB and removing spaces & punctuation's from where they aren't supposed to be. It's a daunting task.

    Once again, thank you for your help.

Page 2 of 2 FirstFirst 12
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