Creating a formula as criteria

[jody]

Is it possible to create a formula in the criteria row of a
field (LocationID) in the design grid that will return the
proper criterion to that field?

I'd like to base the formula on the presence of certain
data in the following table, but have no idea how to
structure it:

tblLogChange

SubscriberID Date (dd/mm/yy) TableName RecordID
1 01/01/03 "Locn" 1
1 01/01/03 "Locn" 2
1 02/01/03 "Locn" 3
2 01/01/03 "Subs" 2


If, for a specific date, the field TableName contains the
string "Locn", then the criterion returned should be the
corresponding RecordID number(s) for every occurence of
"Locn" on that date. Otherwise, the criteria should be 0
which will result in a dynaset with no data.

In the example above the criterion returned to the
LocationID field for the date 01/01/03 should be literally:

1 or 2

This criterion will produce a dynaset of two records, one
for LocationID 1, and one for LocationID 2.

The user will supply the date.

Does anyone know how the formula should be structured?

Thanks,
jody

 

[Tom Ellison]

Dear Jody:

I'd like to better understand your question. I've put some responses below:
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

Is it possible to create a formula in the criteria row of a
field (LocationID) in the design grid that will return the
proper criterion to that field?

I'd like to base the formula on the presence of certain
data in the following table, but have no idea how to
structure it:

tblLogChange

SubscriberID Date (dd/mm/yy) TableName RecordID
1 01/01/03 "Locn" 1
1 01/01/03 "Locn" 2
1 02/01/03 "Locn" 3
2 01/01/03 "Subs" 2


If, for a specific date, the field TableName contains the
string "Locn", then the criterion returned should be the
corresponding RecordID number(s) for every occurence of
"Locn" on that date. Otherwise, the criteria should be 0
which will result in a dynaset with no data.

This starts to sound like you would write:

IIf(TableName = "Locn", <something>, <something else>)

But I cannot tell what you want. You say, "the criterion returned should be
the corresponding RecordID number(s) for every occurence of "Locn" on that
date."

A criterion is not "returned" from a query. It is only used inside a query
to filter the results.

And you say it, "should be the corresponding RecordID number(s) for every
occurence of "Locn" on that date." There could be thousands, tens of
thousands, any unlimited number of occurrances of "Locn" for that date. If
you want to "return" something from this, could you give an example?

I suggest you write what you want the query to do in simpe language,
avoiding any use of technical terms, and give the actual results you want
from the sample data, given one or more user-input dates.

 

[jody]

Thanks Tom. I think we're on the right track with the IF
function.

Let me clarify what I mean by returning the 'criterion' -
I should have said 'building' the criterion. There are
actually 2 steps:

(1) building the criterion for the LocationID field of
qryLocnFind via the result of the IF function and;

(2) returning the recordset (dynaset) by running the
query - qryLocnFind

My challenge is building the criterion. That is,
supplying the arguments of the IF function.

In plain english, here are the elements of the IF
function:

If (on a given date) the TableName field (in
tblLogChange) contains the data string "Locn", then
return the corresponding RecordID data element (i.e. 2)
as the result of the function, otherwise return zero as
the result of the function.

In the case where there is only one occurence of "Locn"
in the TableName field for a specific date, a number
(i.e. 2) will be returned to the criteria row. When the
query is actually run, the dynaset will have one record -
with a LocationID of 2)

However, if there are 2 or 3 occurences of "Locn" in the
TableName field for a specific date (see my example below
has 2), a concatenation of both numbers separated by
an "Or" will be returned to the criteria row. (i.e. 1 or
2). When the query is actually run, the dynaset will
have two records - one with Location ID 1 and one with
Location ID 2.

What makes this complicated is the possibilty of having 2
or 3 (max) records in tblLogChange that have "Locn" on
the same date, which requires concatenating the
corresponding record IDs and putting the result as the
second argument of the IF funtion. Something like:

IF(for each TableName "Locn" occurence, RecordID
concatenation, 0)

Is there a way to boil down the tblLogChange data (using
a query for example) that would make it easier to supply
the required arguments of the IF function? In other
words call the arguments from the query instead of the
table.

Thanks very much for your help,
Jody

 

[jody]

Tom,

I found a workaround for my challenge. Ultimately, I
wanted to create the query to print specific records. As
an alternative, I will just print the form.

Thanks for you help!!

Cheers,
Jody