Override Query Filter

[wdrspens]

Hi,
I have a database with a limited number of Organisations in a field called [Organisation] and I usually need to see what only one of the Organisations in the field has entered against it, so I use the expression [Enter Organisation's Name] in the criteria line of the Design View of the relevant query.
Is there anyway in which, if I just hit <Enter> when the message box opens I can view details for ALL the Organisations, as if I did not have that restriction at all?
Also is there anyway I can do a search on the organisations so that all Organisations that have the string of characters entered are shown?
Many thanks
David

 

[noboffinme]

Hi

If you remove the criteria [Enter Organaisation Name] when you run the query you will retrieve all records.

I'm not sure what you meant in the second part about the string, can you advise further?

 

[raskew]

Also is there anyway I can do a search on the organisations so that all Organisations that have the string of characters entered are shown?

Something like this will prompt the user to enter a string.

SELECT tblCats.*
FROM tblCats
WHERE (((tblCats.CatName) Like "*" & [Enter Keyword] & "*"));

HTH - Bob

 

[noboffinme]

OK, So you're prompting for some info to be entered into a dialogue box from 'tblcats'.

Where/How does the 'Organisation' table relate to the 'Cats' table?

You will need a relationship between the two if you need this output.

So, first up, do you have the tables related?

 

[wdrspens]

Hi noboffinme,

I appreciate your help, but it is not the ability to see all records that is the problem, that I can always do by removing the restriction as you suggest, it is trying to ensure that all records are shown with the restriction in place, i.e. without having to alter the underlying query.

Hi raskew,

I have trouble with code. Where do I put it, and how do I get to where I put it?

Can I just put "SELECT tblCats.* FROM tblCats WHERE (((tblCats.CatName) Like "*" & [Enter Keyword] & "*"));"
in the criteria line of the [Organisation] field under the relevant query by using the Zoom feature?

Also the query I use, is itself based on a query, and where you refer to "tblCats.CatName" should that be the original table or a query based on that table?

Many thanks for your assistance.

David

 

[wdrspens]

Thank you everyone for your help.

I have now resolved the problem by inserting the following code in the criteria under the [Organisation] field in the relevant query:

Like "*" & [Enter Organisation's Name] & "*"

It works and solves both my problems. First, if I just hit enter when the Message box appears all Organisation records are revealed, and Second if I just enter part of the name all Organisation records containing that part are revealed.

 

[raskew]

Glad to hear you resolved it.

Best wishes - Bob