Data Validation - Comparing new number to LAST entered data to check if within range

[JayRab]

I have a form that is filled out and on this form I capture equipment engine hours, I would like to put a data validation step in the beforeupdate event to check the existing data for the last engine hour entry and make sure the NEW entry is within 36 hrs of it.




I am not sure how to do this.

The last entered data would have to be sorted and filtered, based on the equipment name. Since the data contains 42 pieces of equipment and each piece of equipment can have between 2 - 20 entries/events per day, but only 2 of those entries/events contain engine hours and only 1 entry/event is ACTIVE at a time per piece of equipment. So filtered based on equipment ID and sorted to last entry or max entry, engine hours are usually only on INACTIVE/CLOSED events. .

I have used a Dlookup() code before to check to see if that equipment exists in the ACTIVE data for validation before, but this just gave me a Yes it exists or No it doesnt. How do I find exact info and compare it?

Any help would be appreciated. I can provide screenshots if that can help.

Thanks
JR

[June7]

So you need to pull the latest date/time for an engine hour entry? Maybe a DMax would accomplish that.

[JayRab]

Never used Dmax but ill look into. I dont need to pull it exactly. Just need to compare current entry is at within +- 36 of Last entry.

Ill play with Dmax and see how it goes.

Thanks

[rpeare]

Are you recording the total engine hours at the termination of an event? i.e. you send out a piece of equipment with 10 hours on the engine, when it comes back it has 12 hours, so you store 12, or do you record 2 for the hours of usage?

if it's the former the DMAX function june suggested will work just fine, if you have to add all the hours prior to the current record to get the total engine hours used you could use the DSUM function.

The only thing I would suggest is that you NOT use domain functions in queries (or as part of a data source for a form). They are extremely memory intensive and will slow things down. You may likely be able to do it with queries as well but we'd probably have to see a sample of the database to suggest something along those lines.

[JayRab]

rpeare :

The engine hours are recorded at beginning and the end of the day. They are actual engine hours so no need for a DSUM function.

I have noticed the domain functions have been slowing the queries down dramatically, mainly the Dlookup()functions I use inside queries with JOINS. Once I get to the optimazation phase of this project I will definitely post a sample so I can get help with running it, only problem with that is the database is fairly large 1 GB and continues to grow so might have to figure something out on getting sample size down.

Will post updates later this weekend when I get time to try it out.

[June7]

Yes, domain aggs can be slow. Nested subquery might be possible.

1GB already! How long been using?

[JayRab]

Was used minimally for the first 7 months of 2013 has been used extensively since then. I archive data from the used tables to an archive table monthly. So all daily reports/calculation pull from a small table. But I need all data for yearly analysis.

I currently have this data linked into excel for pivot table analysis, 8 different pivot tables from 1 data set.

I took over this so when I first got into it, there was a lot of corrupt data. I have been slowly finding ways to validate data and of course adding options. Just sucks because information is all over the place so have to do allot of queries with inner joins as well as imports from excel.

[rpeare]

Compact/Repair is your friend. especially if you're using domain functions and pivots/crosstabs.

Is this problem solved? Or are you still looking for a way to get the previous record's check in engine hours and compare them?

[JayRab]

Other projects are taking precident over this project will be later this week before i get a chance to get to this. Ill update accordingly.

[JayRab]

Ok I have worked on this today. I have create a query lstEqHrsQ, that lists all equipment and the max equipment hours. All equipment IDs listed once and max Date and Max Equipment Hours listed.


Now all i need to do is pull the value for a specific EQ ID, and compare to entered engine hours. Should I just use a DLookup() for this or is there a better way to do this.

Really all I need is a where statement in the query where [EQ ID] = "what is active in EQ ID txt box", and return the Equipment Hours.

]lstEqHrs = dlookup("[Equipment Hours]","lstEqHrsQ","[EQ ID]='" & Forms![Event Dashboard]![EQ ID] &"'"

Once I get this value above add an if statement

 if me.EngineHrsTxt > (lstEqHrs - 30) or me.EngineHrsTxt < (lstEqHrs +30)
msgbox "Engine hours exceed the 30 hr limit from last entry. Please double check your entry!"
Cancel = True
End if

Havent gotten this to work yet...just wondering if there is a better way beside a dlookup?

thanks

[rpeare]

if your EQ ID is a number (autonumber) your statement should be

lstEqHrs = dlookup("[Equipment Hours]","lstEqHrsQ","[EQ ID]=" & Forms![Event Dashboard]![EQ ID])

you can also throw in a

debug.print lstEqHrs

to see if it's pulling the correct value from your query.

and your if statement (in my opinion) should be something more like

if enginehrstext > (lsteqhrs - 30) then
    msgbox "Engine Hours entered is more than 30 hours less than last reported hours"
    <Do whatever else you're going to do>
elseif enginehrstext > (lsteqhrs + 30) then
    msgbox "Engine hours entered is more than 30 hours greater than last reported hours"
    <do whatever else you're going to do>
else
    <Do whatever you're going to do if everything is ok>
endif

Not planning for all possible outcomes of a true/false statement can lead to extensive hair pulling

[June7]

I think the DLookup in VBA is fine. I have scads of them throughout my db. Don't notice a performance issue.

[JayRab]

EQ ID isnt an auto number it is a text string. Ill try that

oh and thanks for the debug.print completely forgot about that. will help me immensely in my coding in the future.

Thanks again.

[JayRab]

Got it working.

Going to add some options and msgbox stuff will update shortly.

[JayRab]

Got it working great.

Just want to share code incase someone has a similar issues. Just created a query to list all equipment with last entry hours

Private Sub EngineHrsTxt_BeforeUpdate(Cancel As Integer)
Dim lstEngineHrs As Long
lstEngineHrs = DLookup("[MaxofEquipment Hours]", "[lstEqHrsQ]", "[EQ ID] = '" & Me![EQ ID] & "'")
Debug.Print lstEngineHrs
Debug.Print EngineHrsTxt
    If EngineHrsTxt < (lstEngineHrs - 30) Then
    Dim LResponse As Integer
    LResponse = MsgBox("Engine Hours entered is less than last reported hours!  Please double check you typed it correctly!" & vbCrLf & "Last Hours Reported - " & lstEngineHrs & vbCrLf & "Engine Hours Entered - " & EngineHrsTxt & vbCrLf & " If this Number is correct click YES" & vbCrLf & "To go back and change the number CLICK NO", vbYesNoCancel Or vbExclamation, "Equipment Hours Error")
        If LResponse = vbYes Then
             Cancel = False
        Else
            Cancel = True
        End If
    ElseIf EngineHrsTxt > (lstEngineHrs + 30) Then
      LResponse = MsgBox("Engine hours entered is more than 30 hours greater than last reported hours! Please double check you typed it correctly!" & vbCrLf & "Last Hours Reported - " & lstEngineHrs & vbCrLf & "Engine Hours Entered - " & EngineHrsTxt & vbCrLf & " If this Number is correct click YES" & vbCrLf & "To go back and change the number CLICK NO", vbYesNoCancel Or vbExclamation, "Equipment Hours Error")
        If LResponse = vbYes Then
             Cancel = False
        Else
            Cancel = True
        End If
    Else
        Cancel = False
    End If
              
End Sub