Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to combine the Len(Trim) and IsNull functions as i believe they can bring different results depending on the data as follows:
if(len(trim([Doctor Number])) = 0 or If(IsNull([Doctor Number])), '00000', if(len(trim([Doctor Number])) >= 7 and [Retail Pharmacy]='PSMAS', '00001', [Doctor Number])) as [Doctor Number]
The above expression is not right, can anyone assist with the right one.
Regards
Len(Trim()) will work for NULLs as well as blanks. So if you use Len(Trim()), you do not need IsNull() as well.
IsNull() works with NULLs but not blanks.
Hi,
Try below
If (len(trim([Doctor Number]))=0,'00000',
if(len(trim([Doctor Number]))>=7 and wildmatch([Retail Pharmacy],'PSMAS','00001',[Doctor Number]
)
)
Regards
Hi Christopher,
Try,
If(Len(Trim([Doctor Number])) = 0 or IsNull([Doctor Number]), '00000',
If(Len(trim([Doctor Number])) >= 7 and [Retail Pharmacy]='PSMAS', '00001',
[Doctor Number]) ) as [Doctor Number]
Edit: Check the below thread for better understanding.!! As mentioned by others, you can use Len(Trim(FieldName)) alone in this case.
Hi Max
As mentioned in my post, it is not the second statement which is at issue. I am looking at using both IsNull and Len(Trim) to check for NULL or blanks
Len(Trim()) will work for NULLs as well as blanks. So if you use Len(Trim()), you do not need IsNull() as well.
IsNull() works with NULLs but not blanks.
I already mentioned here?
With Isnull() If not required as you are comparing field in same block of IF()...
Try this way
if(len(trim([Doctor Number])) = 0 or If(IsNull([Doctor Number]) = -1), '00000', if(len(trim([Doctor Number])) >= 7 and [Retail Pharmacy]='PSMAS', '00001', [Doctor Number]))as [Doctor Number]
Regards,
Anand
Hi,
as suggested Jonathan Len(Trim()) will work for Nulls as well as blanks and IsNull() works with nulls but not blanks.
So if you use one condition instead of two, It will work same.
Regards
Many thanks, Tamil. I am reloading the application and will check to see if my results are different. I will advise accordingly.
Regards.