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

[Scott Whetsell, A.S. - WVSP]

Using the code below, I am getting Error 3464 on the line that looks up the
CCNo value. The code works fine on my computer running Vista SP1 / Office
2003 SP3 but will not run on the front line comptuers running XP Pro SP3 /
Office 2003 SP3.

====== 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 " & _
"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 = DLookup("UL_CCNo", "qry_UnitSts2", "[Unit]='" & rst.Fields(2) & "'")
'<---
EVCD = DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & CCNo & "'")
Call unitLog(CCNo, UNIT, "10-4", "10-50", EVCD)

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

Code fails on line indicated with '<---

Fields Defined:
OM_Timer Text "ON" / "OFF" / "" Values only
OM_Expiration Date/Time yyyy-mm-dd hh:nn:ss
OM_UnitID Text 3 - 6 AlphaNumeric Characters incl
hyphens
EVENTCODE Text 3 - 6 AlphaNumeric Characters
CCNo fields Text 11 Characters formatted yyyy-000000
AssignedUnit Text 3 - 50 AlphaNumeric Chars incl
hypens and space

This function is in an Autokeys module. The recordset generally will not
have more than one record at a time, but if there is, it should select the
oldest record first. unitLog is another function which should have no effect
on this function.

Thanks for any assistance.

 

[Scott Whetsell, A.S. - WVSP]

Variants are used in fields where the result may be null. A CCNo is not
applicable to all records, so variant was used to handle the null.

My guess is you are getting a Null in the previous DLookup() that sets CCNo.
Why have you declared these variables as Variants rather than strings?

Using the code below, I am getting Error 3464 on the line that looks up the
CCNo value. The code works fine on my computer running Vista SP1 / Office
2003 SP3 but will not run on the front line comptuers running XP Pro SP3 /
Office 2003 SP3.

====== 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 " & _
"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 = DLookup("UL_CCNo", "qry_UnitSts2", "[Unit]='" & rst.Fields(2) & "'")
'<---
EVCD = DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & CCNo & "'")
Call unitLog(CCNo, UNIT, "10-4", "10-50", EVCD)

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

Code fails on line indicated with '<---

Fields Defined:
OM_Timer Text "ON" / "OFF" / "" Values only
OM_Expiration Date/Time yyyy-mm-dd hh:nn:ss
OM_UnitID Text 3 - 6 AlphaNumeric Characters incl
hyphens
EVENTCODE Text 3 - 6 AlphaNumeric Characters
CCNo fields Text 11 Characters formatted yyyy-000000
AssignedUnit Text 3 - 50 AlphaNumeric Chars incl
hypens and space

This function is in an Autokeys module. The recordset generally will not
have more than one record at a time, but if there is, it should select the
oldest record first. unitLog is another function which should have no effect
on this function.

Thanks for any assistance.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Scott Whetsell, A.S. - WVSP]

I should have clarified earlier, so I apologize. I have tried Nz, CStr, and
Str functions on both sides of the criteria and source data. Again it works
fine on the computer running Vista, but not on ones running XP.

Why not use the Nz() function and turn a Null into a ZLS ("")?

Variants are used in fields where the result may be null. A CCNo is not
applicable to all records, so variant was used to handle the null.

My guess is you are getting a Null in the previous DLookup() that sets CCNo.
Why have you declared these variables as Variants rather than strings?

[quoted text clipped - 53 lines]

Thanks for any assistance.

 

[Scott Whetsell, A.S. - WVSP]

No Luck that way either. Same error.

How about if you use Nz() this way?
CCNo = Nz(DLookup("UL_CCNo", "qry_UnitSts2", "[Unit]='" & rst.Fields(2) & "'")
,"")

...I would think that if the original CCNo line returned a Null that the next
line
EVCD = DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & CCNo & "'")
...would barf in any version.

I should have clarified earlier, so I apologize. I have tried Nz, CStr, and
Str functions on both sides of the criteria and source data. Again it works
fine on the computer running Vista, but not on ones running XP.

Why not use the Nz() function and turn a Null into a ZLS ("")?

