Date Range: Start Date or End Date not in DBase

mkam
mkam used Ask the Experts™
on
I have a table in Access that contains info arranged according to dates. I am writing a Selection formula in vb to obtain the records within a specified date range.

ILLUSTRATION:
Database contains records for Dec 5 to Dec 10.
When I enter sth. LIKE the following formula in my vb:

CR.Selectionformula= "{datefield} in (Dec 1) to (Dec 10)"

it does not work; apparently, because Dec 1 is not specifically found in the DBase.
I expect it to see that my range Dec 1 to Dec 10 covers Dec 5 to Dec 10 in the DBase.

Any help, folks?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,

Why don't you try using like the following :

(datefield) >= (dec 1) and (datefield) <= (dec 10)

and I dunno if the syntax BETWEEN is available in access or not, but you can try using between. The sql statement should be something like :

SELECT * FROM TABLE WHERE DATE BETWEEN '2002-12-01' AND '2002-12-10'

Good Luck
Dirk HaestProject manager

Commented:
But remember to format your date's
For example:
in database they are stored like MM dd YYYY

sql = "SELECT * FROM TABLE WHERE DATE BETWEEN '" & format(fromdate,"MM dd YYYY") & "' AND '" & format(enddate,"MM dd YYYY") & "'"
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
try this
CR only expects the where part of the SQL

CR.Selectionformula= "{datefield} in " & format(fromdate,"MM dd YYYY") & " AND " & format(enddate,"MM dd YYYY")

mlmcc
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Lets say for arguments sake your to dates are in variables called dFrom and dTo
sFrom="Dec 1"
sTo="Dec 10"

Assume the year is the current year.

CR.Selectionformula= "datefield between '" & vba.format(dateserial(year(now),left(sFrom,3), mid(sFrom,instr(sFrom, " ")+1)), "dd-mmm-yyyy") & "' and '" & vba.format(dateserial(year(now),left(sTo,3), mid(sTo,instr(sTo, " ")+1)), "dd-mmm-yyyy") & "' "


Cheers
Louis
Commented:
I totally agree with ayufans

1- Use BETWEEN     and if access97 not 2000 use # not '

2- Use    yyyy/mm/dd format to avoid english date problems ( for systems use dd/mm/yyyy format

Author

Commented:
Thnx to u all, especially ayufans and Bahnass. That was what worked for me.

I realise that (DateField IN ... TO ...) searches specifically for the date values following the IN and the TO. If those dates are not found in the DBase, then error!

The use of <= and >= works whether those specific dates are found in the DBase of not.

Commented:
Thanks

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Deemed answered by ayufans and bahnass, the later expert included with his use of # with dates.

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
apptsolu
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial