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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Separate Text In Query Design, Windows 7    
 
   
Quinto1
post May 17 2018, 11:00 AM
Post#1



Posts: 90
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
 
theDBguy
post May 17 2018, 11:06 AM
Post#2


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You could try using the InStr() function in your query. For example:

Left$([FieldName], InStr([FieldName], "/")-1)

Hope it helps...
Go to the top of the page
 
zaxbat
post May 17 2018, 11:12 AM
Post#3



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#4



Posts: 6,175
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
 
Quinto1
post May 17 2018, 11:39 AM
Post#5



Posts: 90
Joined: 23-April 16



Thank you theDBGuy that work very well and very simple.

I created the field below in order to select the item delivered in one click but if possible now I would like to reverse it and save each item in their original field.
The first part is the common number that I could link but that would not be totally right because some of the entries could be edited such as price change
I can change the way I joined the field but I use that symbol to distinguish the data

1002410 / Bumper Bar Chrome Coach / Univ / EY / $350

Go to the top of the page
 
theDBguy
post May 17 2018, 11:42 AM
Post#6


UA Moderator
Posts: 76,801
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I know you didn't want "code," but maybe you could benefit from this one from our Wiki.

With this function, you can create a query as follows:

SELECT GetMember([FieldName],1,"/") AS CommonNumber, GetMember([FieldName],2,"/") AS Description, etc... GetMember([FieldName],5,"/") AS Price
FROM TableName

Let us know if you need help implementing it.

Cheers!
Go to the top of the page
 
BruceM
post May 18 2018, 07:36 AM
Post#7


UtterAccess VIP
Posts: 7,993
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#8



Posts: 90
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#9


UA Moderator
Posts: 76,801
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
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 07:50 AM