REPORT HELP!

[youdaman]

I have a form that contains an unbound list box that shows data from a inventory table. I have code that lets you select multiple items from that list box and stores the values in the main table separated by a semicolon. The problems is that when I want a report on just one item, the report shows the correct data but the report header shows the whole list. i.e. I select socks, shoes and shirt from the list box. Those values are stored in the table. I have a report that I enter soc* when prompted for the item I want. On the report header, it shows Item: shoes,shirt,socks. I guess the values are seen as one big String. How would I parse just to get socks to show up on the header? Tks!!

 

[scriverb]

I think this should do it for you. I will test it out to make sure.

Mid$(<FieldName>,Instr(1,<FieldName>, &quot;;&quot; & vSelParameter)+1, Instr(Instr(1,<FieldName>, &quot;;&quot; & vSelParameter)+1,<FieldName>, &quot;;&quot- 1)

Bob Scriver

 

[scriverb]

Sorry, there is a small adjustment.

Mid$([StringField], InStr(1, [StringField], &quot;;&quot; & <vParameter>) + 1, (InStr(InStr(1, [StringField], &quot;;&quot; & <vParameter>) + 1, [StringField], &quot;;&quot - 1) - InStr(1, [StringField], &quot;;&quot; & <vParameter>))
End Sub

[StringField] is your field from your table with the string of values seperated by semi-colon. Make sure that the last record also has a semi-colon behind it also. <vParameter> is the search value. Shi for shirts or enough characters to uniquely identify the word requested. If you only have one S then a single S will find Shirt. But if you have Shirts and Shoes you need to use Shi to find the Shirts.

I hope this helps you.

Bob Scriver

 

[youdaman]

thanks for the response. So I put this code in the report header field area or add a text box and add the code on the report??

 

[scriverb]

This can be the Control Source of a text box on your report. Or you can use it in code and use it in an expression to assign the value returned to a text box. Or, you could use in in the query that provides the recordset for the report and create a new column with this expression.

All of these will work. You just have to supply the correct parameters which are the red coded areas. This could be a field name, control name, global variables, etc.

If you have a specific situation describe it and I can help you to fill in the blanks.

Bob Scriver

 

[youdaman]

I am using a like statement for the user to enter the search string via a msg box. For vParameter, how would I specify the input box as the vParam? I plugged the above code into the control area of my text box in my report and I get a syntax error when I run the report. Thanks for your help.....