SQL insert into dates (1 Viewer)

N

NT100

Registered User.
Local time
Today, 10:31
Joined
Jul 29, 2017
Messages
148
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

Ranman256

Well-known member
Local time
Yesterday, 22:31
Joined
Apr 9, 2015
Messages
4,339
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:31
Joined
May 7, 2009
Messages
19,229
here is the complete revision:

Code: 
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
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:31
Joined
Feb 19, 2002
Messages
43,233
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
 
M

Mark_

Longboard on the internet
Local time
Yesterday, 19:31
Joined
Sep 12, 2017
Messages
2,111
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

MarkK

bit cruncher
Local time
Yesterday, 19:31
Joined
Mar 17, 2004
Messages
8,180
The method arnelgp is proposing solves the date format problem.
Mark
 
N

NT100

Registered User.
Local time
Today, 10:31
Joined
Jul 29, 2017
Messages
148
Pat Hartman said:
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
Click to expand...

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 & "');"
 
N

NT100

Registered User.
Local time
Today, 10:31
Joined
Jul 29, 2017
Messages
148
Ranman256 said:
you have mixed delimiters: # and '....
use:

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

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

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 & "');"
 
N

NT100

Registered User.
Local time
Today, 10:31
Joined
Jul 29, 2017
Messages
148
arnelgp said:
here is the complete revision:

Code: 
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
Click to expand...

This approach seems compact, I'll study it. Thank you.
 
You must log in or register to reply here.

Similar threads

N
execute sql with textbox for date
Replies
3
Views
413
Micron
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom