UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Separate Text In Query Design, Windows 7    
 
   
Quinto1
post May 17 2018, 11:00 AM
Post#1



Posts: 100
Joined: 23-April 16



I would like use a query to select text in an separate control field before the symbol "/"
Can I get some suggestion how by using the query design without using code

Thank you
Go to the top of the page
 
 
Start new topic
Replies
zaxbat
post May 17 2018, 11:12 AM
Post#2



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


You know it is pretty involved because the code must be put into nested IIF functions and needs to check for null as well as presence of "/" then get location and select left part of text if it is there. It turns into a pretty involved piece of SQL that is very confusing to sort through.....for a very simple operation. Also, I believe it will run pretty slow.

iif(nz(myfield,0)>0, iif(instr(myfield, '/')>0, left(myfield, instr(myfield, '/')-1), ""), ""), "")


this might do it....but also might have lost track of parenthesis....but at least this is the normal approach to this type of operation
Go to the top of the page
 
tina t
post May 17 2018, 11:28 AM
Post#3



Posts: 6,356
Joined: 11-November 10
From: SoCal, USA


QUOTE
might have lost track of parenthesis

anytime you have an expression with nesting, the easy way to check for proper pairing of parentheses is to count all the left parens, then count all the right parens. if the totals match, you're good. if they don't, start looking for where you're over or short.

another thing i often do is to break a nested expression into its' components - with placeholders if needed - then put them together one by one. something like

iif(nz(myfield,0)>0, iif(instr(myfield, '/')>0, left(myfield, instr(myfield, '/')-1), ""), ""), "")

IIf(Nz(myfield,0)>0, x, x) <2 left parens, 2 right parens>
IIf(Instr(myfield, '/')>0, x, x) <2 left parens, 2 right parens>
Left(myfield, Instr(myfield, '/')-1) <2 left parens, 2 right parens>

IIf(Nz(myfield,0)>0, IIf(Instr(myfield, '/')>0, x, x), x)

IIf(Nz(myfield,0)>0, IIf(Instr(myfield, '/')>0, Left(myfield, Instr(myfield, '/')-1), x), x)

IIf(Nz(myfield,0)>0, IIf(Instr(myfield, '/')>0, Left(myfield, Instr(myfield, '/')-1), ""), "")

hth
tina
Go to the top of the page
 
BruceM
post May 18 2018, 07:36 AM
Post#4


UtterAccess VIP
Posts: 8,025
Joined: 24-May 10
From: Downeast Maine


I find that a text editor such as Notepad++ is very useful for managing parentheses. Sometimes I break it down like this:
CODE
IIf
  (
    Nz
      (
        myfield,0
      )
      > 0
    ,IIf
      (
        Instr
          (
            myfield
            , '/'
          )
          > 0
        ,Left
          (
            myfield
            ,Instr
              (
                myfield, '/'
              )
              -1
          )
        ,""
      )
    ,""
  )

Notepad++ can be set up to show a line from an opening parentheses to its corresponding closing parentheses. Also, if you place the commas at the beginning of a line you can more easily keep track of which argument goes with which function.

This may be more slicing and dicing than you want. It could be like this:
CODE
IIf
  (
    Nz(myfield,0) > 0
    ,IIf
      (
        Instr(myfield, '/') > 0
        ,Left(myfield,Instr
          (
            myfield, '/'
          )
          - 1)
        ,""
      )
    ,""
  )

The point is that if you line up your parentheses and commas it can help in sorting out the expressions.
Go to the top of the page
 
Quinto1
post May 23 2018, 10:23 AM
Post#5



Posts: 100
Joined: 23-April 16



Thank you all.
I had to use multiple queries and selecting left or mid and got to work

Thank you for your time, your help is always appreciated

Quinto
Go to the top of the page
 
theDBguy
post May 23 2018, 10:26 AM
Post#6


UA Moderator
Posts: 77,481
Joined: 19-June 07
From: SunnySandyEggo


Hi Quinto,

You're very welcome. We're all happy to assist. Good luck with your project.
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    16th February 2020 - 10:32 PM