Access date information to SQL Server

[boycie]

Hi,
I have an Access form that saves some simple data to a SQL Server table. Everything except the date field data is saved. I get the following:
1900-01-01 00:00:00.000 in the SQL date field.

Here is my code;

Dim dteEntry As Date
dteEntry = Date

sql = "Insert Into tblFranchiseDiary (Franchise,EntryType,Staff,EntryDate) Values ('" & cmbFranchise & "','" & cmbType & "','" & cmbUser & "', " & dteEntry & " )"

Debug.Print sql

Set rs = cn.Execute(sql)

debug output is as follows;

Insert Into tblFranchiseDiary (Franchise,EntryType,Staff,EntryDate) Values ('Arkham','Ticket','Joe Bloggs', 17/10/2012 )

What am I doing wrong?

thanks

Ian

 

[pr2-eugin]

Hello Ian,

Dates should be surrounded by hash symbols.. so try enclosing them in hash, also I think that you have Date and Time in the server, so you might as well make sure you add the time value.. something like..

....,'" & cmbUser & "', #" & Format(dteEntry,"yyyy-mm-dd " & TimeValue(Now)) & "# )"

 

[boycie]

Paul, thanks for your reply and it certainly makes sense what you say.
I tried your code but got an error. I then realised there is probably a ) missing after format of date and so amended as follows;

sql = "Insert Into tblFranchiseDiary (Franchise,EntryType,Staff,EntryDate) Values ('" & cmbFranchise & "','" & cmbType & "','" & cmbUser & "', #" & Format(dteEntry, "yyyy-mm-dd") & "# )"

This may be incorrect. The error I now have is;

...incorrect syntax near '#'

Ian

 

[boycie]

sorry, post should have said;

sql = "Insert Into tblFranchiseDiary (Franchise,EntryType,Staff,EntryDate) Values ('" & cmbFranchise & "','" & cmbType & "','" & cmbUser & "', #" & Format(dteEntry, "yyyy-mm-dd") & TimeValue(Now) & "# )"

error is incorrect syntax near ':'

 

[pr2-eugin]

Hello Ian, I did edit the code I gave earlier.. Could you please check it again and try it?

....,'" & cmbUser & "', #" & Format(dteEntry,"yyyy-mm-dd " & TimeValue(Now)) & "# )"

 

[boycie]

Paul,

I get; incorrect syntax near '14'. The debug print is;

Insert Into tblFranchiseDiary (Franchise,EntryType,Staff,EntryDate) Values ('Edinburgh','Ticket','Ben Bloggs, #2012-10-17 14:29:00# )

thanks

Ian

 

[pr2-eugin]

Okay what is the Data type of the Field in the table? If it is something like Short date, then remove the TimeValue part and use just format.. Like..

....,'" & cmbUser & "', #" & Format(dteEntry,"yyyy-mm-dd ") & "# )"

 

[boycie]

the field is datetime. My original code was inserting;

1900-01-01 00:00:00.000 in the SQL date field

 

[pr2-eugin]

So what is the code in Post #7 inserting?

 

[mdlueck]

sql = "Insert Into tblFranchiseDiary (Franchise,EntryType,Staff,EntryDate) Values ('" & cmbFranchise & "','" & cmbType & "','" & cmbUser & "', #" & Format(dteEntry, "yyyy-mm-dd") & TimeValue(Now) & "# )"

You could also issue a Pass-Through query, and use SQL constant CURRENT_TIMESTAMP as in...

UPDATE [dbo].[projects]
SET [authid] = @authid,
    [B][logtimestamp] = CURRENT_TIMESTAMP,[/B]
    [title] = @title,
    [budget] = @budget,
    [rptactiveflg] = @rptactiveflg,
    [rpttitle] = @rpttitle
WHERE [id] = @id
AND [logtimestamp] = @logtimestamp;

and rely on the server time rather than trust each workstations own clock.

 

[boycie]

incorrect near syntax '#'.

 

[boycie]

also tried putting an actual date in instead of a variable;

sql = "Insert Into tblFranchiseDiary (Franchise,EntryType,Staff,EntryDate) Values ('Edinburgh','Ticket','Ben Bloggs', #2012-10-17# )"

error is still; incorrect synatx near '#'

 

[boycie]

if I take away the # and use following string,;

sql = "Insert Into tblFranchiseDiary (Franchise,EntryType,Staff,EntryDate) Values ('Edinburgh','Ticket','Ben Bloggs', '2012-10-17' )"

I get this entry in the table;

2012-10-17 00:00:00.000

 

[boycie]

Paul,thanks very much your help. I will keep trying different syntax and keep you posted to how I get on.

also thanks to mdlueck

 

[boycie]

Hi, just to let you know I resolved the issue very simply by assigning getdate() as default in the datetime field

thanks again for help

 

[pr2-eugin]

That's good to hear.. Sorry I was not of great help.. Good luck..