DLookup returns Error '3464'-Data Type Mismatch in Criteria Expres

[Steve Sanford]

RG,

I'm not doing a very good job of explaining, so i was looking for a site for
an example.

I found this:

http://www.mvps.org/access/forms/frm0001.htm


Within a string, a function is not evaluated, because it is a string. (Great
logic <g>)
You have to concatenate the results of the function to the string, to build
the proper SQL syntax.


I have an excuse for not being able to 'splain it ... I'm working 12 hour
night shifts. Time for bed..
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I would think it would resolve at run time, but I could be wrong.

The point I was trying to make was the Now() function was inside the text
string of the SQL and did not evaluate to a date.

Steve,
Your input is more than welcome but just for the record, direct from VBA Help:

[quoted text clipped - 160 lines]

However, you are correct that the CCNo is the line returning the error. I
apologize for the earlier confusion.

 

[Scott Whetsell, A.S. - WVSP]

Hey guys,

I will agree with Steve, that after checking my sql statement in the
immediate window, that it is not qualifying against a date/time. I have
added the #s to get it to do so.

I have made a work around that seems to be functioning for the time being by
adding an additional column to my table which is written to through the
unitLog Sub.

The purpose of using the Function as opposed to a sub is that the function
is called from a hot-key (F2 specifically) as opposed to a control on a form.
If it should be a sub, I'll change it, but it's working.

My new code is as follows:
====== CODE STARTS HERE ======
Function mcr_SafetyTimerReset()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID,
tbl_OrganizationMembers.OM_CCNo " & _
"FROM tbl_OrganizationMembers " & _
"WHERE (((tbl_OrganizationMembers.OM_Timer) = ""ON"") And
((tbl_OrganizationMembers.OM_Expiration) <= #" & Now() & "#)) " & _
"ORDER BY tbl_OrganizationMembers.OM_Expiration"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If rst.EOF = True Then GoTo Exit_Function_STR
Dim CCNo As Variant
Dim EVCD As Variant
Dim UNIT As String
UNIT = Nz(DLookup("AssignedUnit", "qry_AssignedUnit", "[OM_UnitID]='" &
rst.Fields(2) & "'"), rst.Fields(2))
CCNo = rst.Fields(3)
EVCD = Nz(DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & rst.Fields(3) &
"'"), "10-50")
Call unitLog(CCNo, UNIT, "10-4", "10-50", "UNIT LOG", DLookup("EventTimer",
"tbl_EventCodes", "[EventCode] = '" & EVCD & "'"))

Exit_Function_STR:
Set dbs = Nothing
Set rst = Nothing
Exit Function
End Function
====== CODE ENDS HERE ======

Essentially I took the problem lookup out of the picture by adding the
field. I know that's not best practice in database design. I did extensive
testing last night and the error is occurring when the criteria field is set
to match against UnitID. I can lookup UnitID with other criteria, just not
using it as criteria. I tried converting it to every type of field i could
think of in both the code and the underlying query as well as converting the
rst field to no avail.

I just don't understand why it works on some and not on others.

If either of you have any more suggestions, I'm open to them. Otherwise
I'll consider it resolved for now, and mark each of you for helpful posts.

Thanks!

 

[ruralguy via AccessMonster.com]

Thanks for posting back with your solution Scott.

Hey guys,

I will agree with Steve, that after checking my sql statement in the
immediate window, that it is not qualifying against a date/time. I have
added the #s to get it to do so.

I have made a work around that seems to be functioning for the time being by
adding an additional column to my table which is written to through the
unitLog Sub.

The purpose of using the Function as opposed to a sub is that the function
is called from a hot-key (F2 specifically) as opposed to a control on a form.
If it should be a sub, I'll change it, but it's working.

My new code is as follows:
====== CODE STARTS HERE ======
Function mcr_SafetyTimerReset()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID,
tbl_OrganizationMembers.OM_CCNo " & _
"FROM tbl_OrganizationMembers " & _
"WHERE (((tbl_OrganizationMembers.OM_Timer) = ""ON"") And
((tbl_OrganizationMembers.OM_Expiration) <= #" & Now() & "#)) " & _
"ORDER BY tbl_OrganizationMembers.OM_Expiration"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If rst.EOF = True Then GoTo Exit_Function_STR
Dim CCNo As Variant
Dim EVCD As Variant
Dim UNIT As String
UNIT = Nz(DLookup("AssignedUnit", "qry_AssignedUnit", "[OM_UnitID]='" &
rst.Fields(2) & "'"), rst.Fields(2))
CCNo = rst.Fields(3)
EVCD = Nz(DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & rst.Fields(3) &
"'"), "10-50")
Call unitLog(CCNo, UNIT, "10-4", "10-50", "UNIT LOG", DLookup("EventTimer",
"tbl_EventCodes", "[EventCode] = '" & EVCD & "'"))

Exit_Function_STR:
Set dbs = Nothing
Set rst = Nothing
Exit Function
End Function
====== CODE ENDS HERE ======

Essentially I took the problem lookup out of the picture by adding the
field. I know that's not best practice in database design. I did extensive
testing last night and the error is occurring when the criteria field is set
to match against UnitID. I can lookup UnitID with other criteria, just not
using it as criteria. I tried converting it to every type of field i could
think of in both the code and the underlying query as well as converting the
rst field to no avail.

I just don't understand why it works on some and not on others.

If either of you have any more suggestions, I'm open to them. Otherwise
I'll consider it resolved for now, and mark each of you for helpful posts.

