Re: Need to simplify Switch()

Arrrrgghhh

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

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


Nick X <Nicknnator (no) @ (spam) aol.123com> wrote:
>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
>
>
>"Marshall Barton" wrote:
>> 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 ;-)
>>
>>
>> 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
>>
>>

.

Relevant Pages

  • Re: Need to simplify Switch()
    ... simplify the switch and it works great now. ... > Rule #5 was partially taken care of in my suggested ... then the result of the IIf will be: ... > Be sure to review my earlier explanation of how + and & work ...
    (microsoft.public.access.reports)
  • RE: How "large" can a IIf be in a query?
    ... > Switch is kind of similar to a multipart iif(). ... >> You may want to look at whether you can normalize the database such that you ... >> custom function could do the calc for you, which you could then call from any ...
    (microsoft.public.access.queries)
  • RE: How "large" can a IIf be in a query?
    ... Switch is kind of similar to a multipart iif(). ... > You may want to look at whether you can normalize the database such that you ... > custom function could do the calc for you, which you could then call from any ...
    (microsoft.public.access.queries)
  • FYI: IIf() oder Switch()?
    ... Dim res As Double ... x = Timer() ... IIf(): 6,714844 ... Switch(): 11,62109 ...
    (microsoft.public.de.access)
  • Re: Help with time
    ... Any MVP's that would like to help out with this "iif" clause I'd ... The Switch() ... and DayPart with these values. ... FROM yourtable INNER JOIN Dayparts ...
    (microsoft.public.access.queries)