dates in vb code

[Guest]

Hi,
I am using a combo box to select whether students are 16-18 years old or 19+
years old. The combo box returns "16-18" or "19+".
The query that contains the students contains their date of birth. How do I
code in VBA so that when 16-18 is selected it returns all students with dates
of birth between 01/09/1986 and 31/08/1989. and likewise returns dates of
birth before 01/09/1986 for 19+.

Thanks in advance
rhys

 

[Allen Browne]

rhys, you need to design the table so that it is based on a Number field
representing the first age in the range.

The last value in the age range is then one less than the next range value.
You can combine these values in a query, and generate a calculated text
field such as "16-18" if you wish. You can also use DateAdd() to add a
negative number of years from Date() to get the date range.

Tom Ellison has details on how to set up this kind of table in:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

This may not be what you were expecting to do, but I would encourage you to
do it right.

 

[Guest]

Rhys:

There are various ways of calculating a person's age from their date of
birth. Take at look at the following for a few examples:


http://www.mvps.org/access/datetime/date0001.htm

Using one of these add a function to a standard module, e.g.

Public Function CalcAge(DOB As Date, AgeAt As Date) As Integer

CalcAge = DateDiff("yyyy", DOB, AgeAt) + _
Int(Format(AgeAt, "mmdd") < Format(DOB, "mmdd"))

End Function

Create a table AgeRanges with 3 columns:

LowerAge HigherAge Range
16 18 16-18
19 100 19+

This assumes there are no students over 100 years old!

For the RowSource property of the combo box use:

SELECT Range FROM AgeRanges ORDER BY Range;

In your query join the tables and restrict the result set by means of a
parameter which references the combo box. Pass the date of birth and the
date at which you want the age calculated into the function, so if you wanted
it to return all students in the selected age range on 1 September 2005:

