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

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

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

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

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

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

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

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

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

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

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

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

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.

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") & "#"

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.

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

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.

--

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.