Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Unanswered: Access97 "This expression is typed incorrectly, or it is too complex to be evaluated.

    Everytime I open a database query in Access97 this is the error I get

    Should be a BMP file of the error


    Option Compare Database
    Option Explicit

    Function Convert_WIP_Date(TSTAMP As Long, QStart As Long) As Date
    Dim NumDays As Long
    Dim Remainder, Remainder2, Remainder3 As Long
    Dim NewDate As Date
    Dim OriginDate As Date
    Dim TimeZoneOffset As Integer
    Dim NewHour, NewMinute, NewSecond As Long
    Dim strNewTime As String
    Dim NewTime As Date
    Dim WIPDate As Long

    Const SECS_PER_DAY = 86400
    Const SECS_PER_HOUR = 3600
    Const SECS_PER_MINUTE = 60

    If TSTAMP = 0 Then
    WIPDate = QStart
    Else
    WIPDate = TSTAMP
    End If

    TimeZoneOffset = 7 * 60 * 60 'seven hours in seconds
    OriginDate = CDate("01/01/70")

    NumDays = (WIPDate - TimeZoneOffset) \ SECS_PER_DAY
    Remainder = (WIPDate - TimeZoneOffset) Mod SECS_PER_DAY

    NewHour = Remainder \ SECS_PER_HOUR 'get the number of hours since midnight
    Remainder2 = Remainder Mod SECS_PER_HOUR

    NewMinute = Remainder2 \ SECS_PER_MINUTE 'GET THE NUMBER OF MINUTES
    Remainder3 = Remainder2 Mod SECS_PER_MINUTE

    NewSecond = Remainder3

    NewDate = OriginDate + NumDays

    If WIPDate <> 0 Then
    strNewTime = Trim(Str(NewHour)) & ":" & Trim(Str(NewMinute)) & ":" & Trim(Str(NewSecond))
    NewTime = CDate(strNewTime)
    Convert_WIP_Date = NewDate + NewTime 'set return date

    Else
    Convert_WIP_Date = NewDate 'set return date
    End If


    ' MsgBox NewDate
    End Function
    Attached Thumbnails Attached Thumbnails error.bmp  

  2. #2
    Join Date
    Jul 2006
    Posts
    108
    If WIPDate <> 0 Then
    strNewTime = Trim(Str(NewHour)) & ":" & Trim(Str(NewMinute)) & ":" & Trim(Str(NewSecond))
    NewTime = CDate(str(NewTime)

  3. #3
    Join Date
    Aug 2006
    Posts
    2

    reply

    thanks it worked

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yep. Except for the "fix" has a typo ... The missing ending paren for the CDate function call ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this reminds me of some clever code i saw a while back using dateadd() to do the conversion. the problem is that dateadd() works with longs and wont handle the big unix datetime. here's my reproduction of the clever code:

    Code:
    Private Sub Test()
    
        Dim dtUnx As Currency
        Dim dtWin As Date
        Dim lngTmp As Long
        Const lngMax As Long = 200000000
        
        'get test unix datetime from a form
        dtUnx = Me.uDate
        
        'initialise dtWin to start of unix time
        dtWin = #1/1/1970#
        
        'strip off lumps of lngMax seconds
        Do While dtUnx > lngMax
            dtWin = DateAdd("s", lngMax, dtWin)
            dtUnx = dtUnx - lngMax
        Loop
    
        'now dtUnx <= lngMax seconds
        lngTmp = dtUnx
        dtWin = DateAdd("s", lngMax, dtWin)
        
        'show the Access datetime on the form
        Me.aDate = dtWin
    
    End Sub
    (...doesn't do your timezone, so you need to adjust)

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •