Age in Days If Then

[Chuck W]

Hello,
I have a table called tblPatients with about 200,000 records with two date
fields. One is AdmitDate and the other is DateOfBirth. I am trying to
calculate two additional fields based on these date fields. One is called
AgeYears which is ([AdmitDate]-[DateOfBirth]/ 365). I want to calculate
another field called AgeDays. which is the AdmitDate-DateOfBirth. However, I
only want to have a value be shown if it is less than one year or <366 days.
If it is more than 365 days, I want the value to appear blank. I still want
my query to return all 200,000 records in my table and not just those who are
a year old or less. Can someone help?

Thanks,

 

[Douglas J. Steele]

Try:

IIf(DateSerial(Year([DateOfBirth]) + 1, Month([DateOfBirth]),
Day([DateOfBirth]) <= [AdmitDate], Null, DateDiff("d", [DateOfBirth],
[AdmitDate]))

 

[Arvin Meyer MVP]

Write a public function in a standard module, something like:

Public Function IsBaby([Date1 As Date, Date2 As Date) As String
On Error Resume Next
Dim x
x = DateDiff("d", Date1, Date2)
If x < 366 Then
IsBaby = x
Else
IsBaby = ""
End If
End Function

In your query, add a column like:

AgeDays: IsBaby([DateOfBirth], [AdmitDate])

 

[Chuck W]

Arvin,
Thanks for your help. I don't have much experience with VBA and modules but
am interested in learning. I created a module called IsBaby (should I call
it something else?). Also, When I created it, the first line automatically
was "Option Compare Database". I then pasted your code into it. Although
the line "Public Function IsBaby([Date1 As Date, Date2 As Date) As String"
appears in red which I think is an error. I tried deleting the "Option
Compare Database" line but the Public Function line is still red. I did
create a new field in my query AgeDays: IsBaby([DateOfBirth],[AdmitDate])
but get an error when I run the query which is "Undefined Function 'IsBaby'
in expression". Any thoughts?

Thanks,

Write a public function in a standard module, something like:

Public Function IsBaby([Date1 As Date, Date2 As Date) As String
On Error Resume Next
Dim x
x = DateDiff("d", Date1, Date2)
If x < 366 Then
IsBaby = x
Else
IsBaby = ""
End If
End Function

In your query, add a column like:

AgeDays: IsBaby([DateOfBirth], [AdmitDate])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello,
I have a table called tblPatients with about 200,000 records with two date
fields. One is AdmitDate and the other is DateOfBirth. I am trying to
calculate two additional fields based on these date fields. One is called
AgeYears which is ([AdmitDate]-[DateOfBirth]/ 365). I want to calculate
another field called AgeDays. which is the AdmitDate-DateOfBirth.
However, I
only want to have a value be shown if it is less than one year or <366
days.
If it is more than 365 days, I want the value to appear blank. I still
want
my query to return all 200,000 records in my table and not just those who
are
a year old or less. Can someone help?

Thanks,

 

[Douglas J. Steele]

Get rid of the open square bracket [ in

Public Function IsBaby([Date1 As Date, Date2 As Date)

It should be

Public Function IsBaby(Date1 As Date, Date2 As Date)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Arvin,
Thanks for your help. I don't have much experience with VBA and modules
but
am interested in learning. I created a module called IsBaby (should I
call
it something else?). Also, When I created it, the first line
automatically
was "Option Compare Database". I then pasted your code into it. Although
the line "Public Function IsBaby([Date1 As Date, Date2 As Date) As String"
appears in red which I think is an error. I tried deleting the "Option
Compare Database" line but the Public Function line is still red. I did
create a new field in my query AgeDays: IsBaby([DateOfBirth],[AdmitDate])
but get an error when I run the query which is "Undefined Function
'IsBaby'
in expression". Any thoughts?

Thanks,

Write a public function in a standard module, something like:

Public Function IsBaby([Date1 As Date, Date2 As Date) As String
On Error Resume Next
Dim x
x = DateDiff("d", Date1, Date2)
If x < 366 Then
IsBaby = x
Else
IsBaby = ""
End If
End Function

In your query, add a column like:

AgeDays: IsBaby([DateOfBirth], [AdmitDate])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello,
I have a table called tblPatients with about 200,000 records with two
date
fields. One is AdmitDate and the other is DateOfBirth. I am trying to
calculate two additional fields based on these date fields. One is
called
AgeYears which is ([AdmitDate]-[DateOfBirth]/ 365). I want to
calculate
another field called AgeDays. which is the AdmitDate-DateOfBirth.
However, I
only want to have a value be shown if it is less than one year or <366
days.
If it is more than 365 days, I want the value to appear blank. I still
want
my query to return all 200,000 records in my table and not just those
who
are
a year old or less. Can someone help?

Thanks,

 

[John W. Vinson]

I created a module called IsBaby (should I call
it something else?).

Yes. The name of the function must NOT duplicate the name of the module. Name
the module basAge or something other than IsBaby.