Creating queries asking for counts

I don't see any error there except a wrapping error where the "THEN" wrapped
to the next line. It should be on the same line as

If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))

Public Function GetAge(varDob, Optional varDateAt)
'Calculates the age in years based on an input date
'or the current date if there is no input date (varDateAt
Dim intYears As Integer

' return age at current date if no second argument
' passed into function
If IsMissing(varDateAt) Then varDateAt = VBA.Date

If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob)) Then
intYears = intYears - 1
End If

GetAge = intYears
End If

End Function

I would be passing a specific date in for age calculation. Otherwise your
numbers are going to change as time goes on. In a year the 18 year old will
be 19 (as a matter of fact tomorrow could be an 18 year olds date of birth and
the figures will then change if you ran this tomorrow).

Do you have a diagnosis date for when the condition was diagnosed? Or some
other static date such as a record created date or ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John wrote:
John and Ken
Again thank you for all your help!!! You guys are increadible!!!!! I am new
to access and have only written simple queries. I get now one final error.
When I type in the date of birth I receive the following error:
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
I need to show how many people had gestational diabetes from 0-18 years old
and how many people from 19 and older.
I have pasted the whole module below:
Public Function GetAge(varDob, Optional varDateAt)

Dim intYears As Integer

' return date at current date if no second argument
' passed into function
If IsMissing(varDateAt) Then varDateAt = VBA.Date

If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
Then
intYears = intYears - 1
End If

GetAge = intYears
End If

End Function

Lastly - where can I enter a paramater to ask for a begining date and an end
date so I can limit the query to just a particular range?
Again thank you for all your help and Merry Christmas.


"KenSheridan via AccessMonster.com" wrote:

John:

Its not necessary to include a constant in a GROUP BY clause, only column
names or expressions which include them.

Ken Sheridan
Stafford, England

John Spencer wrote:
First: That is the query and it should be entered into the SQL view not the
query design view. Second it had several syntax errors - which I hope I've
corrected below

SELECT "Type 1 diabetes" AS [Category],
COUNT(*) AS [Number of Patients]
FROM [Diabetes]
WHERE Diabetes_Type = "Type 1 diabetes"
GROUP BY "Type 1 diabetes"

UNION ALL
SELECT "Type 2 diabetes",
COUNT(*)
FROM [Diabetes]
WHERE Diabetes_Type = "Type 2 diabetes"
GROUP BY "Type 2 diabetes"

UNION ALL
SELECT "Gestational diabetes 1-18 years",
COUNT(*)
FROM [Diabetes]
WHERE Diabetes_Type = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 18
GROUP BY "Gestational diabetes 1-18 years"

UNION ALL
SELECT "Gestational diabetes 19+ years",
COUNT(*)
FROM [Diabetes]
WHERE Diabetes_Type = "Gestational diabetes"
AND GetAge([DateOfBirth]) = 19
GROUP BY "Gestational diabetes 19+ years"

That said this is overly complicated way to do this. A simpler query might be
the following (again this would be entered in the QUERY SQL view. Although
unlike a UNION query it can be constructed in query design view.

SELECT Diabetes_Type
, Count(Diabetes_Type) as CountAll
, Count(IIF(GetAge(DateOfBirth)=18,1,null)) as Under19
, Count(IIF(GetAge(DateOfBirth=19),1,Null) as Over19
FROM Diabetes
WHERE Diabetes_Type in
("Type 1 diabetes","Type 2 diabetes","Gestational diabetes"))
GROUP BY Diabetes_Type

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Ken and everyone else
Thank You for your responses. However I am still having the same issue. When
[quoted text clipped - 149 lines]
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

.

I think you've been clobbered by the newsgroup word-wrap demon! The lines:

If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
Then

Should be one unbroken line:

If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob)) Then

Unfortunately newsreaders break long lines of code into two separate lines,
which is sometimes not readily apparent.

To restrict a query to a date range defined by parameters you can enter the
following into the criteria row of the relevant date column in design view:

Between [Enter start date:] And [Enter end date:]

This will translate to an expression in the query's WHERE clause such as:

WHERE [YourDateField] Between [Enter start date:] And [Enter end date:]

With date/time parameters its always a good idea to declare them as such to
avoid their being misinterpreted as arithmetical expressions. This can be
done via the interface in design view or a PARAMETERS clause can be added to
the start of the query in SQL view:

PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT etc

If using a UNION or UNION ALL query you only need declare the parameters once
at the start of the query, but you'd need to include them separately in the
WHERE clauses of each part of the UNION or UNION ALL operations.

One thing to note about a Between….And operation however is that the date
values must not include non-zero times of day or any such date/time values on
the last day will be missed. The best way to avoid this is to give the date
field a validation rule to allow only dates with zero times of day, but
otherwise, by defining the range in a different way any such date/time values
will be caught:

WHERE [YourDateField] = [Enter start date:] And [YourDateField] DateAdd
("d", 1,[Enter end date:])

This looks for dates on or after the start date and before the day following
the end date, so any date/time values on the final day are caught regardless
of their time of day element.

Ken Sheridan
Stafford, England

John wrote:
John and Ken
Again thank you for all your help!!! You guys are increadible!!!!! I am new
to access and have only written simple queries. I get now one final error.
When I type in the date of birth I receive the following error:
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
I need to show how many people had gestational diabetes from 0-18 years old
and how many people from 19 and older.
I have pasted the whole module below:
Public Function GetAge(varDob, Optional varDateAt)

Dim intYears As Integer

' return date at current date if no second argument
' passed into function
If IsMissing(varDateAt) Then varDateAt = VBA.Date

If Not IsNull(varDob) Then
' get difference in years
intYears = DateDiff("yyyy", varDob, varDateAt)
' adjust result if date of birth falls later in year
' than date at which age to be calculated
If varDateAt DateSerial(Year(varDateAt), Month(varDob), Day(varDob))
Then
intYears = intYears - 1
End If

GetAge = intYears
End If

End Function

Lastly - where can I enter a paramater to ask for a begining date and an end
date so I can limit the query to just a particular range?
Again thank you for all your help and Merry Christmas.

John:

[quoted text clipped - 60 lines]
introduce a count into the equation I ge the complicated expression again.
Any help would be greatly appreciated!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1