A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Strings: Parsing data into other fields in the same table



 
 
Thread Tools Display Modes
  #1  
Old August 10th, 2004, 09:27 AM
external usenet poster
 
Posts: n/a
Default Strings: Parsing data into other fields in the same table

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.
  #2  
Old August 10th, 2004, 02:35 PM
Jackie L
external usenet poster
 
Posts: n/a
Default Strings: Parsing data into other fields in the same table

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.


" wrote:

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.

  #3  
Old August 10th, 2004, 02:39 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default Strings: Parsing data into other fields in the same table

wrote in
:

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


  #4  
Old August 13th, 2004, 03:15 AM
external usenet poster
 
Posts: n/a
Default Strings: Parsing data into other fields in the same table

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.


" wrote:

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.

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to extract original data from a pivot table? cmagnus98 General Discussion 1 July 9th, 2004 03:55 PM
Trying to break up data in the label format to fields, for easy entry into table Visali Mailmerge 1 June 7th, 2004 10:35 PM
Combining data from multiple fields into Pivot table Os Worksheet Functions 5 June 1st, 2004 05:04 PM
HELP: Update 1 table with calculated fields from another table Samora Running & Setting Up Queries 0 May 23rd, 2004 05:16 PM
Mial merge data base problems Rachael Mailmerge 16 May 21st, 2004 06:22 PM


All times are GMT +1. The time now is 10:45 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.