Default Where criteria for Queries

[Gari]

Hello,

I am stuck with a problem here.

I am building a database and I have several filter queries.

In most of those queries I do a "Where" filter with always the same criteria, being something like:

<>"AAAA" And <>"BBBB"

I may want to change this criteria later on.

Is there any way to assign a constant value for this criteria for the whole database so that, if I change it, I do not need to change several queries but only one line of code only?

I have been looking on the internet for a solution and tryed different things on access/VBA but couldn't come up with something that works.

If you need more info on my request please let me know and thank you very much for your help.

Best regards,

G.

[MGrowneyARSI]

Have a table with one record at a time and just do a Dlookup for the critira you should be able to put the dlookups right into the query but if not just hide text boxes on your forms set to the dlookups that way all you have to do is change the data in that on record you can even make a form so that the end user can do so.

[Gari]

Hello,

First of all thanks for your reply.

I have tried as you suggested the Dlookup inserted directly in the query.

I have created a table called [Restrictions] with 2 fields: [ID], [Statusrestrict]. with one record as follows: [ID]= 1 and [Statusrestrict]=<>"AAA" And <>"BBB"

In the query I call:

DLookup("[Statusrestrict]", "Restrictions", "[ID]=1")

And my query looks like this:

SELECT tblProspects.CompName, tblProspects.Status
FROM tblProspects
WHERE (((tblProspects.Status)=DLookUp("[Statusrestrict]","Restrictions","[ID]=1")))
GROUP BY tblProspects.CompName, tblProspects.Status;

However, when I run the query, it does not read the value in [Statusrestrict] and it returns nothing.

Anyone can help?

Thank you very much.

Best regards,

G.

[Scott Price]

Try changing your Dlookup statement from this:

DLookup("[Statusrestrict]", "Restrictions", "[ID]=1")

To this:

DLookup("[Statusrestrict]", "Restrictions")

If this doesn't work, you might have some misspelling somewhere that 'tis making the lookup not find the field and/or table referred to, or is returning a value from the table restrictions that is causing the main query to return no records.

To debug this, try running a simple select query using the table "Restrictions" to see what data it returns for the ID = 1 criteria. Then make sure you have data in the table queried by your main query that matches the value returned by your second test query.

Regards,
Scott

[Gari]

Hello,

I have tried as you said but it does not work either.

I feel that there is a problem of syntax in the code as when you build up the query in access, it puts by default an "=" into the code for the criteria field if nothing else is specified.

So my original query (the one that does not work) looks like this:

SELECT tblProspects.CompName, tblProspects.Status
FROM tblProspects
WHERE (((tblProspects.Status)=DLookUp("[Statusrestrict]","Restrictions","[ID]=1")))
GROUP BY tblProspects.CompName, tblProspects.Status;

In my table field [Statusrestrict], I have this data:

<>"AAA" And <>"BBB"

Then I have tried to put only one criteria in the field, that is to say that I tried with [Statusrestrict]=AAA and the code:

SELECT tblProspects.CompName, tblProspects.Status
FROM tblProspects
WHERE (((tblProspects.Status)<>DLookUp("[Statusrestrict]","Restrictions","[ID]=1")))
GROUP BY tblProspects.CompName, tblProspects.Status;

This actually is working very fine, but only with a single data to be filtered out. And in my case, I need to filter two or more data, without having to go through all the queries if this might change later.

I am wondering how the query then reads the cell: as a simple data (then it won't work, as in the first case), or as a string item, that is to say a line of code "to be inserted" into the code.

Is there any way to tell the query something like "you read the data in this cell and you insert it as a line of code"? ....

Thank you for your help.

Best regards,

G.

[Scott Price]

Hello,

I have tried as you said but it does not work either.

I feel that there is a problem of syntax in the code as when you build up the query in access, it puts by default an "=" into the code for the criteria field if nothing else is specified.

So my original query (the one that does not work) looks like this:

SELECT tblProspects.CompName, tblProspects.Status
FROM tblProspects
WHERE (((tblProspects.Status)=DLookUp("[Statusrestrict]","Restrictions","[ID]=1")))
GROUP BY tblProspects.CompName, tblProspects.Status;

In my table field [Statusrestrict], I have this data:

<>"AAA" And <>"BBB"

Then I have tried to put only one criteria in the field, that is to say that I tried with [Statusrestrict]=AAA and the code:

SELECT tblProspects.CompName, tblProspects.Status
FROM tblProspects
WHERE (((tblProspects.Status)<>DLookUp("[Statusrestrict]","Restrictions","[ID]=1")))
GROUP BY tblProspects.CompName, tblProspects.Status;

This actually is working very fine, but only with a single data to be filtered out. And in my case, I need to filter two or more data, without having to go through all the queries if this might change later.

I am wondering how the query then reads the cell: as a simple data (then it won't work, as in the first case), or as a string item, that is to say a line of code "to be inserted" into the code.

Is there any way to tell the query something like "you read the data in this cell and you insert it as a line of code"? ....

Thank you for your help.

Best regards,

G.

Access will read the data contained in your cell as a string. Try changing the criteria store in your cell to this: "AAA" And <>"BBB"
Use this with the second method that you have there... I.e. the <>DLookup() one.

Regards,
Scott