Well I get some data that they need imported into access.
I am sure that anyone who has done this knows what I am going to ask.

yes blue means ROFL
and the red cells are not finished yet

I am not lying about this one
a double precision number field

15.9 4/5 HUH

well its 15.9 and four fifths
again HUH four fifths of what?

I just didn't understand I guess


anyway its friday and I just had to laugh and ask a question

on one of the fields its text...OK they are all text LOL

but I have something like this
ID desc
1 rift/counter

I need to put this in a seperate little table for a continuous field
to make a subform.

so I need to put this in the table as
1 rift
1 counter

can this be done easily in a query or should I just code it and have
the code make the the records as I cut up the string?

I am guessing that you were able to import all the contents of your
excel sheet into a table in Access, and it imported as you say - in text
format.

If this is the case, and you were able to import all of the data then
what you will need to do to get it into a usable format is to parse out
all text. If we are talking 50 records of 10 fields or less - you may
be better off just doing it by hand. But if it is hundreds of records
with dozens of fields you will have to write some code. Or -- if it is
just a few records but you want to practice writing some code - you can
do that too.

To write the code you will have to note all the patterns of text. It
looks like spaces and forward slashes will be your flags for where/what
to parse. Use the InStr, Mid functions for the parsing. Can try
something like the following (this is just a sample to give you some
ideas on string parsing - you will have to modify it as you need):

I will name your import table as ImportTbl, and you will parse the
fields into a table called DestinationTbl. Note: determine which
fields you need to parse. Maybe you don't need to parse all the fields.
In this sample I will assume all fields need to be parsed.

Sub ParseFields()
Dim RS1 As DAO.Recordset, RS2 As DAO.Recordset
Dim str1 As String, str2 As String, str3 As String
Dim bIsNumber As Boolean
Dim i As Integer, j As Integer, k As Integer
Set RS1 = CurrentDB.OpenRecordset("ImportTbl")
Set RS2 = CurrentDB.OpenRecordset("DestinationTbl")
Do While Not RS1.EOF
RS2.AddNew
For i = 0 To RS1.Fields.Count - 1
str1 = RS1(i)
'--check for digits in str1
For j = 1 To Len(str1)
bIsNumber = False
str2 = Mid(str1, j, 1)
If Asc(str2) >= 48 and Asc(str2) <= 57 Then
bIsNumber = True
str3 = str3 & str2
Else
bIsNumber = False
Exit For
End If
If bIsNumber = True Then
RS2(i) = CInt(str3)
Else
RS2(i) = str1
End If
Next
Next
RS2.Update
RS1.MoveNext
Loop

End Sub




Rich

*** Sent via Developersdex http://www.developersdex.com ***