Combine Text Boxes

[Biss]

Hi I am using 2007.

I have a a form that is based on a table.

We import into the table data from excel file

In the table we have these fields that are relevant to my question.

[DOB]
[DOBDay]
[DOBMn]
[DobYr]

When we import the data the following fields have data in them.

[DOBDay]
[DOBMn]
[DobYr]

What I would like to do is on the form that corresponds to this table is
when the form is opened the data in these fields, text boxes [DOBDay],
[DOBMn],[DobYr] are combined in the text box [DOB] and stored in the field
[DOB].

I have tried this in various places as well as event procedures and have had
no results.

=[DOBDay] & "-" & [DOBMn] & "-" & [DOBYr]

Once I have that data I would like to have text box to display the age.. The
age does not need to be stored in the table..

I know that this should be simple but just cant get my hear around it.

Thanks in advance

Bob

 

[Jeff Boyce]

Biss

Access offers a Date/Time datatype that you could use to store an actual
date.

One possibility is to use the DateSerial() function to generate that actual
date, based on the Day, Month & Year values.

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Biss]

Thanks I will give that a shot..

But what I am trying to do is combine the 3 text boxes..

Also where would I put the DateSerial() function.

Biss

Access offers a Date/Time datatype that you could use to store an actual
date.

One possibility is to use the DateSerial() function to generate that
actual date, based on the Day, Month & Year values.

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi I am using 2007.

I have a a form that is based on a table.

We import into the table data from excel file

In the table we have these fields that are relevant to my question.

[DOB]
[DOBDay]
[DOBMn]
[DobYr]

When we import the data the following fields have data in them.

[DOBDay]
[DOBMn]
[DobYr]

What I would like to do is on the form that corresponds to this table is
when the form is opened the data in these fields, text boxes [DOBDay],
[DOBMn],[DobYr] are combined in the text box [DOB] and stored in the
field [DOB].

I have tried this in various places as well as event procedures and have
had no results.

=[DOBDay] & "-" & [DOBMn] & "-" & [DOBYr]

Once I have that data I would like to have text box to display the age..
The age does not need to be stored in the table..

I know that this should be simple but just cant get my hear around it.

Thanks in advance

Bob

 

[Jeff Boyce]

Biss

Create a new query in design view. Add the table. Add the three fields.

Create a new field (e.g., [MyDate]) with something like (untested):

MyDate: DateSerial([YourYearField], [YourMonthField], [YourDayField])

This should generate a date/time value from the three fields you start with.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Thanks I will give that a shot..

But what I am trying to do is combine the 3 text boxes..

Also where would I put the DateSerial() function.

Biss

Access offers a Date/Time datatype that you could use to store an actual
date.

One possibility is to use the DateSerial() function to generate that
actual date, based on the Day, Month & Year values.

Good luck

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi I am using 2007.

I have a a form that is based on a table.

We import into the table data from excel file

In the table we have these fields that are relevant to my question.

[DOB]
[DOBDay]
[DOBMn]
[DobYr]

When we import the data the following fields have data in them.

[DOBDay]
[DOBMn]
[DobYr]

What I would like to do is on the form that corresponds to this table is
when the form is opened the data in these fields, text boxes [DOBDay],
[DOBMn],[DobYr] are combined in the text box [DOB] and stored in the
field [DOB].

I have tried this in various places as well as event procedures and have
had no results.

=[DOBDay] & "-" & [DOBMn] & "-" & [DOBYr]

Once I have that data I would like to have text box to display the age..
The age does not need to be stored in the table..

I know that this should be simple but just cant get my hear around it.

Thanks in advance

Bob

 

[John W. Vinson]

Hi I am using 2007.

I have a a form that is based on a table.

We import into the table data from excel file

In the table we have these fields that are relevant to my question.

[DOB]
[DOBDay]
[DOBMn]
[DobYr]

When we import the data the following fields have data in them.

[DOBDay]
[DOBMn]
[DobYr]

What I would like to do is on the form that corresponds to this table is
when the form is opened the data in these fields, text boxes [DOBDay],
[DOBMn],[DobYr] are combined in the text box [DOB] and stored in the field
[DOB].

Try putting the following code in the Form's Current event (which fires
whenever you move to another record):

Private Sub Form_Current()
If IsNull(Me!DOB) Then ' don't overwrite an existing date
' check to see if there is data to calculate a date
If Not(IsNull(Me!DOBDay) Or IsNull(Me!DOBMn) Or IsNull(Me!DOBYr)) Then
Me!DOB = DateSerial(Me!DOBYr, Me!DOBMn, Me!DOBDay)
End If
End If
End Sub

This assumes that the three fields are numeric, and correspond to valid dates
(e.g. DOBYr would have a number like 1946, DOBMn 5, DOBDay 16, or the like).
DateSerial will calculate a date even if the month is outside the range 1-12
or DOBDay outside the number of days in that month, but it may not be the date
that you want - you might want to put in some validity check and warn the user
that there was no 14th month in 1954, and no 31st day in February 1981!

