Expression in Conditional macro

  • Thread starter Thread starter Steve Stad
  • Start date Start date
S

Steve Stad

I think my expression is getting truncated at 255 characters. I would like
to use msgbox based on condition such as ...[Forms]![frmTest]![city]
<>"Washington DC" <> "BALTIMORE" <> "NEW YORK" <> "PORTLAND" <> "BOSTON" <>
"NEW PROVIDENCE" ..... etc - but the conditional expression with all of my
cities is over 255 chars long. Can you tell me how to rewrite the
conditional expression in the macro so the msgbox will display if the city is
not equal to a long list of many many cities.
 
Your expression is wrong - needs to be like this --
... [Forms]![frmTest]![city] <>"Washington DC" AND [Forms]![frmTest]![city]
<> "BALTIMORE" AND [Forms]![frmTest]![city] <> "NEW YORK" AND
[Forms]![frmTest]![city] <> "PORTLAND" AND [Forms]![frmTest]![city] <>
"BOSTON" AND [Forms]![frmTest]![city] <>

Easier to place your cities in a table.
... [Forms]![frmTest]![city] = [TableList].[City]

OR with a UseCity Yes/No field --
... [Forms]![frmTest]![city] = [TableList].[City] AND
[TableList].[UseCity] = -1
 
Steve,

I agree with Karl, that this should be table driven. But you would need to
use a domain function, or a subquery, to get it to work. Something like
this, for example, as your Condition...
DCount("*","ExcludedCitiesTable","[City]='" & [City] & "'")=0

--
Steve Schapel, Microsoft Access MVP


KARL DEWEY said:
Your expression is wrong - needs to be like this --
... [Forms]![frmTest]![city] <>"Washington DC" AND
[Forms]![frmTest]![city]
<> "BALTIMORE" AND [Forms]![frmTest]![city] <> "NEW YORK" AND
[Forms]![frmTest]![city] <> "PORTLAND" AND [Forms]![frmTest]![city] <>
"BOSTON" AND [Forms]![frmTest]![city] <>

Easier to place your cities in a table.
... [Forms]![frmTest]![city] = [TableList].[City]

OR with a UseCity Yes/No field --
... [Forms]![frmTest]![city] = [TableList].[City] AND
[TableList].[UseCity] = -1

--
Build a little, test a little.


Steve Stad said:
I think my expression is getting truncated at 255 characters. I would
like
to use msgbox based on condition such as ...[Forms]![frmTest]![city]
<>"Washington DC" <> "BALTIMORE" <> "NEW YORK" <> "PORTLAND" <> "BOSTON"
<>
"NEW PROVIDENCE" ..... etc - but the conditional expression with all of
my
cities is over 255 chars long. Can you tell me how to rewrite the
conditional expression in the macro so the msgbox will display if the
city is
not equal to a long list of many many cities.
Click to expand...
Click to expand...
 
Steve Schapel/Karl,

Thanks for replies. You are both right. The conditional macro expression
is limited to 255 chars. So I can only fit a few cities in the expression.
I will try the Dcount function with a table. Does VBA code have the same
limitation for if/then condition. e.g., could I convert the conditional
macro to VBA code and have a long expression more than 255 chars?

Steve Schapel said:
Steve,

I agree with Karl, that this should be table driven. But you would need to
use a domain function, or a subquery, to get it to work. Something like
this, for example, as your Condition...
DCount("*","ExcludedCitiesTable","[City]='" & [City] & "'")=0

--
Steve Schapel, Microsoft Access MVP


KARL DEWEY said:
Your expression is wrong - needs to be like this --
... [Forms]![frmTest]![city] <>"Washington DC" AND
[Forms]![frmTest]![city]
<> "BALTIMORE" AND [Forms]![frmTest]![city] <> "NEW YORK" AND
[Forms]![frmTest]![city] <> "PORTLAND" AND [Forms]![frmTest]![city] <>
"BOSTON" AND [Forms]![frmTest]![city] <>

Easier to place your cities in a table.
... [Forms]![frmTest]![city] = [TableList].[City]

OR with a UseCity Yes/No field --
... [Forms]![frmTest]![city] = [TableList].[City] AND
[TableList].[UseCity] = -1

--
Build a little, test a little.


Steve Stad said:
I think my expression is getting truncated at 255 characters. I would
like
to use msgbox based on condition such as ...[Forms]![frmTest]![city]
<>"Washington DC" <> "BALTIMORE" <> "NEW YORK" <> "PORTLAND" <> "BOSTON"
<>
"NEW PROVIDENCE" ..... etc - but the conditional expression with all of
my
cities is over 255 chars long. Can you tell me how to rewrite the
conditional expression in the macro so the msgbox will display if the
city is
not equal to a long list of many many cities.
Click to expand...
Click to expand...

.
Click to expand...
 
Steve Schapel/Karl,

I am trying to use the Dcount expression in the conditional macro. Since I
am not a programmer I am trying to translate the example to my application.
For my example, I have a personel db w/ a global list of cities nationwide
and a regional list of cities. If a person changes cities outside of their
region I would like the msgbox to display.

Steve Stad said:
Steve Schapel/Karl,

Thanks for replies. You are both right. The conditional macro expression
is limited to 255 chars. So I can only fit a few cities in the expression.
I will try the Dcount function with a table. Does VBA code have the same
limitation for if/then condition. e.g., could I convert the conditional
macro to VBA code and have a long expression more than 255 chars?

Steve Schapel said:
Steve,

I agree with Karl, that this should be table driven. But you would need to
use a domain function, or a subquery, to get it to work. Something like
this, for example, as your Condition...
DCount("*","ExcludedCitiesTable","[City]='" & [City] & "'")=0

--
Steve Schapel, Microsoft Access MVP


KARL DEWEY said:
Your expression is wrong - needs to be like this --
... [Forms]![frmTest]![city] <>"Washington DC" AND
[Forms]![frmTest]![city]
<> "BALTIMORE" AND [Forms]![frmTest]![city] <> "NEW YORK" AND
[Forms]![frmTest]![city] <> "PORTLAND" AND [Forms]![frmTest]![city] <>
"BOSTON" AND [Forms]![frmTest]![city] <>

Easier to place your cities in a table.
... [Forms]![frmTest]![city] = [TableList].[City]

OR with a UseCity Yes/No field --
... [Forms]![frmTest]![city] = [TableList].[City] AND
[TableList].[UseCity] = -1

--
Build a little, test a little.


:

I think my expression is getting truncated at 255 characters. I would
like
to use msgbox based on condition such as ...[Forms]![frmTest]![city]
<>"Washington DC" <> "BALTIMORE" <> "NEW YORK" <> "PORTLAND" <> "BOSTON"
<>
"NEW PROVIDENCE" ..... etc - but the conditional expression with all of
my
cities is over 255 chars long. Can you tell me how to rewrite the
conditional expression in the macro so the msgbox will display if the
city is
not equal to a long list of many many cities.
Click to expand...

.
Click to expand...
Click to expand...
 
Steve,

You could do this in a VBA procedure by hard-coding all the cities to be
excluded. But this is not the recommended approach. It is by far
preferable to have the criteria based on selection from a table.
 
Steve,

If you mean that you have the national list and the regional list in two
separate tables, then this is probably not the ideal table structure.
Preferable would be a field in your national list to indicate the region for
each city.

Anyway, we can advise more explicitly if you can give us the details of yout
table names and field names. Also, which version of Access are you using?

--
Steve Schapel, Microsoft Access MVP


Steve Stad said:
Steve Schapel/Karl,

I am trying to use the Dcount expression in the conditional macro. Since
I
am not a programmer I am trying to translate the example to my
application.
For my example, I have a personel db w/ a global list of cities nationwide
and a regional list of cities. If a person changes cities outside of
their
region I would like the msgbox to display.

Steve Stad said:
Steve Schapel/Karl,

Thanks for replies. You are both right. The conditional macro
expression
is limited to 255 chars. So I can only fit a few cities in the
expression.
I will try the Dcount function with a table. Does VBA code have the same
limitation for if/then condition. e.g., could I convert the conditional
macro to VBA code and have a long expression more than 255 chars?

Steve Schapel said:
Steve,

I agree with Karl, that this should be table driven. But you would
need to
use a domain function, or a subquery, to get it to work. Something
like
this, for example, as your Condition...
DCount("*","ExcludedCitiesTable","[City]='" & [City] & "'")=0

--
Steve Schapel, Microsoft Access MVP


Your expression is wrong - needs to be like this --
... [Forms]![frmTest]![city] <>"Washington DC" AND
[Forms]![frmTest]![city]
<> "BALTIMORE" AND [Forms]![frmTest]![city] <> "NEW YORK" AND
[Forms]![frmTest]![city] <> "PORTLAND" AND [Forms]![frmTest]![city]
<>
"BOSTON" AND [Forms]![frmTest]![city] <>

Easier to place your cities in a table.
... [Forms]![frmTest]![city] = [TableList].[City]

OR with a UseCity Yes/No field --
... [Forms]![frmTest]![city] = [TableList].[City] AND
[TableList].[UseCity] = -1

--
Build a little, test a little.


:

I think my expression is getting truncated at 255 characters. I
would
like
to use msgbox based on condition such as ...[Forms]![frmTest]![city]
<>"Washington DC" <> "BALTIMORE" <> "NEW YORK" <> "PORTLAND" <>
"BOSTON"
<>
"NEW PROVIDENCE" ..... etc - but the conditional expression with
all of
my
cities is over 255 chars long. Can you tell me how to rewrite the
conditional expression in the macro so the msgbox will display if
the
city is
not equal to a long list of many many cities.

.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top