Removing Text From Column for Report

[theKabz]

Hello everyone!

I have a relatively basic understanding of Access, SQL and queries, and I could use a little bit of help here.

I work at a small bank. We're using Access to run a query against several .DBF files to generate a report for our users which displays a list of declined loan applications for a given date range.

Here is how the current query for the report looks:

SELECT undrwrit.decstatus, transact.branchnum, transact.decsndt, regbdeny.denynum, transact.officerid, lpdetail.classcode, cra.craincome, entity.lastorbusn, entity.first_name, regbdeny.reason

FROM regbdeny INNER JOIN (((lpdetail INNER JOIN (transact INNER JOIN entity ON (transact.primborr = entity.entitynum) AND (transact.trankey = entity.trankey)) ON lpdetail.trankey = transact.trankey) INNER JOIN cra ON transact.trankey = cra.trankey) INNER JOIN undrwrit ON lpdetail.trankey = undrwrit.trankey) ON regbdeny.trankey = transact.trankey

WHERE (((undrwrit.decstatus)="Denied") AND ((transact.branchnum)<="35") AND ((transact.decsndt)>=[Enter Start Date MM/DD/YYYY] And (transact.decsndt)<=[Enter Date MM/DD/YYYY]) AND ((regbdeny.denynum)<=1));

transact.officerid lists the officer who declined the application.

All of the data within this column displays as DOMAINNAME\USERNAME.

How can I remove DOMAINNAME\ from showing in the report? I've tried a few things but it generally results in an empty report.

Any help on this would be greatly appreciated.

Thank you.

 

[plog]

You would use 2 string functions:

Mid(http://www.techonthenet.com/access/functions/string/mid.php) - extracts a substring from a string.

InStr(http://www.techonthenet.com/access/functions/string/instr.php) - finds the position that a string (or character) occurs within a string.

So you would use InStr to find that slash (/), then use that position as the starting position in your Mid function.

Give it a shot and post back here if you have any trouble--be sure to include the code you have tried and what you are getting back.

 

[theKabz]

Alright, I feel like I'm close. I was able to get this working on one of the W3Schools demos with phone numbers and the "-" sign, but was unable to get it working in my environment. Yields #FUNC! error throughout the officer column.

I bolded my changes.

SELECT undrwrit.decstatus, transact.branchnum, transact.decsndt, regbdeny.denynum, [B]MID([transact.officerid], inStr(1,transact.officerid,'\')) as officeridnew[/B], lpdetail.classcode, cra.craincome, entity.lastorbusn, entity.first_name, regbdeny.reason

FROM  regbdeny INNER JOIN (((lpdetail INNER JOIN (transact INNER JOIN entity  ON (transact.trankey = entity.trankey) AND (transact.primborr =  entity.entitynum)) ON lpdetail.trankey = transact.trankey) INNER JOIN  cra ON transact.trankey = cra.trankey) INNER JOIN undrwrit ON  lpdetail.trankey = undrwrit.trankey) ON regbdeny.trankey =  transact.trankey

WHERE (((undrwrit.decstatus)="Denied") AND  ((transact.branchnum)<="35") AND ((transact.decsndt)>=[Enter Start  Date MM/DD/YYYY] And (transact.decsndt)<=[Enter Date MM/DD/YYYY])  AND ((regbdeny.denynum)<=1));

Also, thank you for responding in a manner that allows me to learn, instead of just answering the question. I had to do a little more digging using the information you gave me, but I think I'm on the right track.

Could it be returning #FUNC! because there are other values in officerid that do not have \ within them?

Sorry I didn't respond very quickly. Lots of distractions.

 

[plog]

Let's divide and conquer. Get rid of the Mid function for now--let's see if we can get InStr to work and then replace the Mid function once we do.

I'm not entirely sure, but it might be because you are using single instead of double quotes. Also, get rid of that 1 for the first argument. Try this:

InStr(transact.officerid, "\")

Run that and see if you get results. If you do, sort the results in ascending order and tell me if you have any 0 values.

 

[theKabz]

All returned values are 0;

But I just noticed something, and I should have seen this before.... Until yesterday I've only seen the report once or twice and always just assumed the report was cutting off letters because we only saw "domainnam" down the list.

When I run the query (without inStr or Mid) it only returns domainname, without the \ and anything afterwards. transact.officerid definitely has domainname\userid however.

Note:
I copied all of the DB files to my local machine and started a new query and it works fine. All of the values for officeridnew show "9" which would be the correct when only using InStr(transact.officerid, "\"). There are a few 0's, but those few should be 0's.

What could be limiting the characters displayed in my original query?
Sorry that this is becoming more involved, and thank you for your continued help.

 

[plog]

You lost me here:

When I run the query (without inStr or Mid) it only returns domainname, without the \ and anything afterwards. transact.officerid definitely has domainname\userid however.

It doesn't have the '\' but definitely has the '\'? Lost me.

If you have values without the '\', then you need to test for them, prior to running the Mid function because Mid doesn't recognize 0 as a position in the string. So you would have this for your officeridnew field:

officeridnew: Iif(Instr(transact.officerid, "\")=0, transact.officerid, MID([transact.officerid], inStr(transact.officerid,'\')))

 

[theKabz]

Sorry let me try to clarify.

If I open the database file transact, the column officerid contains domainname\userid

But when I run the query I originally posted at the beginning of the thread, it's only returning dominname.

 

[plog]

So opening the table it shows this:

"domainname/userid"

But if you run this query:

SELECT officerid FROM transact

you get this:

"domainname"

?

 

[theKabz]

That is correct.

 

[plog]

I don't know what to tell you. Is there a specific input mask on the field in Design view of the table?

 

[theKabz]

The field size is set to 8 for the table's properties. Unfortunately access doesn't allow me to change it because it's a linked table.

I'm going to have to edit the DBF files, but I don't have the proper software for it. I'm going to reach out to our application vendor to see if they can update the field size for me.

The query you provided me works correctly with the test environment I used;
Iif(Instr(transact.officerid, "\")=0, transact.officerid, MID([transact.officerid], inStr(transact.officerid,'\')+1))

I added the +1 to stop the \ from appearing in the results.

Thank you for your help!

Note to explain my DBF situation:
When I try to open the transact DBF in access to update the values, I receive an error: "External Table is not in the expected format". If I open the file with LibreOffice, resave it as the same file type, I can then open it in Access to update the Field Size. I'm just concerned that LibreOffice is stripping something important within the dbf file, which is why I'm reaching out to our application vendor to see if they can update the field for me.