To calculate the age, set the Control Source of a textbox on the form to

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(),
"mmdd", 1, 0)

all on one line.
--

John W. Vinson [MVP]

I have tried this in various places as well as event procedures and have had
no results.

=[DOBDay] & "-" & [DOBMn] & "-" & [DOBYr]

Once I have that data I would like to have text box to display the age.. The
age does not need to be stored in the table..

I know that this should be simple but just cant get my hear around it.

Thanks in advance

Bob

 

[Biss]

Hi John,

Many thanks but this calculates the age in correctly
=DateDiff("yyyy", [DOB], Date())

If I have DOB of 07-Jun-85
Then the age should be 24 but the result of 25 is returned.

When I add this
- IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)

I receive an error that says that I am missing a [ or |

Thanks

Bob

To calculate the age, set the Control Source of a textbox on the form to

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
Format(Date(), "mmdd", 1, 0) all on one line.
--

John W. Vinson [MVP]

I have tried this in various places as well as event procedures and have
had
no results.

=[DOBDay] & "-" & [DOBMn] & "-" & [DOBYr]

Once I have that data I would like to have text box to display the age..
The
age does not need to be stored in the table..

I know that this should be simple but just cant get my hear around it.

Thanks in advance

Bob

 

[John W. Vinson]

Hi John,

Many thanks but this calculates the age in correctly
=DateDiff("yyyy", [DOB], Date())

If I have DOB of 07-Jun-85
Then the age should be 24 but the result of 25 is returned.

When I add this
- IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)

I receive an error that says that I am missing a [ or |

Sorry, typo on my part. The entire expression - all on one line - should be

=DateDiff("yyyy", [DOB], Date()) -
IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd"), 1, 0)

The DateDiff function itself returns the number of year changes between the
dates - so a baby born December 30 2009 would be shown as a year old on
January 2 2010; the IIF statement (if you put all the parentheses in it, which
I didn't) will subtract 1 if the birthday anniversary has not yet been reached
this year.

 

[Biss]

John,, I am sorry

But I still get the same error.

Old expression

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd"), 1, 0)

New Expressoin

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)

Same error missin [ or |

Sorry for the bother but I can seem to figure our what is missing.. If I use builder I come up with same as you do.. A real head scratcher.

sorry but thanks for the help

Bob

Hi John,

Many thanks but this calculates the age in correctly
=DateDiff("yyyy", [DOB], Date())

If I have DOB of 07-Jun-85
Then the age should be 24 but the result of 25 is returned.

When I add this
- IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)

I receive an error that says that I am missing a [ or |

Sorry, typo on my part. The entire expression - all on one line - should be

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd"), 1, 0)
=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)


The DateDiff function itself returns the number of year changes between the
dates - so a baby born December 30 2009 would be shown as a year old on
January 2 2010; the IIF statement (if you put all the parentheses in it, which
I didn't) will subtract 1 if the birthday anniversary has not yet been reached
this year.

 

[John W. Vinson]

John,, I am sorry

But I still get the same error.

Old expression

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd"), 1, 0)

New Expressoin

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") < Format(Date(), "mmdd", 1, 0)

Same error missin [ or |

Sorry for the bother but I can seem to figure our what is missing.. If I use builder I come up with same as you do.. A real head scratcher.

Hrm. The Old expression looks correct! What is the context? I was assuming
that this was in the Control Source property of a Textbox on a form (or
report). Is it? Or is it someplace else? Do you perhaps have a field or a
control named Date (which would cause conflicts with the Date() function)?

 

[Biss]

John

The old expression works now for some reason while it did not the other day

Old expression

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
Format(Date(), "mmdd"), 1, 0)

But It stills returns wrong age..

DOB of 07-Jun-85 I get 25 years old.

Yes it is a text box on a form

John,, I am sorry

But I still get the same error.

Old expression

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
Format(Date(), "mmdd"), 1, 0)

New Expressoin

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") <
Format(Date(), "mmdd", 1, 0)

Same error missin [ or |

Sorry for the bother but I can seem to figure our what is missing.. If I
use builder I come up with same as you do.. A real head scratcher.

Hrm. The Old expression looks correct! What is the context? I was assuming
that this was in the Control Source property of a Textbox on a form (or
report). Is it? Or is it someplace else? Do you perhaps have a field or a
control named Date (which would cause conflicts with the Date() function)?

 

[John W. Vinson]

But It stills returns wrong age..

DOB of 07-Jun-85 I get 25 years old.

<blush> Silly error on my part - should be > not <:

=DateDiff("yyyy", [DOB], Date()) -
IIF(Format([DOB], "mmdd") > Format(Date(), "mmdd"), 1, 0)

 

[Biss]

John..

Many thanks for your patience..

Bob

Many thanks for all of your patience

But It stills returns wrong age..

DOB of 07-Jun-85 I get 25 years old.

<blush> Silly error on my part - should be > not <:

=DateDiff("yyyy", [DOB], Date()) -
IIF(Format([DOB], "mmdd") > Format(Date(), "mmdd"), 1, 0)