date import trouble

[toon10]

Hi

I’m having trouble getting some dates into an Access table. I have a visual basic routine which reads a text file and populates my table. All of the other fields work fine except my dates. In the file, the date is listed in “dd/mm/yyyy” format. My Access table date field is set to a type of date/time and a format of short date (i.e. the same “dd/mm/yyyy”) I’m expecting the data to copy straight in without any issues but it’s not.

When I open my table in datasheet view, all of the date fields are listed as “30/12/1899”. The thing is, if I click on a specific date in the table this changes to a time format of “00:02:47” until I click on another then it goes back to “30/12/1899”. It seems to think it’s a time format?

Any ideas.

Andrew

 

[BobStubbs]

How is your Visual Basic routine preparing / formatting the dates? Can you post the code fragment which shows how the date is prepared, then imported?

My guess is that your problem is something to do with the date being loaded into the table is if it is a string, but seeing your code will help.


Bob Stubbs

 

[SkipVought]

Hi,

You need t COMVERT your string to a date...

Dim sDate as String, dDate as Date
sDate = “dd/mm/yyyy”
dDate = DateSerial(right(sDate, 4), left(sDate,2), mid(sDate3,2))

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[willir]

Would this work...

dDate = CDate(YourDateString)

For example...
dDate = CDate("05/15/2005")

Richard

 

[toon10]

Hi.

Here's my code.

...
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & VB.App.Path & "\Data\Chart.mdb;"

'delete current information
db.Execute " DELETE FROM [tblSAPSales]"

Open ImportDataFile For Input As 1
While Not EOF(1)
Line Input #1, strTemp
ImportString = ImportString & strTemp
ImportString = Replace(strTemp, "'", "''")
FItemArray = Split(ImportString, ",")
f1 = "'" & FItemArray(0) & "'"
f2 = "'" & FItemArray(1) & "'"
f3 = "'" & FItemArray(2) & "'"
f4 = "'" & FItemArray(3) & "'"
f5 = "'" & FItemArray(4) & "'"
f6 = "'" & FItemArray(5) & "'"
f7 = "'" & FItemArray(6) & "'"
sDate = Mid(f7, 2, 2) & "/" & Mid(f7, 5, 2) & "/" & Mid(f7, 8, 4)
f8 = CDate(sDate)
Debug.Print f8

If varCounter = 0 Then
'do nothing as the header record and and criteria layer has just been read
Else 'insert fields into database
db.Execute " INSERT INTO [tblSAPSales] " _
& "([Part Number],Region,[Sales Group],[Product Group],Revenue,[Cost Of Sales],[Sale Date]) VALUES " _
& "(" & f3 & ", " & f1 & ", " & f2 & ", " & f4 & ", " & f5 & ", " & f6 & ", " & f8 & ");"
End If

varCounter = varCounter + 1
...

Thanks

 

[willir]

Did it work, or are you looking for help? If you are still looking for help, what and where is the error?

A couple of short cuts here...

Set db = New Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & VB.App.Path & "\Data\Chart.mdb;"

    'delete current information
    db.Execute " DELETE FROM [tblSAPSales]"

    Open ImportDataFile For Input As 1 
    While Not EOF(1)
        Line Input #1, strTemp
        ImportString = ImportString & strTemp 
        ImportString = Replace(strTemp, "'", "''")         
        FItemArray = Split(ImportString, ",") 
             f1 = "'" & FItemArray(0) & "'"
             f2 = "'" & FItemArray(1) & "'"
             f3 = "'" & FItemArray(2) & "'"
             f4 = "'" & FItemArray(3) & "'"
             f5 = "'" & FItemArray(4) & "'"
             f6 = "'" & FItemArray(5) & "'"
             f7 = "'" & FItemArray(6) & "'"
[COLOR=blue]
             f8 = CDate(Mid(f7, 2, 2) & "/" _
             & Mid(f7, 5, 2) & "/" & Mid(f7, 8, 4))
[/color]
             Debug.Print f8
[COLOR=blue]
             If varCounter > 0 Then[/color]
                'insert fields into database
                db.Execute " INSERT INTO [tblSAPSales] " _
                    & "([Part Number],Region,[Sales Group],[Product Group],Revenue,[Cost Of Sales],[Sale Date]) VALUES " _
                    & "(" & f3 & ", " & f1 & ", " & f2 & ", " & f4 & ", " & f5 & ", " & f6 & ", " & f8 & ");"
             End If

             varCounter = varCounter + 1

 

[toon10]

The debug.print f8 line shows the correct date e.g. "12/05/2005" after the conversion and the database table field is set to a date/time type using a short date format e.g. "dd/mm/yyyy" but it's still giving me garbage.

 

[toon10]

Aha, got it.

I needed to put the old "#" on the insert statement!

db.Execute " INSERT INTO [tblSAPSales] blah blah VALUES blah blah & "#" & f8 & "#" & ");"

Thanks for the help guys
Andrew

 

[willir]

Correct - encapsulate dates with #, and strings with

 

[PHV]

Hint: use non ambiguous date format:
f8 = "#" & Mid(f7, 8, 4) & "-" & Mid(f7, 5, 2) & "-" _
& Mid(f7, 2, 2) & "#"
db.Execute "INSERT INTO tblSAPSales (blah blah) VALUES (blah blah, " & f8 & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244