Dmax Runtime error 13, Type mismatch

[bsc gmcc]

I am receiving a Runtime error 13, Type mismatch error when running the
below. I know that CInt(Year(Forms!Complaint!txtDateReceived)) returns the
year such as 2009 which is what I want. I also know that if I run a query
with Year([Date_Received]) it also returns the year. So, I have been playing
with this and still can't figure out why I am getting the error.


lngComplaintID = DMax("[StateSeq]", "Complaint", _
"[State_ID] = " & Forms!Complaint!State_ID And "Year([Date_Received]) =
" & CInt(Year(Forms!Complaint!txtDateReceived))) _
+ 1

 

[Dirk Goldgar]

I am receiving a Runtime error 13, Type mismatch error when running the
below. I know that CInt(Year(Forms!Complaint!txtDateReceived)) returns the
year such as 2009 which is what I want. I also know that if I run a query
with Year([Date_Received]) it also returns the year. So, I have been
playing
with this and still can't figure out why I am getting the error.


lngComplaintID = DMax("[StateSeq]", "Complaint", _
"[State_ID] = " & Forms!Complaint!State_ID And "Year([Date_Received]) =
" & CInt(Year(Forms!Complaint!txtDateReceived))) _
+ 1

You've got your quotes misplaced in one spot. It seems to me you may also
have a problem if there are no records that meet your criteria, in which
case DMax will return Null. Try this:

lngComplaintID = 1 + _
Nz( _
DMax("[StateSeq]", "Complaint", _
"[State_ID] = " & Forms!Complaint!State_ID & _
" And Year([Date_Received]) = " & _
Year(Forms!Complaint!txtDateReceived)), _
0)

 

[fredg]

I am receiving a Runtime error 13, Type mismatch error when running the
below. I know that CInt(Year(Forms!Complaint!txtDateReceived)) returns the
year such as 2009 which is what I want. I also know that if I run a query
with Year([Date_Received]) it also returns the year. So, I have been playing
with this and still can't figure out why I am getting the error.

lngComplaintID = DMax("[StateSeq]", "Complaint", _
"[State_ID] = " & Forms!Complaint!State_ID And "Year([Date_Received]) =
" & CInt(Year(Forms!Complaint!txtDateReceived))) _
+ 1

There is a difference in usage between the word And and the Ampersand
(&).

lngComplaintID = DMax("[StateSeq]", "Complaint", "[State_ID] = " &
Forms!Complaint!State_ID & " And Year([Date_Received]) = " &
Year(Forms!Complaint!txtDateReceived)) + 1

The above assumes the [State_ID] criteria field is a Number datatype,
and [Date_Received] is a Date datatype.