Delete part of a sring out.

[BryanF]

I'm trying to use a form's filter after it has been filtered by form. Good ole MSAccess seems to tack on a Lookup_ in front of all the controls that you use. To me this is horrible design, because i should be able to use the same string as a where clause to open a report. Can anyone tell me how to go into a string and delete out every instance of "Lookup_"?

 

[gwinn7]

Tack on a "lookup_"? Are you refering to the setting of the ControlSource property of each bound control? If you are needing to manipulate the text itself, then you could simply use a Mid function whenever you need the "Lookup_" text stripped from the string.

An example could be: Mid(Me.Mycontrol,7,Len(Me.Mycontrol))

Forgive, but I am still not quite sure exactly what your issue is.

Gary
gwinn7

 

[GingerR]

i'm confused too. I was not able to duplicate your issue in Acc97. are you in a diff version?

 

[BryanF]

In access 2000, if you use Filter by form. and apply your filter... then the form has a filter... let's say i put a form in filter by form mode and choose txtbox1 to say "New Orleans". The filter for the form is then Lookup_txtbox1.City = "New Orleans". If I had a report with the exact name txtbox1 with a column related to city then i should be able to use the same filter on the report. However since it has the "Lookup_" attached to it, It doesn't work.

 

[gwinn7]

Something doesn't seem right here that is still confusing me. However, I think I understand generally what you are having issue with. I believe I have duplicated your situation. When you apply a filter-by-form, the Filter property is populated with the column name from your form's Rowsource property. For example:

SELECT MyTable.MyField as Field1 FROM MyTable

The result of performing the filter-by-form on the contol holding this control source of "MyTable.MyField" would be something like...

((MyTable.MyField) = "My Value&quot

I don't think it has anything to do with the name of the control, but the ControlSource based on the Rowsource of the form.

Does this help or am I still not getting it?

Gary
gwinn7

 

[BryanF]

I am explaining my problem incorrectly. Please excuse. The last two days I have quit smoking and am at wit's end now. Now. I have a form with 6 combo boxes. let's say the first one is city. second one state. The form is strictly to narrow down the records i need to spit out in report. if i use filter by form and choose my SystemID combo box to say "DW" then apply the filter. I can look in the form's Filter property and see ((Lookup_SystemID.SystemCode="DW&quot) this is ok to use in the form. But if i wanted the same Filter on my report, because i have a systemid combo box there too. I have to take off the Lookup_. I hope i didn't forget anything this time around.

 

[gwinn7]

Is your Rowsource in the form the same in your report? If not, then that's where I would look first. Because, I believe that its not the Control's name, its the Control's ControlSource property that the filter is created by. You can change the ControlSource value by first altering field in the Rowsource.

Gary
gwinn7