[quoted text clipped - 6 lines]

Thanks for any assistance.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Scott Whetsell, A.S. - WVSP]

The simple answer is yes to all. I have tried where CCNo is both null and
has a value.

Did you change
Dim CCNo As Variant
to
Dim CCNo As String

Have you applied the applied the HotFix fpr SP3?
http://support.microsoft.com/kb/945674

Have you verified the value of CCNo that is causing the error?

No Luck that way either. Same error.

How about if you use Nz() this way?
CCNo = Nz(DLookup("UL_CCNo", "qry_UnitSts2", "[Unit]='" & rst.Fields(2) & "'")

[quoted text clipped - 14 lines]

Thanks for any assistance.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Scott Whetsell, A.S. - WVSP]

I tried importing all objects into a new mdb, made an mde off the old and
new, and still no luck. Any other ideas?

Maybe there's some corruption. Try importing everything into a new mdb and
see if that solves anything.

The simple answer is yes to all. I have tried where CCNo is both null and
has a value.

Did you change
Dim CCNo As Variant

[quoted text clipped - 13 lines]

Thanks for any assistance.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Scott Whetsell, A.S. - WVSP]

Allan,

The line causing the trouble is the line before which sets the CCNo value.
I've done more troubleshooting, and installed the databases on my PC at home
which runs WinXP and Office 2003. The code works fine there. I thought it
might be a network issue, so I tried running it on the computer hosting the
databases on the network, and it still won't work. Maybe an install issue?
My Office copies were installed from retail CDs, the ones at work were
installed from a volume license file. I don't see where that should cause a
functional issue tho. Any ideas?

To be honest Scott, I'm out of ideas.
Did I understand you correctly? Did you say that this line:
EVCD = DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & CCNo & "'")
gets error 3464 when CCNo has a value? That makes no sense.
Are you using Access tables or do you have some other back end?

I tried importing all objects into a new mdb, made an mde off the old and
new, and still no luck. Any other ideas?

Maybe there's some corruption. Try importing everything into a new mdb and
see if that solves anything.

[quoted text clipped - 7 lines]

Thanks for any assistance.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Scott Whetsell, A.S. - WVSP]

Allan,

The purpose of the UNIT line is to lookup a full ID for the Unit selected,
if the unit is not in the system, it should return just the entered UnitID.
I haven't tested it against one that isn't in the system yet.

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

Hi Scott.
You know that this line:
UNIT = Nz(DLookup("AssignedUnit", "qry_AssignedUnit", "[OM_UnitID]='" &
rst.Fields(2) & "'"), rst.Fields(2))
...is not protected from Nulls, right?

And if I understand you correctly now, this line:
CCNo = DLookup("UL_CCNo", "qry_UnitSts2", "[Unit]='" & rst.Fields(2) & "'")
...is the one giving you the error, right?

Allan,

The line causing the trouble is the line before which sets the CCNo value.
I've done more troubleshooting, and installed the databases on my PC at home
which runs WinXP and Office 2003. The code works fine there. I thought it
might be a network issue, so I tried running it on the computer hosting the
databases on the network, and it still won't work. Maybe an install issue?
My Office copies were installed from retail CDs, the ones at work were
installed from a volume license file. I don't see where that should cause a
functional issue tho. Any ideas?

To be honest Scott, I'm out of ideas.
Did I understand you correctly? Did you say that this line:

[quoted text clipped - 10 lines]

Thanks for any assistance.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[Steve Sanford]

Hi Scott ,


PMFJI....

You said that on some computers you are getting the error
"Error '3464'-Data Type Mismatch in Criteria "

when your code runs. I don't know why you get the error message on some
computers but not on others, but I did find an error in the SQL statement.

I rewrote your Function, but was unable to test it..... I would advise
single stepping thru it to see what values are returned. I don't know why you
are using a function instead of a Sub, but ...

The purpose of the UNIT line is to lookup a full ID for the Unit selected,
if the unit is not in the system, it should return just the entered UnitID.
I haven't tested it against one that isn't in the system yet.

