Search for unique sting in Select Query

[Ada01]

I have a list of accounts that are formatted in the following manner

790DDD.AAAA


790DDDDDD.AAAA
790DDD.AAAA.SubAcct
790DDDDDD.AAAA.SubAcct

790 is a fixed entity code and is the same on all account strings. All accounts have a "Department" (D) but the department is variable in length. All Accounts have and "Account" (A) and all accounts start with A and have a four digit number. Some accounts have a SubAccount (SA) and some do not.

What I need to be able to do in a query is say (as I can in Excel):

IFERROR(IF(SEARCH(".****.","account number"),"SA"&RIGHT("account number",4),""),"NOSUBACCT")

How can this be done. I have been able to isolate the records that meet this criteria in a separate query using the Criteria: "*.####.*" Can i use this query to isolate records in my final desired query?

I have attached sample data and the solution i used in Excel. In access i have solved all sections of the account using the InStr(), but the SubAccount issue alludes me.

Thanks

[rpeare]

So all you're looking to do is to bring up a list of sub accounts if there are any and if there aren't any sub accounts put in the label 'NoSubAcct'? Your example only has 1 sub account per account, is that correct? or can an account have multiple sub accounts? The answer depends on the original data.

To get the label you can do something like

SubAccount: iif(instr(SubLedger, ".") <> instrrev(subledger, "."), right(subledger, len(subledger) - instrrev(subledger, "-") + 1), "NoSubAcct")

[Ada01]

What I am looking to accomplish is to return the subaccount string from the "subLedger" field if the criteria exists to do so. All i have to go on is if the string of .####. exists. There can be only one sub-account per complete account number.

When i apply your solution it returns the entire field, rather than the right four characters, but i think that is adjustable if I can following the functions.

Less elegant and problematic if there is a data entry issue somehow:

SubAccount: IIf(Len([SubLedger])-Len(Replace([SubLedger],".",""))=2,"SA" & Right([SubLedger],4),"NOSUBACCOUNT")