Count dates with value between 6 months and 18 months old (1 Viewer)

Hi,
Hopefully a quick problem to solve but I've been struggling for past few hours.
All I want to do is count how many records (within a set of grouped records) are between 18 months and 6 months old and ALSO count how many records are less than 6 months old
eg. My query "B4Q0_RSR_2FnCalcs_D_A_T_E" generates the following rows using the expression CtHrsY: IIf([B4Q0_RSR_2HistValues]![HDateH]>=(Date()-180),1,0):

HDateH TrkTo TimeTo HorseTo CtHrsY
20/08/2018 Fontwell 14:45 Ace Combat 0
05/12/2018 Fontwell 14:45 Ace Combat 0
25/04/2019 Fontwell 14:45 Ace Combat 0
15/10/2018 Fontwell 14:45 Ace Combat 0
27/12/2018 Fontwell 14:45 Ace Combat 1
15/04/2019 Fontwell 14:45 Ace Combat 0
26/05/2019 Fontwell 14:45 Ace Combat 0
12/09/2018 Fontwell 14:45 Ace Combat 0
23/03/2019 Fontwell 14:45 Ace Combat 0


I just can't understand why the expression doesn't work.

I have also made another query "B4Q0_RSR_2FnSc_Cnts" which goups TrkTo TimeTo HorseTo
and I have tried the following expressions:

CtHrsY: Count([B4Q0_RSR_2FnCalcs_D_A_T_E]![HDateH]>(Date()-548) And [B4Q0_RSR_2FnCalcs_D_A_T_E]![HDateH]<(Date()-183))...... this gives the answer "9"

Sum(IIf([B4Q0_RSR_2FnCalcs_D_A_T_E]![HDateH]>(Date()-548) And [B4Q0_RSR_2FnCalcs_D_A_T_E]![HDateH]<(Date()-183),1,0))...... this gives the answer "0"

Any advice would be most welcome.
Cheers

Dates are always stored as double-precision numbers regardless of how they appear (are formatted) in your table or query. To work reliably, you must compare in the dd/mm/yyyy format (US date) by way of passing criteria in that format, or utilizing Format function where appropriate. Since you didn't specify what the issue is, I can only presume that when specifying 07/05/2019 you think that is May 7th but Access sees it as July 5th.


As for AND I prefer BETWEEN operator.

Hi. Access has a few functions available for dealing with dates. For example: Date(), DateAdd(), DateDiff(), DateSerial(), etc. You should be able to use a combination of these for your query.

Looks rather convoluted. Given the English language statement of trying to get your counts, I might have just used


and

Thanks for the quick reply. I'll have to look into the US date and format issue.

Thanks for the advice everyone. Much appreciated.
I found the root of the problem......
I have a table where it appears that the dates are formatted as:
Data Type = Date/Time and the General Format is dd/mm/yyyy.
Then my first query, which goes on to form the basis of my other queries like those mentioned in my problem, was just picking up the Date from the table.

I changed this and made the first query date field to be:- HDateateValue([RaceDate]). This seemed to solve the problem and I can now use expressions such as CtHrsY: Sum(IIf([B4Q0_RSR_2FnCalcs_D_A_T_E]![HDateH]>(Date()-365),1,0)).

However, solving this has caused another problem I need to investigate.
I'll mention it in this thread just in case you know how to solve it quickly but I will investigate and then start a new thread if I can't find a solution.
Thanks again for the replies

The problem that has arisen as a result of solving the initial problem is as follows:
I have a table where the dates are formatted as:
Data Type = Date/Time and the General Format is dd/mm/yyyy.
My first query changes the date field to be:- HDate: DateValue([RaceDate]). I use this query to feed other queries.
In one of my queries I have a LEFT Join:
SELECT IIf(IsNull([B4Q0_Hist_1]![HDate]),"01/01/01",[B4Q0_Hist_1]![HDate]) AS HDateH,
…etc….. FROM B4Q0_RSR_1 LEFT JOIN B4Q0_Hist_1 ON B4Q0_RSR_1.HorseTo = B4Q0_Hist_1.HHorse

This used to work before I fixed the Date format issue.
Now I get #Error when there is a string in B4Q0_RSR_1.HorseTo but no string in B4Q0_Hist_1.HHorse.
I want to be able to create a Row for every B4Q0_RSR_1 Row and if B4Q0_Hist_1 does not contain a Horse name such that B4Q0_RSR_1.HorseTo = B4Q0_Hist_1.HHorse <<which means there is no [B4Q0_Hist_1]![HDate]>> then I want to ensure that the DATE field is set to a dummy value (eg 01/01/2001) so that the calculations from my initial problem still work – the #Error prevents them from working.
Once again, thanks for your thought on this problem.

With a LEFT JOIN and a case where there is no matching record from B4Q0_Hist_1 (i.e. no matching HHorse), you get nulls returned to that query for any field coming from the B4Q0_Hist_1 table. Which will pretty much trigger the #ERROR response.

Your SELECT Iif(IsNull ... sequence might work better as NZ( [B4Q0_Hist_1]![HDate], "#1-Jan-2001#" ). Certainly would be simpler to type.

Thanks The_Doc_Man,
I tried
SELECT Nz([B4Q0_Hist_1]![HDate],"#1/1/2001#") AS HDateH
but still get the same #Error.

If you are seeing #Error in that simple query then something else is terribly wrong. That query is too simple to fail on its own. (It needs help...)

Make a copy of the DB as a backup before you do anything else with it. Then try to perform a Compact & Repair on it. The query you described cannot possibly show you an #Error as a result unless the table (structure or data) has somehow gotten corrupted.

The backup copy is there in case something else goes wrong, which CAN happen if the DB is corrupted badly enough. But the C&R should clean it up. If not, then something else is going on that might require a greater effort.

As an aside, and PLEASE take this as a curious but friendly comment. Given those really long table names... do you enjoy typing that much?

Thanks for the reply.
I was perplexed as to why it didn't work so I reverted to my original sql to try and fix it from that angle.
Now it seems the original sql works again.
I think you were right - something must have got corrupted and I inadvertently fixed it.
I'm going to give your method another go and see if that works as well.
Thanks again

You are welcome, but watch out. Things that seem to go away by themselves can come back by themselves.