DMAX Function

  • Thread starter Thread starter Kirk P.
  • Start date Start date
K

Kirk P.

I must have a syntax issue here. When I "hard code" the values I want into
txtADM_ID and cboSite_ID I get the expected results, but when I refer to the
values in the controls themselves, I get a "type mismatch" error. Can anyone
spot the problem here?

Me.txtStart_Date = DMax("End_Date", "tblADM_Site_Assign", "ADM_ID = '" &
Me.txtADM_ID & "'" And Site_ID = "'" & Me.cboSite_ID & "'") + 1
 
I must have a syntax issue here. When I "hard code" the values I want into
txtADM_ID and cboSite_ID I get the expected results, but when I refer to the
values in the controls themselves, I get a "type mismatch" error. Can anyone
spot the problem here?

Me.txtStart_Date = DMax("End_Date", "tblADM_Site_Assign", "ADM_ID = '" &
Me.txtADM_ID & "'" And Site_ID = "'" & Me.cboSite_ID & "'") + 1
Click to expand...


Datatypes matter when using access.
You're showing both fields as Text datatype, but even then you have
incorrect double quotes, so the second criteria is outside the string.

What are the datatypes of the ADM_ID and the Site_ID fields.

If both are indeed text datatypes, then use:

Me.txtStart_Date = DMax("[End_Date]", "tblADM_Site_Assign", "[ADM_ID]
= ' " & Me.[txtADM_ID] & " ' And [Site_ID] = ' " & Me.[cboSite_ID] &
" ' ") + 1

Note: I've placed a space between the single and double quotes just
for clarity. Remove the spaces.
The above also assumes the bound column of cboSite_ID is text.

If both fields are Number datatypes, then use:

Me.txtStart_Date = DMax("[End_Date]", "tblADM_Site_Assign", "[ADM_ID]
= " & Me.[txtADM_ID] & " And [Site_ID] = " & Me.[cboSite_ID]) + 1
 
Back
Top