Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2015
    Posts
    51

    Unanswered: is it possible to use dmax with criteria from a form?

    is it possible to have a dmax function with criteria from a form like below?


    dmax( field1, table1, fielda = form1.fielda & fieldb = form1.fieldb & fieldc = form1.fieldc)

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    Jul 2015
    Posts
    51
    =DLookUp("pass#","passlog","year = " & [Forms]![Multiple_Pass_Sale]![year]_ & ,"month = " & [Forms]![Multiple_Pass_Sale]![month]_ & ,"passtype = " & [Forms]![Multiple_Pass_Sale]![passtype])

    Im getting a syntax error..... where is it?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Extraneous underscores and commas, no AND's. Try

    =DLookUp("[pass#]","passlog","year = " & [Forms]![Multiple_Pass_Sale]![year] & " AND month = " & [Forms]![Multiple_Pass_Sale]![month] & " AND passtype = " & [Forms]![Multiple_Pass_Sale]![passtype])
    Paul

  5. #5
    Join Date
    Jul 2015
    Posts
    51
    well no errors, but nothing is happening......


    I'm applying it to a control in a subform that is in datasheet view. could that be it?

    should the code be pointing to the hidden controls in my subform? they are linked to the main form so the data autofills......

  6. #6
    Join Date
    Jul 2015
    Posts
    51

    Exclamation Need Experts Help

    I just cant get this to work. Everywhere I've read says it can..... I have attached a mock db with the form im trying to get this to work on. All im trying to do is get the default value for the pass to increment by +1 based on the date and passtype.

    Can anyone help me see what im doing wrong so I can fix this in my real db.Database2.zip

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Where is it? I don't see it right off. I see a function with no form references in it.
    Paul

  8. #8
    Join Date
    Jul 2015
    Posts
    51
    I have it in my default value for the pass Click image for larger version. 

Name:	Capture1.PNG 
Views:	4 
Size:	35.7 KB 
ID:	16623

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Having that form in the sample would help.

    Not sure it would work as a default value, unless the form fields will already have been filled out when that form goes to a new record.
    Paul

  10. #10
    Join Date
    Jul 2015
    Posts
    51
    TestDB.zip

    Wow its been a long day.... I attached the wrong sample...

    here it is.... and yes the fields would be filled before you get the pass field

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your reference to the subform is incorrect:

    http://www.mvps.org/access/general/gen0018.htm

    Also, since no values would be present when the form loads, I doubt the default property will work. I'd put the value there at some other point. Also, all your data types are text, so need the delimiters shown in the link.
    Paul

  12. #12
    Join Date
    Jul 2015
    Posts
    51
    Okay so in my real database should I put the code in the subform, like it afterupdate in one of my child fields to the main form that i would put data in? or like gotfocus, since I want to tab down to get the next number to fill in?


    Im also getting a compile error putting in the delimiters:

    Pass# = DMax("[pass#]", "passlog", "year = '" & [Forms]![Multiple_Pass_Sale]![Year] & "'" And Month = '" & [Forms]![Multiple_Pass_Sale]![month] & "'" And PassType = ," & [Forms]![Multiple_Pass_Sale]![passtype] & "'") + 1

    The year and month, are auto calculated for the current date, should I be putting date delimiters there? i tried but also got a compile error.


    I feel like the code is almost there, im just not sure when to put it to make this function.......

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The red quotes are bad, plus you snuck a comma back in there in place of an apostrophe:

    DMax("[pass#]", "passlog", "year = '" & [Forms]![Multiple_Pass_Sale]![Year] & "'" And Month = '" & [Forms]![Multiple_Pass_Sale]![month] & "'" And PassType = ,'" & [Forms]![Multiple_Pass_Sale]![passtype] & "'") + 1
    Paul

Posting Permissions

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