There is nothing in the code where the "UnitId" is entered. Or a control on
the form is referenced. You might look at what you want to happen if the UNIT
is NULL/Empty. Should you try to get a CCNo if the UNIT is Null???


When I added a "Debug.Print strSQL" command to your code, this is the result:

SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID FROM
tbl_OrganizationMembers WHERE (((tbl_OrganizationMembers.OM_Timer) = "ON")
And ((tbl_OrganizationMembers.OM_Expiration) <= Now())) ORDER BY
tbl_OrganizationMembers.OM_Expiration


Note the WHERE clause for the "OM_Expiration" criteria. You are trying to
filter on a date type, but the value is text .. ie "Now()".

Here is the Debug result after I modified the strSQL line (see the code):

SELECT OM_Timer, OM_Expiration, OM_UnitID FROM tbl_OrganizationMembers WHERE
OM_Timer = "ON" And OM_Expiration <= #6/30/2008 4:03:11 AM# ORDER BY
tbl_OrganizationMembers.OM_Expiration

That is what I would expect the SQL to look like.


In the Function, I used recordsets instead of the DLookUp() function.
So, anyway, here is the code I modified.....

'--------code beg------------------------------
Function mcr_SafetyTimerReset_SS()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim rstTmp As DAO.Recordset
Dim strSQL As String
Dim strSQLTmp As String

' your SQL
' strSQL = "SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID " & _
' "FROM tbl_OrganizationMembers " & _
' "WHERE (((tbl_OrganizationMembers.OM_Timer) = ""ON"") And
((tbl_OrganizationMembers.OM_Expiration) <= Now())) " & _
' "ORDER BY tbl_OrganizationMembers.OM_Expiration"
'
'Debug.Print strSQL
'Debug.Print

' Where there is only one table, I remove the table prefix
' it is easier to read
strSQL = "SELECT OM_Timer, OM_Expiration, OM_UnitID"
strSQL = strSQL & " FROM tbl_OrganizationMembers"
strSQL = strSQL & " WHERE OM_Timer = ""ON"" And OM_Expiration <= #" &
Now() & "#"
strSQL = strSQL & " ORDER BY tbl_OrganizationMembers.OM_Expiration"

'Debug.Print strSQL

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

If Not rst.EOF And Not rst.EOF Then
Dim CCNo As Variant
Dim EVCD As Variant
Dim UNIT As String

'---------------------------
' Find the UNIT number
' UNIT = Nz(DLookup("AssignedUnit", "qry_AssignedUnit",
"[OM_UnitID]='" & rst.Fields(2) & "'"), rst.Fields(2))
strSQLTmp = "SELECT AssignedUnit"
strSQLTmp = strSQLTmp & " FROM qry_AssignedUnit"
strSQLTmp = strSQLTmp & " WHERE [OM_UnitID] = '" & rst.Fields(2) & "'"

Set rstTmp = dbs.OpenRecordset(strSQLTmp)
If Not rstTmp.BOF And Not rstTmp.EOF Then
UNIT = rstTmp.Fields(0)
Else
UNIT = ""
End If
rstTmp.Close
'---------------------------


' If the UNIT or (rst.Fields(2)) is NULL (/Empty),
'do you still want to look up the CCNo???

'---------------------------
' Find the CCNo number
' CCNo = DLookup("UL_CCNo", "qry_UnitSts2", "[Unit]='" &
rst.Fields(2) & "'") '<---

strSQLTmp = "SELECT UL_CCNo"
strSQLTmp = strSQLTmp & " FROM qry_UnitSts2"
strSQLTmp = strSQLTmp & " WHERE [Unit] = '" & rst.Fields(2) & "'"

Set rstTmp = dbs.OpenRecordset(strSQLTmp)
If Not rstTmp.BOF And Not rstTmp.EOF Then
CCNo = rstTmp.Fields(0)
Else
CCNo = ""
End If
rstTmp.Close
'---------------------------

rst.Close

'---------------------------
' Find EVCD
' EVCD = DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & CCNo & "'")

