2000 vs. 2002 VB Problem

[Jeff G]

Now that I am running my access on 2002 instead of 2000 I am having a problem
getting some VB script to run. My programmer is out of the country for two
months so it's up to me. Here is the script that isn't running properly
below...


Sub CreateUserStatsRecords()
' assumes date is in fparam(4)

On Error Resume Next
Dim db As Database
Set db = DBEngine.Workspaces(0)(0)
Dim cs As Recordset
Dim sql As String
Dim TheDate As String
Dim date1 As String
date1 = Format$(fparam(4), "short date")
'thedate = "#" & date1 & "#"
TheDate = "#" & Left$(date1, Len(date1) - 2) & "20" & Right$(date1, 2) &
"#"
Dim crit As String

Dim us As Recordset
sql = "select * from [User Stats] where [Day] = " & TheDate
Set us = db.OpenRecordset(sql, DB_OPEN_DYNASET)

Set cs = db.OpenRecordset("Call Summary query 3", DB_OPEN_SNAPSHOT)
Do Until cs.EOF
DoEvents
crit = "[User Ptr] = " & Str$(cs![User Ptr])
us.FindFirst crit

If us.NoMatch Then
us.AddNew
us![User Ptr] = cs![User Ptr]
us![Day] = fparam(4)
us![Paid Hours] = cs![Default Hours]
us.Update
End If

cs.MoveNext
If Err Then Exit Do
Loop

cs.Close
us.Close


End Sub


If I run it on a Access 2000 machine, it runs properly. But if I run it on
Access 2002, I don't get anything.

I don't know much about this, so any help I can get would be greatly
appreciated. Thanks.

 

[Douglas J. Steele]

The calculations you're going through to get TheDate look suspect to me.

The implication is that you're expecing date1 to be a date formatted like
mm/dd/yy or dd/mm/yy (depending on what you've set in Regional Settings),
and you're trying to make sure it's in this century. What happens, though,
it you've set Short Date to mm/dd/yyyy or dd/mm/yyyy? TheDate is definitely
going to be wrong.

Never mind that: if you have set Short Date to dd/mm/yy, it's not going to
work, because Access will ALWAYS try to use mm/dd/yy(yy) it if can,
regardless of the Regional Settings. That means even if you've formatted the
year correctly, Access will always be wrong for the first 12 days of each
month (once you hit the 13th day, Access will start accepting the value as
dd/mm/yy(yy)).

If that's not the problem you're encountering, perhaps you can explain what
"if I run it on Access 2002, I don't get anything" means.