Strings: Parsing data into other fields in the same table

G

Guest

I am making a simple database and I was wondering how can
the information from a field containing strings of both
numbers and characters be broken and entered other
fields. I am dealing with a code that looks like this:
01MP, and has four parts. The first number refers to the
year 2000, the second number refers the season of the
year (spring), the third to type of media (a magazine),
and the fourth the genre (politics). I have a field for
the code and fields for each of the four parts but I was
wondering how I could automate the entry of the four
extra fields as the data is redundant.
 
T

Tim Ferguson

I have a field for
the code and fields for each of the four parts but I was
wondering how I could automate the entry of the four
extra fields as the data is redundant.

You would be better off having four fields for the parts and getting rid of
the four-char field altogether. It is easy enough to populate the four
short ones from existing data -- this is a single update query (see help
for details) using a Mid() function (ditto help). It is easy enough to
create a text box to display the code as a single string (use the
ControlSource property) on a form or report.

The trick is to develop a smooth and idiot-proof method of allowing your
users to enter it. If they will go for four controls (eg textboxes,
dropdowns, or whatever) then your problems are over. If they insist on
entering four bits of information into one box then you'll have to do a
little bit of VBA trickery to validate it and then chop it up. This kind of
might work

' the txtCode control is an Unbound Text Box. You can fill
' it in for existing records using the Current event
'
private sub txtCode_BeforeUpdate(Cancel as Integer)

if mid(txtCode,1,1) in ("0", "1", "2", "3", "4", "5") Then
me!yeardigit = CInt(mid(txtCode(1,1)))

else
' no good value
Cancel = True

end if

if mid(txtCode,2,1) in ("1", "2", "3", "4") Then
me!seasondigit = CInt(mid(txtCode(1,1)))

else
' no good value
Cancel = True

end if

if ' etc, checking the other two digits
end if

if Cancel = True then
' warn the user
msgbox "That code is no good!"
end if

' setting the Cancel flag will prevent user from moving off, so
' you don't have to do anything else
End Sub


Hope that helps


Tim F
 
G

Guest

Thanks for your help! I have one more question though
which you seemed to note. What do I do if for the code
isn't consistently four characters. What if two fields
fluctuate between having 1-2 and 3-4 characters
respectively?
-----Original Message-----
I hope I am understanding your question correctly. Anyway, the following
will work if your field is consistently four
characters. On the After Update
event of your code field put the following:
Me.CodeYear = Left(
Code:
,1)
Me.CodeSeason = Mid([Code],2,1)
Me.CodeMedia = Mid([Code],3,1)
Me.CodeGenre = Right([Code],1)

This will separate that four digit field into four different fields (you
will have to use your own field names).

Hope this helps.


[QUOTE="[email protected]"]
I am making a simple database and I was wondering how can
the information from a field containing strings of both
numbers and characters be broken and entered other
fields. I am dealing with a code that looks like this:
01MP, and has four parts. The first number refers to the
year 2000, the second number refers the season of the
year (spring), the third to type of media (a magazine),
and the fourth the genre (politics). I have a field for
the code and fields for each of the four parts but I was
wondering how I could automate the entry of the four
extra fields as the data is redundant.
[/QUOTE]
.
[/QUOTE]
 

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.

Ask a Question

Top