Trim first 4 characters and link to another table (1 Viewer)

[ruenells]

Mike,
Thanks but that is not always going to be the case. There will be cases where i may only have EBF- and nothing else.

We are so close to getting this to work between you an Brian I know I will have the

 

[ruenells]

Brian,

I tried yours and I am still getting no records back, just the column headers, again when I remove the TRUE criteria i get 5 records times 5 times. meaning I see the same record 5 times. We are so close

Thank you both so very much for helping me with this!!!

 

[Brianwarnock]

Sorry Rue but that should be +4 not +5, I think, heads going round

Brian

 

[Brianwarnock]

You see the same record 5 times when TRUE s removed because the table contains 5 records and therefor you have 5 hits as FALSE, don't worry as soon as we get it working it will look fine.

Brian

 

[ruenells]

Brian,

I made the change by changing the 5 to a 4 and adding back the True and still no records returned. Here is the code maybe I missed something

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], [1:Change-Control1].[Planned-Start], IIf([Approval_Codes].[Reason]=Mid([1:Change-Control1].[Emergency-Approver],InStr([Emergency-Approver],"EBF")+4,1),True,False) AS [#]
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],InStr([Emergency-Approver],"EBF")+4,1),True,False))=True));

 

[Mike375]

It does not matter

EBF-4 >>>>>>>>>>>>>>>>4
EBF 4 >>>>>>>>>>>>>>>>4
EBF- >>>>>>>>>>>>>>>>Null
EBF >>>>>>>>>>>>>>>>>EBF

Easier to separate them because the three outcomes are the number you want, the field is null or the field =s the field you extracted from

 

[ruenells]

Mike,

Really silly question but what is [CT] representing??? I am trying to see what you are talking about but I am getting errors and I am not sure what to put in its place

 

[ruenells]

Brian,

I still am getting no records coming back and like you said when I remove the TRUE I get those 5 records again LOL.

We will get it

 

[Brianwarnock]

Rue I don't understand why it is not working I have modified my little test db and no problems, the mid instr is merely finding the value of the 5thcharacter of the string EBFcc whatever else is there in the emergency-approver

If the field was aaEBF-5 the instr would return3 and thus the mid would read mid(field,7,1) returning 5, does EBF appear more than once?

I have to go and eat soon can you zip and attach the db?

Brian

 

[Mike375]

Rue

I attached db with a table and query. Open the query

The [CT] and [TryRt] are field names I use as I have heaps and heaps of these things. In other words I used what I have.

 

[ruenells]

Mike,

I tried yours and when I ran it, it returned the 5 records but it is showing each record 5 times as well.

I truly appreicate your help!!
Rue

 

[Mike375]

Rue,

Just to be sure I opened via the attachment and all OK. I printed the query and have attached the scan

 

[Mike375]

Just checked and the attached scan is OK

 

[ruenells]

Ok I have attached what I am working with....sorta LOL

I have put in the true and you can see the table I am ODBC linked in my real db but I wanted to give you data

I hope this helps.

 

[Mike375]

Rue,

That is very different to what you were describing

Wilma (Marie) R, Julie M >EBF-1 Habib S

in one field of the table

A query is needed to pull all the records that have EBF-3etc and EBF" "

I have a little data base I made that will split a field of up to 7 words/entities and place each entity in its own field. It works on the basis of one space between each entity or word. If there is more than one space then I have to fiddle about quite a lot and will only do for my own stuff.

I will do a query to get the EBFs and if that is OK I will see how my data base goes at splitting them. But it won't get 100% of them......you will need to do a lot manually

 

[Mike375]

Rue

There are 129 records with EBF of which 94 are EBF-1, 3 are EBF-2, 8 are EBF-3 and 8 are EBF-4

There are 8 EBF- and about 30 EBF without the -

121 of the 129 records with EBF have the EBF followed by names etc.

Thus 121 of the records are:

EBF-1 John Smith or similar

Thus EBF is like first name not last name

 

[ruenells]

Ok now i am even more confused LOL

 

[Mike375]

Here is your data base back as an attachment with a new field next to your [Emergency-Approver] called [EBFNumber] and that field has the numbers you want.

I did it manually in about 30 seconds......just used the Access toolbar filter etc

 

[Brianwarnock]

Rue
You have 2 people trying to solve this in entirely different ways. I am not sure where Mike is going with his "names" approach so cannot comment on that.

Looking at you data and what I think you are trying to do I see a number of problems. I do not think that we can do this in 1 query, you need to find the code number then extract the reason and join this to the data from 48 hour pull. The other problem is that not all emergency-approver fields contain EBF, what do we do then Instr will return a 0 and we would pick up the 4th character in the field, also what if we have EBF- , what do we do with a blank?

I think that in the end we will need a function that does a series of tests.
Will the number of codes increase to say double figures? If so it would be better if this code was identified in a better more precise manner.

Brian

 

[Mike375]

Brian


The "names" is simply because what I have here works on a field called FullName. To change the name of the field at my end is not on