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

Brian,

The number will always be a single digit so that will never change. As for pulling a second query totally not a problem at all.
 
So umm where does that leave me???

Does that data base you posted contain all your data. If so, I have have put the number following each of the EBRs into another field and have attached that a couple of postings above.

I assume that was all the data because the number of records indicated it was not a sample
 
So umm where does that leave me???

I have been struggling with the problem but the blank emergency-approvers are giving me a problem as they cause my code to return an error, I should be able to handle that but haven't figured it out. Also what is the unique key to the table 48_hour_pull as I will need one to pick up the data in the second query.

Brian
 
if it is blank it can return a ZERO that is what is preferable

as for unique it will be the Change ID

I really cannot tell you how much this means I have been working on this for the last week and now i am unable to figure it out so THANK YOU BOTH so very much!!
 
in the table it is actually EXPR1, sorry I have renamed it in the original database
 
This is what I have done.

Code:
Function codenumber(emergapp As String) As Integer

If InStr(emergapp, "EBF") = 0 Then
codenumber = 99
End If
Exit Function

If Not IsNumeric(Mid(emergapp, InStr(emergapp, "EBF") + 4, 1)) Then
codenumber = 98
Else
codenumber = Mid(emergapp, InStr(emergapp, "EBF") + 4, 1)
End If

End Function

query bjwfindcodenumber

SELECT [48_Hour_Pull].Expr1, [48_Hour_Pull].[Emergency-Approver], codenumber([emergency-approver]) AS codeNo
FROM 48_Hour_Pull
WHERE ((([48_Hour_Pull].[Emergency-Approver]) Is Not Null));

query modified yours

