SQL insert into dates

[NT100]

Hi,

I've a question of insert into a table with the following codes.

CurrentDb.Execute "Insert into tblTNewAppt (TRef, AppointedBy, RankCode, JobPosition, Company, Honorarium, Allowance, FCF_No, Budget, DaysNotAvailable, ApptStart_Dt, ApptEnd_Dt, Remarks) " _
& "VALUES (' " & lngID & "', '" & fraApptedBy & "', '" & fraHSR & "', '" & txtJobPosition & "', '" & txtCompany & "', '" & sHon & "', '" & sAll & "', '" & txtFCF_No & "', '" & txtBudget & "', '" & txtDaysNotAvailable & "', #" & txtApptStart_Dt & "# , '" & "#" & txtApptEnd_Dt & "#" & "', '" & txtRemarks & "');"

The execution is fine except that no value is inserted into "ApptEnd_Dt" of tblTNewAppt with value from txtApptEnd_Dt.

Any suggestions

Thanks in advance.

 

[Ranman256]

you have mixed delimiters: # and '....
use:

& txtDaysNotAvailable & "',#" & txtApptStart_Dt & "#,#" & txtApptEnd_Dt & "#,'" & txtRemarks & "');"

if you use queries instead of sql, you wont get syntax errors.

 

[arnelgp]

here is the complete revision:

Dim strSQL As String
strSQL = _
	"Insert Into tblTNewAppt (" & _
		"TRef, AppointedBy, RankCode, " & _
		"JobPosition, Company, Honorarium, " & _
		"Allowance, FCF_No, Budget, DaysNotAvailable, " & _
		ApptStart_Dt, ApptEnd_Dt, Remarks) "

strSQL = strSQL & "SELECT " & _
		"p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13"

With CurrentDb.CreateQueryDef("", strSQL)
	.Parameters(0) = [lngID]
	.Parameters(1) = [fraApptedBy]
	.Parameters(2) = [fraHSR]
	.Parameters(3) = [txtJobPosition]
	.Parameters(4) = [txtCompany]
	.Parameters(5) = [sHon]
	.Parameters(6) = [sAll]
	.Parameters(7) = [txtFCF]
	.Parameters(8) = [txtBudget]
	.Parameters(9) = [txtDaysNotAvailable]
	.Parameters(10)= [txtApptStart_Dt]
	.Parameters(11)= [txtApptEnd_Dt]
	.Parameters(12)= [txtRemarks]

	.Execute
End With

 

[Pat Hartman]

NT100, you didn't include your country so I can't tell what date format you normally use. SQL REQUIRES string dates to be in m/d/y or y/m/d order. It will not recognize d/m/y which is your standard format in the UK and much of the world. Therefore, whenever you have to create a STRING date for an SQL statement as you are doing, you MUST format it.

CurrentDb.Execute "Insert into tblTNewAppt (TRef, AppointedBy, RankCode, JobPosition, Company, Honorarium, Allowance, FCF_No, Budget, DaysNotAvailable, ApptStart_Dt, ApptEnd_Dt, Remarks) " _
& "VALUES (' " & lngID & "', '" & Me.fraApptedBy & "', '" & Me.fraHSR & "', '" & Me.txtJobPosition & "', '" & Me.txtCompany & "', '" & sHon & "', '" & sAll & "', '" & Me.txtFCF_No & "', '" & Me.txtBudget & "', '" & txtDaysNotAvailable & "', #" & Format(Me.txtApptStart_Dt, "mm/dd/yyyy") & "# , #" & Format(Me.txtApptEnd_Dt, "mm/dd/yyyy") & "#" & "', '" & txtRemarks & "');"

I added "Me." to all the fields that I think are controls. I removed one extraneous single quote that would have caused a syntax error. You should also probably remove the single quote delimiters from several other fields for example, I would expect Me.txtBudget to be numeric and numeric fields should NOT be enclosed in quotes.

