If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combine Text Boxes
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 |
#2
|
|||
|
|||
Combine Text Boxes
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" wrote in message ... 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 |
#3
|
|||
|
|||
Combine Text Boxes
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. "Jeff Boyce" wrote in message ... 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" wrote in message ... 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 |
#4
|
|||
|
|||
Combine Text Boxes
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. "Biss" wrote in message news 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. "Jeff Boyce" wrote in message ... 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" wrote in message ... 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 |
#5
|
|||
|
|||
Combine Text Boxes
On Tue, 19 Jan 2010 16:37:23 -0500, "Biss" wrote:
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 |
#6
|
|||
|
|||
Combine Text Boxes
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 |
#7
|
|||
|
|||
Combine Text Boxes
On Tue, 19 Jan 2010 23:55:41 -0500, "Biss" wrote:
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. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Combine Text Boxes
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 "John W. Vinson" wrote in message ... On Tue, 19 Jan 2010 23:55:41 -0500, "Biss" wrote: 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 [MVP] |
#9
|
|||
|
|||
Combine Text Boxes
On Wed, 20 Jan 2010 15:48:39 -0500, "Biss" wrote:
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 [MVP] |
#10
|
|||
|
|||
Combine Text Boxes
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 W. Vinson" wrote in message ... On Wed, 20 Jan 2010 15:48:39 -0500, "Biss" wrote: 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 [MVP] |
|
Thread Tools | |
Display Modes | |
|
|