Calculate age in years and months from birthdate?

[Guest]

I named the module something entirely different.

No, I am running the code within VB. I guess that was my main question. I
have it in a "Select" event. I use the SQL statement that the query
generates and I put it in VB after: myRecordset.Open

My Access book says, "You don't have to base a recordset on a single table.
You can base it on a query. But you can't use the query's name in the
myRecordset.Open statement because only table names are allowed." It then
says to get the SQL statement from the query and paste it into VB after the
myRecordset.Open and inbetween the double quotes.

So that's what I did. But then I get that error. I know I am totally
confused here, so I hope I'm not confusing you. One more thing: I am not
putting a user-defined function into the Access query. The query works
perfectly. It generates the person's age by year, month and day. I just
need to translate that into VB so that it will populate the text box.

Thanks again!

When you copied the code for Diff2Dates into your application, what did you
name the module? If you named the module Diff2Dates, rename it: modules
can't be named the same as functions or subs in them.

I'm assuming you're actually running this code from within Access, not from
a VB program. You can't put user-defined functions into Access queries if
you're running them from outside of Access.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug:

Thank you so much for getting back to me. I spent all day yesterday
reading
about Access queries. I program in VB6 and have very little experience in
Access. Here is my code:

Dim myConnection As ADODB.Connection
Set myConnection = New Connection
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection

' Open PatientInfo Query

myRecordset.Open "SELECT PatientInfo.PatientID, PatientInfo.Birthdate,
Diff2Dates('ymd',[Birthdate],Now()) AS Age FROM PatientInfo", dBase,
adOpenDynamic, adLockOptimistic

myRecordset.Filter = "(PatientID) like '" & strPatientID & "*'"
frmPatientInfo.txtAge.Text = rs![Age]

myRecordset.Close

I have the "Open" statement on one line. I set a break at rs![Age] and it
gives me that "Item cannot be found..." error. Can you please tell me
what I
am doing wrong? I'm curious, how does VB know what query to look in? I
see
where it refers to the table, but not the query. Any help would be
greatly
appreciated!

Lynn

You'll need to post the code you're trying to use.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My question is similar but it has to do with connecting the database
with
Visual Basic. I built a query to determine a person's age per the
above
instructions. It works perfectly (thank you). I copied the SQL code
and
put it in my VB code after "myRecordset.open." I want it to fill the
textbox
(in VB) with the person's age. Now it gives me the error: "Item
cannot
be
found in the collection corresponding to the recordset." I have other
databases that work fine in VB and fill out textboxes just fine. I
triple-checked the spelling of the field names, etc., and they all
match.
Can anyone help?

Thanks,

Lynn

:

in message:

Hi Jeff and Doug, this works perfectly! Thank you SO much for your
help.

You're welcome, glad we could help.
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

 

[Douglas J Steele]

Actually, you have succeeded in completely baffling me! <g>

