J
Jack M
Is there a way to take a number used in one field, drop the last three digits
and place them in the next field?
and place them in the next field?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
BruceM
You are talking about a calculation, and there is probably no need to store
it.
Without knowing anything about the purpose of the number or whether it is a
text or a number field I can suggest only a general approach. In the After
Update event for the text box bound to the original number field you could
place the following code:
Me.txtShortenedNumber = Left((Me.NumberField), Nz(Len(Me.NumberField) - 3))
The same code would probably be placed in the form's Current event, with
some additional lines to account for a NumberField with no value:
If Len(Me.NumberField) <= 3 Then
Me.txtShortenedNumber = ""
Else
Me.txtShortenedNumber = Left((Me.NumberField),
Nz(Len(Me.NumberField) - 3))
End If
The first line of code could also be If Me.NewRecord Then
A few questions you may need to answer if you need more than the above:
Does your number have leading zeros? Will it always be over three digits
(or characters) long? Is there a default value? Does it enter into
calculations?
it.
Without knowing anything about the purpose of the number or whether it is a
text or a number field I can suggest only a general approach. In the After
Update event for the text box bound to the original number field you could
place the following code:
Me.txtShortenedNumber = Left((Me.NumberField), Nz(Len(Me.NumberField) - 3))
The same code would probably be placed in the form's Current event, with
some additional lines to account for a NumberField with no value:
If Len(Me.NumberField) <= 3 Then
Me.txtShortenedNumber = ""
Else
Me.txtShortenedNumber = Left((Me.NumberField),
Nz(Len(Me.NumberField) - 3))
End If
The first line of code could also be If Me.NewRecord Then
A few questions you may need to answer if you need more than the above:
Does your number have leading zeros? Will it always be over three digits
(or characters) long? Is there a default value? Does it enter into
calculations?
J
Jack M
I think you missed my issue. I want to lose the last three digits. This is a
family database that uses a UID and a birth-order composite number to
identify a record holder. The reason for the UID does not need to enter here,
but the birth-order composite helps me with a <i>family tree</I> issue. Each
generation has a three digit birth-order number. When I put the birth-order
of the parents, grandparents, etc. together into a composite (via an
expression), then I have the birthline of the record owner back to his/her
origin in the family db. This is not a calculation, and the result is needed
in its own field.
The purpose for truncating the birth-order composite is to store the full
composite number applying only to the record owner's parent, etc into a
separate field. That field will help later in printing out the actual blood
line of the record owner.
Jack
family database that uses a UID and a birth-order composite number to
identify a record holder. The reason for the UID does not need to enter here,
but the birth-order composite helps me with a <i>family tree</I> issue. Each
generation has a three digit birth-order number. When I put the birth-order
of the parents, grandparents, etc. together into a composite (via an
expression), then I have the birthline of the record owner back to his/her
origin in the family db. This is not a calculation, and the result is needed
in its own field.
The purpose for truncating the birth-order composite is to store the full
composite number applying only to the record owner's parent, etc into a
separate field. That field will help later in printing out the actual blood
line of the record owner.
Jack
J
Jack M
See my response to what you wrote below, intertwined with your response.
<b>No, this is not a calculation, but a truncation, dropping the last three
digits of a birth-order composite number in a family database. This is a
special identification of record owners in the family db that has a different
purpose than the UID. Each record owner has a special birth-order rank
expressed in a three digit number beginning with 001 within his/her family of
origin. There is also a birth-order composite that combines the birth-rank of
each previous generation.
Truncating the composite by dropping the last three digits will allow me to
directly identify the record owner's parent in the blood line. That new
number identifying the parent needs to be stored in a new field in a query.
Then comes the grandparent, with the same truncation need.
<b>Whoa!
I'm pretty uninformed as to these codes you mention. I'm sure they are
helpful, but I'm not really a word thinker -- a picture thinker, in fact.
When words and codes are needed, I need more background to make the picture
so I can understand the words. (You might find it interesting how much
trouble I had going from an industrial world into a psychology degree where
the words often had a totally different meaning. Imagine then, when I went
into theology where the meanings were kind of the same, but applied to a
totally different concept.) Then, again, my point in this paragraph may be a
total bore to you.
<b>No, there is no leading zero on the composite. The original <i>parent</i>
in the blood line will always be 100.</b>
Jack
<br>BruceM said:You are talking about a calculation, and there is probably no need to store
it.
<b>No, this is not a calculation, but a truncation, dropping the last three
digits of a birth-order composite number in a family database. This is a
special identification of record owners in the family db that has a different
purpose than the UID. Each record owner has a special birth-order rank
expressed in a three digit number beginning with 001 within his/her family of
origin. There is also a birth-order composite that combines the birth-rank of
each previous generation.
Truncating the composite by dropping the last three digits will allow me to
directly identify the record owner's parent in the blood line. That new
number identifying the parent needs to be stored in a new field in a query.
Then comes the grandparent, with the same truncation need.
<BR>Does that make better sense? said:Without knowing anything about the purpose of the number or whether it is a
text or a number field I can suggest only a general approach. In the After
Update event for the text box bound to the original number field you could
place the following code:
Me.txtShortenedNumber = Left((Me.NumberField), Nz(Len(Me.NumberField) - 3))
<b>Whoa!
I'm pretty uninformed as to these codes you mention. I'm sure they are
helpful, but I'm not really a word thinker -- a picture thinker, in fact.
When words and codes are needed, I need more background to make the picture
so I can understand the words. (You might find it interesting how much
trouble I had going from an industrial world into a psychology degree where
the words often had a totally different meaning. Imagine then, when I went
into theology where the meanings were kind of the same, but applied to a
totally different concept.) Then, again, my point in this paragraph may be a
total bore to you.
<br>You may just have to write more slowly for me. ;-) said:The same code would probably be placed in the form's Current event, with
some additional lines to account for a NumberField with no value:
If Len(Me.NumberField) <= 3 Then
Me.txtShortenedNumber = ""
Else
Me.txtShortenedNumber = Left((Me.NumberField),
Nz(Len(Me.NumberField) - 3))
End If
The first line of code could also be If Me.NewRecord Then
A few questions you may need to answer if you need more than the above:
Does your number have leading zeros? Will it always be over three digits
(or characters) long? Is there a default value? Does it enter into
calculations?
<b>No, there is no leading zero on the composite. The original <i>parent</i>
in the blood line will always be 100.</b>
Jack
B
BruceM
My responses, in turn, are inline below. By the way, you seem to be using
html e-mail, which is probably why the html tags such as <b> and </b> are
showing up in the message. See your e-mail's Help to find out how to send
in Plain Text.
It is a calculation in that one value is being derived from another. A
calculation in Access is not necessarily a mathematical operation.
You seem to be on the right track. A query is another way of producing the
number, which will not be stored in the query as such, but rather calculated
on the fly, which is as it should be. A query organizes data, is does not
directly store them.
If I understand correctly, three digits are added for each generation. Will
you want to be able to go directly to the parent's record based on the
truncated value? In other words, would you like to click a button to take
you directly to the parent's record, or something like that? There are some
tried and true methods for keeping genealogy information, but I am not
really familiar with the options. I could describe how to make a command
button work as I mentioned, but I doubt it's the best way. I would suggest
that after taking what you can from this thread that you start a new thread
focused on navigating through a family tree, if that is what you need to do.
Among the potential difficulties I am not prepared to discuss are such
things as the fact that a person may well be both a parent and a child.
I was a bit cryptic there. The idea is that you would place an unbound text
box on the form, and name it txtShortenedNumber. To create an unbound text
box, just click on the Text Box icon in the toolbox, and draw a text box on
your form. NumberField is the table field containing the long (untruncated)
number. A text box bound to that field could be named txtNumberField. If
you look up Len and Left in Help you will get a better idea of what I have
done with the code. To place the code into the After Update event of
txtNumberField, right click the text box, click Properties, click the Events
tab, click After Update, click the three dots, click Code Builder, and
finally click OK. Place the code between Private Sub ... and End Sub. To
place code in the form's Current event, click anywhere on a blank place on
the form, then click View > Properties and proceed as described for the text
box, except that you will click the Current event rather than Before Update.
To use a query to get the same results, in a query design grid place
something like this at the top of an empty column:
TruncatedNumber:
IIf(Len([NumberField])<=3,"",Left([NumberField],Len([NumberField])-3))
html e-mail, which is probably why the html tags such as <b> and </b> are
showing up in the message. See your e-mail's Help to find out how to send
in Plain Text.
Jack M said:See my response to what you wrote below, intertwined with your response.
<br>
<b>No, this is not a calculation, but a truncation, dropping the last
three
digits of a birth-order composite number in a family database. This is a
special identification of record owners in the family db that has a
different
purpose than the UID. Each record owner has a special birth-order rank
expressed in a three digit number beginning with 001 within his/her family
of
origin. There is also a birth-order composite that combines the birth-rank
of
each previous generation.
It is a calculation in that one value is being derived from another. A
calculation in Access is not necessarily a mathematical operation.
Truncating the composite by dropping the last three digits will allow me
to
directly identify the record owner's parent in the blood line. That new
number identifying the parent needs to be stored in a new field in a
query.
You seem to be on the right track. A query is another way of producing the
number, which will not be stored in the query as such, but rather calculated
on the fly, which is as it should be. A query organizes data, is does not
directly store them.
Then comes the grandparent, with the same truncation need.
Does that make better sense?</b>
<br>
If I understand correctly, three digits are added for each generation. Will
you want to be able to go directly to the parent's record based on the
truncated value? In other words, would you like to click a button to take
you directly to the parent's record, or something like that? There are some
tried and true methods for keeping genealogy information, but I am not
really familiar with the options. I could describe how to make a command
button work as I mentioned, but I doubt it's the best way. I would suggest
that after taking what you can from this thread that you start a new thread
focused on navigating through a family tree, if that is what you need to do.
Among the potential difficulties I am not prepared to discuss are such
things as the fact that a person may well be both a parent and a child.
<BR>
<b>Whoa!
I was a bit cryptic there. The idea is that you would place an unbound text
box on the form, and name it txtShortenedNumber. To create an unbound text
box, just click on the Text Box icon in the toolbox, and draw a text box on
your form. NumberField is the table field containing the long (untruncated)
number. A text box bound to that field could be named txtNumberField. If
you look up Len and Left in Help you will get a better idea of what I have
done with the code. To place the code into the After Update event of
txtNumberField, right click the text box, click Properties, click the Events
tab, click After Update, click the three dots, click Code Builder, and
finally click OK. Place the code between Private Sub ... and End Sub. To
place code in the form's Current event, click anywhere on a blank place on
the form, then click View > Properties and proceed as described for the text
box, except that you will click the Current event rather than Before Update.
I'm pretty uninformed as to these codes you mention. I'm sure they are
helpful, but I'm not really a word thinker -- a picture thinker, in fact.
When words and codes are needed, I need more background to make the
picture
so I can understand the words. (You might find it interesting how much
trouble I had going from an industrial world into a psychology degree
where
the words often had a totally different meaning. Imagine then, when I went
into theology where the meanings were kind of the same, but applied to a
totally different concept.) Then, again, my point in this paragraph may be
a
total bore to you.
To use a query to get the same results, in a query design grid place
something like this at the top of an empty column:
TruncatedNumber:
IIf(Len([NumberField])<=3,"",Left([NumberField],Len([NumberField])-3))
J
John Vinson
Is there a way to take a number used in one field, drop the last three digits
and place them in the next field?
You can use an Update query to split the number: to extract the last
three digits from a numeric field use the Mod() function. E.g. you can
update the SeqNo field to
Mod([ID], 1000)
If the field is of Text type use the Right() function instead:
Right([ID], 3)
Do note that your current design, storing two disparate pieces of
information in the single ID, violates the principle that fields
should be "atomic". If you have a Family ID and a sequence number,
consider storing them in separate fields; note that a Primary Key can
consist of up to ten fields, so there is no strong need to create this
composite field!
John W. Vinson[MVP]
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.