Limiting Field Data in a Report

[mdurschlag]

I am trying to limit the data displayed on a packing slip. The ItemName field contains the name of the item and our supplier. For example, "Hubcap (Ford)". The supplier's name is always in parentheses. I would like the report to simply display "Hubcap" without the "(Ford)". I don't know how to do this. Any suggestions?

 

[pbaldy]

Check out the Left() function, using the InStr() function to find the position of the "(".

 

[mdurschlag]

Thank you for the suggestion. I used =Left([ProductName],(InStr(1,[ProductName],"(")-1)) which worked well for items that had a supplier but caused an error if there was no "(". Thank you for your help.

 

[pbaldy]

I must have misinterpreted this:

"The supplier's name is always in parentheses"

which I took to mean there would always be a parentheses. You can wrap that in an IIf() function that tests whether the InStr() finds a "(".

 

[mdurschlag]

I've tried that but had difficulty creating the "IIF". Each time I try I get an error. =IIF((InStr(1,[ProductName],”(“)-1=False, Left([ProductName],(InStr(1,[ProductName],"(")-1)),[ProductName])).

 

[pbaldy]

Sorry, I forgot an IIf() will evaluate both True and False parts, so you'd still get the error. One kludge that comes to mind is always adding an opening parentheses to the end. It will get trimmed off either way. See if this works:

=Left([ProductName],(InStr(1,[ProductName] & "(","(")-1))

 

[mdurschlag]

Thanks Paul! That worked fine. What does the (")-1)) accomplish (the "new" info you added)?

 

[pbaldy]

What I did was to concatenate an opening parentheses to whatever the field value is. By itself it looks like:

[ProductName] & "("

just to make sure there always was one. Given your sample data that could result in:

Hubcap (Ford)(
Hubcap(

Either way, Access will find the first ( and give you everything to the left of it.

 

[mdurschlag]

Very clever. Thanks again.

 

[pbaldy]

No problem. I'm good at kludge workarounds.