Link to home
Start Free TrialLog in
Avatar of enryo
enryo

asked on

Inserting date

How can i insert a variable to a date field?.
I get a data type mismatch error.
Avatar of Cloud_1
Cloud_1

Use the format #mm/dd/yyyy# including the '#' symbols (and allowing for your local date format)
Avatar of enryo

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
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 changed the proposed answer to a comment
"INSERT INTO (field1,fieldDate..) VALUES (value1,#" & value2 & "#)"

this would work for date
Avatar of enryo

ASKER

I appreciate all your comments ,but I am still getting a syntax error.
Avatar of Brendt Hess
As they said, can you show your exact SQL Statement?

Also, what DB are you using?
Avatar of enryo

ASKER

This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#02/12/2000# );".

The date is in string format... not numeric.


Or...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 2 & "/" & 12 & "/" & 2000 & "# );".

Or...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 2 & "/" & 12 & "/" & 2000 & "# );".

Avatar of enryo

ASKER

This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

Avatar of enryo

ASKER

This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

You're still not formatting the date as a string... try either...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#02/12/2000# );"

....or...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 2 & "/" & 12 & "/" & 2000 & "# );"

8-)

Avatar of enryo

ASKER

This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

Avatar of enryo

ASKER

This is my SQL :
"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#" & 02 / 12 / 2000 & "# );".
Im using a DBF DB.

Avatar of enryo

ASKER

Thanks,I have tried both but still getting the syntax error.
ASKER CERTIFIED SOLUTION
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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,DATE) VALUES
(""""Smith"""",""""Charles"""",""""18684"""",#02/12/2000#);"

Perhaps place single speech marks around the date...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684','#02/12/2000#');"

Perhaps a combination of both...

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
(""""Smith"""",""""Charles"""",""""18684"""",""""#02/12/2000#"""");"
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-2000')



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-2000')