dmax multiple criteria - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

dmax multiple criteria

Gary Becker

2 years ago

I have some VBA code to open a form with multiple criteria. If I use only one of them, the code runs and the form opens. But when I try to use them both with AND I get errors. Sometime it's a type mismatch error. Here are the two lines of code that work.

DoCmd.OpenForm "PayEstimateDetailF", acNormal, , "[PayEstNumber] = " & DMax("PayEstNumber", "PayEstimatesT", "Jobnumber= " & """" & TempVars("JobNumber") & """"), acFormReadOnly

DoCmd.OpenForm "PayEstimateDetailF", acNormal, , "JobNumber = """ & TempVars("jobnumber") & """", acFormReadOnly

But when the criteria are put together like this--Run-time error 13 Type mismatch.
DoCmd.OpenForm "PayEstimateDetailF", acNormal, , "[PayEstNumber] = " & DMax("PayEstNumber", "PayEstimatesT", "Jobnumber= " & """" & TempVars("JobNumber") & """") And "JobNumber = """ & TempVars("jobnumber") & """"

	Kevin Robertson @Reply 2 years ago
You have too many double quotes and you are closing DMax bore the end of the function. Try this: `DoCmd.OpenForm "PayEstimateDetailF", , , "PayEstNumber=" & _ DMax("PayEstNumber", "PayEstimatesT", "Jobnumber=""" & _ TempVars("JobNumber") & """ And JobNumber=""" & TempVars("jobnumber") & """")`

Gary Becker OP @Reply

2 years ago

In your example: The closing parenthesis needs to be before the AND (It's the end of the DMAX function). The form needs to be opened with largest payestimate number for the TempVar JobNumber. That is what the The DMAX returns. Then come AND and the second criteria for opening the form, the JobNumber = TempVars JobNumber. I can message box the criteria separately and they return the correct value. And I can open the form either way with only one criteria. But when I join them together with the AND it won't work.

	Gary Becker OP @Reply 2 years ago
These return the values I'm looking for. I am trying to join them together in the OpenForm criteria. MsgBox DMax("PayEstNumber", "PayEstimatesT", "Jobnumber= " & """" & TempVars("JobNumber") & """") MsgBox TempVars("jobnumber")

	Kevin Robertson @Reply 2 years ago
Something like this? `DoCmd.OpenForm "PayEstimateDetailF", , , "PayEstNumber=" & _ DMax("PayEstNumber", "PayEstimatesT", "Jobnumber=""" & _ TempVars("JobNumber") & """") & " And JobNumber=""" & TempVars("jobnumber") & """"`

	Gary Becker OP @Reply 2 years ago
Yes, sir!! That does it. I'm so sad/mad. I was only missing the ampersand and the double quote before the AND. I spent hours on it this morning trying it every which way. I was pretty sure it was in the AND part because they would run by themselves. How do you post code in the lavender shape and blue mono-spaced text? I haven't figured that out either.

	Alex Hedley @Reply 2 years ago
Unfortunately you can't yet, it's an admin/mod option atm

	John Campbell @Reply 2 years ago
Domain functions can be a huge pain. What I do is I have a database with the endless possibilities of different criteria options and then copy and past those examples into the new situation. This can save a lot of time...

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View