Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Hey guys,
I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Try wrapping the date in the FORMAT function so, instead of #15/01/2005#,
you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Have you tried an unambiguous date string like "15-Jan-2005"?
-- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
My code usually collects a date from somewhere, assigns it to a variable and
then passes it on to something like the OP's query. I was interpretting the example in those terms and not assuming the OP was actually using a hard-coded date :-) -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Bob Phillips" wrote in message ... Have you tried an unambiguous date string like "15-Jan-2005"? -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
I've tried both: with the format() function and also with unambiguous
dates... doesn't work... It's quite annoying I must say... you work out your code and doesn't work because of these format matters... :-( "Bob Phillips" wrote: Have you tried an unambiguous date string like "15-Jan-2005"? -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
:-(
-- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... I've tried both: with the format() function and also with unambiguous dates... doesn't work... It's quite annoying I must say... you work out your code and doesn't work because of these format matters... :-( "Bob Phillips" wrote: Have you tried an unambiguous date string like "15-Jan-2005"? -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
I'll try assigning the contents of the cell previously to a Date variable,
but I'm not positive towards this... It's really ****ing me off! "Andy Wiggins" wrote: :-( -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... I've tried both: with the format() function and also with unambiguous dates... doesn't work... It's quite annoying I must say... you work out your code and doesn't work because of these format matters... :-( "Bob Phillips" wrote: Have you tried an unambiguous date string like "15-Jan-2005"? -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Finally made it work!!!! a combination of lots of functions:
dim vdate as date vdate = dateserial(year(cell), month(cell), day(cell)) sSQL = sSQL & "#" & format(vdate, "dd-mmm-yyyy") & "#" Thanks all for the assistance Bregards Santiago "Santiago" wrote: I'll try assigning the contents of the cell previously to a Date variable, but I'm not positive towards this... It's really ****ing me off! "Andy Wiggins" wrote: :-( -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... I've tried both: with the format() function and also with unambiguous dates... doesn't work... It's quite annoying I must say... you work out your code and doesn't work because of these format matters... :-( "Bob Phillips" wrote: Have you tried an unambiguous date string like "15-Jan-2005"? -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
:-)
-- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Finally made it work!!!! a combination of lots of functions: dim vdate as date vdate = dateserial(year(cell), month(cell), day(cell)) sSQL = sSQL & "#" & format(vdate, "dd-mmm-yyyy") & "#" Thanks all for the assistance Bregards Santiago "Santiago" wrote: I'll try assigning the contents of the cell previously to a Date variable, but I'm not positive towards this... It's really ****ing me off! "Andy Wiggins" wrote: :-( -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... I've tried both: with the format() function and also with unambiguous dates... doesn't work... It's quite annoying I must say... you work out your code and doesn't work because of these format matters... :-( "Bob Phillips" wrote: Have you tried an unambiguous date string like "15-Jan-2005"? -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Even collected it can be formatted thus.
-- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... My code usually collects a date from somewhere, assigns it to a variable and then passes it on to something like the OP's query. I was interpretting the example in those terms and not assuming the OP was actually using a hard-coded date :-) -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Bob Phillips" wrote in message ... Have you tried an unambiguous date string like "15-Jan-2005"? -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Santiago,
Humour me. Try sSQL = sSQL & """" & format(vdate, "dd-mmm-yyyy") & """" -- HTH RP (remove nothere from the email address if mailing direct) "Santiago" wrote in message ... Finally made it work!!!! a combination of lots of functions: dim vdate as date vdate = dateserial(year(cell), month(cell), day(cell)) sSQL = sSQL & "#" & format(vdate, "dd-mmm-yyyy") & "#" Thanks all for the assistance Bregards Santiago "Santiago" wrote: I'll try assigning the contents of the cell previously to a Date variable, but I'm not positive towards this... It's really ****ing me off! "Andy Wiggins" wrote: :-( -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... I've tried both: with the format() function and also with unambiguous dates... doesn't work... It's quite annoying I must say... you work out your code and doesn't work because of these format matters... :-( "Bob Phillips" wrote: Have you tried an unambiguous date string like "15-Jan-2005"? -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
I'll take your word for it, but my experience tells me to use format.
-- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Bob Phillips" wrote in message ... Even collected it can be formatted thus. -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... My code usually collects a date from somewhere, assigns it to a variable and then passes it on to something like the OP's query. I was interpretting the example in those terms and not assuming the OP was actually using a hard-coded date :-) -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Bob Phillips" wrote in message ... Have you tried an unambiguous date string like "15-Jan-2005"? -- HTH RP (remove nothere from the email address if mailing direct) "Andy Wiggins" wrote in message ... Try wrapping the date in the FORMAT function so, instead of #15/01/2005#, you have FORMAT(#15/01/2005#,"dd-mmm-yyyy"). It just might be that VBA or SQL (or both) are trying to recognise an American date format rather than the English format. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Santiago" wrote in message ... Hey guys, I'm trying to update some data in an Access DB from Excel using ADO & SQL. I'd like to update some fields with a defined criteria using the WHERE clause in SQL, but dates seem not to work... I use the SQL string like: UPDATE [table] SET [field1] = 'aa', [field2] = #15/01/2005# etc... _ WHERE [field3] = 'bla' AND [field4] = #01/01/2005# I believe that the problem is the date I'm filtering... I copied the whole SQL string into access and did not work. But if I remove the date criteria seems to work. I appreciate your help. Thanks & Bregards Santiago |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Andy Wiggins wrote: my experience tells me to use format. My experience tells me to collect the data as a date value rather than text (e.g. assign to a Date variable in VBA), use a dedicated OLE DB provider of the correct version (e.g. OLE DB for Jet 4.0), use a Command object with a dependent Parameter object typed appropriately (e.g. adDate for a Jet 4.0 DATETIME column), if using Jet then possibly use the ADOX.Procedures(<n).Command method to create the Command and Parameter objects for me, then assign the strongly-typed date value to the stronly-typed Parameter object. In other words, I assume the OLE DB provider is better at remembering rules on formats, type conversion, SQL parsing, regional settings, etc than I will. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Bob Phillips wrote: Santiago, Humour me. Try sSQL = sSQL & """" & format(vdate, "dd-mmm-yyyy") & """" I'd go for the *international* standard format, which even Access/Jet understands i.e. sSQL = sSQL & "#" & Format$(vdate, "yyyy-mm-dd") & "#" |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Jamie Collins wrote: which even Access/Jet understands I meant to imply Jet and Access applications are US-centric. Apologies to Access fans who I though I was implying that the non- entry level ANSI-92 relational style view of what is little more than a file based ISAM under the covers excuse for a DBMS that is Access/Jet is in some way inferior <g. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
Another problem that can occur with dates results from the fact that DATE field is actually a DATE&TIME field. Depending on how you forma your output you might not see that a time is part of the value Depending on how the date field gets populated you may get a time valu as well. This will affect the selection as 1/1/5 10:15 is not the sam as 1/1/5 Regard -- tony ----------------------------------------------------------------------- tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107 View this thread: http://www.excelforum.com/showthread.php?threadid=39214 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
tony h wrote: Another problem that can occur with dates results from the fact that a DATE field is actually a DATE&TIME field. Good point but you are preaching to the converted <g e.g. here's a typical bit of my DDL, note how I have constrained my DATETIME columns only the minimum/maximum value (as appropriate) which the data type allows: CREATE TABLE Earnings ( pilot_ID CHAR(10) NOT NULL REFERENCES Pilots (pilot_ID) ON DELETE CASCADE ON UPDATE CASCADE, earnings_amt DECIMAL(19,4) DEFAULT 0 NOT NULL, start_date DATETIME NOT NULL, CONSTRAINT start_date_value CHECK ( HOUR(start_date) = 0 AND MINUTE(start_date) = 0 AND SECOND(start_date) = 0), end_date DATETIME NULL, CONSTRAINT end_date_value CHECK ( HOUR(end_date) = 23 AND MINUTE(end_date) = 59 AND SECOND(end_date) = 59), CONSTRAINT chrono_order CHECK (start_date < end_date), PRIMARY KEY (pilot_ID, start_date)); You see I am a fan *really* of Jet and I use it properly <g. Jamie. -- |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / SQL: problem with dates
I was just about to pull you up on that very point!
Bob "Jamie Collins" wrote in message oups.com... Jamie Collins wrote: which even Access/Jet understands I meant to imply Jet and Access applications are US-centric. Apologies to Access fans who I though I was implying that the non- entry level ANSI-92 relational style view of what is little more than a file based ISAM under the covers excuse for a DBMS that is Access/Jet is in some way inferior <g. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates problem | Excel Discussion (Misc queries) | |||
Problem with dates | Excel Discussion (Misc queries) | |||
Problem with dates | Excel Worksheet Functions | |||
Dates Problem | Excel Discussion (Misc queries) | |||
Problem with dates | Excel Worksheet Functions |