concatenate type mismatch error 13

[Guest]

I'm trying to concatenate two strings but it just doesn't seem to be working
for me.
The two I want to concatenate are as follows:

strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle Inspection]", _
"[VEHInspType] = 2 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo) +
5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 1 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
And _
.txtReportDate >= DateAdd("m", 6, Date)

The reason I want to do this is because I want to have 3 types of criteria
that will trigger a msgbox

I would like to have
strKm
strDate
strAsix (which is written out as strAsix = strKm + " and " + strDate) - this
is where the type mismatch error that I keep receiving is.

Is there some kind of trick I'm unaware of? because this has always worked
for me in the past. I've also tried it with the ampersand and I get the same
error.

It works for me if I don't concatenate it, but then it's basically
ridiculous because I have virtually the same code written out twice.

Any help will be appreciated

 

[Brendan Reynolds]

Your two expressions appear to be comparing the value of a form control with
the result of a call to the DMax() function, using the > operator. The
result of such a comparison is not a String, but a Boolean (True or False)
value.

 

[Brendan Reynolds]

I should add that the result could also be Null, if no records match the
criteria.

--
Brendan Reynolds (MVP)

Your two expressions appear to be comparing the value of a form control
with the result of a call to the DMax() function, using the > operator.
The result of such a comparison is not a String, but a Boolean (True or
False) value.

--
Brendan Reynolds (MVP)

I'm trying to concatenate two strings but it just doesn't seem to be
working
for me.
The two I want to concatenate are as follows:

strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 2 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
+
5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 1 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
And _
.txtReportDate >= DateAdd("m", 6, Date)

The reason I want to do this is because I want to have 3 types of
criteria
that will trigger a msgbox

I would like to have
strKm
strDate
strAsix (which is written out as strAsix = strKm + " and " + strDate) -
this
is where the type mismatch error that I keep receiving is.

Is there some kind of trick I'm unaware of? because this has always
worked
for me in the past. I've also tried it with the ampersand and I get the
same
error.

It works for me if I don't concatenate it, but then it's basically
ridiculous because I have virtually the same code written out twice.

Any help will be appreciated

 

[Guest]

would it be
Dim strAsix AS Boolean

because I tried that and it still doesn't work.

I should add that the result could also be Null, if no records match the
criteria.

--
Brendan Reynolds (MVP)

Your two expressions appear to be comparing the value of a form control
with the result of a call to the DMax() function, using the > operator.
The result of such a comparison is not a String, but a Boolean (True or
False) value.

--
Brendan Reynolds (MVP)

I'm trying to concatenate two strings but it just doesn't seem to be
working
for me.
The two I want to concatenate are as follows:

strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 2 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
+
5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 1 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
And _
.txtReportDate >= DateAdd("m", 6, Date)

The reason I want to do this is because I want to have 3 types of
criteria
that will trigger a msgbox

I would like to have
strKm
strDate
strAsix (which is written out as strAsix = strKm + " and " + strDate) -
this
is where the type mismatch error that I keep receiving is.

Is there some kind of trick I'm unaware of? because this has always
worked
for me in the past. I've also tried it with the ampersand and I get the
same
error.

It works for me if I don't concatenate it, but then it's basically
ridiculous because I have virtually the same code written out twice.

Any help will be appreciated

 

[Douglas J. Steele]

The Criteria in your DMax statements are incorrect. You cannot use

[VEHInspType] = 2 OR 3.

You need to use either

([VEHInspType] = 2 OR [VEHInspType] = 3)

or

[VEHInspType] IN (2, 3)

 

[Brendan Reynolds]

While I can tell you why the code you've posted is failing, I'm not sure
that I can advise you on what the solution might be, because I haven't been
able to understand what it is you are trying to do.

Declaring strAsix as Boolean will not help, because the final expression
that you posted ...

strAsix = strKm + " and " + strDate

.... is attempting to add the two Boolean values contained in the strKm and
strDate variables to the String literal " and " - it is attempting to add
incompatible data types, two Booleans and a string.

If you converted the result of the first two expressions to strings, using
the CStr() function, then this final expression would work, but it would
return a string result something like "True and True" or "True and False".
I'm not sure what use that result would be, but If that's what you want,
then you would need to wrap the first two expressions (the onces using > and
DMax()) in calls to CStr() like so ...

