ADODB: Issues with a WHERE condition in a SELECT

kekk0

New Member
Joined
Mar 30, 2007
Messages
45
Hi,

I'm having issues with the following code:
Code:
Dim date1 as Date
[..]

date1 = date - 20  '20 days ago

oCm.CommandText = "Select * From mytable where resource = """ & resourcename & """ AND " & _
"date1 >= #" & date1 & "#"

Set rs = oCm.Execute(iRecAffected)

this statement is supposed to select records with field date1 >= 20 days ago
but it's not working as expected (according to the input date, the Select might return everything or nothing)
the filter for field "Resource" is working fine.

- the field "date1" is declared as a date field on the db table
- I tried with #date1# "date1" 'date1' within the SELECT statement, without success.

Any hint?

Thanks
kekko
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hi, use for query such date format
"#" & Format$(date1, "mm\/dd\/yyyy") & "#"
Regards,
 
Upvote 0
Hi

Try :-
Code:
"dateserial(Year(date1), Month(date1), Day(date1)) = " & date1 & ""

or
Code:
"dateserial(Year(date1), Month(date1), Day(date1)) = " & Date - 20 & ""

hth
 
Upvote 0
Where does the database reside? Access? MySQL? MS SQL Server?
 
Upvote 0
As far as I know, only access requires # for dates, so that would be my guess. They're stings in sql server
 
Upvote 0
Thank you!
kyle's solution seems to work.
Coud it be impacted by localization? this workbook might be used by various EU users .
Thanks again.
 
Upvote 0
You can always use the ISO date format of yyyy-mm-dd, that should take care of localization issues re the date.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top