If statement with wildcard

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lbsstacey - 17 Jul 2008 22:49 GMT

I am trying to write code where an If statement woud look at a cell and
determine if the cell contains certain data elements and, if so, then the If
statement will perform a function. This, however, is not working for me:

If ActiveCell.Value = "* STREET" Then

I'm positive that it's because the code is looking for an exact match on "*
STREET" and does not recognize the * variable.

Any suggestions would be appreciated.

Reply to this Message

Jeff Boyce - 17 Jul 2008 22:59 GMT

I don't believe Access has "ActiveCell" ... isn't that an Excel property?

Regards

Jeff Boyce
Microsoft Office/Access MVP

>I am trying to write code where an If statement woud look at a cell and
> determine if the cell contains certain data elements and, if so, then the
[quoted text clipped - 8 lines]
>
> Any suggestions would be appreciated.

Reply to this Message

lbsstacey - 17 Jul 2008 23:13 GMT

Yes, this is an Access module which refers to an Excel spreadsheet.

> I don't believe Access has "ActiveCell" ... isn't that an Excel property?
>
[quoted text clipped - 15 lines]
> >
> > Any suggestions would be appreciated.

Reply to this Message

pietlinden@hotmail.com - 17 Jul 2008 23:42 GMT

On Jul 17, 4:49 pm, lbsstacey <lbssta...@discussions.microsoft.com>
wrote:

> I am trying to write code where an If statement woud look at a cell and
> determine if the cell contains certain data elements and, if so, then the If
[quoted text clipped - 6 lines]
>
> Any suggestions would be appreciated.

Sounds like you want INSTR(). Then you can search for a string inside
of something and have your code respond accordingly.

Reply to this Message

lbsstacey - 17 Jul 2008 23:56 GMT

Can you show me how that would like in my IF statement example.

> On Jul 17, 4:49 pm, lbsstacey <lbssta...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 11 lines]
> Sounds like you want INSTR(). Then you can search for a string inside
> of something and have your code respond accordingly.

Reply to this Message

pietlinden@hotmail.com - 18 Jul 2008 02:53 GMT

On Jul 17, 5:56 pm, lbsstacey <lbssta...@discussions.microsoft.com>
wrote:

> Can you show me how that would like in my IF statement example.
>
[quoted text clipped - 14 lines]
> > Sounds like you want INSTR(). Then you can search for a string inside
> > of something and have your code respond accordingly.

InStr() returns a number, the position of one string inside another.
If it's >0 then it's in there.

straight from the help file...

Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".

' A textual comparison starting at position 4. Returns 6.
MyPos = Instr(4, SearchString, SearchChar, 1)

' A binary comparison starting at position 1. Returns 9.
MyPos = Instr(1, SearchString, SearchChar, 0)

' Comparison is binary by default (last argument is omitted).
MyPos = Instr(SearchString, SearchChar) ' Returns 9.

MyPos = Instr(1, SearchString, "W") ' Returns 0.

so you'd just have something like

IIf(Instr(1, SearchString, SearchChar,0)>0, "True Part", "False Part")

Reply to this Message