perform query based on part of text

  • Thread starter Thread starter papa jonah
  • Start date Start date
P

papa jonah

I have a table that has a field (org) that has data like
AA-2
AA-2
AA-2
AA-1
AA-D
NMT-D
NMT-4
NMT-4

I want a query that will list all of the organizations (1 time) that
are represented to the left of the dash.
For example, the results of the query on the above table would be
AA
NMT

TIA
 
I have a table that has a field (org) that has data like
AA-2
AA-2
AA-2
AA-1
AA-D
NMT-D
NMT-4
NMT-4

I want a query that will list all of the organizations (1 time) that
are represented to the left of the dash.
For example, the results of the query on the above table would be
AA
NMT

TIA
Click to expand...

SELECT DISTINCT IIf(InStr([Org],"-")>0,Left([Org],InStr([Org],"-")-1))
AS Exp
FROM YourTableName;
 
That almost got me where I need to go. I did not characterize my table
well enough before as I forgot to mention that not all orgs have a "-".
For example, EO is just EO. The query only reports back those that had
"-" to begin with.
How can I adjust this to also include those orgs that do not have
dashes in them?

If necessary, I can change the field that I select from, in which case
I may be able to get away with just identifying the orgs (from the
other field) - again I need to limit the response to just once per org.

In the above code from fredg, I understand the if statement, but I do
not understand what about the code limits the results from duplicating.

TIA
 
Simple - change the IIF statement so it returns the Org if there is no dash.

IIf(InStr([Org],"-")>0,Left([Org],InStr([Org],"-")-1),[Org])
 
That works great. Thank you. Can you tell me what it is about this
code that limits results to once per.
For example, in my original post, I listed AA-2 three times, but the
result of the query only shows AA once. That is great, but I'd like to
know why the query works.

Thanks again
 
That works great. Thank you. Can you tell me what it is about this
code that limits results to once per.
For example, in my original post, I listed AA-2 three times, but the
result of the query only shows AA once. That is great, but I'd like to
know why the query works.

Thanks again
Click to expand...

Select DISTINCT etc.......
 
Back
Top