functions in access

[bikebanditcom]

Gentlemen,

I am in serious need of an excel find function in access, i understand that SEARCH works the same way but it is an undefined function in my access and for the life of me can't figure out how to define it. Any help would be greatly appreciated. Thanks

Dan

 

[BillPower]

Hi Dan,

InStr is the Access equivalent to Search in Excel.

See help for how to use it, I'm sure the syntax is identical.

Bill

 

[JeremyNYC]

Dan,

You don't want help from women?

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[bikebanditcom]

ok let me rephrase that...Gentlemen and Ladies,

The Instr function i'm not sure fits my problem. If anyone could help me define FIND as a module i would greatly appreciate it. thanks

Dan

 

[BillPower]

Hi bikebanditcom,

Can you elaborate on FIND, do you just want to open the Find and Replace window:

DoCmd.RunCommand acCmdFind

Or something else

Bill

 

[bikebanditcom]

In excel there is a text function called FIND that will find a character in a string and return the number of characters it is in the string. I would like to define that function in access, if you look in help and search for SEARCH then you'll find the same function for access but its not defined and i can't figure out how to use it. please help.

 

[BillPower]

Gotcha,

The Access Instr Function replaces both the Excel Find and Search functions.

Dim SearchString, SearchChar, MyPos
SearchString = "ABCabc"
SearchChar = "B"

MyPos = InStr(1, SearchString, SearchChar) 'Not case sensitive
MsgBox MyPos
MyPos = InStr(1, SearchString, LCase(SearchChar)) 'Not case sensitive
MsgBox MyPos
MyPos = InStr(1, SearchString, SearchChar, 0) 'Case sensitive
MsgBox MyPos
MyPos = InStr(1, SearchString, LCase(SearchChar), 0) 'Case sensitive
MsgBox MyPos

The above should return 2, 2, 2 and 5.

As I said look in Help for syntax and options.

Bill