Solved

Inserting date

Posted on 2000-04-25
22
191 Views
Last Modified: 2010-05-02
How can i insert a variable to a date field?.
I get a data type mismatch error.
0
Comment
Question by:enryo
  • 8
  • 7
  • 2
  • +3
22 Comments
 

Expert Comment

by:Cloud_1
ID: 2749049
Use the format #mm/dd/yyyy# including the '#' symbols (and allowing for your local date format)
0
 

Author Comment

by:enryo
ID: 2749080
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.
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2749088
Lets see your exact SQL statement.

M
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 12

Expert Comment

by:mark2150
ID: 2749098
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
0
 

Expert Comment

by:Cloud_1
ID: 2749180
Cloud_1 changed the proposed answer to a comment
0
 
LVL 6

Expert Comment

by:Marine
ID: 2749231
"INSERT INTO (field1,fieldDate..) VALUES (value1,#" & value2 & "#)"

this would work for date
0
 

Author Comment

by:enryo
ID: 2749335
I appreciate all your comments ,but I am still getting a syntax error.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 2749341
As they said, can you show your exact SQL Statement?

Also, what DB are you using?
0
 

Author Comment

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

0
 

Expert Comment

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

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


0
 

Expert Comment

by:Cloud_1
ID: 2749395
Or...

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

0
 

Expert Comment

by:Cloud_1
ID: 2749400
Or...

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

0
 

Author Comment

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

0
 

Author Comment

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

0
 

Expert Comment

by:Cloud_1
ID: 2749457
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-)

0
 

Author Comment

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

0
 

Author Comment

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

0
 

Author Comment

by:enryo
ID: 2749482
Thanks,I have tried both but still getting the syntax error.
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 50 total points
ID: 2749528
The problem is that, with your query as formatted below:

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

What actually is seen by the SQL Parser is:

"INSERT INTO SRMM(LNAME,FNAME,EENUM,DATE) VALUES
('Smith','Charles','18684',#8.333333e05# );".

This is because the section of code:

02 / 12 / 2000

Is evaluated as a mathematical expression.

What you need to do is make that a string of text:

"02/12/2000"

So the full code line would be (as you have it):

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

No spaces, date inside quotes.

It is possible that the DBase format does not recognize the pound sign for dates.  In this case, you need to check the date identifier for xBase.  A quick review of some info at msdn.microsoft.com indicates that Foxpro, at least, uses { and } to delimit dates.  Try:

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

Expert Comment

by:Cloud_1
ID: 2749536
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#"""");"
0
 
LVL 8

Expert Comment

by:stefanx
ID: 2749577
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')



0
 
LVL 8

Expert Comment

by:stefanx
ID: 2749583
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')



0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question