Instrings

[Guest]

Ive been trying to build an expression that removes the decimals (".") out of
my fields and replaces them with two spaces. I came up with something like
this....
Mid([findDOT]![LOT],1,InStr(1,"."," ",1)-1)
however, I keep getting the return that there is not enough virtual or free
memory to run the query. Ive free'd up plenty space and tried to run it on
other computers as well. FYI, the name of my field that Im trying to query is
"findDOT".

 

[John Spencer]

Also, why aren't you using the Replace function? (Access 97 doesn't have
it, but 2000 SP2 on does).

InStr(1,"."," ",1)
Instr is always going to return zero and then you subtract 1 from that so
you are always going to end up with negative 1. There is never going to be
two spaces in a string that consists of a period. This (-1) will cause an
error in the mid function.

Assumptions:
The period is never the first character in the string
There is only one period in the string
If the period is the last character you just want to eliminate it

TRIM(Left(FindDOT.Lot,Instr(1,FindDOT.Lot,".",1)-1) & " " &
Mid(FindDot.Lot,Instr(1,FindDOT.Lot,".",1)+1))

And I would add a where clause to the query to only attempt updating the
field if it had a peiod.

WHERE FindDot.LOT LIKE "*.*"

 

[John Nurick]

If you have a text field called [XXX] and you are using a recent version
of Access, this expression in an update query will do what you ask:

Replace([XXX], ".", " ")

"Recent" in this context means Access 2000 with service packs installed,
or Access 2002 or later.

Ive been trying to build an expression that removes the decimals (".") out of
my fields and replaces them with two spaces. I came up with something like
this....
Mid([findDOT]![LOT],1,InStr(1,"."," ",1)-1)
however, I keep getting the return that there is not enough virtual or free
memory to run the query. Ive free'd up plenty space and tried to run it on
other computers as well. FYI, the name of my field that Im trying to query is
"findDOT".