Code suggestions (1 Viewer)

[steve711]

Hello Everyone,

I need to generate a report that shows the number of NVG landings in the previous 60 days and how many days remaining before they become uncurrent. Each pilot must perform a minimum of 6 NVG landings every 60 days.

I have 2 queries for this - 1) totals the landings in the previous 60 days; 2) the other shows each NVG entry by pilot, date, # landings for that flight entry.

The code below works somewhat but not totally. It gives me the total number of landings but the days remaining is flawed. For instance:

7-16-11 to 9-13-11 (60 day window)
Pilot A flew 7-16-11 = 2 landings
8-8-11 = 8 landings
9-2-11 = 9 landings
9-10-11 = 3 landings

The output is Pilot A = 22 landings * 0 days remaining. Because of course my code is using the first date in the query of 7-16-11. I know this but couldn't figure the correct way.

So I need to evaluate from the most recent and work backwards to get the correct number of days remaining. Remember they need 6 in 60.

Any guidance/help would be appreciated as always.
Thanks.
Steve (coding for fun / pilot for work)

Dim nvgdate As Variant
Dim formlevel As Variant


numpilots = DCount("[Pilot_Name]", "qry_Pilot_NVG") - 1


For X = 0 To numpilots
formlevel = Me.lstNVGLandings.Column(0, X)


'Get 1st NVG date
    nvgdate = DLookup("[Log_Date]", "qry_Pilot_NVG_date", "Pilot_Name =" & Chr(34) & formlevel & Chr(34))
    nvgpilot = DLookup("[Pilot_Name]", "qry_Pilot_NVG", "Pilot_Name=" & Chr(34) & formlevel & Chr(34))
    nvglandings = DLookup("[PilotNVG]", "qry_Pilot_NVG", "Pilot_Name=" & Chr(34) & formlevel & Chr(34))

    backdate = Date - 59
    numdays = nvgdate - backdate
    
    MyPos = InStr(nvgpilot, ",")
    pilot = Left(nvgpilot, MyPos)
    
    'numdays = format
    If IsNull(numdays) = False Then
        If nvglandings > 5 Then
            lstCombine.AddItem (pilot & nvglandings & " HNVGO ** " & numdays & " days remaining"), X
        Else
            lstCombine.AddItem (pilot & "NVG UNCURRENT"), X
        End If
    Else
        'lstPilotNVG.AddItem (medic & totalnvglandings)
    End If
'--------------------
Next X

 

[Guus2005]

First i don't know what uncurrent means. If it means not current, that doesn't help.
second, a report is usually based on a recordset being a query or a table. why are you using a procedure when you could do this in a query?

What do you mean by "days remaining"?

 

[spikepl]

Suggestions:

Create a tblNVGCurrency containing PilotID, Landings60 and CurrentUntilDate

On each run, clear this table (in VBA)

For number of landings within the last 60 days: make a query that for each pilot totals the number of landings WHERE Log_date BETWEEN Date()-60 AND Date. Append PilotID and corresponding Landinsg60 to tblNVGCurrency

The currency issue is a bit more tricky: you need to find the latest date on which each pilot still has in total at least 6 NVG landings in his log and add 60 days to that. I think I would make some VBA:

1. Open the recordset (with the pilot log data, ORDER BY PilotID ASC AND Log_date DESC, so the most recent date is first)
2. For each PilotID, loop though that pilot's records, and add landings. When 6 or more is reached the Log_date is the date. Add 60 days to that date. Store date in the tblNVGCurrency for that pilot. If you base your report on a query (based on tblNVGCurrency) in the query you can calculate the days remaining from today's date until "uncurrency."

#2 uncurrent means that the requirement of 6 landings within last 60 days is no longer fulfilled. So if a pilot makes 6 landings today, he's "current" for another 60 days. If he made the 6 landings 30 days ago, he remains current for another 30 days, unless he makes more landings.

 

[Guus2005]

so it's a requirement to have at least 6 landings within 60 days to remain current. what happens is i am no longer current or uncurrent? can't i fly when i am uncurrent?

Just trying to understand the philosophy behind the restrictions.

 

[spikepl]

Flying is full of stuff like that When you are no longer current on some qualification, then you can no longer exercise it. Eg, as a pilot you may not carry passengers unless you are current on the type, which means a certain number of landings within a certain preceding period of time.

In this instance OP speaks of NVG landings - NVG=night-vision goggles. I presume a check ride (with an instructor) can requalify the pilot.

And the point of all the above is to prevent rusty fliers killing themselves or others

 

[steve711]

To clear a few things up although some of you have already done so.

Yes, NVG = night vision goggles. It is a sliding 60 day window always looking back from today 60 days and counting the number of times you landed with NVG's. To remain current a pilot must have made 6 landings in that 60 day window.

I have 2 queries that 1 grabs each pilot's name and the total number of landings in the 60 day window. The other query lists all pilots and each date they flew NVGs and the number of landings they made.

My theory is simple enough but the code escapes me. I would like to start by taking a pilot and looking through the second query - find the latest date of NVG landings - ask the question is this => 5 if so stop right there and calculate the days remaining for currency based on this date. If not hold onto the number of landings from this one and get the next latest date ask the question again and move on or quit.

I experimented with findlast and findprevious but this isn't working for me, probably because the query I'm using has all pilots (my organization only has 4).

I only recently added the days remaining code since my guys were asking for it. Previously I only showed them the total # of landings in the window. Which is great unless today is your last day of currency and tomorrow you become uncurrent (yes this is not a good word but this is what is used in aviation).

spikepl hit is on the head, once my guys become uncurrent they can no longer carry anyone in the aircraft (not good for a medevac pilot) if he wants to use NVG's.

Thanks for the discussion hopefully someone sparks an idea in my head that I can roll with, that seems to be the case for me as I've read this forum for a few years now.

Steve

 

[spikepl]

#6 WHat is it that escapes you in my suggestions as to how to go about it? For you haven't reacted to those at all.

 

[steve711]

#6 WHat is it that escapes you in my suggestions as to how to go about it? For you haven't reacted to those at all.

Hey spikepl, nothing as of yet. I'm working through your suggestion to see how it works for me. I like to keep my options open as usually there are multiple ways of accomplishing a single task. I'm away from work at the moment, until I get there to work on your idea others my chime in as well.

Thanks for the help, I do appreciate it...Mainly I was talking about myself prior to posting for help...

 

[ddodd]

Steve711, Can you show the table structure used to store this data? pilot id, date, #s of landing with NVG. I understand currency just like 3 takeoff/landing to full stop at night for Single Engine land... no NVG!

Also would your report have something like:
Report Date 2011/09/10

Pilot ID OldestdateOfLast6NVG NVG Current thru (date)
21 Fred 2011/04/21 ** NOT Current**
44 John 2011/08/01 2011/09/29 41 Days left on currency
19 Zach **Does Not have 6** ** NOT Current**

Something like this for the trainer scheduler?
Dan