Storing Filter criteria in a TempVar

[omwoods]

Hello All,

After much searching of forums, the internet in general and help files, I have yet to find a reason why my use of tempvars is inconsistent when used in filtering a query.

My query criteria is IIf(IsNull([tempvars]![TempVarLVL]),[LVL],[tempvars]![TempVarLVL])

This criteria filters a table containing the levels of a building stored as Text. TempVarLVL is set using a combo-box on an unbound form, the record source of which matches is the field [LVL] in the same table the query filters to show a set of records to the user.

The problem comes when I try and allow the user to invert the filter, so that it is not equal (<>) to TempVarLVL.

I tried setting the value of TempVarLVL itself to <>"03" for the not equal setting: the inverse (which works fine) is just 03

Does anyone have any insight here? I've checked the VarType is still a string, CStr() conversion etc. and just can't figure it out.

Thanks in advance.
Oliver

 

[pr2-eugin]

Hello Oliver, welcome to AWF..

I am sorry but I am a bit confused here.. Could you give another shot in explaining the problem?

 

[omwoods]

Word doc with screen shots included in the attachment as further explanation.

Failing that, I'll have a crack at copying out of the front end to give a accdb file as illustration.

Cheers,
Oliver

 

[pr2-eugin]

You could use Dynamic Queries here rather than stored query.. That way you can handle the criteria.. Best to look at the cut down version of your DB to clear things out..

It would be helpful if you could give us a Access 2000 file format.. As many others here do not have A2010..

 

[nanscombe]

I've used a form for the criteria instead of TempVar but I hope the attached may prove useful.

 

[omwoods]

Nigel,

Thank you for the reply. It certainly got me thinking differently, and the use of Nz() was a classic example of Occam's razor.

I can replicate your method with a single field and corresponding False/True criteria rows, but I can't get it to work with multiple fields being filtered at once. I hoped an IIf would allow me to compress it into one criteria field: I think a set of functions, one for each field's criteria may do the trick.

I'll try and post a sample db later to show you the scale of the problem (nine combo boxes) filtering a table.

Thanks again,
Oliver

 

[nanscombe]

If you've got nine boxes then you may be better off doing this programatically than driving yourself bananas trying to do it with a single query.

I'm just taking a look at your earlier attachment. Yes could be a case of :banghead:

I'll have a quick think about a better way of this.

 

[omwoods]

The annoying thing is it worked so simply (one criterion for each field) for the is equal to criteria; it's the is not equal to criteria that's giving me grief: when someone suggested it would be a useful enhancement to the application to exclude certain criteria I thought it would be reasonably straightforward!

I might remove that "Provide Feedback" button from the front end...

 

[nanscombe]

It may be a lot to take in but it should give you clues to how I would go about tackling it.

 

[omwoods]

Sorry for the late reply, I've been out at meetings.

That certainly appears to be the way to do it: thank you for your help!

I've kept the tempvars on the AfterUpdates for the moment as they help restrict the combos (which have independent queries as their record source) to values that are available based on other entries, i.e. if there are no drawings for level 03, then you can't filter to find drawings once you have selected level 03; you will see reports, schematics, etc. as they are applicable to that level.

Lastly, I'll invert the filter to show no records when opening the form, to stop loading records for fun, before setting the search criteria.

Thanks again Nigel.

Oliver

 

[nanscombe]

That's all right.

The idea of attaching databases like the one I did is that, even though they may not be of use at the moment, they may be of use to you in the future.