Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 36
  1. #16
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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 Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    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 Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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 Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    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 Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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 Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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 Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    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.

  12. #27
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Hi June7

    I hope you have had a great weekend. I spent a bit of time trying to get to the bottom of the strange results I mentioned I was getting on Friday.

    I don't know how this is possible, but initially it seemed that the results only show up if the Service Date is less than 4 days from the current date

    If you open the xServices table and look at the last entry (record no 20249) it is dated 13/06/2019. When I open the form Machine Components form I get my red warning box appear. Great!

    If I change this to 12/06/2019 then the re warning box doesn't appear??

    Strangely, If I change it to 01/01/2019 for this record, the red warning box appears??

    Theoretically, if I open the xServices table and put some data in any of the last few entries' Follow Up Required fields, they should throw up a result. But not all of them do, and it seems as if depends on the Service Date value.

    Another example is rec no. 20236 where the Service Date is 09/04/2018. It should show the message box, but doesn't. If I change the date to 01/01/2019, or even 01/01/18, the message box does appear.

    I'm at a complete loss with this and wonder if you have any clue.

    I've supplied the DB here for you to look at
    WIP Multicam Newcastle 2010 -v11.zip

    Once again, thank you for your help.

  13. #28
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You appear to be using international date format. Where are you located? Review http://allenbrowne.com/ser-36.html

    I am in the USA so this is not an issue for me.
    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. #29
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Hi June7

    I'm in Australia. So We use the dd/mm/yyyy format. Regional settings on our PC's are set to Australia.

  15. #30
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Does Allen's article help?
    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 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-03-2021, 05:06 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
  •  
Other Forums: Microsoft Office Forums