I'm not even sure what you mean by "running the code within VB". I'm
assuming you mean VB6 (some people refer to code in Access as VB, even
though it's actually VBA)

Are you using a Jet database (an MDB) to hold the data, or are you going
against some other DBMS? If you're strictly going against Jet, DAO may make
more sense than ADO, and you can use a query name in the DAO OpenRecordset
method. (DAO is actually still the preferred method for dealing with Jet
databases)

The biggest error I see with your code is that you've named the recordset
myRecordset, yet you're referring to rs![Age]. That should be
myRecordset![Age]

Of course, there are other things that I don't understand about your code:
you instantiate myConnection, but you never open it, so that there's no way
for ADO to know where it's supposed to look for its data!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I named the module something entirely different.

No, I am running the code within VB. I guess that was my main question. I
have it in a "Select" event. I use the SQL statement that the query
generates and I put it in VB after: myRecordset.Open

My Access book says, "You don't have to base a recordset on a single table.
You can base it on a query. But you can't use the query's name in the
myRecordset.Open statement because only table names are allowed." It then
says to get the SQL statement from the query and paste it into VB after the
myRecordset.Open and inbetween the double quotes.

So that's what I did. But then I get that error. I know I am totally
confused here, so I hope I'm not confusing you. One more thing: I am not
putting a user-defined function into the Access query. The query works
perfectly. It generates the person's age by year, month and day. I just
need to translate that into VB so that it will populate the text box.

Thanks again!

When you copied the code for Diff2Dates into your application, what did you
name the module? If you named the module Diff2Dates, rename it: modules
can't be named the same as functions or subs in them.

I'm assuming you're actually running this code from within Access, not from
a VB program. You can't put user-defined functions into Access queries if
you're running them from outside of Access.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug:

Thank you so much for getting back to me. I spent all day yesterday
reading
about Access queries. I program in VB6 and have very little experience in
Access. Here is my code:

Dim myConnection As ADODB.Connection
Set myConnection = New Connection
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection

' Open PatientInfo Query

myRecordset.Open "SELECT PatientInfo.PatientID, PatientInfo.Birthdate,
Diff2Dates('ymd',[Birthdate],Now()) AS Age FROM PatientInfo", dBase,
adOpenDynamic, adLockOptimistic

myRecordset.Filter = "(PatientID) like '" & strPatientID & "*'"
frmPatientInfo.txtAge.Text = rs![Age]

myRecordset.Close

I have the "Open" statement on one line. I set a break at rs![Age] and it
gives me that "Item cannot be found..." error. Can you please tell me
what I
am doing wrong? I'm curious, how does VB know what query to look in? I
see
where it refers to the table, but not the query. Any help would be
greatly
appreciated!

Lynn

:

You'll need to post the code you're trying to use.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My question is similar but it has to do with connecting the database
with
Visual Basic. I built a query to determine a person's age per the
above
instructions. It works perfectly (thank you). I copied the SQL code
and
put it in my VB code after "myRecordset.open." I want it to fill the
textbox
(in VB) with the person's age. Now it gives me the error: "Item
cannot
be
found in the collection corresponding to the recordset." I have other
databases that work fine in VB and fill out textboxes just fine. I
triple-checked the spelling of the field names, etc., and they all
match.
Can anyone help?

Thanks,

Lynn

:

in message:

Hi Jeff and Doug, this works perfectly! Thank you SO much for your
help.

You're welcome, glad we could help.
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

 

[Brendan Reynolds]

In addition to the points that Doug raises, if the book says that, then
perhaps it is the book that is the source of confusion, as that statement
has no basis in reality - you most certainly can use the name of a saved
query with the Open method of an ADO recordset. Perhaps you've just been
reading the wrong book.

A further potential source of confusion is that in your most recent post you
say that you are not using a user-defined function in the Access query, but
the SQL you posted in the previous post includes a call to Doug's
user-defined function 'Diff2Dates'. As Doug says, that will only work within
Access, and I don't think your code is running within Access, as you mention
a 'Select event', and no built-in Access, ADO, or VBA object has a Select
event.

--
Brendan Reynolds
Access MVP

I named the module something entirely different.

No, I am running the code within VB. I guess that was my main question.
I
have it in a "Select" event. I use the SQL statement that the query
generates and I put it in VB after: myRecordset.Open

My Access book says, "You don't have to base a recordset on a single
table.
You can base it on a query. But you can't use the query's name in the
myRecordset.Open statement because only table names are allowed." It then
says to get the SQL statement from the query and paste it into VB after
the
myRecordset.Open and inbetween the double quotes.

So that's what I did. But then I get that error. I know I am totally
confused here, so I hope I'm not confusing you. One more thing: I am not
putting a user-defined function into the Access query. The query works
perfectly. It generates the person's age by year, month and day. I just
need to translate that into VB so that it will populate the text box.

Thanks again!

When you copied the code for Diff2Dates into your application, what did
you
name the module? If you named the module Diff2Dates, rename it: modules
can't be named the same as functions or subs in them.

I'm assuming you're actually running this code from within Access, not
from
a VB program. You can't put user-defined functions into Access queries if
you're running them from outside of Access.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug:

Thank you so much for getting back to me. I spent all day yesterday
reading
about Access queries. I program in VB6 and have very little experience
in
Access. Here is my code:

Dim myConnection As ADODB.Connection
Set myConnection = New Connection
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection

' Open PatientInfo Query

myRecordset.Open "SELECT PatientInfo.PatientID, PatientInfo.Birthdate,
Diff2Dates('ymd',[Birthdate],Now()) AS Age FROM PatientInfo", dBase,
adOpenDynamic, adLockOptimistic

myRecordset.Filter = "(PatientID) like '" & strPatientID & "*'"
frmPatientInfo.txtAge.Text = rs![Age]

myRecordset.Close

I have the "Open" statement on one line. I set a break at rs![Age] and
it
gives me that "Item cannot be found..." error. Can you please tell me
what I
am doing wrong? I'm curious, how does VB know what query to look in?
I
see
where it refers to the table, but not the query. Any help would be
greatly
appreciated!

Lynn

:

You'll need to post the code you're trying to use.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My question is similar but it has to do with connecting the database
with
Visual Basic. I built a query to determine a person's age per the
above
instructions. It works perfectly (thank you). I copied the SQL
code
and
put it in my VB code after "myRecordset.open." I want it to fill
the
textbox
(in VB) with the person's age. Now it gives me the error: "Item
cannot
be
found in the collection corresponding to the recordset." I have
other
databases that work fine in VB and fill out textboxes just fine. I
triple-checked the spelling of the field names, etc., and they all
match.
Can anyone help?

Thanks,

Lynn

:

in message:

Hi Jeff and Doug, this works perfectly! Thank you SO much for
your
help.

You're welcome, glad we could help.
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

 

[Guest]

Doug & Brendan:

I am so sorry, guys! I know so little about Access that I shouldn't even be
posting a question until I know a bit more. I really appreciate your help
and the fact that you didn't give up. This is way over my head, so I will
just drop it for now.

To answer your question, though: I am working in Visual Basic 6. I was
just trying to get the "Age" field from my Access query to populate a text
box in VB6. All my other Access table fields populate my VB6 text boxes just
fine. I think it's because I am working with a query and not a table that
it's not working. Again, I apologize.

Lynn

In addition to the points that Doug raises, if the book says that, then
perhaps it is the book that is the source of confusion, as that statement
has no basis in reality - you most certainly can use the name of a saved
query with the Open method of an ADO recordset. Perhaps you've just been
reading the wrong book.

A further potential source of confusion is that in your most recent post you
say that you are not using a user-defined function in the Access query, but
the SQL you posted in the previous post includes a call to Doug's
user-defined function 'Diff2Dates'. As Doug says, that will only work within
Access, and I don't think your code is running within Access, as you mention
a 'Select event', and no built-in Access, ADO, or VBA object has a Select
event.

--
Brendan Reynolds
Access MVP

I named the module something entirely different.

No, I am running the code within VB. I guess that was my main question.
I
have it in a "Select" event. I use the SQL statement that the query
generates and I put it in VB after: myRecordset.Open

My Access book says, "You don't have to base a recordset on a single
table.
You can base it on a query. But you can't use the query's name in the
myRecordset.Open statement because only table names are allowed." It then
says to get the SQL statement from the query and paste it into VB after
the
myRecordset.Open and inbetween the double quotes.

So that's what I did. But then I get that error. I know I am totally
confused here, so I hope I'm not confusing you. One more thing: I am not
putting a user-defined function into the Access query. The query works
perfectly. It generates the person's age by year, month and day. I just
need to translate that into VB so that it will populate the text box.

Thanks again!

When you copied the code for Diff2Dates into your application, what did
you
name the module? If you named the module Diff2Dates, rename it: modules
can't be named the same as functions or subs in them.

I'm assuming you're actually running this code from within Access, not
from
a VB program. You can't put user-defined functions into Access queries if
you're running them from outside of Access.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug:

Thank you so much for getting back to me. I spent all day yesterday
reading
about Access queries. I program in VB6 and have very little experience
in
Access. Here is my code:

Dim myConnection As ADODB.Connection
Set myConnection = New Connection
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection

' Open PatientInfo Query

myRecordset.Open "SELECT PatientInfo.PatientID, PatientInfo.Birthdate,
Diff2Dates('ymd',[Birthdate],Now()) AS Age FROM PatientInfo", dBase,
adOpenDynamic, adLockOptimistic

myRecordset.Filter = "(PatientID) like '" & strPatientID & "*'"
frmPatientInfo.txtAge.Text = rs![Age]

myRecordset.Close

I have the "Open" statement on one line. I set a break at rs![Age] and
it
gives me that "Item cannot be found..." error. Can you please tell me
what I
am doing wrong? I'm curious, how does VB know what query to look in?
I
see
where it refers to the table, but not the query. Any help would be
greatly
appreciated!

Lynn

:

You'll need to post the code you're trying to use.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My question is similar but it has to do with connecting the database
with
Visual Basic. I built a query to determine a person's age per the
above
instructions. It works perfectly (thank you). I copied the SQL
code
and
put it in my VB code after "myRecordset.open." I want it to fill
the
textbox
(in VB) with the person's age. Now it gives me the error: "Item
cannot
be
found in the collection corresponding to the recordset." I have
other
databases that work fine in VB and fill out textboxes just fine. I
triple-checked the spelling of the field names, etc., and they all
match.
Can anyone help?

Thanks,

Lynn

:

in message:

Hi Jeff and Doug, this works perfectly! Thank you SO much for
your
help.

You're welcome, glad we could help.
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

 

[Brendan Reynolds]

No apology necessary, Lynn. I'd guess every developer has experienced the
difficulty of struggling with an unfamiliar application or technology at
times - I know I have.

--
Brendan Reynolds
Access MVP

Doug & Brendan:

I am so sorry, guys! I know so little about Access that I shouldn't even
be
posting a question until I know a bit more. I really appreciate your help
and the fact that you didn't give up. This is way over my head, so I will
just drop it for now.

To answer your question, though: I am working in Visual Basic 6. I was
just trying to get the "Age" field from my Access query to populate a text
box in VB6. All my other Access table fields populate my VB6 text boxes
just
fine. I think it's because I am working with a query and not a table that
it's not working. Again, I apologize.

Lynn

In addition to the points that Doug raises, if the book says that, then
perhaps it is the book that is the source of confusion, as that statement
has no basis in reality - you most certainly can use the name of a saved
query with the Open method of an ADO recordset. Perhaps you've just been
reading the wrong book.

A further potential source of confusion is that in your most recent post
you
say that you are not using a user-defined function in the Access query,
but
the SQL you posted in the previous post includes a call to Doug's
user-defined function 'Diff2Dates'. As Doug says, that will only work
within
Access, and I don't think your code is running within Access, as you
mention
a 'Select event', and no built-in Access, ADO, or VBA object has a Select
event.

--
Brendan Reynolds
Access MVP

I named the module something entirely different.

No, I am running the code within VB. I guess that was my main
question.
I
have it in a "Select" event. I use the SQL statement that the query
generates and I put it in VB after: myRecordset.Open

My Access book says, "You don't have to base a recordset on a single
table.
You can base it on a query. But you can't use the query's name in the
myRecordset.Open statement because only table names are allowed." It
then
says to get the SQL statement from the query and paste it into VB after
the
myRecordset.Open and inbetween the double quotes.

So that's what I did. But then I get that error. I know I am totally
confused here, so I hope I'm not confusing you. One more thing: I am
not
putting a user-defined function into the Access query. The query works
perfectly. It generates the person's age by year, month and day. I
just
need to translate that into VB so that it will populate the text box.

Thanks again!

:

When you copied the code for Diff2Dates into your application, what
did
you
name the module? If you named the module Diff2Dates, rename it:
modules
can't be named the same as functions or subs in them.

I'm assuming you're actually running this code from within Access, not
from
a VB program. You can't put user-defined functions into Access queries
if
you're running them from outside of Access.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug:

Thank you so much for getting back to me. I spent all day yesterday
reading
about Access queries. I program in VB6 and have very little
experience
in
Access. Here is my code:

Dim myConnection As ADODB.Connection
Set myConnection = New Connection
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection

' Open PatientInfo Query

myRecordset.Open "SELECT PatientInfo.PatientID,
PatientInfo.Birthdate,
Diff2Dates('ymd',[Birthdate],Now()) AS Age FROM PatientInfo", dBase,
adOpenDynamic, adLockOptimistic

myRecordset.Filter = "(PatientID) like '" & strPatientID & "*'"
frmPatientInfo.txtAge.Text = rs![Age]

myRecordset.Close

I have the "Open" statement on one line. I set a break at rs![Age]
and
it
gives me that "Item cannot be found..." error. Can you please tell
me
what I
am doing wrong? I'm curious, how does VB know what query to look
in?
I
see
where it refers to the table, but not the query. Any help would be
greatly
appreciated!

Lynn

:

You'll need to post the code you're trying to use.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


My question is similar but it has to do with connecting the
database
with
Visual Basic. I built a query to determine a person's age per
the
above
instructions. It works perfectly (thank you). I copied the SQL
code
and
put it in my VB code after "myRecordset.open." I want it to fill
the
textbox
(in VB) with the person's age. Now it gives me the error: "Item
cannot
be
found in the collection corresponding to the recordset." I have
other
databases that work fine in VB and fill out textboxes just fine.
I
triple-checked the spelling of the field names, etc., and they
all
match.
Can anyone help?

Thanks,

Lynn

:

in message:

Hi Jeff and Doug, this works perfectly! Thank you SO much for
your
help.

You're welcome, glad we could help.
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html