Need to simplify Switch()

[Guest]

=Switch([Street] ... (1,981 characters later) " - " & [LocationDetails])

Hi all,
My Switch() has turned into a monster. These are my fields:
strSingleAddress, Street, From, To, LocationDetails.

These are examples of what I want to display:
[strSingleAddress] & " " & [Street] & " between " & [From] & " and " & [To]
& " - " & [LocationDetails]

[StrSingleAddress] & " " & [Street] & " at the corner of " & [From]

Is there a simpler way to express the combinations of these fields (Street
will be constant)

Thank you in advance,
NickX

 

[Marshall Barton]

=Switch([Street] ... (1,981 characters later) " - " & [LocationDetails])

Hi all,
My Switch() has turned into a monster. These are my fields:
strSingleAddress, Street, From, To, LocationDetails.

These are examples of what I want to display:
[strSingleAddress] & " " & [Street] & " between " & [From] & " and " & [To]
& " - " & [LocationDetails]

[StrSingleAddress] & " " & [Street] & " at the corner of " & [From]

Is there a simpler way to express the combinations of these fields (Street
will be constant)

I doubt it, but without a larger context, I can't be sure.

It would probably be a lot easier to manage if you scrapped
the Switch function in favor of a user defined function that
uses VBA code instead of trying to do that much in a built
in function.

 

[Guest]

Thank you for your response.

What I have seems to work, but there are to many switches for me to know if
everything is covered.

Which user-defined function should I use? How do I reference that function
to that particular field?

I actually had to cut this down in size to fit it in the text box, but here
it is:

