Excel formula converted to access formula

RobTuby

Registered User.
Local time
Today, 15:40
Joined
Nov 24, 2014
Messages
25
Hi

I built this and it works in excel but i have no idea on how to get it to work in an access query?

Code:
=IF(ISNUMBER(SEARCH("Training",[@Details])),"#TRNG",IF(ISNUMBER(SEARCH("Leave",[@Details])),"#HOLS",IF(ISNUMBER(SEARCH("holiday",[@Details])),"#HOLS",IF([@Details]="","#Free",IFERROR(TRIM(LEFT(SUBSTITUTE(REPLACE([@Details],1,FIND("#",[@Details])-1,"")," ",REPT(" ",LEN([@Details]))),LEN([@Details]))),"No #")))))

The [@Details] field is the field Import.Details


Please help.

Thanks

Rob
 
Hi

I built this and it works in excel but i have no idea on how to get it to work in an access query?

Code:
=IF(ISNUMBER(SEARCH("Training",[@Details])),"#TRNG",IF(ISNUMBER(SEARCH("Leave",[@Details])),"#HOLS",IF(ISNUMBER(SEARCH("holiday",[@Details])),"#HOLS",IF([@Details]="","#Free",IFERROR(TRIM(LEFT(SUBSTITUTE(REPLACE([@Details],1,FIND("#",[@Details])-1,"")," ",REPT(" ",LEN([@Details]))),LEN([@Details]))),"No #")))))

The [@Details] field is the field Import.Details


Please help.

Thanks

Rob

I'm new myself, so don't take my word as law but have you tried making it a function? and using a VBA module to input that into your access query?
 
I'm new myself, so don't take my word as law but have you tried making it a function? and using a VBA module to input that into your access query?


Thanks for the suggestion buti dont have a clue on how to turn this into a function?

Can anyone assist with that or another suggestion?
 
Maybe if you make your formula readable AND understandable?

Try also explaining what its supposed to do
 
On a 1:1 translation

IF = IIF
ISNUMBER = IsNumeric() or possibly "not isnull()" depending on...

I can do more 1:1 but so can google
 
On a 1:1 translation

IF = IIF
ISNUMBER = IsNumeric() or possibly "not isnull()" depending on...

I can do more 1:1 but so can google

=IF(ISNUMBER(SEARCH("Training",[@Details])),"#TRNG",IF(ISNUMBER(SEARCH("Leave",[@Details])),"#HOLS",IF(ISNUMBER(SEARCH("holiday",[@Details])),"#HOLS",IF([@Details]="","#Free",IFERROR(TRIM(LEFT(SUBSTITUTE(REPLACE([@Details],1,FIND("#",[@Details])-1,"")," ",REPT(" ",LEN([@Details]))),LEN([@Details]))),"No #")))))It is looking within a cell for the exact words, if it finds them then it puts the hashtag in,
 
Youcallthatreadable?INSTRistheaccessversionofSearch,withINSTRandIIFyouprobablygetalongway
 
Sorry i was having a real bad day yesterday

I have tried to construct the formula to extract the hashtag but it is failing

Code:
Hashtag: Mid([test2]![Details],"#")+1-InStrRev([test2]![Details]," ")-InStr([test2]![Details],"#")-1

The string in the field can be absolutly anything but will contain #??????

The hashtag can be from 3 characters to 7 characters?

My logic says i need to find the hashtag (#) count 1 back, then extract up to the end of the word?
 
I don't understand your Mid, mid is
Mid(string,start,length)

Your closing bracket is after "#" ?.

It is interesting that you say word rather than field, why? Because unlike in Excel a mid in a query will extract the rest of the field from the starting point if no length is given.

Mid(details,instr(details,"#")-1)

If you are needing the length upto the first space after the starting point that would be obtained by
Instr(instr(details,"#"),details," ")


Brian
 
Looking to see if Rob had ever come back I realised that both formulae had errors in them , I think I got ahead of myself when writing them as the -1 is in the wrong one

Mid(details,instr(details,"#"))

If you are needing the length upto the first space after the starting point that would be obtained by
Instr(instr(details,"#"),details," ")-instr(details,"#")-1


Brian
 

Users who are viewing this thread

Back
Top Bottom