CODEWITHSUNDEEP
ms-access
Yoddlenod
Yoddlenod

Reputation: 41

Access Age Range Query Not Returning Ages Less Than 1

I have a query in my Access database that takes 2 user inputted ages and returns database entries that are between those inputs. It works fine when you input a range where the lowest value is >=1 but if the lowest value is < 1 then it won't return anything, even though entries exist where this is true.

Initially I thought this might be something to do with how I'm calculating the ages (I'm doing this on the fly within the query) so I tried with and without the datediff functions: (Date()-[DOB])/365.25 and Age: (DateDiff("y",[DOB],Date()))/365.25 but neither of those made any difference to the results. Is it related to the age calculation or is there something in the between...and operator that isn't working?

The complete SQL code for this part of the query is:

DateDiff("y",[DOB],Date()))/365.25) Between [Forms]![AgeRangeQueryForm]![Youngest Age in Range] And [Forms]![AgeRangeQueryForm]![Oldest Age In Range]

And the code for the whole query is (inclusive of edits suggested by Gustav):

PARAMETERS [Forms]![AgeRangeQueryForm]![Youngest Age in Range] Short, [Forms]![AgeRangeQueryForm]![Oldest Age in Range] Short, [Forms]![AgeRangeQueryForm]![GenderOptionGroup] Short, [Forms]![AgeRangeQueryForm]![PostcodeSearch] Short;
SELECT [Carer Contact Details_1].[Parents Name], [Carer Contact Details_1].[Parents Surname], [Carer Contact Details_1].[Email Address], [Carer Contact Details_1].[Contact Number], [Carer Contact Details_1].Postcode, [Child Details1].[First Name], UCase(Left(Nz([Child Details1].[Gender],""),1)) AS Gender, Round((DateDiff("d",[DOB],Date())/365.25),2) AS Age, [Child Details1].DOB, [Child Details1].[Date of Last Test]
FROM [Carer Contact Details_1] INNER JOIN [Child Details1] ON [Carer Contact Details_1].[Parent ID] = [Child Details1].[Parent ID]
WHERE (((UCase(Left(Nz([Child Details1].[Gender],""),1))) Like Choose([Forms]![AgeRangeQueryForm]![GenderOptionGroup],"M","F","[MF]")) AND ((Round((DateDiff("d",[DOB],Date())/365.25),2)) Between [Forms]![AgeRangeQueryForm]![Youngest Age in Range] And [Forms]![AgeRangeQueryForm]![Oldest Age In Range]) AND ((Left([Carer Contact Details_1]![Postcode],Len(Nz([Forms]![AgeRangeQueryForm]![PostcodeSearch]))))=Nz([Forms]![AgeRangeQueryForm]![PostcodeSearch])));

Obviously, my expected result is that it is able to handle values less than 1 i.e. can search for and find values from 0.1 - 0.9. Currently, it returns blank.

I'm quite new to both Access and SQL, so I apologise if this has an obvious solution that I'm missing.

Thanks in advance.

EDIT: I thought I would update this as I'm still having problems with this. I have tried both of the suggestions listed below (by Gustav and SunKnight0) but neither has worked. It still won't return anything less than 1 year old (and interestingly, if I set the Age part of the query to return the age in days then it won't return anything less then 525 days). I thought this might be due to missing values in the "Gender" field of my search form, as it wasn't returning any entries that did not have an entry in that field (regardless of age). With that in mind I added this line:

((UCase(Left(Nz([Child Details1].[Gender],""),1)))

To the part of the query where I select the designated gender. I've updated the whole query above so it reflects the most recent attempt I'm working on.

I'm completely stumped now as to why it won't return anything less than. Is it something to do with the functions I use to calculate age? Does DateDiff do some automatic rounding down or something?

Upvotes: 0

Views: 415

Answers (2)

SunKnight0
SunKnight0

Reputation: 3351

To get the age in days (and then divide by 365.25 to get age as a fractional year) use

DateDiff("d",[DOB],Date())

not

DateDiff("y",[DOB],Date())

Upvotes: 0

Gustav
Gustav

Reputation: 55816

It is "yyyy", not "y" (the day of the year) - but use a function that calculates age correctly like this:

Public Function AgeSimple( _
  ByVal datDateOfBirth As Date) _
  As Integer

' Returns the difference in full years from datDateOfBirth to current date.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
' After an idea of Markus G. Fischer.
'
' 2007-06-26. Cactus Data ApS, CPH.

  Dim datToday  As Date
  Dim intAge    As Integer
  Dim intYears  As Integer

  datToday = Date
  ' Find difference in calendar years.
  intYears = DateDiff("yyyy", datDateOfBirth, datToday)
  If intYears > 0 Then
    ' Decrease by 1 if current date is earlier than birthday of current year
    ' using DateDiff to ignore a time portion of datDateOfBirth.
    intAge = intYears - Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) > 0)
  End If

  AgeSimple = intAge

End Function

Then your query will be:

Parameters 
    [Forms]![AgeRangeQueryForm]![Youngest Age in Range] Short,
    [Forms]![AgeRangeQueryForm]![Oldest Age In Range] Short;
Select
    *,
    AgeSimple([DOB])
From
    YourTable
Where
    AgeSimple([DOB]) Between [Forms]![AgeRangeQueryForm]![Youngest Age in Range] And [Forms]![AgeRangeQueryForm]![Oldest Age In Range]

Upvotes: 0

Related Questions