Access Database Insert Error

T

tate

Well-known member
Joined
Nov 10, 2003
Messages
116
Ok, Im really tired banging my head on the monitor over the correct syntax for this one. Im trying to add a record to an Access database and I receive the following error;

Syntax error (missing operator) in query expression 8:00:00 AM.

Here is the associated SQL statement Im using;

datEventDate = CDate(txtDate.Text)
datStart = CDate(txtStart.Text)
datEnd = CDate(txtEnd.Text)
datToday = DateTime.Today
Dim strCmd As String
strCmd = "INSERT INTO tblEvent (AdminName, AdminDate, Event, EventDate, StartTime, EndTime, Location, Childcare, AddDetail) VALUES (" & strAdmin & ", " & datToday & ", " & txtEvent.Text & ", " & datEventDate & ", " & datStart & ", " & datEnd & ", " & lstLocation.SelectedValue & ", " & strChildcare & ", " & txtComments.Text & ")"

Here is the data Im attempting to add;

AdminName = determined in application
AdminDate = determined in application
Event = Test event
EventDate = 04/09/2005
StartTime = 08:00
EndTime = 21:00
Location = Other
Childcare = False
AddDetail = Its my birthday


Thanks for taking the time to help.
Tate
 
In the Access database set the "EventDate & StartTime & EndTime" as a date / time and then set the format to either short date and short time.

Hope this helps
 
The format within the Access table for the date fields is mm/dd/yyyy and the time fields were set to ShortTime. Yet, I receive the error?
 
Just on the off chance try changing the format of those fields to text.

I have taken date formats off a form before but I used a datetimepicker not a text box.

Just had a quick thought. With the Access DB format being set to Short Date. When you convert the text box into a date using the CDate function, what format does it leave the date as? I.E. Short or Long.
 
Last edited by a moderator:
The CDate(txtDate.Text) conversion will result in 4/9/2005. Based on the error it looks like something is wrong with the two time values (datStart and datEnd) not the date field.

Thanks again for your suggestions.
 
If your date fields are setup as date/time fields then you need to use the date delimiter (#) in front of and behind your date/time fields in your sql statement. I have run into that problem before and that has fixed it.
 
I have tried the following with no luck;

VALUES (" & strAdmin & ", #" & datToday & "#, " & txtEvent.Text & ", #" & datEventDate & "#, #" & datStart & "#, #" & datEnd & "#, " & lstLocation.SelectedValue & ", " & strChildcare & ", " & txtComments.Text & ")"
 
After way too many hours I have been able to get the code to work. The problem was with a combination of things;

1. Dropdown list value needed to use Test property instead of Value
2. Checkbox field (boolean field) didnt need quotes surrounding it in the SQL
3. CDate conversion of time fields didnt work for Access ShortTime, so forced the value


CODE:

Dim strChildcare As String
If chkYes.Checked Then
strChildcare = "True"
ElseIf chkNo.Checked Then
strChildcare = "False"
Else
strChildcare = "False"
End If

Dim datToday As DateTime
Dim datEventDate As DateTime
Dim strStart As String
Dim strEnd As String
Dim strListItem As String
strListItem = lstLocation.SelectedItem.Text
datToday = DateTime.Today
datEventDate = CDate(txtDate.Text)
strStart = txtStartHr.Text + ":" + txtStartMin.Text
strEnd = txtEndHr.Text + ":" + txtEndMin.Text
Dim strCmd As String
strCmd = "INSERT INTO tblEvent (AdminName, AdminDate, Event, EventDate, StartTime, EndTime, Location, Childcare, AddDetail) VALUES (" & strAdmin & ", " & DateTime.Today & ", " & txtEvent.Text & ", #" & txtDate.Text & "#, #" & strStart & "#, #" & strEnd & "#, " & strListItem & ", " & strChildcare & ", " & txtComments.Text & ")"
 
Back
Top