Date value

[access7]

Hello

Can anyone tell me where I am going wrong with the date part of this

lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_ContactDetails ( CompanyRef, Forename, Surname, CreatedBy, CreatedWhen, MainContact ) "
lsSQL = lsSQL & " VALUES ( " & ICompanyRef & ", '" & lsForenames & "', '" & lsSurname & "', '" & SUser & "', 'Date()', " & False & ")"

It is returning the following:
INSERT INTO Tbl_ContactDetails ( CompanyRef, Forename, Surname, CreatedBy, CreatedWhen, MainContact ) VALUES ( 0, '', '', '', #Date()#, False)

Dont worry about the "" blanks (there is another step to the code) - I'm just wondering how to get the date to show as the current date??

Thanks

 

[Kiwiman]

Howzit

Try this

lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_ContactDetails ( CompanyRef, Forename, Surname, CreatedBy, CreatedWhen, MainContact ) "
lsSQL = lsSQL & " VALUES ( " & ICompanyRef & ", '" & lsForenames & "', '" & lsSurname & "', '" & SUser & ", Date(), 0)"

 

[access7]

Hello

thanks, but that gives me;
INSERT INTO Tbl_ContactDetails ( CompanyRef, Forename, Surname, CreatedBy, CreatedWhen, MainContact ) VALUES ( 0, '', '', '', Date(),0)

Am I wrong to expect the actual date to appear in the immediate window when I step through the code e.g. 17.02.2012... Would this (Date()) put the correct date in my table or not?

 

[Kiwiman]

Howzit

Yes it should. In testing be sure to take regular backups - especially if you are unsure of anything.

 

[ScottEnsink]

I think you need to let the VBA put in the actual date value, that way it would show up in the immediate window.

String should be something like:

lsSQL = lsSQL & " VALUES ( " & ICompanyRef & ", '" & lsForenames & "', '" & lsSurname & "', '" & SUser & "', " & Date() & ", " & False & ")"