Combine Text Boxes
in [Form]
|
From: Biss on 19 Jan 2010 16:37 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
From: Jeff Boyce on 19 Jan 2010 17:54 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" <raleygroupky(a)hotmail.com> wrote in message news:57C48A38-24F2-4BF7-BA79-E2BE296588A9(a)microsoft.com... > 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 > >
From: Biss on 19 Jan 2010 18:46 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" <nonsense(a)nonsense.com> wrote in message news:u7#tUpVmKHA.2160(a)TK2MSFTNGP02.phx.gbl... > 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" <raleygroupky(a)hotmail.com> wrote in message > news:57C48A38-24F2-4BF7-BA79-E2BE296588A9(a)microsoft.com... >> 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 >> >> > >
From: Jeff Boyce on 19 Jan 2010 20:01 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" <raleygroupky(a)hotmail.com> wrote in message news:D9CC14F4-8744-4FB4-8069-E9B5275272AB(a)microsoft.com... > 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" <nonsense(a)nonsense.com> wrote in message > news:u7#tUpVmKHA.2160(a)TK2MSFTNGP02.phx.gbl... >> 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" <raleygroupky(a)hotmail.com> wrote in message >> news:57C48A38-24F2-4BF7-BA79-E2BE296588A9(a)microsoft.com... >>> 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 >>> >>> >> >>
From: John W. Vinson on 19 Jan 2010 22:15 On Tue, 19 Jan 2010 16:37:23 -0500, "Biss" <raleygroupky(a)hotmail.com> 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 >
|
Next
|
Last
Pages: 1 2 3 Prev: new navigation control Access 2010beta Next: Textbox Character Counter |