how to calculate 6months in query

[Toeying]

Hello All,
This may sound bizzare but I have not been able to fine my way round it!!
I need to add a criteria to my programme to select records for children who have turned 6 months in the week ending a particular date. i.e if i put in this friday as the date, I want all records for children who turned 6 months from the last saturday, sunday,mon,tue,wed,thurs and fri.
my calculation is something like this:
strFilter = "Me.txtDateTo.Value - [HSChildDOB]>=178 AND Me.txtDateTo.Value - [HSChildDOB]<=185 "

[HSChildDOB] is from a query underpining the form to be opened with strFilter and Me.txtDateTo.Value is on a form the initiates the process.

access keeps saying that the query is too complex to be calculated. What am I supposed to do?

 

[Toeying]

think I've found a solution. used the dateDiff function. working for now. thanks and cheers

 

[Brianwarnock]

Sorry to be the bearer of bad news but datediff will not work
with datediff 31st-Jan to 28th Feb gives 1 month.

The other problem with months is the fact that they have different number of days in so normal calculations have to allow for that.

Also don't you need to know where in the week you are inorder to compute the date range you are looking at, the use of weekday looks inevitable.

All of the above points to writing a function.


Brian

 

[Toeying]

Hmm thanks Brian will look into that.

 

[Toeying]

what I did was to use the day interval to compare the result is btwn 185 days and 178days. sounds ctude but like i said working for now. I'll probably revert to a function as I get my head round VBA. cheers