If Then / ElseIf Problem

[ch5150]

My code works to a point... As long as there are only 4 digits in the entry to update the field it works. However, if there are more or less than 4 digits in the entry to update the field [JobTotalFootage] then the response is wrong in [SetsPerHour] or it gives no response. Can someone help?




Private Sub JobTotalFootage_AfterUpdate()
If [JobTotalFootage] < "3000" Then
[SetsPerHour] = "10"
ElseIf [JobTotalFootage] < "7000" Then
[SetsPerHour] = "6"
ElseIf [JobTotalFootage] > "6999" Then
[SetsPerHour] = "3"
End If
End Sub

[pbaldy]

It appears you're trying to do a numerical comparison with a text value. What is the data type of JobTotalFootage? I would use a numeric data type, and not have any quotes around the values in the tests.

[ch5150]

I changed the data type in the underlying table from text to number and removed the quotes around the three test values but left the return values in quotes in the code and it appears to work correctly. Thank you very much... I had a real rough day and it was nice to end it with at least a small victory. It may be that later as I develop this application that I may run up against this with the return values, but at least now I'll know where to look.
Sincerely,
ch5150

[pbaldy]

Happy to help!

[ranman256]

try not to use ELSEIF, use SELECT CASE , much easier to read / decode

select case val([JobTotalFootage])
case  < 3000
    [SetsPerHour] = "10"
case < 7000
    [SetsPerHour] = "6"
case > 6999
    [SetsPerHour] = "3"
End select