strKm = CStr(.txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]","[VEHInspType] = 2 OR 3 AND [VEHVehicleNo] = " &
..txtVEHvehicleNo) + 5000)

If this doesn't help, try posting more of the surrounding code, including
the declarations of the variables involved. Seeing a little more of the
context might enable someone to provide a more explicit answer.

--
Brendan Reynolds (MVP)

would it be
Dim strAsix AS Boolean

because I tried that and it still doesn't work.

I should add that the result could also be Null, if no records match the
criteria.

--
Brendan Reynolds (MVP)

Your two expressions appear to be comparing the value of a form control
with the result of a call to the DMax() function, using the > operator.
The result of such a comparison is not a String, but a Boolean (True or
False) value.

--
Brendan Reynolds (MVP)


I'm trying to concatenate two strings but it just doesn't seem to be
working
for me.
The two I want to concatenate are as follows:

strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 2 OR 3 AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
+
5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 1 OR 3 AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
And _
.txtReportDate >= DateAdd("m", 6, Date)

The reason I want to do this is because I want to have 3 types of
criteria
that will trigger a msgbox

I would like to have
strKm
strDate
strAsix (which is written out as strAsix = strKm + " and " +
strDate) -
this
is where the type mismatch error that I keep receiving is.

Is there some kind of trick I'm unaware of? because this has always
worked
for me in the past. I've also tried it with the ampersand and I get
the
same
error.

It works for me if I don't concatenate it, but then it's basically
ridiculous because I have virtually the same code written out twice.

Any help will be appreciated

 

[Brendan Reynolds]

Thanks Doug. I had missed that. I don't think fixing it will change the type
mismatch problem, though. I think they're two separate errors.

--
Brendan Reynolds (MVP)

The Criteria in your DMax statements are incorrect. You cannot use

[VEHInspType] = 2 OR 3.

You need to use either

([VEHInspType] = 2 OR [VEHInspType] = 3)

or

[VEHInspType] IN (2, 3)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to concatenate two strings but it just doesn't seem to be
working
for me.
The two I want to concatenate are as follows:

strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 2 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
+
5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 1 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
And _
.txtReportDate >= DateAdd("m", 6, Date)

The reason I want to do this is because I want to have 3 types of
criteria
that will trigger a msgbox

I would like to have
strKm
strDate
strAsix (which is written out as strAsix = strKm + " and " + strDate) -
this
is where the type mismatch error that I keep receiving is.

Is there some kind of trick I'm unaware of? because this has always
worked
for me in the past. I've also tried it with the ampersand and I get the
same
error.

It works for me if I don't concatenate it, but then it's basically
ridiculous because I have virtually the same code written out twice.

Any help will be appreciated

 

[Guest]

I'm still having trouble here is the whole tidbit in it's original state:

Private Sub Form_AfterUpdate()
Dim strKm As String
Dim strDate As String
Dim strAsix As String

With Me
strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle Inspection]", _
"([VEHInspType] = 2 OR 3) AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
+ 5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"([VEHInspType] = 1 OR 3) AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
And _
.txtReportDate > DateAdd("m", 6, Date)
strAsix = strKm + " and " + strDate

If strAsix Then
MsgBox "A-Service and Six Month Inspection are both Overdue",
vbCritical, "Overdue Notice"
ElseIf strKm Then
MsgBox "A-Service is Overdue", vbCritical, "OverdueNotice"
ElseIf strDate Then
MsgBox "6 Month Inspection is Overdue", vbCritical, "Overdue Notice"
End If
End With
End Sub

To break it down a little, I have several things that I have to track of, it
can be a real pain doing it manually. Every week I enter the present
kilometres of every company vehicle and the date, including the km and date
of each type of inspection.
In this database, I have a separate form and table for inspections.
The form I'm attempting to run this code on is the one I enter the weekly
report on.
My goal is to have a msgbox/warning/popup/whatever jump out at me after the
record has been updated to let me know if a particular type of inspection is
due/overdue based on the last inspection for that type (Just in case it was
missed for whatever reason).

If you have any better suggestions, I'd be happy to hear them

and

Thank you Douglas, I actually realized that right after I posted my question.

The Criteria in your DMax statements are incorrect. You cannot use

[VEHInspType] = 2 OR 3.

You need to use either

([VEHInspType] = 2 OR [VEHInspType] = 3)

or

[VEHInspType] IN (2, 3)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to concatenate two strings but it just doesn't seem to be
working
for me.
The two I want to concatenate are as follows:

strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 2 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
+
5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 1 OR 3 AND [VEHVehicleNo] = " & .txtVEHvehicleNo)
And _
.txtReportDate >= DateAdd("m", 6, Date)

The reason I want to do this is because I want to have 3 types of criteria
that will trigger a msgbox

I would like to have
strKm
strDate
strAsix (which is written out as strAsix = strKm + " and " + strDate) -
this
is where the type mismatch error that I keep receiving is.

Is there some kind of trick I'm unaware of? because this has always worked
for me in the past. I've also tried it with the ampersand and I get the
same
error.

It works for me if I don't concatenate it, but then it's basically
ridiculous because I have virtually the same code written out twice.

Any help will be appreciated

 

[Brendan Reynolds]

I would suggest something more like the following. This is untested
air-code, as I obviously don't have tables with these names/fields to test
against. But it should help to illustrate the general principle, which is to
assign the result of Boolean tests to Boolean variables, and to perform
Boolean tests against Boolean values, and to avoid mixing Boolean values and
strings.

Dim ServiceDue As Boolean
Dim InspectionDue As Boolean

With Me

ServiceDue = .txtVehKilometres > DMax("[kilometres]", _
"[tbl Vehicle Inspection]", "([VEHInspType] = 2 OR [VEHInspType] =
3) " & _
"AND [VEHVehicleNo] = " & .txtVEHvehicleNo) + 5000

InspectionDue = .txtReportDate > DMax("[dateOfInspection]", _
"[tbl Vehicle Inspection]", "([VEHInspType] = 1 OR [VEHInspType] =
3) " & _
"AND [VEHVehicleNo] = " & .txtVEHvehicleNo) And .txtReportDate >
DateAdd("m", 6, Date)

If ServiceDue And InspectionDue Then
MsgBox "A-Service and Six Month Inspection are both overdue."
Else If ServiceDue Then
MsgBox "A-Service is overdue"
Else If InspectionDue Then
MsgBox "Six Month Inspection is overdue"
End If

End With

--
Brendan Reynolds (MVP)

I'm still having trouble here is the whole tidbit in it's original state:

Private Sub Form_AfterUpdate()
Dim strKm As String
Dim strDate As String
Dim strAsix As String

With Me
strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]", _
"([VEHInspType] = 2 OR 3) AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
+ 5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"([VEHInspType] = 1 OR 3) AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
And _
.txtReportDate > DateAdd("m", 6, Date)
strAsix = strKm + " and " + strDate

If strAsix Then
MsgBox "A-Service and Six Month Inspection are both Overdue",
vbCritical, "Overdue Notice"
ElseIf strKm Then
MsgBox "A-Service is Overdue", vbCritical, "OverdueNotice"
ElseIf strDate Then
MsgBox "6 Month Inspection is Overdue", vbCritical, "Overdue
Notice"
End If
End With
End Sub

To break it down a little, I have several things that I have to track of,
it
can be a real pain doing it manually. Every week I enter the present
kilometres of every company vehicle and the date, including the km and
date
of each type of inspection.
In this database, I have a separate form and table for inspections.
The form I'm attempting to run this code on is the one I enter the weekly
report on.
My goal is to have a msgbox/warning/popup/whatever jump out at me after
the
record has been updated to let me know if a particular type of inspection
is
due/overdue based on the last inspection for that type (Just in case it
was
missed for whatever reason).

If you have any better suggestions, I'd be happy to hear them

and

Thank you Douglas, I actually realized that right after I posted my
question.

The Criteria in your DMax statements are incorrect. You cannot use

[VEHInspType] = 2 OR 3.

You need to use either

([VEHInspType] = 2 OR [VEHInspType] = 3)

or

[VEHInspType] IN (2, 3)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to concatenate two strings but it just doesn't seem to be
working
for me.
The two I want to concatenate are as follows:

strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 2 OR 3 AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
+
5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 1 OR 3 AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
And _
.txtReportDate >= DateAdd("m", 6, Date)

The reason I want to do this is because I want to have 3 types of
criteria
that will trigger a msgbox

I would like to have
strKm
strDate
strAsix (which is written out as strAsix = strKm + " and " + strDate) -
this
is where the type mismatch error that I keep receiving is.

Is there some kind of trick I'm unaware of? because this has always
worked
for me in the past. I've also tried it with the ampersand and I get the
same
error.

