report based on query variable issue

[vipa2000]

I have a report that looks at a query. The base data contains a field called 'functlocation'. Now this data can vary in length and looks something like the following

12080026335PDPV5448-1
12080025507PUMP
12080004003BUILDINGS
12081925507PUMP
12081925507PUMP

Now the first 6 digits represent a particular building no. There are actually 6 buildings. When I run the report I am trying to get the report only to show all tasks against 120819 but with a wildcard at the end. I created a mini table with the 6 building numbers in, but i can't work out how to incorporate the wildcard into a parameter query.

regards

vipa

 

[FoFa]

Where LEFT([columnname],6) = buildingnumber
as one way
Where columnname like buildingnumber & "*"
as another way

 

[vipa2000]

Hi FoFa. I am slightly lost. Probably because i need this to be dynamic. In an ideal world i want to open a report which is based on a query and be asked to select a building no. Once this building no is selected which is only 6 digits long it goes away and finds for an example all tasks on 120800* i.e it would return 3 tasks based on example data given.
I am just trying to link form into the criteria grid at the moment, may not be the right way, but heh ho. Keep trying.

hope this helps

regards

vipa

 

[vipa2000]

Am i thinking about this back to front. Should i use a combo box on a form, select the building no and then hit a command button to open the report which would then look at the query. On the query grid i would then set the criteria equal to the form? Only issue, how do i get my wildcards in?

regards

vipa

 

[Ian Mac]

Hi,

Firstly I wouldn't base the parameter on a control like:

Forms![Form1]![Combo1]

If that's what you are doing.

I'd Create a Function to pass the value to.
I will expand on this if you need it (not now I'm off to bed)

However you can use:

Like Forms![Form1]![Combo1] & "*"

In your criteria.

Hope that helps,

 

[vipa2000]

worked a treat thanks

regards

vipa