search a table using part of a field

[Maclain]

Not sure why, but

Like "*" & IIf(Nz(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," "),0)=0,[Forms]![Job Register and Report Log]![Drawing No],Left([Forms]![Job Register and Report Log]![Drawing No],InStr([Forms]![Job Register and Report Log]![Drawing No]," ")-1)) & "*"

works fine.

Thank you very, very much.

 

[vbaInet]

You're welcome!

By the way, the code I gave you in my last post is what you should use. You'll find that your code doesn't work completely.

 

[Maclain]

When I use your code, it's throws a property not found on drawing numbers that contain a space...

 

[vbaInet]

It should have been:

IIf(Nz(InStr(1,[Forms]![Job Register and Report Log]![Drawing No]," "),0)=0,"|",Left([Forms]![Job Register and Report Log]![Drawing No],InStr([Forms]![Job Register and Report Log]![Drawing No]," ") - 1))

 

[Maclain]

That doesn't give me an error when there isn't a space.

But will not search for drawing numbers without a space. replacing the | with the full value of drawing No field lets the code search for the full value in Drawing No if there is no space in the field.

IE
drawing No = 12345
you code searches |
my tweak searches 12345

drawing No = 12345 67
your code searches 12345
my tweak searches 12345

unless I'm mistaken?

 

[vbaInet]

Oh, you want it to use the entire string when there's no space?

 

[Maclain]

Yes, the only reason originally to just use the part before the space is that the information entered after the space can be sporadic. When there is no space, I need to use the entire field.

Apologies.

 

[vbaInet]

Alright! You're right on track.