Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: problem with dates

    this code keeps creating non excistent dates for example the source querys largest date is 23/02/2003 but the table created has 02/12/2003
    Code:
    Sub makescraptable()
        Dim rs As DAO.Recordset, i As Integer
        Set rs = CurrentDb.OpenRecordset("qryScrap")
        rs.MoveFirst
        DoCmd.SetWarnings False
        While Not rs.EOF
            For i = 2 To 50
                If rs(i).Value > 0 Then DoCmd.RunSQL "INSERT INTO Scrap ( MachineID, PostingDate, ScrapCode,
     Quantity )SELECT '" & rs(0).Value & "' , #" & rs(1).Value & "# , " & Val(Right(rs(i).Name, 2)) & " , " & rs(i).Value & ";"
            Next
            rs.MoveNext
        Wend
        DoCmd.SetWarnings True
    End Sub
    yet if it's using the wrong date format why does 31/03/2001 come through okay, whats the problem and how do i fix it?
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Jet SQL is a bit icky with dates. It defaults to us format (mm/dd/yyyy) on input. Only if that doesn't apply (there is no month 31) it tries others. The most relieable way to handle dates in Jet SQL i found is converting the dates to double (the format Jet actually handles dates internally). You only need to be careful when having date/time values and a decimal seperator othe than . in you locale. I wrote a little helper function quite a while back to handle that (and other stuff for injecting parameters into an SQL in code):

    Code:
    Public Function EscapeSQLParameter( _
        ByVal vParameter As Variant) _
        As String
    ' This function converts the usual vb data types in
    ' string compatible with jet sql. You should cast the
    ' parameter explicitely to the data type you want it
    ' to have (e.g. when you read out a date from an edit
    ' box you need to cast it to date with cdate(myfield)
    ' as it's otherwise string type which will result in
    ' wrong handling
        Dim strBuffer As String
        Dim lngSeperatorPos As Long
        
        Dim parType As VbVarType
        
        parType = VarType(vParameter)
        
        Select Case parType
        
            Case vbNull
                strBuffer = "NULL"
            Case vbObject
                Err.Raise 443 'object has no default value
            Case vbBoolean
                If vParameter Then
                    strBuffer = "true"
                Else
                    strBuffer = "false"
                End If
            Case vbByte, vbInteger, vbLong
                strBuffer = CStr(vParameter)
            Case vbCurrency, vbDecimal, vbSingle, vbDouble
                strBuffer = Trim(Str(vParameter)) 'str always uses . as decimal point character
            Case vbDate
                'converting to double seems to be the
                'safest way for jet workspaces
                strBuffer = EscapeSQLParameter((CDbl(vParameter)))
            Case vbString
                strBuffer = "'" & EscapeSQLString(vParameter) & "'"
            Case Else
                Err.Raise 13 'type mismatch
        End Select
        
        EscapeSQLParameter = strBuffer
    End Function
    oh, nearly forgot, you need another helper function (credits to John DeHope) to get the string escaping right.
    Code:
    Public Function EscapeSQLString( _
        ByVal Text As Variant) As String
    'by John DeHope
    
    Dim x
    
    If IsNull(Text) Then
        EscapeSQLString = ""
    Else
        x = 1
        Do While x <= Len(Text)
            
            If Mid(Text, x, 1) = "'" Then
                Text = Left(Text, x - 1) & "''" & Right(Text, Len(Text) - x)
                x = x + 2
            Else
                x = x + 1
            End If
            
        Loop
        EscapeSQLString = Text
    End If
    End Function
    Last edited by Apel; 02-24-03 at 12:22.

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Read this also - may help

    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    will this work then?
    Code:
    Sub makescraptable()
        Dim rs As DAO.Recordset, i As Integer
        Set rs = CurrentDb.OpenRecordset("qryScrap")
        rs.MoveFirst
        DoCmd.SetWarnings False
        While Not rs.EOF
            For i = 2 To 50
                If rs(i).Value > 0 Then DoCmd.RunSQL "INSERT INTO Scrap ( MachineID, PostingDate, ScrapCode,
     Quantity )SELECT '" & rs(0).Value & "' , format(#" & changeDate(rs(1).Value) & "#,"dd/mm/yyyy") , " & Val(Right(rs(i).Name, 2)) & " , " & rs(i).Value & ";"
            Next
            rs.MoveNext
        Wend
        DoCmd.SetWarnings True
    End Sub
    
    Function changeDate(x as date)as string
        changeDate = Month(x) & "/" & Day(x) & "/" & Year(x)
    End Function
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    No, the us date format has to be within the string. Like this then:
    Code:
    DoCmd.RunSQL _
        "INSERT INTO Scrap " & _
        "(MachineID, PostingDate, ScrapCode, Quantity) " & _
        "SELECT '" & rs(0).Value & "', " & _
        "#" & changeDate(rs(1).Value) & "#, " & _
        Val(Right(rs(i).Name, 2)) & ", " & rs(i).Value & ";"
    I would still prefer to convert it to double though as it handles times correctly, has less overhead and you won't need to escape it with hashes.

    Code:
    DoCmd.RunSQL _
        "INSERT INTO Scrap " & _
        "(MachineID, PostingDate, ScrapCode, Quantity) " & _
        "SELECT '" & rs(0).Value & "', " &  _
        Str(CDbl(rs(1).Value)) & ", " & _
        Val(Right(rs(i).Name, 2)) & ", " & rs(i).Value & ";"

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i thought thats what i'd done
    ie give the date in us to the sql and then foramt the date to the form i need inside the SQL, we're a british company and need the british date format

    , format(#" & changeDate(rs(1).Value) & "#,"dd/mm/yyyy")

    but i'll give you second method a try


    Originally posted by Apel
    No, the us date format has to be within the string. Like this then:
    Code:
    DoCmd.RunSQL _
        "INSERT INTO Scrap " & _
        "(MachineID, PostingDate, ScrapCode, Quantity) " & _
        "SELECT '" & rs(0).Value & "', " & _
        "#" & changeDate(rs(1).Value) & "#, " & _
        Val(Right(rs(i).Name, 2)) & ", " & rs(i).Value & ";"
    I would still prefer to convert it to double though as it handles times correctly, has less overhead and you won't need to escape it with hashes.

    Code:
    DoCmd.RunSQL _
        "INSERT INTO Scrap " & _
        "(MachineID, PostingDate, ScrapCode, Quantity) " & _
        "SELECT '" & rs(0).Value & "', " &  _
        Str(CDbl(rs(1).Value)) & ", " & _
        Val(Right(rs(i).Name, 2)) & ", " & rs(i).Value & ";"
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    the second method seems to do the job so i'll leave it at that

    Originally posted by m.timoney
    i thought thats what i'd done
    ie give the date in us to the sql and then foramt the date to the form i need inside the SQL, we're a british company and need the british date format

    , format(#" & changeDate(rs(1).Value) & "#,"dd/mm/yyyy")

    but i'll give you second method a try
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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