Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

2000 vs. 2002 VB Problem

Discussion in 'Information Technology' started by Jeff G, Jul 28, 2006.

Tags:
  1. Jeff G

    Jeff G
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    Manmeet Kaur 2000 Joins Sikh Philosophy Network! New SPN'ers Sep 26, 2016
    Islam Number of Moques increase by 74% in USA after 2000! Interfaith Dialogues Mar 2, 2012
    1984 Hyde Park 2000 Rally in Memory of Bluestar History of Sikhism Jun 13, 2009
    Sikh News Fox 2000 bags rights for 'The Jury' (ANI via Yahoo! India News) Breaking News Oct 3, 2007
    Sikh News Punjab to get Rs 2000 crore from Rural Health Mission (New Kerala) Breaking News Aug 18, 2007

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Jeff G" <Jeff G@discussions.microsoft.com> wrote in message
    news:955A5718-6CA1-4B76-AD11-715AE3FE72FE@microsoft.com...
    > 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.
     

Share This Page