Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Inserting date

Posted on 2000-04-25
22
Medium Priority
?
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:Brendt Hess
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:
Brendt Hess earned 200 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
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…
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…
Suggested Courses

722 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