My "Condition" String is too long

[Guest]

Hello All,

I have a macro in which I have conditions that must be met before a certain
Macro is run. The problem is I have a number of conditions that must be met
before this command macro will be run and it exceeds the max character length
allowed in the conditions column. This is principally because I have had to
use the the complete identifier (i.e. [Forms]![Work
RequisitionsBackup]![FieldName] = ....) to identify a field on a form instead
of simply it's field name (i.e. [FieldName]=....).
If is use just the field name in the condition it generates an error
evaluating the condition saying you basically tried to fool me and it's not
going to happen.

Here's the Full identifier condition:

[Forms]![Work RequisitionsBackup]![TotalPriceAsProvided]>[Forms]![Work
RequisitionsBackup]![Funding Available] And [Forms]![Work
RequisitionsBackup]![Funding Available]>0 And [Forms]![Work
RequisitionsBackup]![Approval number]<>"As Per Letter" .... I want to add
another condition but I am maxed out character wise

Field name only identifier condition:

[TotalPriceAsProvided]>[Funding Available] And [Funding Available]>0 And
([Approval number]<>"As Per Letter" Or [Approval number] Is Null)

My first question is , is there any way to identify more than one condition
for a field using some operator like "Like"?

for instance will ([Approval number]<>"As Per Letter" Or Is Null) work?

Second, for the life of me I cannot find a description of the 'Like"
operator. Is that written somewhere? If so where and are there other
operators that are not available in the regular Access help files but will
work in Access? And if so how do you get info on them?

Thanks all
Andy

 

[Guest]

Consider using a query with added field like --
Expr1: 1
Then have condition test query results of that field for 1.

 

[Steve Schapel]

Andy,

I would be interested to see the actual error message you received. It
seems very odd to me... If the macro is being run on an event on the
[Work RequisitionsBackup] form, then you definitely should not need to
specify the form in your Condition statement. However, I would
parenthesise the different parts of the statement, like this:
([TotalPriceAsProvided]>[Funding Available]) And ([Funding Available]>0)
And (([Approval number]<>"As Per Letter" Or [Approval number] Is Null))

Other than that, kludgy solutions like changing the names of the
controls or the name of the form, or, as Karl suggested, using alias
names for the fields, would solve the problem for now.

In answer to your specific question about:
([Approval number]<>"As Per Letter" Or Is Null)
.... the answer is no, that is not correct syntax.
This would work though:
Nz([Approval number],"")<>"As Per Letter"

I can't see that the use of the Like operator is likely to be applicable
in this example.

 

[Guest]

The macro in question is called from a different form than the one that the
fields in question are located. But one of the macro's I inserted before it
reaches these conditions sets the focus to the form on which these fields are
located... if that makes any sense.

The error message is something like you tried to perform an operation that
Access does not allow. I can get the exact wording ASAP.

Thanks
Andy

Andy,

I would be interested to see the actual error message you received. It
seems very odd to me... If the macro is being run on an event on the
[Work RequisitionsBackup] form, then you definitely should not need to
specify the form in your Condition statement. However, I would
parenthesise the different parts of the statement, like this:
([TotalPriceAsProvided]>[Funding Available]) And ([Funding Available]>0)
And (([Approval number]<>"As Per Letter" Or [Approval number] Is Null))

Other than that, kludgy solutions like changing the names of the
controls or the name of the form, or, as Karl suggested, using alias
names for the fields, would solve the problem for now.

In answer to your specific question about:
([Approval number]<>"As Per Letter" Or Is Null)
.... the answer is no, that is not correct syntax.
This would work though:
Nz([Approval number],"")<>"As Per Letter"

I can't see that the use of the Like operator is likely to be applicable
in this example.

--
Steve Schapel, Microsoft Access MVP

Hello All,

I have a macro in which I have conditions that must be met before a certain
Macro is run. The problem is I have a number of conditions that must be met
before this command macro will be run and it exceeds the max character length
allowed in the conditions column. This is principally because I have had to
use the the complete identifier (i.e. [Forms]![Work
RequisitionsBackup]![FieldName] = ....) to identify a field on a form instead
of simply it's field name (i.e. [FieldName]=....).
If is use just the field name in the condition it generates an error
evaluating the condition saying you basically tried to fool me and it's not
going to happen.

Here's the Full identifier condition:

[Forms]![Work RequisitionsBackup]![TotalPriceAsProvided]>[Forms]![Work
RequisitionsBackup]![Funding Available] And [Forms]![Work
RequisitionsBackup]![Funding Available]>0 And [Forms]![Work
RequisitionsBackup]![Approval number]<>"As Per Letter" .... I want to add
another condition but I am maxed out character wise

Field name only identifier condition:

[TotalPriceAsProvided]>[Funding Available] And [Funding Available]>0 And
([Approval number]<>"As Per Letter" Or [Approval number] Is Null)

My first question is , is there any way to identify more than one condition
for a field using some operator like "Like"?

for instance will ([Approval number]<>"As Per Letter" Or Is Null) work?

Second, for the life of me I cannot find a description of the 'Like"
operator. Is that written somewhere? If so where and are there other
operators that are not available in the regular Access help files but will
work in Access? And if so how do you get info on them?

Thanks all
Andy

 

[Steve Schapel]

Andy,

Sorry, I misunderstood. No, if the controls being referred to in the
macro Condition are not on the form where the macro is being run, then
you do need to fully qualify the form name. Setting the focus to the
other form doesn't alter this.

 

[Guest]

Thanks Steve,
For the life of me I cannot find a description of the 'Like"
operator in any of the standard access help files. Is that written
somewhere? If so where and are there other operators that are not available
in the regular Access help files but will work in Access? And if so how do
you get info on them? (i.e. this Nz command?) I

Thanks Again
Andy