=Switch([Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Not Null,[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location],[strSingleAddress] Is Null And [Street] Is Not Null And [From] Is
Not Null And [To] Is Not Null And [Street Address or Location] Is Not
Null,[Street] & " between " & [From] & " and " & [To] & " - " & [Street
Address or Location],[Street] Is Not Null And [From] Is Not Null And [To] Is
Not Null And [Street Address or Location] Is Null,[StrSingleAddress] & " " &
[Street] & " between " & [From] & " and " & [To],[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To],[Street] Is Not Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[StrSingleAddress] & " " & [Street]
& " at the corner of " & [From] & " - " & [Street Address or
Location],[strSingleAddress] Is Null And [Street] Is Not Null And [From] Is
Not Null And [To] Is Null And [Street Address or Location] Is Not
Null,[Street] & " at the corner of " & [From] & " - " & [Street Address or
Location],[Street] Is Not Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Null,[StrSingleAddress] & " " & [Street] & "
at the corner of " & [From],[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And [Street Address or Location]
Is Null,[Street] & " at the corner of " & [From],[Street] Is Not Null And
[From] Is Null And [To] Is Null And [Street Address or Location] Is
Null,[StrSingleAddress] & " " & [Street],[strSingleAddress] Is Null And
[Street] Is Not Null And [Street Address or Location] Is
Null,[Street],[Street] Is Not Null And [Street Address or Location] Is Not
Null,[StrSingleAddress] & " " & [Street] & " - " & [Street Address or
Location])

 

[Marshall Barton]

Thank you for your response.

What I have seems to work, but there are to many switches for me to know if
everything is covered.

Which user-defined function should I use? How do I reference that function
to that particular field?

I actually had to cut this down in size to fit it in the text box, but here
it is:

=Switch([Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location],
[strSingleAddress] Is Null And [Street] Is Not Null And [From] Is
Not Null And [To] Is Not Null And [Street Address or Location] Is Not
Null,
[Street] & " between " & [From] & " and " & [To] & " - " & [Street
Address or Location],[Street] Is Not Null And [From] Is Not Null And [To] Is
Not Null And [Street Address or Location] Is Null,[StrSingleAddress] & " " &
[Street] & " between " & [From] & " and " & [To],[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To],[Street] Is Not Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[StrSingleAddress] & " " & [Street]
& " at the corner of " & [From] & " - " & [Street Address or
Location],[strSingleAddress] Is Null And [Street] Is Not Null And [From] Is
Not Null And [To] Is Null And [Street Address or Location] Is Not
Null,[Street] & " at the corner of " & [From] & " - " & [Street Address or
Location],[Street] Is Not Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Null,[StrSingleAddress] & " " & [Street] & "
at the corner of " & [From],[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And [Street Address or Location]
Is Null,[Street] & " at the corner of " & [From],[Street] Is Not Null And
[From] Is Null And [To] Is Null And [Street Address or Location] Is
Null,[StrSingleAddress] & " " & [Street],[strSingleAddress] Is Null And
[Street] Is Not Null And [Street Address or Location] Is
Null,[Street],[Street] Is Not Null And [Street Address or Location] Is Not
Null,[StrSingleAddress] & " " & [Street] & " - " & [Street Address or
Location])

Holy cow, Batman, the Switch is out of control

It appears that all you want to do is deal with the
possibility of some missing values in a concatenated string.
This can usually be dealt with much more easily by using an
expression that judiciously uses + and & to concatenate the
parts. The key to this concept is that:
"xx" + Null results in Null
"xx" & Null results in "xx"

I can't unravel the Switch that you posted, much less the
parts that you didn't post, but try experimenting with
something like this:

=(strSingleAddress + " ") & [Street] & (" between " + [From]
+ " and " + [To]) & (" - " & [Street Address or Location])

 

[Guest]

Thanks, unfortunately I haven't been able to get that last solution to work
properly. Allow me to give some examples of how the final product should
read. (These would be different records, just similar data):

([strSingleAddress] Is Not Null [Street] Is Not Null And [From] Is Not Null And
[To] Is Not Null And [Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location]

Example: 123 N Main St between First St and Second St - on east side of street

[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To]

Example: N Main St between First St and Second St

[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[Street] & " at the corner of " & [From]>& " - " & [Street Address or Location]

Example: N Main St at the corner of First St - NW Corner

Thank you for your response.

What I have seems to work, but there are to many switches for me to know if
everything is covered.

Which user-defined function should I use? How do I reference that function
to that particular field?

I actually had to cut this down in size to fit it in the text box, but here
it is:

=Switch([Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location],
[strSingleAddress] Is Null And [Street] Is Not Null And [From] Is
Not Null And [To] Is Not Null And [Street Address or Location] Is Not
Null,
[Street] & " between " & [From] & " and " & [To] & " - " & [Street
Address or Location],[Street] Is Not Null And [From] Is Not Null And [To] Is
Not Null And [Street Address or Location] Is Null,[StrSingleAddress] & " " &
[Street] & " between " & [From] & " and " & [To],[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To],[Street] Is Not Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[StrSingleAddress] & " " & [Street]
& " at the corner of " & [From] & " - " & [Street Address or
Location],[strSingleAddress] Is Null And [Street] Is Not Null And [From] Is
Not Null And [To] Is Null And [Street Address or Location] Is Not
Null,[Street] & " at the corner of " & [From] & " - " & [Street Address or
Location],[Street] Is Not Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Null,[StrSingleAddress] & " " & [Street] & "
at the corner of " & [From],[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And [Street Address or Location]
Is Null,[Street] & " at the corner of " & [From],[Street] Is Not Null And
[From] Is Null And [To] Is Null And [Street Address or Location] Is
Null,[StrSingleAddress] & " " & [Street],[strSingleAddress] Is Null And
[Street] Is Not Null And [Street Address or Location] Is
Null,[Street],[Street] Is Not Null And [Street Address or Location] Is Not
Null,[StrSingleAddress] & " " & [Street] & " - " & [Street Address or
Location])

Holy cow, Batman, the Switch is out of control

It appears that all you want to do is deal with the
possibility of some missing values in a concatenated string.
This can usually be dealt with much more easily by using an
expression that judiciously uses + and & to concatenate the
parts. The key to this concept is that:
"xx" + Null results in Null
"xx" & Null results in "xx"

I can't unravel the Switch that you posted, much less the
parts that you didn't post, but try experimenting with
something like this:

=(strSingleAddress + " ") & [Street] & (" between " + [From]
+ " and " + [To]) & (" - " & [Street Address or Location])

 

[Marshall Barton]

As far as I can tell, my suggested expression will deal with
those examples. Maybe a set of rules to follow would explan
it better than the examples. OTOH, if you pasted the
expression that you tried along with the result it produced
and explained what is wrong with the result, I might be able
to figure it out.
--
Marsh
MVP [MS Access]

Thanks, unfortunately I haven't been able to get that last solution to work
properly. Allow me to give some examples of how the final product should
read. (These would be different records, just similar data):

([strSingleAddress] Is Not Null [Street] Is Not Null And [From] Is Not Null And
[To] Is Not Null And [Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location]

Example: 123 N Main St between First St and Second St - on east side of street

[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To]

Example: N Main St between First St and Second St

[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[Street] & " at the corner of " & [From]>& " - " & [Street Address or Location]

Example: N Main St at the corner of First St - NW Corner

 

[Guest]

I'm sorry, I actually just wasn't sure how to apply:

=(strSingleAddress + " ") & [Street] & (" between " + [From]
+ " and " + [To]) & (" - " & [Street Address or Location])

to my expression. I have never dealt with + before. This expression leaves
out rule #2 and leaves spaces and dashes when applying rules #3, #4, #5.
Maybe if I use the Switch() combining your expression and my rules?...

Rule #1- assuming Is Not Null on all fields:
[strSingleAddress] (space) [Street] " between " [From] " and " [To] " – "
[Street Address or Location]

Rule #2- assuming [To] Is Null and Is Not Null on all other fields:
[strSingleAddress] (space) [Street] " at the corner of " [From] " – "
[Street Address or Location]

Rule #3- apply to #1 and #2
If [strSingleAddress] is null, leave out [strSingleAddress] (space)

Rule #4- apply to #1 and #2
If [Street Address or Location] is null, leave out " – " [Street Address or
Location]

Rule #5- apply to #1 and #2
If [From] and [To] are both Null, leave out " between " [From] " and " [To]
, as well as, " at the corner of " [From]

Rule #6
[Street] is constant

The finished product should be left with no extra spaces, dashes or other
extraneous characters.

Maybe if I use the Switch() combining your expression and my rules?...

Thanks for your help in my learning process.
Nick X

As far as I can tell, my suggested expression will deal with
those examples. Maybe a set of rules to follow would explan
it better than the examples. OTOH, if you pasted the
expression that you tried along with the result it produced
and explained what is wrong with the result, I might be able
to figure it out.
--
Marsh
MVP [MS Access]

Thanks, unfortunately I haven't been able to get that last solution to work
properly. Allow me to give some examples of how the final product should
read. (These would be different records, just similar data):

([strSingleAddress] Is Not Null [Street] Is Not Null And [From] Is Not Null And
[To] Is Not Null And [Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location]

Example: 123 N Main St between First St and Second St - on east side of street

[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To]

Example: N Main St between First St and Second St

[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[Street] & " at the corner of " & [From]>& " - " & [Street Address or Location]

Example: N Main St at the corner of First St - NW Corner

 

[Marshall Barton]

Ok, that really helps. I don't think Switch is the most
effective way to approach even part of this question. Let
me try to address each rule separately before trying to put
it all together.

Rule #3 is taken care of by:
(strSingleAddress + " ")

Rule #4 is dealt with by:
(" - " & [Street Address or Location])

Rule #6 is covered by:
& [Street] &

Rule #5 was partially taken care of in my suggested
expression by using:
(" between " + [From] + " and " + [To])
but it does not deal with the Rule #2 situation, so let's
try to beef that up by using this instead:
IIf([To] Is Null, " at the corner of " + [From],
" between " + [From] + " and " + [To])
I'll try to explain how that's supposed to do what we want.

If To is Null, then the result of the IIf will be:
" at the corner of " + [From]
for Rule #2 and if From is also Null, the result will be
Null, which takes care of Rule #5.

If To is not Null, then the IIf will use:
" between " + [From] + " and " + [To]
Which is what we want for Rule #1, as long as From is also
not null. The situation where To is not Null, but From is
Null is a hole in your rules so I'm not sure what you want
for this case. Maybe you have set up some kind data
integrity to prevent that situation from occuring? If not,
either you can figure out from this discussion how to handle
it or post back with another rule.

To put it all that together, the complete expression is:

=(strSingleAddress + " ") & [Street]
& IIf([To] Is Null,
" at the corner of " + [From],
" between " + [From] + " and " + [To])
& (" - " & [Street Address or Location])

That should all be on one line in your expression. I just
broke it up to make it easier to read.

Be sure to review my earlier explanation of how + and & work
with Null so can get a grasp on how that expression evolves
to a solution to your problem. If nothing else, it is a
whole lot shorter than using Switch ;-)
--
Marsh
MVP [MS Access]

I'm sorry, I actually just wasn't sure how to apply:

=(strSingleAddress + " ") & [Street] & (" between " + [From]
+ " and " + [To]) & (" - " & [Street Address or Location])

to my expression. I have never dealt with + before. This expression leaves
out rule #2 and leaves spaces and dashes when applying rules #3, #4, #5.
Maybe if I use the Switch() combining your expression and my rules?...

Rule #1- assuming Is Not Null on all fields:
[strSingleAddress] (space) [Street] " between " [From] " and " [To] " – "
[Street Address or Location]

Rule #2- assuming [To] Is Null and Is Not Null on all other fields:
[strSingleAddress] (space) [Street] " at the corner of " [From] " – "
[Street Address or Location]

Rule #3- apply to #1 and #2
If [strSingleAddress] is null, leave out [strSingleAddress] (space)

Rule #4- apply to #1 and #2
If [Street Address or Location] is null, leave out " – " [Street Address or
Location]

Rule #5- apply to #1 and #2
If [From] and [To] are both Null, leave out " between " [From] " and " [To]
, as well as, " at the corner of " [From]

Rule #6
[Street] is constant

The finished product should be left with no extra spaces, dashes or other
extraneous characters.

As far as I can tell, my suggested expression will deal with
those examples. Maybe a set of rules to follow would explan
it better than the examples. OTOH, if you pasted the
expression that you tried along with the result it produced
and explained what is wrong with the result, I might be able
to figure it out.

Thanks, unfortunately I haven't been able to get that last solution to work
properly. Allow me to give some examples of how the final product should
read. (These would be different records, just similar data):

([strSingleAddress] Is Not Null [Street] Is Not Null And [From] Is Not Null And
[To] Is Not Null And [Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location]

Example: 123 N Main St between First St and Second St - on east side of street

[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To]

Example: N Main St between First St and Second St

[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[Street] & " at the corner of " & [From]>& " - " & [Street Address or Location]

Example: N Main St at the corner of First St - NW Corner

 

[Guest]

Thank you very much for your help. After writing out the rules I was able to
simplify the switch and it works great now. I've also pasted your expression
into my test report so I can compare results. There are no extra spaces left
over but the dash still appears when [Street Address or Location] is null. I
think I have more than enough to work with now. You have been a big help and
have given me a new understanding of conctenation that I'm sure will come in
handy in many of my Db's

Thanks
NickX

Ok, that really helps. I don't think Switch is the most
effective way to approach even part of this question. Let
me try to address each rule separately before trying to put
it all together.

Rule #3 is taken care of by:
(strSingleAddress + " ")

Rule #4 is dealt with by:
(" - " & [Street Address or Location])

Rule #6 is covered by:
& [Street] &

Rule #5 was partially taken care of in my suggested
expression by using:
(" between " + [From] + " and " + [To])
but it does not deal with the Rule #2 situation, so let's
try to beef that up by using this instead:
IIf([To] Is Null, " at the corner of " + [From],
" between " + [From] + " and " + [To])
I'll try to explain how that's supposed to do what we want.

If To is Null, then the result of the IIf will be:
" at the corner of " + [From]
for Rule #2 and if From is also Null, the result will be
Null, which takes care of Rule #5.

If To is not Null, then the IIf will use:
" between " + [From] + " and " + [To]
Which is what we want for Rule #1, as long as From is also
not null. The situation where To is not Null, but From is
Null is a hole in your rules so I'm not sure what you want
for this case. Maybe you have set up some kind data
integrity to prevent that situation from occuring? If not,
either you can figure out from this discussion how to handle
it or post back with another rule.

To put it all that together, the complete expression is:

=(strSingleAddress + " ") & [Street]
& IIf([To] Is Null,
" at the corner of " + [From],
" between " + [From] + " and " + [To])
& (" - " & [Street Address or Location])

That should all be on one line in your expression. I just
broke it up to make it easier to read.

Be sure to review my earlier explanation of how + and & work
with Null so can get a grasp on how that expression evolves
to a solution to your problem. If nothing else, it is a
whole lot shorter than using Switch ;-)
--
Marsh
MVP [MS Access]

I'm sorry, I actually just wasn't sure how to apply:

=(strSingleAddress + " ") & [Street] & (" between " + [From]
+ " and " + [To]) & (" - " & [Street Address or Location])

to my expression. I have never dealt with + before. This expression leaves
out rule #2 and leaves spaces and dashes when applying rules #3, #4, #5.
Maybe if I use the Switch() combining your expression and my rules?...

Rule #1- assuming Is Not Null on all fields:
[strSingleAddress] (space) [Street] " between " [From] " and " [To] " – "
[Street Address or Location]

Rule #2- assuming [To] Is Null and Is Not Null on all other fields:
[strSingleAddress] (space) [Street] " at the corner of " [From] " – "
[Street Address or Location]

Rule #3- apply to #1 and #2
If [strSingleAddress] is null, leave out [strSingleAddress] (space)

Rule #4- apply to #1 and #2
If [Street Address or Location] is null, leave out " – " [Street Address or
Location]

Rule #5- apply to #1 and #2
If [From] and [To] are both Null, leave out " between " [From] " and " [To]
, as well as, " at the corner of " [From]

Rule #6
[Street] is constant

The finished product should be left with no extra spaces, dashes or other
extraneous characters.

As far as I can tell, my suggested expression will deal with
those examples. Maybe a set of rules to follow would explan
it better than the examples. OTOH, if you pasted the
expression that you tried along with the result it produced
and explained what is wrong with the result, I might be able
to figure it out.


Thanks, unfortunately I haven't been able to get that last solution to work
properly. Allow me to give some examples of how the final product should
read. (These would be different records, just similar data):

([strSingleAddress] Is Not Null [Street] Is Not Null And [From] Is Not Null And
[To] Is Not Null And [Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location]

Example: 123 N Main St between First St and Second St - on east side of street

[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To]

Example: N Main St between First St and Second St

[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[Street] & " at the corner of " & [From]>& " - " & [Street Address or Location]

Example: N Main St at the corner of First St - NW Corner

 

[Marshall Barton]

Arrrrgghhh

After all that I missed the & in that part. It should be:

& (" - " + [Street Address or Location])
--
Marsh
MVP [MS Access]

Thank you very much for your help. After writing out the rules I was able to
simplify the switch and it works great now. I've also pasted your expression
into my test report so I can compare results. There are no extra spaces left
over but the dash still appears when [Street Address or Location] is null. I
think I have more than enough to work with now. You have been a big help and
have given me a new understanding of conctenation that I'm sure will come in
handy in many of my Db's

Ok, that really helps. I don't think Switch is the most
effective way to approach even part of this question. Let
me try to address each rule separately before trying to put
it all together.

Rule #3 is taken care of by:
(strSingleAddress + " ")

Rule #4 is dealt with by:
(" - " & [Street Address or Location])

Rule #6 is covered by:
& [Street] &

Rule #5 was partially taken care of in my suggested
expression by using:
(" between " + [From] + " and " + [To])
but it does not deal with the Rule #2 situation, so let's
try to beef that up by using this instead:
IIf([To] Is Null, " at the corner of " + [From],
" between " + [From] + " and " + [To])
I'll try to explain how that's supposed to do what we want.

If To is Null, then the result of the IIf will be:
" at the corner of " + [From]
for Rule #2 and if From is also Null, the result will be
Null, which takes care of Rule #5.

If To is not Null, then the IIf will use:
" between " + [From] + " and " + [To]
Which is what we want for Rule #1, as long as From is also
not null. The situation where To is not Null, but From is
Null is a hole in your rules so I'm not sure what you want
for this case. Maybe you have set up some kind data
integrity to prevent that situation from occuring? If not,
either you can figure out from this discussion how to handle
it or post back with another rule.

To put it all that together, the complete expression is:

=(strSingleAddress + " ") & [Street]
& IIf([To] Is Null,
" at the corner of " + [From],
" between " + [From] + " and " + [To])
& (" - " & [Street Address or Location])

That should all be on one line in your expression. I just
broke it up to make it easier to read.

Be sure to review my earlier explanation of how + and & work
with Null so can get a grasp on how that expression evolves
to a solution to your problem. If nothing else, it is a
whole lot shorter than using Switch ;-)

I'm sorry, I actually just wasn't sure how to apply:

=(strSingleAddress + " ") & [Street] & (" between " + [From]
+ " and " + [To]) & (" - " & [Street Address or Location])

to my expression. I have never dealt with + before. This expression leaves
out rule #2 and leaves spaces and dashes when applying rules #3, #4, #5.
Maybe if I use the Switch() combining your expression and my rules?...

Rule #1- assuming Is Not Null on all fields:
[strSingleAddress] (space) [Street] " between " [From] " and " [To] " – "
[Street Address or Location]

Rule #2- assuming [To] Is Null and Is Not Null on all other fields:
[strSingleAddress] (space) [Street] " at the corner of " [From] " – "
[Street Address or Location]

Rule #3- apply to #1 and #2
If [strSingleAddress] is null, leave out [strSingleAddress] (space)

Rule #4- apply to #1 and #2
If [Street Address or Location] is null, leave out " – " [Street Address or
Location]

Rule #5- apply to #1 and #2
If [From] and [To] are both Null, leave out " between " [From] " and " [To]
, as well as, " at the corner of " [From]

Rule #6
[Street] is constant

The finished product should be left with no extra spaces, dashes or other
extraneous characters.


:
As far as I can tell, my suggested expression will deal with
those examples. Maybe a set of rules to follow would explan
it better than the examples. OTOH, if you pasted the
expression that you tried along with the result it produced
and explained what is wrong with the result, I might be able
to figure it out.


Thanks, unfortunately I haven't been able to get that last solution to work
properly. Allow me to give some examples of how the final product should
read. (These would be different records, just similar data):

([strSingleAddress] Is Not Null [Street] Is Not Null And [From] Is Not Null And
[To] Is Not Null And [Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location]

Example: 123 N Main St between First St and Second St - on east side of street

[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To]

Example: N Main St between First St and Second St

[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[Street] & " at the corner of " & [From]>& " - " & [Street Address or Location]

Example: N Main St at the corner of First St - NW Corner

 

[Guest]

Thank you for the clarification. That did the trick. That happy little
accident actually gave me a better understanding of how + is used.

Thanks
NickX

Arrrrgghhh

After all that I missed the & in that part. It should be:

& (" - " + [Street Address or Location])
--
Marsh
MVP [MS Access]

Thank you very much for your help. After writing out the rules I was able to
simplify the switch and it works great now. I've also pasted your expression
into my test report so I can compare results. There are no extra spaces left
over but the dash still appears when [Street Address or Location] is null. I
think I have more than enough to work with now. You have been a big help and
have given me a new understanding of conctenation that I'm sure will come in
handy in many of my Db's

Ok, that really helps. I don't think Switch is the most
effective way to approach even part of this question. Let
me try to address each rule separately before trying to put
it all together.

Rule #3 is taken care of by:
(strSingleAddress + " ")

Rule #4 is dealt with by:
(" - " & [Street Address or Location])

Rule #6 is covered by:
& [Street] &

Rule #5 was partially taken care of in my suggested
expression by using:
(" between " + [From] + " and " + [To])
but it does not deal with the Rule #2 situation, so let's
try to beef that up by using this instead:
IIf([To] Is Null, " at the corner of " + [From],
" between " + [From] + " and " + [To])
I'll try to explain how that's supposed to do what we want.

If To is Null, then the result of the IIf will be:
" at the corner of " + [From]
for Rule #2 and if From is also Null, the result will be
Null, which takes care of Rule #5.

If To is not Null, then the IIf will use:
" between " + [From] + " and " + [To]
Which is what we want for Rule #1, as long as From is also
not null. The situation where To is not Null, but From is
Null is a hole in your rules so I'm not sure what you want
for this case. Maybe you have set up some kind data
integrity to prevent that situation from occuring? If not,
either you can figure out from this discussion how to handle
it or post back with another rule.

To put it all that together, the complete expression is:

=(strSingleAddress + " ") & [Street]
& IIf([To] Is Null,
" at the corner of " + [From],
" between " + [From] + " and " + [To])
& (" - " & [Street Address or Location])

That should all be on one line in your expression. I just
broke it up to make it easier to read.

Be sure to review my earlier explanation of how + and & work
with Null so can get a grasp on how that expression evolves
to a solution to your problem. If nothing else, it is a
whole lot shorter than using Switch ;-)


I'm sorry, I actually just wasn't sure how to apply:

=(strSingleAddress + " ") & [Street] & (" between " + [From]
+ " and " + [To]) & (" - " & [Street Address or Location])

to my expression. I have never dealt with + before. This expression leaves
out rule #2 and leaves spaces and dashes when applying rules #3, #4, #5.
Maybe if I use the Switch() combining your expression and my rules?...

Rule #1- assuming Is Not Null on all fields:
[strSingleAddress] (space) [Street] " between " [From] " and " [To] " – "
[Street Address or Location]

Rule #2- assuming [To] Is Null and Is Not Null on all other fields:
[strSingleAddress] (space) [Street] " at the corner of " [From] " – "
[Street Address or Location]

Rule #3- apply to #1 and #2
If [strSingleAddress] is null, leave out [strSingleAddress] (space)

Rule #4- apply to #1 and #2
If [Street Address or Location] is null, leave out " – " [Street Address or
Location]

Rule #5- apply to #1 and #2
If [From] and [To] are both Null, leave out " between " [From] " and " [To]
, as well as, " at the corner of " [From]

Rule #6
[Street] is constant

The finished product should be left with no extra spaces, dashes or other
extraneous characters.


:
As far as I can tell, my suggested expression will deal with
those examples. Maybe a set of rules to follow would explan
it better than the examples. OTOH, if you pasted the
expression that you tried along with the result it produced
and explained what is wrong with the result, I might be able
to figure it out.


Thanks, unfortunately I haven't been able to get that last solution to work
properly. Allow me to give some examples of how the final product should
read. (These would be different records, just similar data):

([strSingleAddress] Is Not Null [Street] Is Not Null And [From] Is Not Null And
[To] Is Not Null And [Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location]

Example: 123 N Main St between First St and Second St - on east side of street

[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To]

Example: N Main St between First St and Second St

[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[Street] & " at the corner of " & [From]>& " - " & [Street Address or Location]

Example: N Main St at the corner of First St - NW Corner

 

[Marshall Barton]

How kind of you to say that even my mistake helped you ;-)
And I am pleased to know that we have finally banished that
humongus Switch with something easier to manage.

Good luck on the next issue, what ever it is ;-)
--
Marsh
MVP [MS Access]

Thank you for the clarification. That did the trick. That happy little
accident actually gave me a better understanding of how + is used.

Arrrrgghhh

After all that I missed the & in that part. It should be:

& (" - " + [Street Address or Location])

Thank you very much for your help. After writing out the rules I was able to
simplify the switch and it works great now. I've also pasted your expression
into my test report so I can compare results. There are no extra spaces left
over but the dash still appears when [Street Address or Location] is null. I
think I have more than enough to work with now. You have been a big help and
have given me a new understanding of conctenation that I'm sure will come in
handy in many of my Db's


:
Ok, that really helps. I don't think Switch is the most
effective way to approach even part of this question. Let
me try to address each rule separately before trying to put
it all together.

Rule #3 is taken care of by:
(strSingleAddress + " ")

Rule #4 is dealt with by:
(" - " & [Street Address or Location])

Rule #6 is covered by:
& [Street] &

Rule #5 was partially taken care of in my suggested
expression by using:
(" between " + [From] + " and " + [To])
but it does not deal with the Rule #2 situation, so let's
try to beef that up by using this instead:
IIf([To] Is Null, " at the corner of " + [From],
" between " + [From] + " and " + [To])
I'll try to explain how that's supposed to do what we want.

If To is Null, then the result of the IIf will be:
" at the corner of " + [From]
for Rule #2 and if From is also Null, the result will be
Null, which takes care of Rule #5.

If To is not Null, then the IIf will use:
" between " + [From] + " and " + [To]
Which is what we want for Rule #1, as long as From is also
not null. The situation where To is not Null, but From is
Null is a hole in your rules so I'm not sure what you want
for this case. Maybe you have set up some kind data
integrity to prevent that situation from occuring? If not,
either you can figure out from this discussion how to handle
it or post back with another rule.

To put it all that together, the complete expression is:

=(strSingleAddress + " ") & [Street]
& IIf([To] Is Null,
" at the corner of " + [From],
" between " + [From] + " and " + [To])
& (" - " & [Street Address or Location])

That should all be on one line in your expression. I just
broke it up to make it easier to read.

Be sure to review my earlier explanation of how + and & work
with Null so can get a grasp on how that expression evolves
to a solution to your problem. If nothing else, it is a
whole lot shorter than using Switch ;-)


I'm sorry, I actually just wasn't sure how to apply:

=(strSingleAddress + " ") & [Street] & (" between " + [From]
+ " and " + [To]) & (" - " & [Street Address or Location])

to my expression. I have never dealt with + before. This expression leaves
out rule #2 and leaves spaces and dashes when applying rules #3, #4, #5.
Maybe if I use the Switch() combining your expression and my rules?...

Rule #1- assuming Is Not Null on all fields:
[strSingleAddress] (space) [Street] " between " [From] " and " [To] " – "
[Street Address or Location]

Rule #2- assuming [To] Is Null and Is Not Null on all other fields:
[strSingleAddress] (space) [Street] " at the corner of " [From] " – "
[Street Address or Location]

Rule #3- apply to #1 and #2
If [strSingleAddress] is null, leave out [strSingleAddress] (space)

Rule #4- apply to #1 and #2
If [Street Address or Location] is null, leave out " – " [Street Address or
Location]

Rule #5- apply to #1 and #2
If [From] and [To] are both Null, leave out " between " [From] " and " [To]
, as well as, " at the corner of " [From]

Rule #6
[Street] is constant

The finished product should be left with no extra spaces, dashes or other
extraneous characters.


:
As far as I can tell, my suggested expression will deal with
those examples. Maybe a set of rules to follow would explan
it better than the examples. OTOH, if you pasted the
expression that you tried along with the result it produced
and explained what is wrong with the result, I might be able
to figure it out.


Thanks, unfortunately I haven't been able to get that last solution to work
properly. Allow me to give some examples of how the final product should
read. (These would be different records, just similar data):

([strSingleAddress] Is Not Null [Street] Is Not Null And [From] Is Not Null And
[To] Is Not Null And [Street Address or Location] Is Not Null,
[strSingleAddress] & " " & [Street]
& " between " & [From] & " and " & [To] & " - " & [Street Address or
Location]

Example: 123 N Main St between First St and Second St - on east side of street

[strSingleAddress] Is Null
And [Street] Is Not Null And [From] Is Not Null And [To] Is Not Null And
[Street Address or Location] Is Null,[Street] & " between " & [From] & " and
" & [To]

Example: N Main St between First St and Second St

[strSingleAddress] Is Null And [Street] Is Not
Null And [From] Is Not Null And [To] Is Null And
[Street Address or Location] Is Not Null,[Street] & " at the corner of " & [From]>& " - " & [Street Address or Location]

Example: N Main St at the corner of First St - NW Corner