strSQLTmp = "SELECT EventCode"
strSQLTmp = strSQLTmp & " FROM tbl_CCNo"
strSQLTmp = strSQLTmp & " WHERE [CCNo] = '" & CCNo & "'"

Set rstTmp = dbs.OpenRecordset(strSQLTmp)
If Not rstTmp.BOF And Not rstTmp.EOF Then
CCNo = rstTmp.Fields(0)
Else
CCNo = ""
End If
rstTmp.Close
'---------------------------


Call unitLog(CCNo, UNIT, "10-4", "10-50", EVCD)

End If

Exit_Function_STR:
Set dbs = Nothing
Set rst = Nothing
Set rstTmp = Nothing

End Function
'--------code end------------------------------


HTH

 

[Steve Sanford]

Oops.... In modifying the code I had two copies of the function, so I added
"_SS" to the name of the function so I wouldn't have duplicate function names.

Please remove the "_SS" if you try to run the modified function.

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

Hi Scott ,


PMFJI....

You said that on some computers you are getting the error
"Error '3464'-Data Type Mismatch in Criteria "

when your code runs. I don't know why you get the error message on some
computers but not on others, but I did find an error in the SQL statement.

I rewrote your Function, but was unable to test it..... I would advise
single stepping thru it to see what values are returned. I don't know why you
are using a function instead of a Sub, but ...

The purpose of the UNIT line is to lookup a full ID for the Unit selected,
if the unit is not in the system, it should return just the entered UnitID.
I haven't tested it against one that isn't in the system yet.

There is nothing in the code where the "UnitId" is entered. Or a control on
the form is referenced. You might look at what you want to happen if the UNIT
is NULL/Empty. Should you try to get a CCNo if the UNIT is Null???


When I added a "Debug.Print strSQL" command to your code, this is the result:

SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID FROM
tbl_OrganizationMembers WHERE (((tbl_OrganizationMembers.OM_Timer) = "ON")
And ((tbl_OrganizationMembers.OM_Expiration) <= Now())) ORDER BY
tbl_OrganizationMembers.OM_Expiration


Note the WHERE clause for the "OM_Expiration" criteria. You are trying to
filter on a date type, but the value is text .. ie "Now()".

Here is the Debug result after I modified the strSQL line (see the code):

SELECT OM_Timer, OM_Expiration, OM_UnitID FROM tbl_OrganizationMembers WHERE
OM_Timer = "ON" And OM_Expiration <= #6/30/2008 4:03:11 AM# ORDER BY
tbl_OrganizationMembers.OM_Expiration

That is what I would expect the SQL to look like.


In the Function, I used recordsets instead of the DLookUp() function.
So, anyway, here is the code I modified.....

'--------code beg------------------------------
Function mcr_SafetyTimerReset_SS()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim rstTmp As DAO.Recordset
Dim strSQL As String
Dim strSQLTmp As String

' your SQL
' strSQL = "SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID " & _
' "FROM tbl_OrganizationMembers " & _
' "WHERE (((tbl_OrganizationMembers.OM_Timer) = ""ON"") And
((tbl_OrganizationMembers.OM_Expiration) <= Now())) " & _
' "ORDER BY tbl_OrganizationMembers.OM_Expiration"
'
'Debug.Print strSQL
'Debug.Print

' Where there is only one table, I remove the table prefix
' it is easier to read
strSQL = "SELECT OM_Timer, OM_Expiration, OM_UnitID"
strSQL = strSQL & " FROM tbl_OrganizationMembers"
strSQL = strSQL & " WHERE OM_Timer = ""ON"" And OM_Expiration <= #" &
Now() & "#"
strSQL = strSQL & " ORDER BY tbl_OrganizationMembers.OM_Expiration"

'Debug.Print strSQL

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

If Not rst.EOF And Not rst.EOF Then
Dim CCNo As Variant
Dim EVCD As Variant
Dim UNIT As String