SELECT [48_Hour_Pull].Expr1, [48_Hour_Pull].[2-Change-Category], [48_Hour_Pull].[Severity-of-Change], [48_Hour_Pull].[Planned-Start], [48_Hour_Pull].[Planned-Finish], [48_Hour_Pull].Status, Approval_Codes.Reason
FROM (BJWfindcodenumber INNER JOIN 48_Hour_Pull ON BJWfindcodenumber.Expr1 = [48_Hour_Pull].Expr1) INNER JOIN Approval_Codes ON BJWfindcodenumber.codeNo = Approval_Codes.[Code #]
WHERE ((([48_Hour_Pull].[Severity-of-Change])="Emer Br/Fix") AND (([48_Hour_Pull].[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""")));

Does this look like anything that you would expect?

Its getting late here so will finish shortly but will try and visit tomorrow.

Brian
 
forgot the result.

Expr1 2-Change-Category Severity-of-Change Planned-Start Planned-Finish Status Reason
40544 CCT-Major Tier 1 Emer Br/Fix 03/01/2008 10:00:00 03/01/2008 10:30:00 Deferred Code Not Numeric
40543 CCT-Moderate Tier 2 Emer Br/Fix 03/01/2008 12:00:00 03/01/2008 13:00:00 Deferred Code Not Numeric
40494 CCT-Moderate Tier 2 Emer Br/Fix 31/12/2007 10:15:00 31/12/2007 11:45:00 Pending Reason Unknown
40498 ENS/MSO Emer Br/Fix 31/12/2007 08:30:00 01/01/2008 08:30:00 Closed Reason Unknown
40493 Remedy-Customer Care Emer Br/Fix 31/12/2007 19:00:00 01/01/2008 00:35:00 Closed Reason Unknown
40531 SAMSON-IT Collection Ops Emer Br/Fix 02/01/2008 14:00:00 05/01/2008 20:00:00 Deferred Reason Unknown
40459 SAMSON-Ref Tbl-Bill Op[Exempt] Emer Br/Fix 01/01/2008 22:30:00 01/01/2008 23:15:00 Pending Reason Unknown
40505 SAMSON-Ref Tbls-IT AR Ops Emer Br/Fix 01/01/2008 21:45:00 01/01/2008 22:00:00 Closed Reason Unknown
40541 SAMSON-Ref Tbls-Roaming Emer Br/Fix 03/01/2008 11:00:00 03/01/2008 14:00:00 Deferred Reason Unknown
40502 SAP-CRM: Master Data Updates Emer Br/Fix 01/01/2008 01:30:00 01/01/2008 04:00:00 Closed Reason Unknown
40536 SmartMart Emer Br/Fix 03/01/2008 03/01/2008 03:00:00 Pending Reason Unknown
40495 Vertex Emer Br/Fix 31/12/2007 23:00:00 01/01/2008 03:15:00 Closed Reason Unknown
 
I've obviously made a mistake in my function but it eludes me so will sign off and return after some zzzzzzzzzzzzzzzz's

Brian
 
Brian,

I am not sure exactly what you did but it looks good. Now comes the big question what do I do with it LOL.

I will wait for your reply before I miss something up lol
 
Couldn't let go, found error in code 1st endif and exit function in wrong order.
Forgot to say that I added to approvals code table.
Too tired for further work so have attached my current efforts.

Hope it works as required, but may need to revisit null emergency approver fields

Cheers
Brian
 

Attachments

Thanks I will try it out and let you know!!

Really appreicate all your efforts towards resolving this!!

Get some rest!!

Rue
 
Hi Rue
Nothing like a few zzz to clear the brain.
Attached new version of DB.

I have changed the Function and the query BJWfindcodenumber, null Emergency-Approver fields are catered for.

Queries bjwquery1/2 are part of my testing/development, 2 lists Reason against Emergency-Approver for a visual check that all are working as desired.

Once you have checked out if my ideas are what you want, then you can modify names and codes to suit yourself, you may abandon codes 97 98 and 99 and make them all 0 for example.

Let me know if all is ok or not as the case maybe.

Brian
 

Attachments

Had another go at doing it in 1 query and found the error that had stopped me last night, in the function freason I had coded [case #] instead of [code #], on's own syntax/coding errors are the most difficult to spot as nobody believes that they are that stupid.:o

All you need is the function Freason and BJWv2etc query.

Brian
 

Attachments

Well I am happy to report IT WORKS!!

That being said, this is the next part I forgot. Go figure right!

Currently it is pulling for the past 2 days. Here is my problem.

Tues, Wed, Thurs, Fri, Need to be prior 2 days standard report
Mon needs to be for the prior 3 days covering Friday, Saturday and Sunday.

My questions is can you put in the code a contingent line based on the day of the week you run it to pull for that specific time line?

Does that make sense?

Thank you again for helping get this fixed you are amazing!!!!
 
All things are usually possible, but total automation worries me as it often means inflexibility, which is what hard coding gives you.
What happens after a holiday such as Christmas?

I always drive my queries from forms picking up the criteria from the form, ok I may set defaults, but the selection criteria can be temporarily changed, thus in your case the from date/time would be Date()-2 and the to date/time Date(),these defaults would then appear on the form, today as,03/01/2008 and 05/01/2006 European format:D, the default time is 00:00:00, of course if anything happened at midnight on the 4th you would pick it up and it would show the 5th, strictly the to should be Date()-#00:00:01# to return 04/01/2008 23:59:59.
Ok all of that is a bit pedantic and you are probably thinking " What's with this guy?":)

You might want to consider picking up other criteria from the form as you may get requests for particular run codes or whatever, the point is forms give flexibility and bosses change their minds.

Brian

Edit Actually the From date/time default should be IIf(Weekday(Date())=2,Date()-3,Date()-2) to give you what you want most of the time.
 
Last edited:
Forgot to check for Dlookup returning a NULL eg if you had EBF-8, to do this put something like

If IsNull(freason) Then freason = "Code # out of Range"

after the Dlookup and before the End Function statements

Brian
 

Users who are viewing this thread

Back
Top Bottom