Setting parameter labels is causing my query to return no data (1 Viewer)

[malfiscious]

Hi,

I'm losing hair over this one so any help would be very welcome.

My parameters are linked to a form and say:
[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null

Ordinarily this works fine in returning either the selected value or all values if left null.

I need to pull in data from a Crosstab query, which means setting my parameter labels to [Forms]![FormName]![Field].

The problem is that setting the parameter labels is conflicting with pulling all records if the form dropdown is left null.

If I keep my parameters simple and just say [Forms]![FormName]![Field] then the query works with the crosstab data, but I can't do that. I need to show any records if the dropdown is left null.

The crosstab data isn't specifically the problem but needing to set the parameter names seems to be

Am I missing something simple? Have I explained properly?

----------------------------------------------------------------------------------------------
UPDATE

I think I may have found a workaround by labeling the column headings in the crosstab, which means I don't have to assign parameter labels

It would still be good to know if there's a way of making it work with the parameter labels but this will do for now

 

[vbaInet]

Pleased to hear you found a workaround but...

Have I explained properly?

... I'm confused.

What exactly were you trying to do?

 

[malfiscious]

Hi vbaInet

I'd setup a select query to look at a form combo box. If the parameter matched then it was to return only the related data but if the form was left blank then it should return all data. Simple enough using '[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null'

The problem was when I needed to add data from a crosstab. Usually I just add the parameter names to the query and the crosstab data runs fine.

If I kept it to a single parameter like '[Forms]![FormName]![Field]' - no problem

If I added 'or [Forms]![FormName]![Field] Is Null' to the end then it returned a blank table

For some reason adding the parameter name means that the 'or [] is null' is a step too far

Not sure how else I can explain so hopefully that makes sense

Having said that, in the future I'm going to make a habit of labeling the column headings in crosstabs - seems to improve the performance a lot as well

 

[vbaInet]

Did you enclose this criteria in parentheses "()" and did you add them in the Parameters list of the query?

 

[malfiscious]

Yes on both counts

I hadn't tried the "()" originally but have now and it didn't work although the result was slightly different

If I run the query with the form closed it seems to work. Once I open the form and leave any of the parameter fields empty the query is blank again

 

[vbaInet]

There's something about crosstab queries and parameters that I used to know about. But I've not done any of this stuff in a long time I've forgotten what it is

Can you upload the db (with some test data) for me to see? It will be easier to solve that way.

 

[malfiscious]

To be honest, because I want to use the crosstabs across a few different queries, labeling the column headings will save me a bunch of time in the long run because I won't need to keep adding to different parameter lists. It just wasn't something I was aware of but wish I had been some time ago - particularly because of the performance boost!

In terms of finding a reason for the original problem, that's more curiosity now so I really appreciate your help but I wouldn't want to waste your time on it since I'll probably stick with what I've got at this point

I appreciate it though, thanks for your time

 

[vbaInet]

It's fine, it won't take me long to find the problem if I had it in front of me.

As long as the names of your column headings don't change, you'll be fine.