Re: Need to simplify Switch()
- From: Marshall Barton <marshbarton@xxxxxxxxxx>
- Date: Wed, 25 Jan 2006 13:53:37 -0600
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]
Nick X <Nicknnator (no) @ (spam) aol.123com> wrote:
>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.
>
>
>"Marshall Barton" wrote:
>> 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.
>>
>>
>> Nick X <Nicknnator (no) @ (spam) aol.123com> wrote:
>> >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
.
- Follow-Ups:
- Re: Need to simplify Switch()
- From: Nick X
- Re: Need to simplify Switch()
- References:
- Re: Need to simplify Switch()
- From: Marshall Barton
- Re: Need to simplify Switch()
- From: Marshall Barton
- Re: Need to simplify Switch()
- From: Nick X
- Re: Need to simplify Switch()
- From: Marshall Barton
- Re: Need to simplify Switch()
- From: Nick X
- Re: Need to simplify Switch()
- Prev by Date: Re: A Strange one
- Next by Date: Re: A Strange one
- Previous by thread: Re: Need to simplify Switch()
- Next by thread: Re: Need to simplify Switch()
- Index(es):
Relevant Pages
|
