VBA / SQL: problem with dates

S

Santiago

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
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
 
A

Andy Wiggins

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.
 
B

Bob Phillips

Have you tried an unambiguous date string like "15-Jan-2005"?

--

HTH

RP
(remove nothere from the email address if mailing direct)


Andy Wiggins said:
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 said:
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
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
 
A

Andy Wiggins

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 said:
Have you tried an unambiguous date string like "15-Jan-2005"?

--

HTH

RP
(remove nothere from the email address if mailing direct)


Andy Wiggins said:
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 said:
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
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
 
S

Santiago

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 said:
Have you tried an unambiguous date string like "15-Jan-2005"?

--

HTH

RP
(remove nothere from the email address if mailing direct)


Andy Wiggins said:
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 said:
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
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
 
A

Andy Wiggins

:-(

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

Santiago said:
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 said:
Have you tried an unambiguous date string like "15-Jan-2005"?

--

HTH

RP
(remove nothere from the email address if mailing direct)


Andy Wiggins said:
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
-

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
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
 
S

Santiago

I'll try assigning the contents of the cell previously to a Date variable,
but I'm not positive towards this... It's really pissing me off!

Andy Wiggins said:
:-(

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

Santiago said:
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 said:
Have you tried an unambiguous date string like "15-Jan-2005"?

--

HTH

RP
(remove nothere from the email address if mailing direct)


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
-

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
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
 
S

Santiago

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 said:
I'll try assigning the contents of the cell previously to a Date variable,
but I'm not positive towards this... It's really pissing me off!

Andy Wiggins said:
:-(

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

Santiago said:
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... :-(

:

Have you tried an unambiguous date string like "15-Jan-2005"?

--

HTH

RP
(remove nothere from the email address if mailing direct)


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
-

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
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
 
A

Andy Wiggins

:)

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

Santiago said:
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 said:
I'll try assigning the contents of the cell previously to a Date variable,
but I'm not positive towards this... It's really pissing me off!

Andy Wiggins said:
:-(

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

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... :-(

:

Have you tried an unambiguous date string like "15-Jan-2005"?

--

HTH

RP
(remove nothere from the email address if mailing direct)


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
-

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
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
 
B

Bob Phillips

Even collected it can be formatted thus.

--

HTH

RP
(remove nothere from the email address if mailing direct)


Andy Wiggins said:
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 said:
Have you tried an unambiguous date string like "15-Jan-2005"?

--

HTH

RP
(remove nothere from the email address if mailing direct)


Andy Wiggins said:
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
-

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
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
 
B

Bob Phillips

Santiago,

Humour me. Try

sSQL = sSQL & """" & format(vdate, "dd-mmm-yyyy") & """"

--

HTH

RP
(remove nothere from the email address if mailing direct)


Santiago said:
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 said:
I'll try assigning the contents of the cell previously to a Date variable,
but I'm not positive towards this... It's really pissing me off!

Andy Wiggins said:
:-(

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

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... :-(

:

Have you tried an unambiguous date string like "15-Jan-2005"?

--

HTH

RP
(remove nothere from the email address if mailing direct)


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
-

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
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
 
A

Andy Wiggins

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 said:
Even collected it can be formatted thus.

--

HTH

RP
(remove nothere from the email address if mailing direct)


Andy Wiggins said:
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
-
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
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
 
T

tony h

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top