IIF Count InStr and InStrRev in standard query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

IIF Count InStr and InStrRev in standard query

Bryon Relyea

2 years ago

I've previously received guidance on using InStr and InStrRev to remove an unwanted string from a field, after "-", while in a standard query. Is there a way to use an "IIF" and "Count" function in a standard query to determine whether to use InStr (single "-") or InStrRev (multiple "-")?

I have the following:

TruncatedField: IIf(Count([TableName].[Field],"-" = 1, Left([TableName].[Field] & "-",InStr([TableName].[Field] & "-","-")-1), Left([TableName].[Field], InStrRev([TableName].[Field], "-",-1))))

I receive a "wrong number of arguments" error message; and cannot see where the error is.

Thank you!!!

	Kevin Robertson @Reply 2 years ago
Use this formula to count the number of hyphens in your string. `HyphenCount: Len([MyString])-Len(Replace([MyString],"-",""))`

	Kevin Robertson @Reply 2 years ago

	Bryon Relyea OP @Reply 2 years ago
hello, Kevin - Thank you for the quick response. That solution was definitely not one that I was expecting. However, it is much easier to read than my tangled mess. As always, I appreciate your assistance and expertise! Bryon

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View