Truncate Until <> ""

[Intui_Sol]

Hello,
I was wondering if someone could help me with this problem.
I am importing a text file into a table. The data that I need is in
the first line of the text file and the data below it is delimited
differently than the first line so I cannot use delimit it(totals are
on top). Luckily the docmd.transfertext imports the data so that the
first line is the first record in the table.
The data I need resides within a 13 character field followed by a 5
character field.
For example the first line in the text file looks like this:

542205082056238954123 11070.00 50000.00 100

Starting from right to left; the 100 is the record count and the
50000.00 is currency. The problem I am having is getting that
currency in a string or integer.

I have the following code:

DoCmd.OpenTable tablename:="TableName"
DoCmd.GoToControl ("ID") '
Data I need is in first record
var1 = DLookup("Field1", "TableName", "ID =1") ' Field1 is the
entire line of the text file
DoCmd.Close acTable, objectname:="TableName"

var2 = Left(Right(var1, 18), 13)

I can only get 50000.00

Does anyone know how to truncate the first 5 characters knowing that
the number could change? (meaning that i might need to truncate only 4
or 6, not always 5)

I wanted to do something like:

for i = 1 to 13
do until left(Left(Right(var1, 18), 13),i) <> ""
i = i +1
next i
loop
var3 = left(Left(Right(var1, 18), 13),i)

 

[fredg]

Hello,
I was wondering if someone could help me with this problem.
I am importing a text file into a table. The data that I need is in
the first line of the text file and the data below it is delimited
differently than the first line so I cannot use delimit it(totals are
on top). Luckily the docmd.transfertext imports the data so that the
first line is the first record in the table.
The data I need resides within a 13 character field followed by a 5
character field.
For example the first line in the text file looks like this:

542205082056238954123 11070.00 50000.00 100

Starting from right to left; the 100 is the record count and the
50000.00 is currency. The problem I am having is getting that
currency in a string or integer.

I have the following code:

DoCmd.OpenTable tablename:="TableName"
DoCmd.GoToControl ("ID") '
Data I need is in first record
var1 = DLookup("Field1", "TableName", "ID =1") ' Field1 is the
entire line of the text file
DoCmd.Close acTable, objectname:="TableName"

var2 = Left(Right(var1, 18), 13)

I can only get 50000.00

Does anyone know how to truncate the first 5 characters knowing that
the number could change? (meaning that i might need to truncate only 4
or 6, not always 5)

I wanted to do something like:

for i = 1 to 13
do until left(Left(Right(var1, 18), 13),i) <> ""
i = i +1
next i
loop
var3 = left(Left(Right(var1, 18), 13),i)

No need to open a table if you are using DLookUp.
No need for var2 or var3. No need to use a Do .... Loop.

It's always the last 18 characters you need to work with regardless of
the number of preceding spaces you wish to remove?

Function TrimSpaces() as String
Dim var1 as String
var1 = DLookup("Field1", "TableName", "ID =1")
var1 = Trim(Right(var1, 18))
var1 = Left(var1,InStr(var1," ")-1)
TrimSpaces = var1
End Functrion

You could call it from the control source of an unbound control:
=TrimSpaces()

 

[Guest]

Why trim spaces? If you are going to convert it to a
number, the spaces doesn't matter - the conversion
will work correctly even with spaces.

I can only get 50000.00

Trim(" 50000.00 ")
"50000.00"

Val(" 50000.00 ")
50000

 

[Intui_Sol]

Thanks guys! I was not aware of this function.