Conditions Not Working

[Zesty]

I have been working for several days on a macro trying to get it to recognize
a field in a table. I want the macro to open the table, go to a record, go to
a control, and then be able to tell if the field content is "true" or "false"
according to the conditions that I have set. I'm not sure what I'm doing
wrong but it isn't working. My conditions bring back the same results no
matter what the data in the field is.

Basically I have numbers in the “cal†field and I want to know if the field
equals 0, if the field is greater than 0, or if the field is less than 0
(negative number). When I run the macro and the filed has any number other
than 0 all of the results will return “true†and when the field has 0 the
result will be “falseâ€.

Any help would be appreciated!

OpenTable Production Parts
GoToRecord Last
GoToControl cal
[cal]=0 Runmacro 001
[cal]<0 Runmacro 002
[cal]>0 Runmacro 003

 

[Steve Schapel]

Zesty,

Tables do not give the ability to refer to the value of fields in any
specific record. Your Condition statements (e.g. [cal]=0) are
essentially meaningless to Access.

Can I suggest an alternative approach?

How do you know which record is the "Last" record? Do you have a
sequential ID field or some such, or a Date/Time field for record
created, or some such? Whatever it is, make a Query based on this
table, and apply a Sort so that the "Last" record comes first. Then, in
design view of the query, right-click anywhere on the background of the
upper panel (the one where the table is shown) of the query design
window, and select Properties from the popup menu. In the Top Values
property, enter 1. Close and save the query.

Ok, now change your macro. Remove completely the first 3 actions. And
then, for the RunMacro actions, change the Condition expressions to the
equivalent of this:
DLookup("[cal]","NameOfYourQuery")=0

 

[Zesty]

I tried your suggestion and could not get it to work. The results still did
not recognize that the number in the "cal" field is "0". If I use "<" symbol
it will return a True result but then I also have negetive numbers which
would also fall under this same condition and return the True result. Any
other suggestions? I appreciate that you have taken the time to help me out -
Thank You!

Zesty,

Tables do not give the ability to refer to the value of fields in any
specific record. Your Condition statements (e.g. [cal]=0) are
essentially meaningless to Access.

Can I suggest an alternative approach?

How do you know which record is the "Last" record? Do you have a
sequential ID field or some such, or a Date/Time field for record
created, or some such? Whatever it is, make a Query based on this
table, and apply a Sort so that the "Last" record comes first. Then, in
design view of the query, right-click anywhere on the background of the
upper panel (the one where the table is shown) of the query design
window, and select Properties from the popup menu. In the Top Values
property, enter 1. Close and save the query.

Ok, now change your macro. Remove completely the first 3 actions. And
then, for the RunMacro actions, change the Condition expressions to the
equivalent of this:
DLookup("[cal]","NameOfYourQuery")=0

--
Steve Schapel, Microsoft Access MVP

I have been working for several days on a macro trying to get it to recognize
a field in a table. I want the macro to open the table, go to a record, go to
a control, and then be able to tell if the field content is "true" or "false"
according to the conditions that I have set. I'm not sure what I'm doing
wrong but it isn't working. My conditions bring back the same results no
matter what the data in the field is.

Basically I have numbers in the “cal†field and I want to know if the field
equals 0, if the field is greater than 0, or if the field is less than 0
(negative number). When I run the macro and the filed has any number other
than 0 all of the results will return “true†and when the field has 0 the
result will be “falseâ€.

Any help would be appreciated!

OpenTable Production Parts
GoToRecord Last
GoToControl cal
[cal]=0 Runmacro 001
[cal]<0 Runmacro 002
[cal]>0 Runmacro 003

 

[Steve Schapel]

Zesty,

It sounds like there could be a data type confusion. One option is that
the Cal field is a Text data type. Can you confirm that it is in fact a
numerrical field. Another option is that it has been set up as a Lookup
field, and what happens in such cases is that the value stored in the
field is not necesarily what you see. Sorry I can't be more specific,
but if you could have a think about these options it would maybe help
identify the problem. Also, can you please open the query and look at
the datasheet (you should only see one record), and check that you
indeed do have a 0 in the Cal field?