After Update Event with power function

[Fish218]

I am working on error-proofing data entry into the database I created for fish data. I am trying to figure out how I can prevent keystroke errors on length and weight data. I plan to use an after update event in the weight field. I can use existing data to figure out the relationship between length and weight for each species (usually a power function e.g. y = 0.1111x^3.0701 for a particular species I work with). What I don't know how to do is write the code to compare the weight typed in to a range of weights expected for a fish of a certain length.



Given the above equation, a fish that is 16" long should weigh 552 grams. I need the code to say, if the fish is 16 inches long the weight should be 552 grams + or - 25%. Any suggestions?

[June7]

Options:

1. a table with fields for length, min weight, max weight, then use DLookup function:

If IsNull(DLookup("length","tablename","length=" & Me.Length & " AND " & Me.Weight & " BETWEEN MaxWt AND MinWt") Then
MsgBox "Weight entered is outside normal range. Is it correct?"
End If

2. a table with fields for length and average weight, then use DLookup function:

Dim AvgWt As Double
AvgWt = DLookup("AvgWt","tablename","Length=" & Me.Length)
If Me.Weight < AvgWt - 0.25 * AvgWt Or Me.Weight > AvgWt + 0.25 * AvgWt Then
MsgBox "Weight entered is outside normal range. Is it correct?"
End If

3. No table and calculate the AvgWt

Dim AvgWt As Double
AvgWt = some formula here
...

[Fish218]

My data entry form (and corresponding table) have field for species, length, weight, etc. Is it possible to make option #1 species specific?

[June7]

Yes, that would be another criteria in the DLookup WHERE CONDITION argument. Is species a text field? If so, need apostrophe delimiters.

"species='" & Me.Species & "' AND length=" & Me.Length & " AND " & Me.Weight & " BETWEEN MaxWt AND MinWt"

If the table has only weight, not MaxWt and MinWt, then I think you need option 2
AvgWt = DLookup("weight","tablename",""species='" & Me.Species & "' AND length=" & Me.Length)

[Fish218]

I like the Min/Max feature of Option #1. I could create a query that has species, min weight, and max weight, correct?

[June7]

You can create query with min and max weights with any of the options.