Date Range Not Working

[JSimoncelli]

I have a report that is based on a query, I have tried to add the “Between [Start Date ] And [End Date]” in the query and it returns a blank report (headers no data)… I have been able to get this work using the manual method “>#1/1/2013# And <#6/30/2013#” but not the user interactive method…

I must explain the query first: The date that I need to filter on is not in the table but is created based on a criteria (see below).. This may be a “cart before the horse” kind of thing, where the date [ReInvestDate] doesn’t exist when the “Between [Start Date ] And [End Date]” function is called…

Critieria:

ReInvestDate: IIf([Eligibility]="SCI",DateAdd("yyyy",+5,[PrevInvestDate]),IIf([Eligibility]="TS",DateAdd("yyyy",+5,[PrevInvestDate]),IIf([Eligibility]="S",DateAdd("yyyy",+10,[PrevInvestDate]))))

The [Eligibility] and [PrevInvestDate] are fields in a table and are used to create the [ReInvestDate] date in the query…

The formula works just fine I simply can’t filter using the “Between [Start Date ] And [End Date]” function in the query…

Any help would be appreciated

John

 

[namliam]

your start date and end date are you filling those from the default popup from access?
If so did you declare them as parameters with a date format?
Are you entering your dates in the US date format of MM/DD/YYYY or ISO date YYYY/MM/DD or YYYY-MM-DD?

 

[JSimoncelli]

Thanks for your help on this, I have identified them as "DateShort" and enter them as MM/DD/YYY.....

John

P.S. I have a blank version with around 50 randomly generated names I can post if it helps...

 

[Mile-O]

In your criteria make sure you apply a Format to the date. It's US-centric when it considers these things, despite regional settings.

ie Between Format([MyDateParameter], "mm/dd/yyyy") And ....

 

[JSimoncelli]

@Mile-O,

Thanks for your help...

I don't think the format is the issue, I feel I need to clarify...

I believe I may have misled everyone... The formula

ReInvestDate: IIf([Eligibility]="SCI",DateAdd("yyyy",+5,[PrevInvestDate]),IIf([Eligibility]="TS",DateAdd("yyyy",+5,[PrevInvestDate]),IIf([Eligibility]="S",DateAdd("yyyy",+10,[PrevInvestDate]))))

Is not in the Criteria section of the query, it is located in the "Field" section making it a calculated date based on existing fields in a table... the ReInvestDate doesn't appear in any table... the “Between [Start Date ] And [End Date]” function is what is in the Criteria section of the query and it not working.... The fact that the ReInvestDate date does not exist until the query is run is what I believe is causing the problem...

John

 

[namliam]

I made a simular query with some simular formula in there, works just fine for me...

Define "cant filter" or "not working", is it throwing an error?
Things to (double) check:
1) Is your PrevInvestDate indeed a date/time field?
2) are you entering your Start date and end date as MM/DD/YYYY or YYYY/MM/DD

If you can post your database that is always usefull in finding any problems

 

[Brianwarnock]

The first thing to try is using the Between ..And.. With date strings

Between #01/01/2013# And ..

If this works then you know that it is a problem with the parameters, you did imply that you defined them ie with the Parameter command in SQL View, I always supplied my parameters from a form from which I also ran the query, maybe you could try that.

Brian

 

[CazB]

I had a look on my db at a similar query (which works!) and this is the code I'm using...

PARAMETERS [Refreshers Due Between Start Date?] DateTime, [and End Date?] DateTime;
SELECT tblRegistration.AttendeeID, tblRegistration.EventTypeID, tblRegistration.RegCompletedDate, IIf([EventTypeID]=215,DateAdd("yyyy",+5,[regcompletedDate]),DateAdd("yyyy",+10,[regcompleteddate])) AS RefresherDue
FROM tblRegistration
WHERE (((IIf([EventTypeID]=215,DateAdd("yyyy",+5,[regcompletedDate]),DateAdd("yyyy",+10,[regcompleteddate]))) Between [Refreshers Due Between Start Date?] And [and End Date?]));

Obviously, substitute your table and field names for mine

 

[JSimoncelli]

Boooya

It is working, @CazB your code worked... The opening parameter statement and adding it at the end of the "WHERE" statement is what did it......

Again thanks to you all.....

John

 

[namliam]

your start date and end date are you filling those from the default popup from access?
If so did you declare them as parameters with a date format?
Are you entering your dates in the US date format of MM/DD/YYYY or ISO date YYYY/MM/DD or YYYY-MM-DD?

Which isnt anything that you havent been told before, except more explicit