SELECT Students.*
FROM Students, AgeRanges
WHERE CalcAge(DoB,#09/01/2005#) BETWEEN LowerAge AND HigherAge
AND Range = Forms!YourForm!cboAgeRanges;

where Students is the table name and DoB is the name of its date of birth
column, and the form is called YourForm and the combo box cboAgeRanges. Note
that the date literal must be in a US or otherwise internationally
unambiguous format. Rather than using a date literal for the 'age at'
argument, however, you might prefer to reference another control on the form
in which the age at' date could be selected, perhaps with a default value of
the current date via the VBA Date() function.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Ken for your help. Unfortunately I d not need to calculate their age.
All I need to do is return all students whose date of births are between
01/09/1986 and 31/08/1989. I have tried the following code but it doesn't
seem to be working;

If Not IsNull(Me.age) Then
If Me.age = "19+" Then
strwhere = strwhere & "([DATE_OF_BIRTH] > """ & Me.date2 & """) And "
Else
strwhere = strwhere & "([DATE_OF_BIRTH] >= """ & Me.date1 & """) &
([DATE_OF_BIRTH] <= """ & Me.date2 & """) And "
End If
End If

me.age is the combo box control name.
date1 = 01/09/1986
date2 = 31/08/1989

if you have any more suggestions
thanks
rhys

 

[Allen Browne]

Your delimiters are wrong, and your dates are not formatted as expected by
JET.

For details on how to format them correctly, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Ken for your help. Unfortunately I d not need to calculate their
age.
All I need to do is return all students whose date of births are between
01/09/1986 and 31/08/1989. I have tried the following code but it doesn't
seem to be working;

If Not IsNull(Me.age) Then
If Me.age = "19+" Then
strwhere = strwhere & "([DATE_OF_BIRTH] > """ & Me.date2 & """) And
"
Else
strwhere = strwhere & "([DATE_OF_BIRTH] >= """ & Me.date1 & """) &
([DATE_OF_BIRTH] <= """ & Me.date2 & """) And "
End If
End If

me.age is the combo box control name.
date1 = 01/09/1986
date2 = 31/08/1989

if you have any more suggestions
thanks
rhys

Rhys:

There are various ways of calculating a person's age from their date of
birth. Take at look at the following for a few examples:


http://www.mvps.org/access/datetime/date0001.htm

Using one of these add a function to a standard module, e.g.

Public Function CalcAge(DOB As Date, AgeAt As Date) As Integer

CalcAge = DateDiff("yyyy", DOB, AgeAt) + _
Int(Format(AgeAt, "mmdd") < Format(DOB, "mmdd"))

End Function

Create a table AgeRanges with 3 columns:

LowerAge HigherAge Range
16 18 16-18
19 100 19+

This assumes there are no students over 100 years old!

For the RowSource property of the combo box use:

SELECT Range FROM AgeRanges ORDER BY Range;

In your query join the tables and restrict the result set by means of a
parameter which references the combo box. Pass the date of birth and the
date at which you want the age calculated into the function, so if you
wanted
it to return all students in the selected age range on 1 September 2005:

SELECT Students.*
FROM Students, AgeRanges
WHERE CalcAge(DoB,#09/01/2005#) BETWEEN LowerAge AND HigherAge
AND Range = Forms!YourForm!cboAgeRanges;

where Students is the table name and DoB is the name of its date of birth
column, and the form is called YourForm and the combo box cboAgeRanges.
Note
that the date literal must be in a US or otherwise internationally
unambiguous format. Rather than using a date literal for the 'age at'
argument, however, you might prefer to reference another control on the
form
in which the age at' date could be selected, perhaps with a default value
of
the current date via the VBA Date() function.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Allen. I've changed my delimiters to # but am not too sure about the
JET. Do I just change the format to mm/dd/yy?

rhys

Your delimiters are wrong, and your dates are not formatted as expected by
JET.

For details on how to format them correctly, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Ken for your help. Unfortunately I d not need to calculate their
age.
All I need to do is return all students whose date of births are between
01/09/1986 and 31/08/1989. I have tried the following code but it doesn't
seem to be working;

If Not IsNull(Me.age) Then
If Me.age = "19+" Then
strwhere = strwhere & "([DATE_OF_BIRTH] > """ & Me.date2 & """) And
"
Else
strwhere = strwhere & "([DATE_OF_BIRTH] >= """ & Me.date1 & """) &
([DATE_OF_BIRTH] <= """ & Me.date2 & """) And "
End If
End If

me.age is the combo box control name.
date1 = 01/09/1986
date2 = 31/08/1989

if you have any more suggestions
thanks
rhys

Rhys:

There are various ways of calculating a person's age from their date of
birth. Take at look at the following for a few examples:


http://www.mvps.org/access/datetime/date0001.htm

Using one of these add a function to a standard module, e.g.

Public Function CalcAge(DOB As Date, AgeAt As Date) As Integer

CalcAge = DateDiff("yyyy", DOB, AgeAt) + _
Int(Format(AgeAt, "mmdd") < Format(DOB, "mmdd"))

End Function

Create a table AgeRanges with 3 columns:

LowerAge HigherAge Range
16 18 16-18
19 100 19+

This assumes there are no students over 100 years old!

For the RowSource property of the combo box use:

SELECT Range FROM AgeRanges ORDER BY Range;

In your query join the tables and restrict the result set by means of a
parameter which references the combo box. Pass the date of birth and the
date at which you want the age calculated into the function, so if you
wanted
it to return all students in the selected age range on 1 September 2005:

SELECT Students.*
FROM Students, AgeRanges
WHERE CalcAge(DoB,#09/01/2005#) BETWEEN LowerAge AND HigherAge
AND Range = Forms!YourForm!cboAgeRanges;

where Students is the table name and DoB is the name of its date of birth
column, and the form is called YourForm and the combo box cboAgeRanges.
Note
that the date literal must be in a US or otherwise internationally
unambiguous format. Rather than using a date literal for the 'age at'
argument, however, you might prefer to reference another control on the
form
in which the age at' date could be selected, perhaps with a default value
of
the current date via the VBA Date() function.

Ken Sheridan
Stafford, England

:

Hi,
I am using a combo box to select whether students are 16-18 years old
or 19+
years old. The combo box returns "16-18" or "19+".
The query that contains the students contains their date of birth. How
do I
code in VBA so that when 16-18 is selected it returns all students with
dates
of birth between 01/09/1986 and 31/08/1989. and likewise returns dates
of
birth before 01/09/1986 for 19+.

Thanks in advance
rhys

 

[Guest]

It all works fine now. Thanks heaps for your help

Rhys

Your delimiters are wrong, and your dates are not formatted as expected by
JET.

For details on how to format them correctly, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Ken for your help. Unfortunately I d not need to calculate their
age.
All I need to do is return all students whose date of births are between
01/09/1986 and 31/08/1989. I have tried the following code but it doesn't
seem to be working;

If Not IsNull(Me.age) Then
If Me.age = "19+" Then
strwhere = strwhere & "([DATE_OF_BIRTH] > """ & Me.date2 & """) And
"
Else
strwhere = strwhere & "([DATE_OF_BIRTH] >= """ & Me.date1 & """) &
([DATE_OF_BIRTH] <= """ & Me.date2 & """) And "
End If
End If

me.age is the combo box control name.
date1 = 01/09/1986
date2 = 31/08/1989

if you have any more suggestions
thanks
rhys

Rhys:

There are various ways of calculating a person's age from their date of
birth. Take at look at the following for a few examples:


http://www.mvps.org/access/datetime/date0001.htm

Using one of these add a function to a standard module, e.g.

Public Function CalcAge(DOB As Date, AgeAt As Date) As Integer

CalcAge = DateDiff("yyyy", DOB, AgeAt) + _
Int(Format(AgeAt, "mmdd") < Format(DOB, "mmdd"))

End Function

Create a table AgeRanges with 3 columns:

LowerAge HigherAge Range
16 18 16-18
19 100 19+

This assumes there are no students over 100 years old!

For the RowSource property of the combo box use:

SELECT Range FROM AgeRanges ORDER BY Range;

In your query join the tables and restrict the result set by means of a
parameter which references the combo box. Pass the date of birth and the
date at which you want the age calculated into the function, so if you
wanted
it to return all students in the selected age range on 1 September 2005:

SELECT Students.*
FROM Students, AgeRanges
WHERE CalcAge(DoB,#09/01/2005#) BETWEEN LowerAge AND HigherAge
AND Range = Forms!YourForm!cboAgeRanges;

where Students is the table name and DoB is the name of its date of birth
column, and the form is called YourForm and the combo box cboAgeRanges.
Note
that the date literal must be in a US or otherwise internationally
unambiguous format. Rather than using a date literal for the 'age at'
argument, however, you might prefer to reference another control on the
form
in which the age at' date could be selected, perhaps with a default value
of
the current date via the VBA Date() function.

Ken Sheridan
Stafford, England

:

Hi,
I am using a combo box to select whether students are 16-18 years old
or 19+
years old. The combo box returns "16-18" or "19+".
The query that contains the students contains their date of birth. How
do I
code in VBA so that when 16-18 is selected it returns all students with
dates
of birth between 01/09/1986 and 31/08/1989. and likewise returns dates
of
birth before 01/09/1986 for 19+.

Thanks in advance
rhys

 

[Douglas J Steele]

Yes, as in

strwhere = strwhere & "([DATE_OF_BIRTH] > " & _
Format(Me.date2, "\#mm\/dd\/yyyy\#") & ") And "


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Allen. I've changed my delimiters to # but am not too sure about the
JET. Do I just change the format to mm/dd/yy?

rhys

Your delimiters are wrong, and your dates are not formatted as expected by
JET.

For details on how to format them correctly, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Ken for your help. Unfortunately I d not need to calculate their
age.
All I need to do is return all students whose date of births are between
01/09/1986 and 31/08/1989. I have tried the following code but it doesn't
seem to be working;

If Not IsNull(Me.age) Then
If Me.age = "19+" Then
strwhere = strwhere & "([DATE_OF_BIRTH] > """ & Me.date2 & """) And
"
Else
strwhere = strwhere & "([DATE_OF_BIRTH] >= """ & Me.date1 & """) &
([DATE_OF_BIRTH] <= """ & Me.date2 & """) And "
End If
End If

me.age is the combo box control name.
date1 = 01/09/1986
date2 = 31/08/1989

if you have any more suggestions
thanks
rhys


:

Rhys:

There are various ways of calculating a person's age from their date of
birth. Take at look at the following for a few examples:


http://www.mvps.org/access/datetime/date0001.htm

Using one of these add a function to a standard module, e.g.

Public Function CalcAge(DOB As Date, AgeAt As Date) As Integer

CalcAge = DateDiff("yyyy", DOB, AgeAt) + _
Int(Format(AgeAt, "mmdd") < Format(DOB, "mmdd"))

End Function

Create a table AgeRanges with 3 columns:

LowerAge HigherAge Range
16 18 16-18
19 100 19+

This assumes there are no students over 100 years old!

For the RowSource property of the combo box use:

SELECT Range FROM AgeRanges ORDER BY Range;

In your query join the tables and restrict the result set by means of a
parameter which references the combo box. Pass the date of birth and the
date at which you want the age calculated into the function, so if you
wanted
it to return all students in the selected age range on 1 September 2005:

SELECT Students.*
FROM Students, AgeRanges
WHERE CalcAge(DoB,#09/01/2005#) BETWEEN LowerAge AND HigherAge
AND Range = Forms!YourForm!cboAgeRanges;

where Students is the table name and DoB is the name of its date of birth
column, and the form is called YourForm and the combo box cboAgeRanges.
Note
that the date literal must be in a US or otherwise internationally
unambiguous format. Rather than using a date literal for the 'age at'
argument, however, you might prefer to reference another control on the
form
in which the age at' date could be selected, perhaps with a default value
of
the current date via the VBA Date() function.

Ken Sheridan
Stafford, England

:

Hi,
I am using a combo box to select whether students are 16-18 years old
or 19+
years old. The combo box returns "16-18" or "19+".
The query that contains the students contains their date of birth. How
do I
code in VBA so that when 16-18 is selected it returns all students with
dates
of birth between 01/09/1986 and 31/08/1989. and likewise returns dates
of
birth before 01/09/1986 for 19+.

Thanks in advance
rhys

 

[Guest]

Rhys:

Determining which range a person's age at a certain date falls within by
calculating their age at that date is the same in the long run as determining
if they were born between two dates. As Allen has pointed out, however, date
literals must be delimited with the # character and be in a US or otherwise
internationally unambiguous format, so try this:

If Not IsNull(Me.age) Then
If Me.age = "19+" Then
strwhere = strwhere & "([DATE_OF_BIRTH] > #" & _
Format(Me.date2,"mm/dd/yyyy") & "#) And "
Else
strwhere = strwhere & "([DATE_OF_BIRTH] >= #" & _
Format(Me.date1,"mm/dd/yyyy") & _
"# And [DATE_OF_BIRTH] <= #" & _
Format(Me.date2,"mm/dd/yyyy") & "#) And "
End If
End If

Ken Sheridan
Stafford, England