Runtime error 13 typing mismatc

[joym]

I am trying to verify if a site selected from a cascading combobox (named cboSite) matches the any of the varies site names in the column "within 10" in the table named SLA. if so i want it to calculate a functioni have come up with the code as show below but i get a runtime error and am not sure how to rectify this.

Private Sub txtRTF_Click()
If (DLookup("Within10", "SLA", "Within10 = '" & Me.cboSite.Value & " ' ")) Then
Me.txtRTF = DateDiff("hm", 0, 8, [Date Fault Lodged])
End If
End Sub

The column type of the column Within10 in SLA table is short text

[Bob Fitz]

What is the Row Source property of cboSite

[andy49]

Me.txtRTF = DateDiff("hm", 0, 8, [Date Fault Lodged])

What are you trying to do here?

Datediff doesn't allow "HM"

and 0 and 8 aren't dates?

[Bob Fitz]

Me.txtRTF = DateDiff("hm", 0, 8, [Date Fault Lodged])

What are you trying to do here?

Datediff doesn't allow "HM"

and 0 and 8 aren't dates?

Don't see that this is relevant. Perhaps you've posted a response to the wrong thread?

[Bob Fitz]

Sorry. Getting myself totally confused today.

[andy49]

Hehe. It happens bob


Sent from my iPhone using Tapatalk

[Bob Fitz]

Hehe. It happens bob


Sent from my iPhone using Tapatalk

Yes, but not sure why. I have noticed that the older I get the more frequently it happens

[John_G]

If (DLookup("Within10", "SLA", "Within10 = '" & Me.cboSite.Value & " ' ")) Then

In that statement, you are treating DLookup as if it returns a True / False. It doesn't - it returns a value if there is a record which matches the criteria, and it returns a Null otherwise.

You need to handle that Null with a Nz, something like this:

If nz(DLookup("Within10", "SLA", "Within10 = '" & Me.cboSite.Value & " ' "),"N/A") <> "N/A" Then

So it will execute the me!txtRTF = ... only if the DLookup does not return a Null, and the Nz does not yield a "N/A"

[joym]

What is the Row Source property of cboSite

the row source proeperty is- SELECT [Site Query].[Site] FROM [Site Query];

[joym]

Me.txtRTF = DateDiff("hm", 0, 8, [Date Fault Lodged])

What are you trying to do here?

Datediff doesn't allow "HM"
and 0 and 8 aren't dates?

the m was a typo it should have been n.
i have a text box which automatically picks up date and time and dispalys it in the format mm/dd/yyyy hh:mm:ss Am/PM
what i am trying to do is add 8 hours to the time displayed in the date fault lodged and show the result in txtRTF. i was experimenting with the datediff and diff2dates equation. the datediff function gives me an error. however the diff2 dates function works but not the way i want it to. this is the diff2dates equation.

=Diff2Dates("hn",0,8,[Date Fault Lodged])

the result i get is 192 hours 0 minutes
the reason i put a 0 before the 8 is because if i use this equation

=Diff2Dates("hn",8,[Date Fault Lodged])

i get a result 1026570 hours 2 minutes
i was able to figure a way to add 8 hours to the time

=Diff2Dates("hn",0,0.334,[Date Fault Lodged])

i get the result 8 hours

displaying 8 hours is ok but i am trying to dispaly the date and time
eg date fault lodged - 2/1/2017 10:00:00 AM
so in my txtRTF i want the result to be displayed 2/1/2017 6:00:00 PM

[joym]

In that statement, you are treating DLookup as if it returns a True / False. It doesn't - it returns a value if there is a record which matches the criteria, and it returns a Null otherwise.

You need to handle that Null with a Nz, something like this:

If nz(DLookup("Within10", "SLA", "Within10 = '" & Me.cboSite.Value & " ' "),"N/A") <> "N/A" Then

So it will execute the me!txtRTF = ... only if the DLookup does not return a Null, and the Nz does not yield a "N/A"

thanks for the help. it did work well, i changed the date calculation equation and it still works fine

Private Sub txtRTF_Click()
If Nz(DLookup("Within10", "SLA", "Within10 = '" & Me.cboSite.Value & " ' "), "N/A") <> "N\A" Then
Me.txtRTF = DateAdd("h", 2, [Date Fault Lodged])
End If
End Sub

i tried adding an else if statement i do not get an error however the elseif is not executed

Private Sub txtRTF_Click()
If Nz(DLookup("Within10", "SLA", "Within10 = '" & Me.cboSite.Value & " ' "), "N/A") <> "N\A" Then
Me.txtRTF = DateAdd("h", 2, [Date Fault Lodged])
ElseIf (DLookup("10_50", "SLA", "10_50 = '" & Me.cboSite.Value & " ' ")) Then
Me.txtRTF = DateAdd("h", 4, [Date Fault Lodged])
ElseIf (DLookup("50_80", "SLA", "50_80 = '" & Me.cboSite.Value & " ' ")) Then
Me.txtRTF = DateAdd("h", 8, [Date Fault Lodged])
ElseIf (DLookup("80_100", "SLA", "80_100 = '" & Me.cboSite.Value & " ' ")) Then
Me.txtRTF = DateAdd("d", 2, [Date Fault Lodged])
ElseIf (DLookup("Over100", "SLA", "Over100 = '" & Me.cboSite.Value & " ' ")) Then
Me.txtRTF = DateAdd("d", 10, [Date Fault Lodged])
End If
End If
End Sub

[John_G]

If Nz(DLookup("Within10", "SLA", "Within10 = '" & Me.cboSite.Value & " ' "), "N/A") <> "N\A" Then

In that statement, you have the slashes in opposite directions, so I'm not sure what the effect is. Change them both to "N/A" to see what happens.

I think too that you should change all the DLookup's to use the Nz as well, checking for "N/A", because they all have the same problem with Null as your original one did.