Date Format (1 Viewer)

[Y2Neil]

Hi folks,

I have an Access 97 database that is populated from an excel spreadsheet.

The excel macro inserts the data into the database but access changes the date format.

The field in Access has a format of dd/mm/yyyy and my regional settings are set to UK.

The excel macro exports the date as dd/mm/yyyy (in my current case 03/08/2012) but access is storing it as mm/dd/yyyy (08/03/2012).

When excel re-imports the data later, the date is in the wrong format.

Is there any way I can get this date stored correctly or do I have to format the date in excel?

Many thanks,

 

[Trevor G]

Welcome to the Forum,

What is the date format in the Excel spreadsheet that should be the place to start looking.

 

[Y2Neil]

Hello,

The date format is dd/mm/yyyy. In the VBA there is a check to make sure the field is not null, if it is, the variable is set to '01/01/1900'.

Before running the script to insert the data into the Access database, I put in a messagebox to make extra sure what the value was it was inserting and the value was 03/08/2012 yet as soon as the insert script has run, Access has the field listed as 08/03/2012.

 

[Trevor G]

Can you copy the excel macro into your thread with some sample data in an excel spreadsheet then I can take a look.

 

[Y2Neil]

Sub UpdateWOTable()
'Open DB connection
    Dim cnx As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strsql, SO, ItemGrp As String
    Dim date_print As Date
    Dim date_finish As Date
    Dim date_proof As Date
    Dim nb_poses, qty_proof, stock_proof, stock_pvc, qty_pvc, item_pvc As Integer
    
    Set cnx = New ADODB.Connection
    cnx.Provider = "Microsoft.Jet.Oledb.4.0"
    'cnx.ConnectionString = "S:\Data Development\Local Tools\Scheduling\Databases\artwork offline queries.mdb"
    cnx.ConnectionString = "S:\Data Development\Local Tools\Scheduling\Databases\PrintDB.mdb"
    cnx.Open
     
'Update rows in Proofs Table with data from Proof worksheet
Sheets("Data").Select
Range("A1").Select
Dim LastRow As Integer
Range("A1").End(xlDown).Select
LastRow = ActiveCell.Row
Range("A1").Select
Range("A1").End(xlToRight).Select
ActiveCell.Offset(1, 1).Select
Dim i As Integer
i = ActiveCell.Column
'ActiveCell.Formula = "=IF(COUNTIF(AccessDB!A:A,A2)=0,""New"",IF(COUNTIF(AccessDB!A:A,A2)=1,""Update"",""""))"
'Cells(ActiveCell.Row, i).Select
'Selection.AutoFill Destination:=Range("AJ2:AJ" & LastRow), Type:=xlFillValues
Range("A2").Select
'Delete rows in Works Order Table
     strsql = "DELETE * from tbl_WO"
     rst.Open strsql, cnx
Do Until ActiveCell.Value = ""
If IsNull(ActiveCell.Offset(0, 9).Value) = True Then
    date_print = DateValue("01/01/1900")
Else
    date_print = ActiveCell.Offset(0, 9).Value
End If
            
If IsNull(ActiveCell.Offset(0, 16).Value) = True Then
    date_finish = DateValue("01/01/1900")
Else
    date_finish = ActiveCell.Offset(0, 16).Value
End If
If IsNull(ActiveCell.Offset(0, 18).Value) = True Then
    date_proof = "12/01/1900"
Else
    date_proof = ActiveCell.Offset(0, 18).Value
End If
If ActiveCell.Offset(0, 14).Value = "" Then
    nb_poses = 0
Else
    nb_poses = ActiveCell.Offset(0, 14).Value
End If
If ActiveCell.Offset(0, 11).Value = "" Then
    item_pvc = 0
Else
    item_pvc = ActiveCell.Offset(0, 14).Value
End If
If ActiveCell.Offset(0, 13).Value = "" Then
    qty_pvc = 0
Else
    qty_pvc = ActiveCell.Offset(0, 13).Value
End If
If ActiveCell.Offset(0, 15).Value = "" Then
    stock_pvc = 0
Else
    stock_pvc = ActiveCell.Offset(0, 15).Value
End If
If ActiveCell.Offset(0, 17).Value = "" Then
    stock_proof = 0
Else
    stock_proof = ActiveCell.Offset(0, 18).Value
End If
If ActiveCell.Offset(0, 19).Value = "" Then
    qty_proof = 0
Else
    qty_proof = ActiveCell.Offset(0, 19).Value
End If
 
    'Insert Record into DB
  strsql = "INSERT INTO tbl_WO (WO, Item, SN, Batch, Proof, Prod_Name, Sales, Item_group, Qty_sched, Date_print, Status, Item_PVC, PVC, Qty_PVC, Nb_poses, Stock_PVC, Date_finish, Stock_proof, Date_proof, Qty_conso_proof, ProdNotes, Unite)" & _
        " VALUES ('" & ActiveCell.Value & "'," & ActiveCell.Offset(0, 1).Value & ", '" & ActiveCell.Offset(0, 2).Value & "','" & ActiveCell.Offset(0, 3).Value & "'" & _
        "," & ActiveCell.Offset(0, 4).Value & ",'" & ActiveCell.Offset(0, 5).Value & "','" & ActiveCell.Offset(0, 6).Value & "','" & ActiveCell.Offset(0, 7).Value & "'" & _
        "," & ActiveCell.Offset(0, 8).Value & ",#" & date_print & "#,'" & ActiveCell.Offset(0, 10).Value & "'," & item_pvc & "" & _
        ",'" & ActiveCell.Offset(0, 12).Value & "'," & qty_pvc & "," & nb_poses & "," & stock_pvc & "" & _
        ",#" & date_finish & "#," & stock_proof & ", #" & date_proof & "#," & qty_proof & ",'" & ActiveCell.Offset(0, 20).Value & "','" & ActiveCell.Offset(0, 21).Value & "')"
rst.Open strsql, cnx
    ActiveCell.Offset(1, 0).Select
    
Loop
'Close Connections
Set rst = Nothing: Set cnx = Nothing
End Sub

Sorry if it looks a little sloppy.

I have however come up with a temporary fix. When I'm importing the data back into excel, I have put a format tag around the code:

'Dlv Date
Cells(i, 10).Value = Format(rst(33).Value, "dd/mm/yyyy")

This appears to have fixed the issue for now, although adds more confusion to my mind. As if I can export the data in dd/mm/yyyy and it appears correctly in Excel, why when I wrote an Access query, did it swap the month and the day around?

Curious.

 

[Trevor G]

I am aware that Excel VBA doesn't like using Date as a Dim Object so perhaps if you change this to Long that should work for you. So change to these

Dim date_print As Long
Dim date_finish As Long
Dim date_proof As Long

 

[Y2Neil]

That's worked!

Thank you so much for you help! Much appreciated.