'---------------------------
' Find the UNIT number
' UNIT = Nz(DLookup("AssignedUnit", "qry_AssignedUnit",
"[OM_UnitID]='" & rst.Fields(2) & "'"), rst.Fields(2))
strSQLTmp = "SELECT AssignedUnit"
strSQLTmp = strSQLTmp & " FROM qry_AssignedUnit"
strSQLTmp = strSQLTmp & " WHERE [OM_UnitID] = '" & rst.Fields(2) & "'"

Set rstTmp = dbs.OpenRecordset(strSQLTmp)
If Not rstTmp.BOF And Not rstTmp.EOF Then
UNIT = rstTmp.Fields(0)
Else
UNIT = ""
End If
rstTmp.Close
'---------------------------


' If the UNIT or (rst.Fields(2)) is NULL (/Empty),
'do you still want to look up the CCNo???

'---------------------------
' Find the CCNo number
' CCNo = DLookup("UL_CCNo", "qry_UnitSts2", "[Unit]='" &
rst.Fields(2) & "'") '<---

strSQLTmp = "SELECT UL_CCNo"
strSQLTmp = strSQLTmp & " FROM qry_UnitSts2"
strSQLTmp = strSQLTmp & " WHERE [Unit] = '" & rst.Fields(2) & "'"

Set rstTmp = dbs.OpenRecordset(strSQLTmp)
If Not rstTmp.BOF And Not rstTmp.EOF Then
CCNo = rstTmp.Fields(0)
Else
CCNo = ""
End If
rstTmp.Close
'---------------------------

rst.Close

'---------------------------
' Find EVCD
' EVCD = DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & CCNo & "'")

strSQLTmp = "SELECT EventCode"
strSQLTmp = strSQLTmp & " FROM tbl_CCNo"
strSQLTmp = strSQLTmp & " WHERE [CCNo] = '" & CCNo & "'"

Set rstTmp = dbs.OpenRecordset(strSQLTmp)
If Not rstTmp.BOF And Not rstTmp.EOF Then
CCNo = rstTmp.Fields(0)
Else
CCNo = ""
End If
rstTmp.Close
'---------------------------


Call unitLog(CCNo, UNIT, "10-4", "10-50", EVCD)

End If

Exit_Function_STR:
Set dbs = Nothing
Set rst = Nothing
Set rstTmp = Nothing

End Function
'--------code end------------------------------


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

Allan,

The purpose of the UNIT line is to lookup a full ID for the Unit selected,
if the unit is not in the system, it should return just the entered UnitID.
I haven't tested it against one that isn't in the system yet.

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

 

[Steve Sanford]

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 S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

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


Now Function

Returns a Variant (Date) specifying the current date and time according your
computer's system date and time.

Hi Scott ,

PMFJI....

You said that on some computers you are getting the error
"Error '3464'-Data Type Mismatch in Criteria "

when your code runs. I don't know why you get the error message on some
computers but not on others, but I did find an error in the SQL statement.

I rewrote your Function, but was unable to test it..... I would advise
single stepping thru it to see what values are returned. I don't know why you
are using a function instead of a Sub, but ...

The purpose of the UNIT line is to lookup a full ID for the Unit selected,
if the unit is not in the system, it should return just the entered UnitID.
I haven't tested it against one that isn't in the system yet.

There is nothing in the code where the "UnitId" is entered. Or a control on
the form is referenced. You might look at what you want to happen if the UNIT
is NULL/Empty. Should you try to get a CCNo if the UNIT is Null???

When I added a "Debug.Print strSQL" command to your code, this is the result:

SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID FROM
tbl_OrganizationMembers WHERE (((tbl_OrganizationMembers.OM_Timer) = "ON")
And ((tbl_OrganizationMembers.OM_Expiration) <= Now())) ORDER BY
tbl_OrganizationMembers.OM_Expiration

Note the WHERE clause for the "OM_Expiration" criteria. You are trying to
filter on a date type, but the value is text .. ie "Now()".

Here is the Debug result after I modified the strSQL line (see the code):

SELECT OM_Timer, OM_Expiration, OM_UnitID FROM tbl_OrganizationMembers WHERE
OM_Timer = "ON" And OM_Expiration <= #6/30/2008 4:03:11 AM# ORDER BY
tbl_OrganizationMembers.OM_Expiration