Delimiters =
text = single or double quotes
Dates = poind signs (#)
Numbers = nothing

 

[Mark_]

To add to the others, are you reviewing the SQL string prior to executing it?

For myself, if I were doing other than what arnelgp suggests I would put the SQL into a string first and the display it either in a MsgBox or with Debug.Print. This way you can tell what is causing your issue. If you wished to you could even put an unbound text field on the screen and put the contents of your SQL into it before executing. That would make copying it here for help rather easy.

 

[MarkK]

The method arnelgp is proposing solves the date format problem.
Mark

 

[NT100]

NT100, you didn't include your country so I can't tell what date format you normally use. SQL REQUIRES string dates to be in m/d/y or y/m/d order. It will not recognize d/m/y which is your standard format in the UK and much of the world. Therefore, whenever you have to create a STRING date for an SQL statement as you are doing, you MUST format it.

CurrentDb.Execute "Insert into tblTNewAppt (TRef, AppointedBy, RankCode, JobPosition, Company, Honorarium, Allowance, FCF_No, Budget, DaysNotAvailable, ApptStart_Dt, ApptEnd_Dt, Remarks) " _
& "VALUES (' " & lngID & "', '" & Me.fraApptedBy & "', '" & Me.fraHSR & "', '" & Me.txtJobPosition & "', '" & Me.txtCompany & "', '" & sHon & "', '" & sAll & "', '" & Me.txtFCF_No & "', '" & Me.txtBudget & "', '" & txtDaysNotAvailable & "', #" & Format(Me.txtApptStart_Dt, "mm/dd/yyyy") & "# , #" & Format(Me.txtApptEnd_Dt, "mm/dd/yyyy") & "#" & "', '" & txtRemarks & "');"

I added "Me." to all the fields that I think are controls. I removed one extraneous single quote that would have caused a syntax error. You should also probably remove the single quote delimiters from several other fields for example, I would expect Me.txtBudget to be numeric and numeric fields should NOT be enclosed in quotes.

Delimiters =
text = single or double quotes
Dates = poind signs (#)
Numbers = nothing

Thank you for the suggestion.

I made few modification for the script to make it work

CurrentDb.Execute "Insert into tblTNewAppt (TRef, AppointedBy, RankCode, JobPosition, Company, Honorarium, Allowance, FCF_No, Budget, DaysNotAvailable, ApptStart_Dt, ApptEnd_Dt, Remarks) " _
& "VALUES (' " & lngID & "', '" & Me.fraApptedBy & "', '" & Me.fraHSR & "', '" & Me.txtJobPosition & "', '" & Me.txtCompany & "', '" & sHon & "', '" & sAll & "', '" & Me.txtFCF_No & "', '" & Me.txtBudget & "', '" & txtDaysNotAvailable & "', #" & Me.txtApptStart_Dt & "# , #" & Me.txtApptEnd_Dt & "#,'" & txtRemarks & "');"

 

[NT100]

you have mixed delimiters: # and '....
use:

& txtDaysNotAvailable & "',#" & txtApptStart_Dt & "#,#" & txtApptEnd_Dt & "#,'" & txtRemarks & "');"

if you use queries instead of sql, you wont get syntax errors.

Thank you for the suggestions
It works. However, I don't understand why " ' " as hightlighted is present at the following partly script

& txtDaysNotAvailable & "',#" & txtApptStart_Dt & "#,#" & txtApptEnd_Dt & "#,'" & txtRemarks & "');"

 

[NT100]

here is the complete revision:

Dim strSQL As String
strSQL = _
	"Insert Into tblTNewAppt (" & _
		"TRef, AppointedBy, RankCode, " & _
		"JobPosition, Company, Honorarium, " & _
		"Allowance, FCF_No, Budget, DaysNotAvailable, " & _
		ApptStart_Dt, ApptEnd_Dt, Remarks) "

strSQL = strSQL & "SELECT " & _
		"p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13"

With CurrentDb.CreateQueryDef("", strSQL)
	.Parameters(0) = [lngID]
	.Parameters(1) = [fraApptedBy]
	.Parameters(2) = [fraHSR]
	.Parameters(3) = [txtJobPosition]
	.Parameters(4) = [txtCompany]
	.Parameters(5) = [sHon]
	.Parameters(6) = [sAll]
	.Parameters(7) = [txtFCF]
	.Parameters(8) = [txtBudget]
	.Parameters(9) = [txtDaysNotAvailable]
	.Parameters(10)= [txtApptStart_Dt]
	.Parameters(11)= [txtApptEnd_Dt]
	.Parameters(12)= [txtRemarks]

	.Execute
End With

This approach seems compact, I'll study it. Thank you.