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
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.
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!!
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.
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
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.
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.
All you need is the function Freason and BJWv2etc query.
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, 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.