Solved Problem with INSERT Date value

[tihmir]

Hi,
I am trying to INSERT date form fields form with that code:

CurrentDb.Execute "INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew ) " & _
                        "VALUES (" & Me.txtVersion & "", #" & Format(Me.txtVersionDate, "mm/dd/yyyy") & "#", '" & Me.txtWhatsNew & "')"

where the fields are: "Value" (Short Text), "VersionDate" (Date/Time), "WhatsNew" (Long Text)

 

[Ranman256]

Too many quotes, try:
CurrentDb.Execute "INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew ) VALUES (" & Me.txtVersion & ", #" & Format(Me.txtVersionDate,'mm/dd/yyyy') & "#, '" & Me.txtWhatsNew & "')"

 

[tihmir]

Too many quotes, try:
CurrentDb.Execute "INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew ) VALUES (" & Me.txtVersion & ", #" & Format(Me.txtVersionDate,'mm/dd/yyyy') & "#, '" & Me.txtWhatsNew & "')"

You are correct. Just replaced the single quote with double 'mm/dd/yyyy'
Thank you!

P.S. As a supplement to ask why the field "Value" ((Short Text)) save me the records the numbers with а comma (9,1) and not as I enter them with dot (9.10). How to format the field?

 

[CJ_London]

Possibly because Value is a reserved word and should not be used as a field name

if it is short text then you should have single quotes around the assignment

VALUES (‘“ & Me.txtVersion & "’, #” &

and if it is a number, perhaps the comma is your format for decimal numbers

 

[Josef P.]

P.S. As a supplement to ask why the field "Value" ((Short Text)) save me the records the numbers with а comma (9,1) and not as I enter them with dot (9.10). How to format the field?

Me.txtVersion is used as a numeric value.

VALUES (" & Me.txtVersion & ", ...

vs

VALUES ('" & Me.txtVersion & "', ...

 

[tihmir]

Me.txtVersion is use as numeric value.

VALUES (" & Me.txtVersion & ", ...

vs

VALUES ('" & Me.txtVersion & "', ...

Exactly! That was my mistake.
Тhank you guys so much!

 

[Josef P.]

[OT]
Tip: use a variable for the SQL string ... so you can check it.

dim InsertSql as String
InsertSql = "INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew ) ...." & _
                        "VALUES ...."
debug.Print InsertSql
CurrentDb.Execute InsertSql, dbfailonerror

Code should not only work, but also be easy to read =>

If you then outsource the conversion of the value to a SQL string to functions, the code becomes even nicer.

dim InsertSql as String
InsertSql = "INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew )" & _
            " VALUES (" & TextToSqlText(Me.txtVersion) & ", " & DateToSqlText(Me.txtVersionDate) & ", " & TextToSqlText(Me.txtWhatsNew) & ")"
debug.Print InsertSql
CurrentDb.Execute InsertSql, dbfailonerror

Note: TextToSqlText and DateToSqlText you have to create.

A further extension could look like this (StringFormat and StringFormatSQL functions can be found in Northwind 2):

InsertSql = StringFormat("INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew ) Values ({0}, {1}, {2})", _
                          TextToSqlText(Me.txtVersion), DateToSqlText(Me.txtVersionDate) , TextToSqlText(Me.txtWhatsNew)

or

InsertSql = StringFormatSQL("INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew ) Values ({0}, {1}, {2})", _
                            Me.txtVersion.value,  Me.txtVersionDate, Me.txtWhatsNew

The variant with a "real" parameter query should also not be forgotten (QueryDef.Execute).

 

[tihmir]

[OT]
Tip: use a variable for the SQL string ... so you can check it.

dim InsertSql as String
InsertSql = "INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew ) ...." & _
                        "VALUES ...."
debug.Print InsertSql
CurrentDb.Execute InsertSql, dbfailonerror

Code should not only work, but also be easy to read =>

If you then outsource the conversion of the value to a SQL string to functions, the code becomes even nicer.

dim InsertSql as String
InsertSql = "INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew )" & _
            " VALUES (" & TextToSqlText(Me.txtVersion) & ", " & DateToSqlText(Me.txtVersionDate) & ", " & TextToSqlText(Me.txtWhatsNew) & ")"
debug.Print InsertSql
CurrentDb.Execute InsertSql, dbfailonerror

Note: TextToSqlText and DateToSqlText you have to create.

A further extension could look like this (StringFormat and StringFormatSQL functions can be found in Northwind 2):

InsertSql = StringFormat("INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew ) Values ({0}, {1}, {2})", _
                          TextToSqlText(Me.txtVersion), DateToSqlText(Me.txtVersionDate) , TextToSqlText(Me.txtWhatsNew)

or

InsertSql = StringFormatSQL("INSERT INTO 01_tblLocalVariables ( Value, VersionDate, WhatsNew ) Values ({0}, {1}, {2})", _
                            Me.txtVersion.value,  Me.txtVersionDate, Me.txtWhatsNew

The variant with a "real" parameter query should also not be forgotten (QueryDef.Execute).

Thanks for the helpful tips, Josef P.!
I appreciate it!