SQL Insert Date Problem

[Guest]

I have two questions

1) I'm trying to execute the following SQL command

sql = "INSERT INTO Session (ClassID, SessionNum, Date, FID, FSNum, StartTime, EndTime, Topic) Values (ddlClass, txtSessionNum, txtDate, ddlFaculty, txtFSNum, txtStartTime, txtEndTime, txtTopic);

I'm getting a syntax error, which I think I'm safe to assume is because I'm not inserting the date correctly. I can't find anything in the help file that shows how to insert the date and times properly. Please provide the formatting

2) Does it matter how the date is stored in the tables if I am going to run a report from the dates in a different format then they are inserted into the tables? Like inserting the date as MM/DD/YYYY would work just fine for inputing, but in a report i will want it to say DAYOFWEEK, MM/DD/YYYY. Will I run into a problem with this

Thanks for any help

Kyle

 

[Marshall Barton]

I have two questions:

1) I'm trying to execute the following SQL command:

sql = "INSERT INTO Session (ClassID, SessionNum, Date, FID, FSNum, StartTime, EndTime, Topic) Values (ddlClass, txtSessionNum, txtDate, ddlFaculty, txtFSNum, txtStartTime, txtEndTime, txtTopic);"

I'm getting a syntax error, which I think I'm safe to assume is because I'm not inserting the date correctly. I can't find anything in the help file that shows how to insert the date and times properly. Please provide the formatting.

2) Does it matter how the date is stored in the tables if I am going to run a report from the dates in a different format then they are inserted into the tables? Like inserting the date as MM/DD/YYYY would work just fine for inputing, but in a report i will want it to say DAYOFWEEK, MM/DD/YYYY. Will I run into a problem with this?

The format that's used to display a date field's value has
nothing to do with how it's strored.

The issue is that the SQL environment does not include your
form's name space. This means that you have to place the
form values into the query, not just their names:

sql = "INSERT INTO Session (ClassID, SessionNum, Date, " _
& "FID, FSNum, StartTime, EndTime, Topic) " _
& "Values (" & ddlClass _
& ", " & txtSessionNum _
& ", " & Format(txtDate "\#m\/d\/yyyy\#") _
& ", " & ddlFaculty _
& ", " & txtFSNum _
& ", " & Format(txtStartTime, "\#h\:n\#")
& ", " & Format(txtEndTime, "\#h\:n\#")
& ", """ & txtTopic & """"
);"