String Manipulation For List??

D

dynamictiger

Registered User.
Local time
Today, 11:31
Joined
Feb 3, 2002
Messages
270
I am receiving data from an external device into my access application. Whilst the data changes in some ways it is consistant in that the first line returned contains information I need, and the last line contains information I need.

The format returned from the input as a string is similar to below:

2003-09-12 18:08:34
Test No.: 2
Code No.: 100
7.25

How can I use code to manipulate to return the last line value only? Can I or do I need to translate this into Ascii or another format to show the line breaks I assume are there?
 
You can use 1 of 2 methods

1) scan the file directly using "Open" and "line input" commands from VBA
2) Import the file into a temp table and use some query to get it....

I think i would go with suggestion 1, it prevents "bloating" of the DB and i think its faster allthough thougher to program...

Read all about text files and handling of them in the access help on Line Input, Open and Close

Regards
 
Thanks for your input guys. The file is being returned from an instrument, so I really don't want to save it. I am going to have a look at using file system object, as it appears the fastest way to manipulate the data.
 
dynamic,

If the device is hooked up to your pc, is it serial or
parallell? Does it buffer info?

Either way, the easiest way would be to:

Code: 
Dim buffer As String

Close #1
Open "LPT:" For Input as #1 ' Or COM1 ...

Line Input #1, buffer
Me.MyDate = buffer

Line Input #1, buffer ' Ignore
Line Input #1, buffer ' Ignore

Line Input #1, buffer
Me.OtherField = buffer

Close #1

When you capture the info, you could easily insert it
into a table, put it in a form, etc.

Wayne
 
Wayne

Yes it buffers, and it is a serial device. I would like to be able to count the lines and then manipulate the data, is this possible? Sorry to ask six million questions but this area is very new for me, and not really covered in any of the books I have.
 
dynamic,

Not to worry, I wish I had a device like hooked up to some
of my apps, the closest I got were some "intelligent"
barcode readers. Only as intelligent as the ones who
designed the firmware.

Does the data come in with line-terminators. Is there really
a carriage return at the end of each line? Or a carriage-return
line-feed?

Or is it fixed length?

Is it always hooked up, or do they plug it in after they go
collect data somewhere?

Reading and parsing what the device sends should be
pretty easy. What to do with it ...

More info please.

Wayne
 
I have the other bit complete "the what to do with it", had this done for about a year waiting for this device.

I have been playing around with the line input idea and get line overflow errors so I ave converted back to trying to parse the results. The biggest problem I have in this is that the final line is not constant. Although I could ask Germany to make it constant, even using blank characters.

For example:
0.05 mg/l FAC as CL2

and

7.25

Are two examples being sent.:mad:
 
A second thought there is a . in every result there is not anywhere else
 
dynamic,

I'll bet that when you use the Line Input method, it never sees
a carriage-return, so it either errors out, or just waits. I'd
guess that it just waits.

You can open it in Binary mode and read it character by
character. I'll try to post some code that could do that.
If you read what's on that port, char by char, you could
see what they use for delimiters.

2003-09-12 18:08:34Test No.: 2Code No.: 1007.25

I'll bet that they don't send it like above. They might use some
unprintable character as a delimiter.

If you can tell me what you used in your prior open statement
it would help.

Wayne
 
Yes,

You're obviously reading from that thing (We were both
responding at the same time).

What was your open statement, what are you reading, and
what problem(s) are you having parsing it?

Wayne
 
Here is the working bit of the code.

Code: 
Private Sub Form_Open(Cancel As Integer)
'This event sets the sComm1 as the new Scomm
'Connects to comport 1
'Sets the com settings to those recommended by the manufacturer
'Then enables and opens the port
 
 Set sComm1 = New SCommDLL.SComm ' then create it
 
sComm1.ComPort = 1 ' prepare its state

sComm1.ComSettings = "19200,n,8,1" '

sComm1.Enabled = True

sComm1.PortOpen ' try a method

End Sub
Sub sComm1_OnReceive(strBuffer As String)
'This is the receive event this is where the action is
'When SComm1 receives the input this event should
'fire.  The results should be stored in mstrDataContainer
'There are seven lines returned
'We are only interested in the first line and the last line
'From the first line we only need to return the code ident

Dim intIdent As Integer
Dim sngResult As Single

     mstrDataContainer = mstrDataContainer + strBuffer
     
     intIdent = Trim$(Left$(mstrDataContainer, 4))
     
    MsgBox ("Received characters: " & intIdent & " " & mstrDataContainer)

End sub

Here is the output from mstrDataContainer:

100 Chlorine
0.02-6 mg/l Cl2
Profi-Mode: no
2003-09-11 20:11:30
Test No.: 1
Code No.: 0
0.05 mg/l free Cl2


330 pH-value
6.5-8.4
Profi-Mode: no
2003-09-12 18:08:34
Test No.: 2
Code No.: 100
7.25

As you can see the results are on the last line 0.05 and 7.25 in these examples are the values I am chasing. I think this could be manipulated from the string, although this may not be the most efficent way to get the value.
 
I am a bit further along now this seems to work, sort of

Code: 
strResult = Trim$(Right$(Left$(mstrDataContainer, intN + 2), 5))

Problem is that it is returning a string that looks like:

strResult =
7.25

For example.

I have tried Trim and other variations to remove this space line feed or whatever it is but have not been able to. Any suggestions?
 
D,

This will let you loop thru the container and see what any
"non-printables" may be. Let me know.

Code: 
Dim ptr As Long
For ptr = 1 to Len(mstgrDataContainer)
    MsgBox("The char is [" & Mid(mstgrDataContainer, ptr, 1) & _
                 "] value: " & Asc(Mid(mstgrDataContainer, ptr, 1)))
   Next Ptr

Wayne
 
Chr(10) and Chr(13) is what it is. I must be returning too much data. Trimmed statement up and it returns 1.61 fine, I now need to make sure it can return 10.00 as well.
 
D,

The 10 & 13 are carriage-return and line-feed. By the way
did you happen to catch the length of the entire transmission.

And I see that you had a Form_Open event (Access), but where
did that code come from in sComm1_receive? Did the
vendor supply it. I just wondered how it hooked into Access.
Maybe a timer.

Wayne
 
You must log in or register to reply here.

Similar threads

N
Solved Getting the key and value from Json collection with VBA Microsoft Access
Replies
9
Views
397
nectorch
N
S
Solved Second SQl delete query raises runtime error 424
Replies
8
Views
190
Pat Hartman
P
F
VBA Webpage search
Replies
3
Views
243
theDBguy
theDBguy
B
Retrieve 6 numbers from string
Replies
2
Views
571
arnelgp
arnelgp
E
Is there any .ocx in Access to open Com Port (VBA)
Replies
2
Views
164
sonic8
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom