Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Date via SQL Query
My underlying database is Excel and I am writing all DB relate
operations using ADO in Visual Basic Editor of Excel (VBA). I am usin Excel Driver. The data is stored directly into Excel columns a mentioned in the insert query. But, when I enter the date value in a column, it attaches single-quote(') before the value. I have applied date-formatting to th respective column as (mm/dd/yyyy), but still it doesn't work. I want t programmatically enter the date value without any single-quote i.e th value should be treated as date datatype. Because of this single-quote(') to the date value which the date-valu in the cell to be considered as TEXT and hence whenever I want to fetc records between date-range, the SELECT query does not pick-up prope records. [Since the end-user of my application will not open the files and mak manual changes, i want the proper date entry to be made without an single quote...] Sample Query: INSERT INTO [mms_instrDR_dtl$](strInstrSeqNbr, dtDR dtCreated,strCreatedBy VALUES('CLDEL01040400002',#7/21/2004#,#7/21/2004#,'ajit') Please help me !!! Trying from last 2 days. -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Date via SQL Query
Have you tried using a string in the SQL Insert statement?
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ajitrajput " wrote in message ... My underlying database is Excel and I am writing all DB related operations using ADO in Visual Basic Editor of Excel (VBA). I am using Excel Driver. The data is stored directly into Excel columns as mentioned in the insert query. But, when I enter the date value in a column, it attaches a single-quote(') before the value. I have applied date-formatting to the respective column as (mm/dd/yyyy), but still it doesn't work. I want to programmatically enter the date value without any single-quote i.e the value should be treated as date datatype. Because of this single-quote(') to the date value which the date-value in the cell to be considered as TEXT and hence whenever I want to fetch records between date-range, the SELECT query does not pick-up proper records. [Since the end-user of my application will not open the files and make manual changes, i want the proper date entry to be made without any single quote...] Sample Query: INSERT INTO [mms_instrDR_dtl$](strInstrSeqNbr, dtDR, dtCreated,strCreatedBy) VALUES('CLDEL01040400002',#7/21/2004#,#7/21/2004#,'ajit') Please help me !!! Trying from last 2 days.. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Date via SQL Query
ajitrajput wrote ...
My underlying database is Excel and I am writing all DB related operations using ADO in Visual Basic Editor of Excel (VBA). I am using Excel Driver. The data is stored directly into Excel columns as mentioned in the insert query. But, when I enter the date value in a column, it attaches a single-quote(') before the value. I have applied date-formatting to the respective column as (mm/dd/yyyy), but still it doesn't work. I want to programmatically enter the date value without any single-quote i.e the value should be treated as date datatype. Because of this single-quote(') to the date value which the date-value in the cell to be considered as TEXT and hence whenever I want to fetch records between date-range, the SELECT query does not pick-up proper records. [Since the end-user of my application will not open the files and make manual changes, i want the proper date entry to be made without any single quote...] Sample Query: INSERT INTO [mms_instrDR_dtl$](strInstrSeqNbr, dtDR, dtCreated,strCreatedBy) VALUES('CLDEL01040400002',#7/21/2004#,#7/21/2004#,'ajit') You need to get an appreciation of how Jet determines the data type of an Excel column i.e. it is not just determined by formatting but also by existing values in the column and machine-specific settings. This may help: http://www.dicks-blog.com/excel/2004...al_data_m.html Once Jet has determined that the majority type for the column is 'Date' (adDBTimeStamp) then it should be able to coerce an unambiguous date string e.g. INSERT INTO [mms_instrDR_dtl$] (strInstrSeqNbr, dtDR, dtCreated,strCreatedBy) VALUES ('CLDEL01040400002', '21 JUL 2004', '21 JUL 2004', 'ajit') ; Having said all that, I can't seem to get it to work with the ODBC Excel driver via the OLE DB provider for ODBC; I get an error, 'Operation must use an updatable query'. With ADO, I usually use the Microsoft OLE DB Provider for Jet and the insert works fine with this. Out of interest, why you are using the ODBC dirver and not the OLE DB Provider for Jet? Jamie. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Date via SQL Query
Jamie Collins wrote:
*ajitrajput wrote ... My underlying database is Excel and I am writing all DB related operations using ADO in Visual Basic Editor of Excel (VBA). I a using Excel Driver. The data is stored directly into Excel columns as mentioned in the insert query. But, when I enter the date value in a column, it attaches a single-quote(') before the value. I have applied date-formatting t the respective column as (mm/dd/yyyy), but still it doesn't work. want to programmatically enter the date value without any single-quote i. the value should be treated as date datatype. Because of this single-quote(') to the date value which th date-value in the cell to be considered as TEXT and hence whenever I want t fetch records between date-range, the SELECT query does not pick-u proper records. [Since the end-user of my application will not open the files an make manual changes, i want the proper date entry to be made withou any single quote...] Sample Query: INSERT INTO [mms_instrDR_dtl$](strInstrSeqNbr, dtDR, dtCreated,strCreatedBy) VALUES('CLDEL01040400002',#7/21/2004#,#7/21/2004#,'ajit') You need to get an appreciation of how Jet determines the data typ of an Excel column i.e. it is not just determined by formatting bu also by existing values in the column and machine-specific settings. This may help: http://www.dicks-blog.com/excel/2004...al_data_m.html Once Jet has determined that the majority type for the column is 'Date' (adDBTimeStamp) then it should be able to coerce a unambiguous date string e.g. INSERT INTO [mms_instrDR_dtl$] (strInstrSeqNbr, dtDR, dtCreated,strCreatedBy) VALUES ('CLDEL01040400002', '21 JUL 2004', '21 JUL 2004', 'ajit') ; Having said all that, I can't seem to get it to work with the ODBC Excel driver via the OLE DB provider for ODBC; I get an error, 'Operation must use an updatable query'. With ADO, I usually us the Microsoft OLE DB Provider for Jet and the insert works fine wit this. Out of interest, why you are using the ODBC dirver and not the OL DB Provider for Jet? Jamie. -- * Can you please pass on the code/connection-string where you have use OLE DB Driver. It's giving an error to me. :( Thanks in advance..... -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entering Date via SQL Query
What does yours look like? Anything like this?
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath\mySpreadsheet.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes""" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ajitrajput " wrote in message ... Jamie Collins wrote: *ajitrajput wrote ... My underlying database is Excel and I am writing all DB related operations using ADO in Visual Basic Editor of Excel (VBA). I am using Excel Driver. The data is stored directly into Excel columns as mentioned in the insert query. But, when I enter the date value in a column, it attaches a single-quote(') before the value. I have applied date-formatting to the respective column as (mm/dd/yyyy), but still it doesn't work. I want to programmatically enter the date value without any single-quote i.e the value should be treated as date datatype. Because of this single-quote(') to the date value which the date-value in the cell to be considered as TEXT and hence whenever I want to fetch records between date-range, the SELECT query does not pick-up proper records. [Since the end-user of my application will not open the files and make manual changes, i want the proper date entry to be made without any single quote...] Sample Query: INSERT INTO [mms_instrDR_dtl$](strInstrSeqNbr, dtDR, dtCreated,strCreatedBy) VALUES('CLDEL01040400002',#7/21/2004#,#7/21/2004#,'ajit') You need to get an appreciation of how Jet determines the data type of an Excel column i.e. it is not just determined by formatting but also by existing values in the column and machine-specific settings. This may help: http://www.dicks-blog.com/excel/2004...al_data_m.html Once Jet has determined that the majority type for the column is 'Date' (adDBTimeStamp) then it should be able to coerce an unambiguous date string e.g. INSERT INTO [mms_instrDR_dtl$] (strInstrSeqNbr, dtDR, dtCreated,strCreatedBy) VALUES ('CLDEL01040400002', '21 JUL 2004', '21 JUL 2004', 'ajit') ; Having said all that, I can't seem to get it to work with the ODBC Excel driver via the OLE DB provider for ODBC; I get an error, 'Operation must use an updatable query'. With ADO, I usually use the Microsoft OLE DB Provider for Jet and the insert works fine with this. Out of interest, why you are using the ODBC dirver and not the OLE DB Provider for Jet? Jamie. -- * Can you please pass on the code/connection-string where you have used OLE DB Driver. It's giving an error to me. :( Thanks in advance...... --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering parameter criteria MS Query | Excel Discussion (Misc queries) | |||
Microsoft Query - Entering Column Names with Blanks | Excel Discussion (Misc queries) | |||
Automatically entering date | Excel Discussion (Misc queries) | |||
In Excel 2003, entering date without slashes, the date is incorre. | Excel Discussion (Misc queries) | |||
Entering Query Parameters by UserForm | Excel Programming |