Parameterized Queries

[WilliamMathias]

Hi,

I've got a number of queries that I'm passing parameters to for use as record selection criteria.

I'm using the parameters tab in query properties to create the parameters.

However, if I want one of the parameters to be ignored (so as to return all rows) how do I do this

I've tried passing Null as the value, no luck
I've tried passing * as the value, no luck

most of the parameters are integers.


thanks

 

[JimHorton]

In criteria you could put the parameter in the Fields row as well, then in the OR section of criteria, put Is Null. Make sure that you deal with 'OR' correctly--if you already have other 'OR' criteria, you need to put the IS Null on still a separate line.
--Jim

 

[WilliamMathias]

Hi,

Thanks for your reply Jim

I've had a go at what you've suggested but I don't think I'm understsanding you properly.

At the mo I'm doing this:

Field: HubID
Table: tblCalculations
Criteria: [parHubID]
or: I'm currently putting nothing here


then if I give parHubID a valid value, only records matching this value are returned.

But if I want all the values what should I do?

I've tried

or: is null([parHub])

thanx mate

 

[JimHorton]

William,
In the Fields row, you add a new field, call it anything, and it's value is the parameter:
Field: HubID X: [parHubID]
Table:
Sort:
Show:
Criteria: [parHubID]
Critera (row 2): Is Null

This should do it.
--Jim

 

[JoaoTL]

Hi Jim is rigth, but if your Field isn´t a string then you have to use other criteria parameters like if the field is True/False use True or False, if you have a number Field you have to use <> 0 because the default value of numeric´s is 0.

Hope it helps
Best Regards

---
JoaoTL
mail@jtl.co.pt
My MS Access Site:

http://www.jtl.co.pt

 

[WilliamMathias]

Genius!

I've never really considered the second, third etc OR rows as remotely useful before.

Rest easy, you've done your good deed for the day

thanks

Will