That is what I would expect the SQL to look like.

In the Function, I used recordsets instead of the DLookUp() function.
So, anyway, here is the code I modified.....

'--------code beg------------------------------
Function mcr_SafetyTimerReset_SS()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim rstTmp As DAO.Recordset
Dim strSQL As String
Dim strSQLTmp As String

' your SQL
' strSQL = "SELECT tbl_OrganizationMembers.OM_Timer,
tbl_OrganizationMembers.OM_Expiration, tbl_OrganizationMembers.OM_UnitID " & _
' "FROM tbl_OrganizationMembers " & _
' "WHERE (((tbl_OrganizationMembers.OM_Timer) = ""ON"") And
((tbl_OrganizationMembers.OM_Expiration) <= Now())) " & _
' "ORDER BY tbl_OrganizationMembers.OM_Expiration"
'
'Debug.Print strSQL
'Debug.Print

' Where there is only one table, I remove the table prefix
' it is easier to read
strSQL = "SELECT OM_Timer, OM_Expiration, OM_UnitID"
strSQL = strSQL & " FROM tbl_OrganizationMembers"
strSQL = strSQL & " WHERE OM_Timer = ""ON"" And OM_Expiration <= #" &
Now() & "#"
strSQL = strSQL & " ORDER BY tbl_OrganizationMembers.OM_Expiration"

'Debug.Print strSQL

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

If Not rst.EOF And Not rst.EOF Then
Dim CCNo As Variant
Dim EVCD As Variant
Dim UNIT As String

'---------------------------
' Find the UNIT number
' UNIT = Nz(DLookup("AssignedUnit", "qry_AssignedUnit",
"[OM_UnitID]='" & rst.Fields(2) & "'"), rst.Fields(2))
strSQLTmp = "SELECT AssignedUnit"
strSQLTmp = strSQLTmp & " FROM qry_AssignedUnit"
strSQLTmp = strSQLTmp & " WHERE [OM_UnitID] = '" & rst.Fields(2) & "'"

Set rstTmp = dbs.OpenRecordset(strSQLTmp)
If Not rstTmp.BOF And Not rstTmp.EOF Then
UNIT = rstTmp.Fields(0)
Else
UNIT = ""
End If
rstTmp.Close
'---------------------------

' If the UNIT or (rst.Fields(2)) is NULL (/Empty),
'do you still want to look up the CCNo???

'---------------------------
' Find the CCNo number
' CCNo = DLookup("UL_CCNo", "qry_UnitSts2", "[Unit]='" &
rst.Fields(2) & "'") '<---

strSQLTmp = "SELECT UL_CCNo"
strSQLTmp = strSQLTmp & " FROM qry_UnitSts2"
strSQLTmp = strSQLTmp & " WHERE [Unit] = '" & rst.Fields(2) & "'"

Set rstTmp = dbs.OpenRecordset(strSQLTmp)
If Not rstTmp.BOF And Not rstTmp.EOF Then
CCNo = rstTmp.Fields(0)
Else
CCNo = ""
End If
rstTmp.Close
'---------------------------

rst.Close

'---------------------------
' Find EVCD
' EVCD = DLookup("EventCode", "tbl_CCNo", "[CCNo]='" & CCNo & "'")

strSQLTmp = "SELECT EventCode"
strSQLTmp = strSQLTmp & " FROM tbl_CCNo"
strSQLTmp = strSQLTmp & " WHERE [CCNo] = '" & CCNo & "'"

Set rstTmp = dbs.OpenRecordset(strSQLTmp)
If Not rstTmp.BOF And Not rstTmp.EOF Then
CCNo = rstTmp.Fields(0)
Else
CCNo = ""
End If
rstTmp.Close
'---------------------------

Call unitLog(CCNo, UNIT, "10-4", "10-50", EVCD)

End If

Exit_Function_STR:
Set dbs = Nothing
Set rst = Nothing
Set rstTmp = Nothing

End Function
'--------code end------------------------------

HTH

Allan,

[quoted text clipped - 4 lines]

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

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[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!

 

[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]