It works for me if I don't concatenate it, but then it's basically
ridiculous because I have virtually the same code written out twice.

Any help will be appreciated

 

[Guest]

It's always something so simple!

all I did was change the first two strings to booleans, and the first part
of the if statement to resemble your suggestion and it worked perfectly.
Thank you so much for your time.

I would suggest something more like the following. This is untested
air-code, as I obviously don't have tables with these names/fields to test
against. But it should help to illustrate the general principle, which is to
assign the result of Boolean tests to Boolean variables, and to perform
Boolean tests against Boolean values, and to avoid mixing Boolean values and
strings.

Dim ServiceDue As Boolean
Dim InspectionDue As Boolean

With Me

ServiceDue = .txtVehKilometres > DMax("[kilometres]", _
"[tbl Vehicle Inspection]", "([VEHInspType] = 2 OR [VEHInspType] =
3) " & _
"AND [VEHVehicleNo] = " & .txtVEHvehicleNo) + 5000

InspectionDue = .txtReportDate > DMax("[dateOfInspection]", _
"[tbl Vehicle Inspection]", "([VEHInspType] = 1 OR [VEHInspType] =
3) " & _
"AND [VEHVehicleNo] = " & .txtVEHvehicleNo) And .txtReportDate >
DateAdd("m", 6, Date)

If ServiceDue And InspectionDue Then
MsgBox "A-Service and Six Month Inspection are both overdue."
Else If ServiceDue Then
MsgBox "A-Service is overdue"
Else If InspectionDue Then
MsgBox "Six Month Inspection is overdue"
End If

End With

--
Brendan Reynolds (MVP)

I'm still having trouble here is the whole tidbit in it's original state:

Private Sub Form_AfterUpdate()
Dim strKm As String
Dim strDate As String
Dim strAsix As String

With Me
strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]", _
"([VEHInspType] = 2 OR 3) AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
+ 5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"([VEHInspType] = 1 OR 3) AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
And _
.txtReportDate > DateAdd("m", 6, Date)
strAsix = strKm + " and " + strDate

If strAsix Then
MsgBox "A-Service and Six Month Inspection are both Overdue",
vbCritical, "Overdue Notice"
ElseIf strKm Then
MsgBox "A-Service is Overdue", vbCritical, "OverdueNotice"
ElseIf strDate Then
MsgBox "6 Month Inspection is Overdue", vbCritical, "Overdue
Notice"
End If
End With
End Sub

To break it down a little, I have several things that I have to track of,
it
can be a real pain doing it manually. Every week I enter the present
kilometres of every company vehicle and the date, including the km and
date
of each type of inspection.
In this database, I have a separate form and table for inspections.
The form I'm attempting to run this code on is the one I enter the weekly
report on.
My goal is to have a msgbox/warning/popup/whatever jump out at me after
the
record has been updated to let me know if a particular type of inspection
is
due/overdue based on the last inspection for that type (Just in case it
was
missed for whatever reason).

If you have any better suggestions, I'd be happy to hear them

and

Thank you Douglas, I actually realized that right after I posted my
question.

The Criteria in your DMax statements are incorrect. You cannot use

[VEHInspType] = 2 OR 3.

You need to use either

([VEHInspType] = 2 OR [VEHInspType] = 3)

or

[VEHInspType] IN (2, 3)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I'm trying to concatenate two strings but it just doesn't seem to be
working
for me.
The two I want to concatenate are as follows:

strKm = .txtVehKilometres > DMax("[kilometres]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 2 OR 3 AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
+
5000
strDate = .txtReportDate > DMax("[dateOfInspection]", "[tbl Vehicle
Inspection]", _
"[VEHInspType] = 1 OR 3 AND [VEHVehicleNo] = " &
.txtVEHvehicleNo)
And _
.txtReportDate >= DateAdd("m", 6, Date)

The reason I want to do this is because I want to have 3 types of
criteria
that will trigger a msgbox

I would like to have
strKm
strDate
strAsix (which is written out as strAsix = strKm + " and " + strDate) -
this
is where the type mismatch error that I keep receiving is.

Is there some kind of trick I'm unaware of? because this has always
worked
for me in the past. I've also tried it with the ampersand and I get the
same
error.

It works for me if I don't concatenate it, but then it's basically
ridiculous because I have virtually the same code written out twice.

Any help will be appreciated