Hi,

i'm trying to insert a new record in Access 2000 via Excel 2000.


In Excel the date is formatted as "dd/mm/yy" (I'm in Australia).
In Access the table has no format but all of the dates appear as 14/10/2008.

As I step through my code

Sub AddARow()
Dim DB As DAO.DataBase
Set DB = DBEngine.OpenDatabase("G:\00_CEN\Oth\Inventory Planning
Reports\POL\POL.MDB")

Application.Goto reference:="RC2"
The_CURR_DATE = ActiveCell.Value
The_sSQL = "INSERT INTO tbl_Plan_Data (CURR_DATE) VALUES (The_CURR_DATE);
DB.Execute The_sSQL
DB.Close
Set DB = Nothing
End Sub

it inserts a record but is shown as 12:00:31 AM.

How do I get it to show as 14/10/2008??

--
Andrew
151008

Dates for Access must be formatted in the American format, rather than in the
format that you and I (from the UK) would both prefer them to be in. Try:-

The_sSQL = "INSERT INTO tbl_Plan_Data (CURR_DATE) VALUES (#" &
format(The_CURR_DATE,"m/d/yy" & "#);

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Andrew" wrote:

Hi,

i'm trying to insert a new record in Access 2000 via Excel 2000.


In Excel the date is formatted as "dd/mm/yy" (I'm in Australia).
In Access the table has no format but all of the dates appear as 14/10/2008.

As I step through my code

Sub AddARow()
Dim DB As DAO.DataBase
Set DB = DBEngine.OpenDatabase("G:\00_CEN\Oth\Inventory Planning
Reports\POL\POL.MDB")

Application.Goto reference:="RC2"
The_CURR_DATE = ActiveCell.Value
The_sSQL = "INSERT INTO tbl_Plan_Data (CURR_DATE) VALUES (The_CURR_DATE);
DB.Execute The_sSQL
DB.Close
Set DB = Nothing
End Sub

it inserts a record but is shown as 12:00:31 AM.

How do I get it to show as 14/10/2008??

--
Andrew
151008

Sorry, missed a quote off. I'm sure that you would have realised, but for
correctness:-

The_sSQL = "INSERT INTO tbl_Plan_Data (CURR_DATE) VALUES (#" &
Format(The_CURR_DATE,"m/d/yy" & "#);"

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Alan Moseley" wrote:

Dates for Access must be formatted in the American format, rather than in the
format that you and I (from the UK) would both prefer them to be in. Try:-

The_sSQL = "INSERT INTO tbl_Plan_Data (CURR_DATE) VALUES (#" &
format(The_CURR_DATE,"m/d/yy" & "#);

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Andrew" wrote:

Hi,

i'm trying to insert a new record in Access 2000 via Excel 2000.


In Excel the date is formatted as "dd/mm/yy" (I'm in Australia).
In Access the table has no format but all of the dates appear as 14/10/2008.

As I step through my code

Sub AddARow()
Dim DB As DAO.DataBase
Set DB = DBEngine.OpenDatabase("G:\00_CEN\Oth\Inventory Planning
Reports\POL\POL.MDB")

Application.Goto reference:="RC2"
The_CURR_DATE = ActiveCell.Value
The_sSQL = "INSERT INTO tbl_Plan_Data (CURR_DATE) VALUES (The_CURR_DATE);
DB.Execute The_sSQL
DB.Close
Set DB = Nothing
End Sub

it inserts a record but is shown as 12:00:31 AM.

How do I get it to show as 14/10/2008??

--
Andrew
151008