Replacing characters in Access 2000

[mik]

Hi, I need to make my applicaiton compatible with Access 2000 and have found that the replace command doesn't work.

Data looks like this
FFFFFFFFFFF123456789
FFFFFFFFFFFFFF123456

And I need to extract the number. Currently my code replaces the 'F' with a ' ' using the 2002 Replace command.

Any suggestions?

 

[RoyVidar]

The replace function should be available in Access 2000, but not in Access 97. In VBE, check Tools | References, see if any is marked as missing or invalid, and remove them. You may have to reselect the missing reference, if it's not alredy there and you're using it.

Edit: It may be, since this is the query forum, that if you're using it in a query, you perhaps need to use a wrapper function. In a standard module (not forms/reports module):

public function myreplace(byval strField) as string
myreplace=replace(strfield,"F","")
end function

Then use this function in the query in stead of the replace function

 

[mik]

Thanks Roy,

I will check the customers references on Monday. I need to fix the Microsoft DAO 3.6 one anway. Do you know what should be checked.

The source file is very large, so I don't think I can put it in an array, also I know bugger all VBA (but I learning), so its a learning curve for me. The file is fixed width fields, so perhaps I could use VBA to alter the physical file?

If all else fails I could try something like this perhaps ?

Update input_table
Set input_field =
trim(
IIF(left(input_field,1)='F',' ',left(input_field,1)) &
IIF(mid(input_field,2,1)='F',' ',mid(input_field,2,1)) &
IIF(mid(input_field,3,1)='F',' ',mid(input_field,3,1)) &
etc till end of file
)

While it could be done in a loop, I'm a bit worried about the performance hit.

 

[RoyVidar]

Sorry, I'm rather close to hating Iif's in queries, don't know why

Are you really trying to store spaces in stead of the characters? Usually Access will remove leading/trailing spaces, but I'm not entirely sure, cause I'm usually programaticly removing them

The InStrRev function should also be available in 2000. Consider

set myfield = mid$([input_field],instrrev([input_field],"F")+1)

to get only the number. To get leading spaces, perhaps

set myfield = right(" " & mid$([input_field],instrrev([input_field],"F")+1), 20)

The number of spaces within the first part of the Right statement got truncated, use the same number of spaces as the length of your field.

But again - Replace does exist, and work in Access 2000. You may have to use a wrapper function as described in previous reply.

 

[mik]

Thanks RoyVidar,

Don't need leading spaces, just easy to trim.

Doing a large concatenated IIf would be ulgy and I'm gessing would give me a performance hit.

InStrRev suffers from the same fate as replace on the customers machine, but in that case I had a simple alternative.

Perhaps checking the customers references on Monday will make the 'replace' problem go away, if it doesn't, then perhaps I cqn get the 'InStrRev' problem to go away.

What about a InStr (ie forward) mid(field,InStr(field,<>'F')) ?

PS Access doco is useless, perhaps I should buy a book. Whats the difference between mid and mid$

 

[RoyVidar]

Both mid() and mid$() works on strings, point is what they return. With $, it returns a string, without a variant. When in need of a string, use $, cause it will be faster (some even state that mid() isn't a string function, mid$() is, but that's perhaps going a bit far?).

Take a look at the topic "Returning Strings from Functions" in the help files for a list of which functions can return both strings and variants.

If the InStr function works, then why shouldn't InStrRev? InStr looks from the start of the string, so how would you determine which is the last "F"?

Point is, for some reasons some functions doesn't work in a query, then just create a wrapper function for it in a standard module (in VBE - Insert | Module), you should be able to just copy paste the function in my first reply, then call it with

Update input_table
Set input_field = myreplace(input_field)

So I think you'll need to either build a (in my eyes) monstrous iif thingie, or use a public function.

And as a final irritation, had it been the reverse, the string had started with numbers, and there was a need to "trim off trailing letters", then the following would probably have worked:

Update input_table
Set input_field = val(input_field)

 

[mik]

2000 vs 2002 references

Have checked the references on the customers Access 2000 machine, who has;
Visual Basic For Applications
Microsoft Access 9.0 Object Library (MSACC9.OLB)
OLE Automation
Microsoft Active X Data Objects 2.1 Library

The only difference to my Access 2002 machine is that I have
Microsoft Access 10.0 Object Library (MSACC.OLB) instead of version 9.0