enryo
asked on
Inserting date
How can i insert a variable to a date field?.
I get a data type mismatch error.
I get a data type mismatch error.
Use the format #mm/dd/yyyy# including the '#' symbols (and allowing for your local date format)
ASKER
I have done that but i get a syntax error.I'm trying to add the data with a SQL instruction "INSERT INTO (field1,fieldDate..) VALUES (value1,value2..)" ,in this case value2 is the one that i'm having trouble with.
Lets see your exact SQL statement.
M
M
Also,
Cloud_1, welcome to E-E, however a point of netequette. We don't post *answers* unless we're absolutely certain that our answer is *perfect*. The questioner can accept any comment as an answer to award the point and keeping the question open insures that more eyeballs will see it giving the questioner maximum input.
M
Cloud_1, welcome to E-E, however a point of netequette. We don't post *answers* unless we're absolutely certain that our answer is *perfect*. The questioner can accept any comment as an answer to award the point and keeping the question open insures that more eyeballs will see it giving the questioner maximum input.
M
Cloud_1 changed the proposed answer to a comment
"INSERT INTO (field1,fieldDate..) VALUES (value1,#" & value2 & "#)"
this would work for date
this would work for date
ASKER
I appreciate all your comments ,but I am still getting a syntax error.
As they said, can you show your exact SQL Statement?
Also, what DB are you using?
Also, what DB are you using?
ASKER
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
Im using a DBF DB.
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#02/12/20 00# );".
The date is in string format... not numeric.
('Smith','Charles','18684'
The date is in string format... not numeric.
Or...
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#" & 2 & "/" & 12 & "/" & 2000 & "# );".
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
Or...
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#" & 2 & "/" & 12 & "/" & 2000 & "# );".
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
ASKER
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
Im using a DBF DB.
ASKER
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
Im using a DBF DB.
You're still not formatting the date as a string... try either...
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#02/12/20 00# );"
....or...
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#" & 2 & "/" & 12 & "/" & 2000 & "# );"
8-)
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
....or...
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
8-)
ASKER
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
Im using a DBF DB.
ASKER
This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
Im using a DBF DB.
ASKER
Thanks,I have tried both but still getting the syntax error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How is the date field store... is it a a date or as another type, possibly?
Do the strings Smith, etc... require 'double' speechmarks (") around them?
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
(""""Smith"""",""""Charles """",""""1 8684"""",# 02/12/2000 #);"
Perhaps place single speech marks around the date...
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
('Smith','Charles','18684' ,'#02/12/2 000#');"
Perhaps a combination of both...
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT E) VALUES
(""""Smith"""",""""Charles """",""""1 8684""""," """#02/12/ 2000#"""") ;"
Do the strings Smith, etc... require 'double' speechmarks (") around them?
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
(""""Smith"""",""""Charles
Perhaps place single speech marks around the date...
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
('Smith','Charles','18684'
Perhaps a combination of both...
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DAT
(""""Smith"""",""""Charles
The # delimiting of dates is only applicable to Microsoft Access. I can't speak for DBF, but for SQL Server it is a "'" (single quote). Also, instead of trying to adjust to your system settings for date, just specify the date in an unambiguous manner, i.e specify it as dd-mmm-yyyy hh:mm:ss.
In short then,
INSERT INTO SRMM (LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684' ,'12-Jan-2 000')
In short then,
INSERT INTO SRMM (LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684'
The # delimiting of dates is only applicable to Microsoft Access. I can't speak for DBF, but for SQL Server it is a "'" (single quote). Also, instead of trying to adjust to your system settings for date, just specify the date in an unambiguous manner, i.e specify it as dd-mmm-yyyy hh:mm:ss.
In short then,
INSERT INTO SRMM (LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684' ,'12-Jan-2 000')
In short then,
INSERT INTO SRMM (LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684'