Solved

Inserting date

Posted on 2000-04-25
22
190 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now