Query: if variable is empty, show all records... how???

[Willem van der Berg]

I have been experimenting all afternoon, trying to get this to work. I'll
try to explain:

I'm working on a database in which invoices can be stored. I want to have a
parameter window, in which the user can specify which records to show. A
form based on my query will then show the records specified.
I'm using a 6 character SupplierID and the user can specify on the parameter
window if he'd like to show all invoices within the specified date range or
all invoices form a specific supplier within the specified date range. If a
specific supplier has been picked from a combobox, the SupplierID is written
to a public variable. In my query I pass this variable on using a public
function and that seems to work just fine.

The problem which I have not been able to fix though is this: if the user
does not want a specific supplier's invoices to show up but rather all
invoices from all supplier's in the specified date range... I don't know how
to tell the query that it should not pick records from a specific supplier
(in other words: it shouldn't work with any criteria for the SupplierID
field).
I've tried RetrieveSupplierID() Or Like "*" but that doesn't work (took me
some time though to figure it out).

Can anyone please help me out? What should I fill in at the Criteria cell
for SupplierID in my query? I hope my explanation makes sense.

In case you were wondering, I'm a relatively inexperienced Access user with
some Visual Basic experience.

Best regards,
Willem van der Berg

 

[Dirk Goldgar]

I have been experimenting all afternoon, trying to get this to work. I'll
try to explain:

I'm working on a database in which invoices can be stored. I want to have a
parameter window, in which the user can specify which records to show. A
form based on my query will then show the records specified.
I'm using a 6 character SupplierID and the user can specify on the parameter
window if he'd like to show all invoices within the specified date range or
all invoices form a specific supplier within the specified date range. If a
specific supplier has been picked from a combobox, the SupplierID is written
to a public variable. In my query I pass this variable on using a public
function and that seems to work just fine.

The problem which I have not been able to fix though is this: if the user
does not want a specific supplier's invoices to show up but rather all
invoices from all supplier's in the specified date range... I don't know how
to tell the query that it should not pick records from a specific supplier
(in other words: it shouldn't work with any criteria for the SupplierID
field).
I've tried RetrieveSupplierID() Or Like "*" but that doesn't work (took me
some time though to figure it out).

Can anyone please help me out? What should I fill in at the Criteria cell
for SupplierID in my query? I hope my explanation makes sense.

You could try

Like RetrieveSupplierID() & "*"

or

= RetrieveSupplierID() Or RetrieveSupplierID() is Null

The exact syntax will depend on whether RetrieveSupplierID returns a
Variant, a String, or a Number. If the function doesn't return a Variant,
then it can't return Null when no SupplierID is specified.

 

[Willem van der Berg]

You could try

Like RetrieveSupplierID() & "*"

That worked like a charm Dirk, thank you very much! I was close but I hadn't
thought of that yet. Thanks again!

Best regards,
Willem van der Berg