Thanks!

[All previous posts clipped]

 

[Steve Sanford]

I would make a couple of changes to your code:

** You should explicitly close the recordset before you set it to Nothing.

** The line "Exit function" is not needed... the function ends at the next
line.


Does the code now run on "....the front line comptuers running XP Pro SP3 /
Office 2003 SP3"?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hey guys,

I will agree with Steve, that after checking my sql statement in the
immediate window, that it is not qualifying against a date/time. I have
added the #s to get it to do so.

I have made a work around that seems to be functioning for the time being by
adding an additional column to my table which is written to through the
unitLog Sub.

The purpose of using the Function as opposed to a sub is that the function
is called from a hot-key (F2 specifically) as opposed to a control on a form.
If it should be a sub, I'll change it, but it's working.

My new code is as follows:
====== CODE STARTS HERE ======
Function mcr_SafetyTimerReset()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID,
tbl_OrganizationMembers.OM_CCNo " & _
"FROM tbl_OrganizationMembers " & _
"WHERE (((tbl_OrganizationMembers.OM_Timer) = ""ON"") And
((tbl_OrganizationMembers.OM_Expiration) <= #" & Now() & "#)) " & _
"ORDER BY tbl_OrganizationMembers.OM_Expiration"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If rst.EOF = True Then GoTo Exit_Function_STR
Dim CCNo As Variant
Dim EVCD As Variant
Dim UNIT As String
UNIT = Nz(DLookup("AssignedUnit", "qry_AssignedUnit", "[OM_UnitID]='" &
rst.Fields(2) & "'"), rst.Fields(2))
CCNo = rst.Fields(3)
EVCD = Nz(DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & rst.Fields(3) &
"'"), "10-50")
Call unitLog(CCNo, UNIT, "10-4", "10-50", "UNIT LOG", DLookup("EventTimer",
"tbl_EventCodes", "[EventCode] = '" & EVCD & "'"))

Exit_Function_STR:
Set dbs = Nothing
Set rst = Nothing
Exit Function
End Function
====== CODE ENDS HERE ======

Essentially I took the problem lookup out of the picture by adding the
field. I know that's not best practice in database design. I did extensive
testing last night and the error is occurring when the criteria field is set
to match against UnitID. I can lookup UnitID with other criteria, just not
using it as criteria. I tried converting it to every type of field i could
think of in both the code and the underlying query as well as converting the
rst field to no avail.

I just don't understand why it works on some and not on others.

If either of you have any more suggestions, I'm open to them. Otherwise
I'll consider it resolved for now, and mark each of you for helpful posts.

Thanks!

[All previous posts clipped]

 

[Scott Whetsell, A.S. - WVSP]

Steve,

From what I have read setting the rst to nothing forces the close, but I'll
take the change into consideration.

The exit function line is in there as error handling has not been added yet.

The answer to the important questions - Yes, the code is now working on the
front line computers.

Thanks again for your help.

I would make a couple of changes to your code:

** You should explicitly close the recordset before you set it to Nothing.

** The line "Exit function" is not needed... the function ends at the next
line.


Does the code now run on "....the front line comptuers running XP Pro SP3 /
Office 2003 SP3"?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hey guys,

I will agree with Steve, that after checking my sql statement in the
immediate window, that it is not qualifying against a date/time. I have
added the #s to get it to do so.

I have made a work around that seems to be functioning for the time being by
adding an additional column to my table which is written to through the
unitLog Sub.

The purpose of using the Function as opposed to a sub is that the function
is called from a hot-key (F2 specifically) as opposed to a control on a form.
If it should be a sub, I'll change it, but it's working.

My new code is as follows:
====== CODE STARTS HERE ======
Function mcr_SafetyTimerReset()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID,
tbl_OrganizationMembers.OM_CCNo " & _
"FROM tbl_OrganizationMembers " & _
"WHERE (((tbl_OrganizationMembers.OM_Timer) = ""ON"") And
((tbl_OrganizationMembers.OM_Expiration) <= #" & Now() & "#)) " & _
"ORDER BY tbl_OrganizationMembers.OM_Expiration"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If rst.EOF = True Then GoTo Exit_Function_STR
Dim CCNo As Variant
Dim EVCD As Variant
Dim UNIT As String
UNIT = Nz(DLookup("AssignedUnit", "qry_AssignedUnit", "[OM_UnitID]='" &
rst.Fields(2) & "'"), rst.Fields(2))
CCNo = rst.Fields(3)
EVCD = Nz(DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & rst.Fields(3) &
"'"), "10-50")
Call unitLog(CCNo, UNIT, "10-4", "10-50", "UNIT LOG", DLookup("EventTimer",
"tbl_EventCodes", "[EventCode] = '" & EVCD & "'"))

Exit_Function_STR:
Set dbs = Nothing
Set rst = Nothing
Exit Function
End Function
====== CODE ENDS HERE ======

Essentially I took the problem lookup out of the picture by adding the
field. I know that's not best practice in database design. I did extensive
testing last night and the error is occurring when the criteria field is set
to match against UnitID. I can lookup UnitID with other criteria, just not
using it as criteria. I tried converting it to every type of field i could
think of in both the code and the underlying query as well as converting the
rst field to no avail.

I just don't understand why it works on some and not on others.

If either of you have any more suggestions, I'm open to them. Otherwise
I'll consider it resolved for now, and mark each of you for helpful posts.

Thanks!

[All previous posts clipped]