Trim first 4 characters and link to another table

[ruenells]

I have the following code:

#: Mid([Emergency-Approver],InStr([Emergency-Approver],"EBF-"),5)

Here is my problem. I need the number that is at the end of EBF, sometimes there will be a dash some times a space, i know that I can use RIGHT to remove the first 4 characters, however I do not know where to place that in the above code.

My second issue is once I have the number only showing in the field I want to link that answer to a table that has a description of what that number represents.

The table name is Approval_Code. I want to have it do a VLookup type of function however that is not available in access. So I need to know how to get that result in a new field.

Any help would so be appreciated

Thanks
Rue

 

[Fuga]

Hi,

At the risk of missing something here, can´t you just do it like this?

variable = right([Emergency-Approver],5)

and then do a select query:

select * from approval_code where [field] = variable

Fuga.

 

[ruenells]

I am not sure how to use variable in Access. Can you please explain.

Thanks
Rue

 

[Brianwarnock]

If I read it correctly you want the 5th character from Emergency-approver.

In the query grid select both tables but do not join.
select fields from the table required say table2 and assume emergency-approver is in table1

then in design grid expr1:IIf([table2].[fieldwith#]=mid([table1].[emergency-approver],5,1),TRUE,FALSE)
and in criteria enter TRUE

That should do it for you

Brian

 

[ruenells]

#: IIf([Approval_Code].[Reason]=Mid([1:Change-Control1].[emergency-approver],5,1),True,False)

I tried what you gave me but it is prompting me for the Approval_Code? It should be automatic right I dont want to have to enter anything.... Help

 

[Brianwarnock]

Can you post full SQL for the query?
Approval_Code is presumably a table name?

Brian

 

[Brianwarnock]

Kocked up a simple test

SELECT Table1.id, Table1.Field1, Table1.Field2, IIf([table1].[id]=Mid([table5].[field1],3,1),True,False) AS Expr1
FROM Table5, Table1
WHERE (((IIf([table1].[id]=Mid([table5].[field1],3,1),True,False))=True));

this selects info from table1 when the id is equal to the 3rd character of field1 in table5, no prompts.

Brian

 

[ruenells]

SELECT Right([Change-ID],5) AS [Change ID], [1:Change-Control1].[2-Change-Category], [1:Change-Control1].[1a-Request-Name], [1:Change-Control1].[Severity-of-Change], [1:Change-Control1].[Planned-Start], [1:Change-Control1].[Planned-Finish], [1:Change-Control1].Status, [1:Change-Control1].[Complete-Status], [1:Change-Control1].[Actual-Start], [1:Change-Control1].[Actual-Finish], [1:Change-Control1].[Emergency-Approver], IIf([Approval_Code].[Reason]=Mid([1:Change-Control1].[emergency-approver],5,1),True,False) AS [#], [1:Change-Control1].[Planned-Start]
FROM [1:Change-Control1], Approval_Codes
WHERE ((([1:Change-Control1].[Severity-of-Change])="Emer Br/Fix") AND (([1:Change-Control1].[Planned-Start]) Between Format(DateAdd("d",0,Date()),"mm/dd/yyyy"" 00"":""00"":""00""") And Format(DateAdd("d",-3,Date()),"mm/dd/yyyy"" 00"":""00"":""00""")) AND ((IIf([Approval_Code].[Reason]=Mid([1:Change-Control1].[emergency-approver],5,1),True,False))=True));

 

[Brianwarnock]

The From statement shows the File as Approval_Codes not Approval_Code and I would have thought that it should be in[]

Brian

 

[ruenells]

Thank you for that, I ran the code it no longer prompts me but now doesnt return anything

 

[Brianwarnock]

You have a lot going on in the where statement and perhaps the conditions are not being met. You may have to creep up on it, gradually building up the selection to see where it fails.

Brian

 

[ruenells]

Here is the code, however I all am getting is just the column headers. What did i miss, I so appreciate your help!!

SELECT Right([Change-ID],5) AS [Change ID], [1:Change-Control1].[2-Change-Category], [1:Change-Control1].[1a-Request-Name], [1:Change-Control1].[Severity-of-Change], [1:Change-Control1].[Planned-Start], [1:Change-Control1].[Planned-Finish], [1:Change-Control1].Status, [1:Change-Control1].[Complete-Status], [1:Change-Control1].[Actual-Start], [1:Change-Control1].[Actual-Finish], [1:Change-Control1].[Emergency-Approver], IIf([Approval_Codes].[Reason]=Mid([1:Change-Control1].[emergency-approver],5,1),True,False) AS [#], [1:Change-Control1].[Planned-Start]
FROM [1:Change-Control1], Approval_Codes
WHERE ((([1:Change-Control1].[Severity-of-Change])="Emer Br/Fix") AND (([1:Change-Control1].[Planned-Start]) Between Format(DateAdd("d",0,Date()),"mm/dd/yyyy"" 00"":""00"":""00""") And Format(DateAdd("d",-3,Date()),"mm/dd/yyyy"" 00"":""00"":""00""")) AND ((IIf([Approval_Codes].[Reason]=Mid([1:Change-Control1].[emergency-approver],5,1),True,False))=True));

 

[ruenells]

It i take out the TRUE in the criteria it return a selection however it returns each record 5 times. When I put back in the true i get no records.

 

[Brianwarnock]

That means you are getting no matches.
Can you do a visual check to see that the condition is met?
Have we selected the correct character ie the 5th?

Brian

 

[Brianwarnock]

I have reread the thread and think that I may have made a mistake, can you print a typical emergency_approver field and indicate the character you require.

Brian

 

[Mike375]

These two calculated fields will do it....assuming there is no word or number before EBF-5 or EBF 5

Ct: (InStrRev([FullName]," ")) This one gives a 4 for a 0

Thus

TryRt: IIf([Ct]=0,Right(Trim([FullName]),Len(Trim([FullName]))-InStr(1,[FullName],"-")),Right(Trim([FullName]),Len(Trim([FullName]))-InStr(1,[FullName]," ")))

Which gives 5 in either case

Just change my field of [FullName] to your field. Paste them into Word and then do Find and Replace in Word.

 

[ruenells]

Oh there are matches i should have at least 5, min i know i could have more.

I think something is overloading the query and returning no values...this is so not good!

 

[ruenells]

It should be EBF-1 through 4 I am only intersted in the number after the EBF so 1 or 2 etc.

Does that make sense?

 

[Mike375]

What I gave you will wil work for EBF-1 or EBF-1000 or EBF 1 or EBF 1000

Or

xxxxxEBF-1 or xxxxxxEBF-1000 or xxxxxEBF 1 or xxxxxxxEBF 1000

In all case you will get what follows either the space or the -

But it won't work for

xxxxxx EBF

 

[Brianwarnock]

I'm now convinced I missed an important point, you do not want the 5th character of emergency_approver, but the 5th character of "EBF-#" or "EBF #" in emergency_approver
therefore you require the reason=mid(table.emergency_approver,instr(table.emergency_approver,"EBF")+5,1), you where nearly there in post1

You'll need to clean up my syntax.

Brian