Extracting a Sub String from a field

[prakashwadhwani]

Hi !!

One of the fields in my Table is DETAILS.

I need to build a query in which The DETAILS field should only reflect characters starting from 1 upto the first blank space it encounters.

eg.
Details field is "DAWAHI AL NABA TRADING-123" ... it should extract and display "DAWAHI"
Details field is "KHIMJI RAMDAS - 3050365871" ... it should extract and display "KHIMJI"
Details field is "INBB22060028 - WATERFRONT" ... it should extract and display "INBB22060028"



Any help would be appreciated.
Thx!

[Micron]

Try
Left([Details],Instr(1,[Details]," ")-1)

EDIT: code correction; added -1. Could use Trim function if the -1 is omitted.

[prakashwadhwani]

Try
Left([Details],Instr(1,[Details]," ")-1)

EDIT: code correction; added -1. Could use Trim function if the -1 is omitted.

Thx Micron .... It works except in the foll condition where the DETAILS field doesn't contain any blank spaces at all

eg
SAVILLS-4144
EXTRA28960

In the above 2 cases it throws up a #ERROR

Any workaround?

[Welshgasman]

So test for the space?

[Micron]

It would help if you could state what ALL of the possible scenarios might be and the desired results for each.
There is always a work around, but I have no idea what you want as results when the data is
SAVILLS-4144
EXTRA28960

You just want
SAVILLS-4144
EXTRA28960 ?

EDIT - your post is in Reports forum. I'm assuming the solution is desired for a calculated query field. Maybe
IIF(Is Null([Details]),[Details],Left([Details],Instr(1,[Details]," ")-1))

[CJ_London]

perhaps

iif(instr(nz([Details])," ")=0,[Details],Left(nz([Details],""),Instr(nz([Details],"")," ")-1))

Although the nz is only required for the first comparison, the whole iif statement will be evaluated so can fail on subsequent evaluations

[prakashwadhwani]

Thank you so much everyone for your Inputs and guidance. With all your help I was finally able to get it to work.

Micron: My apologies on 2 counts ...
1) I wasn't clear as to what I wanted in the workaround ... and
2) For posting in the wrong forum ... This should've been posted in the QUERIES forum.

Once again ... I can't thank you'll enough.

[Micron]

For the benefit of those who might look at this thread in trying to solve a similar problem, it's helpful if you post your solution.

[prakashwadhwani]

For the benefit of those who might look at this thread in trying to solve a similar problem, it's helpful if you post your solution.

I used a combination of yours and C J London's solution, but the client requested me to revert to the previous solution which threw up the #ERROR so they could get an indication of which data was incorrectly entered and rectify those entries.

Once again, thank you folks.

[Micron]

You're welcome. We're glad to be able to help.

[CJ_London]

Happy to help

which data was incorrectly entered

better to tackle this at the point of entry - whether manual input or data import