Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2014
    Posts
    21

    Unanswered: Multiple criteria in MS Access Dmax and DSum

    I have the following macro in an Ms Access form after update event that returns a Max value for the whole table instead of the criteria that I have set. Can anyone please tell me what I am doing wrong! This should be quite simple, but obviously I have missed something. When I use either of the criteria on their own it works fine, but I need a result for both criteria.

    DMax("[Item number]","Items",("[Ticket number] ="&[Ticket number]) And
    ("[Date valid] =#"&[Date valid]&"#"))

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. There are misplaced parentheses in your code(I'm even surprised that the line does nor appears in red in the VBA IDE):
    Code:
    DMax("[Item number]", "Items", "(([Ticket number] =" & [Ticket number] & ") And ([Date valid] = #" & [Date valid] & "#)")
    2. Be sure that the date value is properly formated (ISO: YYYY-MM-DD or US: MM-DD-YYYY).
    Have a nice day!

  3. #3
    Join Date
    Mar 2014
    Posts
    21

    Ms access Dsum and DMax multiple criteria

    Hi thanks for reply. Typed in exactly as you suggested, but there are mismatched parenthesis. I removed the second left hand parenthesis, but still have a problem. Am using short date format, (In the UK) dd/mm/yyyy. Could this be a problem?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Piscator View Post
    Am using short date format, (In the UK) dd/mm/yyyy. Could this be a problem?
    Yes, as I wrote before:
    Quote Originally Posted by Sinndho View Post
    2. Be sure that the date value is properly formated (ISO: YYYY-MM-DD or US: MM-DD-YYYY).
    Have a nice day!

  5. #5
    Join Date
    Mar 2014
    Posts
    21

    Angry Multiple criteria in MS Access Dmax and DSum

    Hi. I obtained info on Domain functions from MS support website:

    Description of DLookup() usage, examples, and troubleshooting ( seems awfully convoluted to achieve something so simple!!

    [Item number] is a numeric field, and the following works Ok:

    DMax("[Item number]","Items","[Tickets] ="&[Tickets])

    so does

    (DMax("[Item number]","Items","[Date valid] =#"&[Date valid]&"#")

    However, if I And the criteria:
    ("[Tickets] ="&[Tickets]) And ("[Date valid] =#"&[Date valid]&"#") it does not work. I have tried your format, but still no luck. Any further suggestions please

  6. #6
    Join Date
    Mar 2014
    Posts
    21
    Hi Many many thanks. removed the extra Left parenthesis, and working absolutely fine. Ms info is obviously wrong/misleading. Thanks for digging me out of a hole.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Piscator View Post
    Hi Many many thanks. removed the extra Left parenthesis, and working absolutely fine. Ms info is obviously wrong/misleading. Thanks for digging me out of a hole.
    me thinks the problem is not MSDN or Microsoft, its the person doing the reading

    Code:
    ("[Tickets] ="&[Tickets]) And ("[Date valid] =#"&[Date valid]&"#")
    it does not work. I have tried your format, but still no luck. Any further suggestions please
    the problem isn't the brackets, although they are superfluous UNLESS you need to use them to denote logical grouping if the terms

    the problem is not correctly delimiting VBA / text literals ie not correctly building your statement. MSDN & Microsoft help is far far better, far, far better well thought out that Android, or for that matter filemaker. Yes it ahs flaws, yes it has deficincies but its nowhere near as developer hostile (or just confused) as Android
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2014
    Posts
    21

    Thumbs up

    Hi, yes entirely my fault. I have read some further documentation, and have a much better appreciation of how to construct the criteria. Again, may thanks for your help - much appreciated.

  10. #10
    Join Date
    Mar 2014
    Posts
    21
    Hi have got another similar problem. I know that I should be able to sort such a simple issue, but its driving me nuts! I have a table named Tickets and am trying to get an "after update" event to work. But no luck - can you tell me where I am going wrong. This again involves a short date field:

    If IsNull(DLookUp("[Ticket_number]","Tickets","[Date_valid] = #"&[Date_valid] &"#"))

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Check the actual value of the code using the debugger
    diagnosing what is happening when the report is 'where I am going wrong' is virtually imposdible
    what error message do you get
    date literals shoul be delimited with the hash symbol # and be in iso yyyy/mm/dd or us mm/dd/yyyy format
    its possible the runtime is getting confused with the date_valid definitions. Id want to call the the control that has the valyse something different to the column name
    you may need a space separating the &
    thinking about it it may also be the square brackets. You dont need them if your tables and column names are made up of valid symbols no spaces. The date_valid in # may be confused if its defined using square brackets. Try rdmoving all square brackets and see if that clears the fault
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2014
    Posts
    21
    I have re-run the original code, and it has worked. I believe I had a system problem with Access, so I re-installed it and it is now OK. Just one other thing: when I am formatting macros, DLookUp for example, sometimes the fields are listed in a drop-down menu and other times not. Is this an option that I can control to be shown all the time, and if so how do I do it? Many thanks for your help - Piscator

Posting Permissions

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