Changing field based on size

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

Hope this insn't a double post the first didn't seem to go..

I have data that shows the measurements of an item. The fields are:

Length (L)
Height (H)
Width (W)

Unfortunately our users can’t seem to enter in the right data for the right
field, meaning the Length might be in the Height field. I am unable to do
the check during data entry since I am just linking to this table and not the
owner.

A constant in this is:
Length will always be the largest
Height will always be the middle
Width will always be the smallest.

How could I code this in vb to check and update this with out many if
statements?
 
Use the BeforeUpdate event procedure of the form to run the checks.

This example assumes you don't have to cope with the case where one of the
numbers is missing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Height > Me.Length Then
Cancel = True
MsgBox "Height cannot be greater than Length."
End If
If Me.Height < Me.Width Then
Cancel = True
MsgBox "Height cannot be less than Width."
End If
End Sub
 
Unfortunately I just linking to this table and do not have access to the
front in where they enter in the data. So I am Updating a table and wanting
to convert it at this point.

Right now I writing something like this, but there has got to ba a better way.

Dim L, H, W As Double

If Elength < EHeight Then
L = EHeight
End If
If Elength < EWidth Then
L = EWidth
End If

rs.edit
ELength = L
rs.Update

I then will continue for the Height and Width.
 
Alright, this is what I wrote. My question now is there a better way? I
thought about creating a table where I put all 3 sizes in and then query on
the table using group by then picking it that way. Which way is best, or is
there a better way?
My code below. ( I hadn't debugged it yet, so might be some issues still )

Dim L, H, W As Double

rs.Edit

' Correct Length
If ELength > EHeight And ELenght > EWidth Then
L = ELenght
Else
If EHeight > EWidth Then
If ELength < EHeight Then
L = EHeight
End If
Else
If ELength < EWidth Then
L = EWidth
End If
End If
End If

' Correct Height
If EHeight < ELength And EHeight > EWidth Then
H = EHeight
Else
If ELength < EWidth Then
If EHeight < ELength Then
H = ELength
End If
Else
If EHeight < EWidth Then
H = EWidth
End If
End If

' Correct Width
If EWidth < ELength And EWidth < EHeight Then
W = EWidth
Else
If ELength < EHeight Then
If EWidth > ELength Then
W = ELength
End If
Else
If EWidth > EHeight Then
W = EHeight
End If
End If
End If

rs!ELength = L
rs!EHeight = H
rs!EWidth = W

rs.Update
 
Okay, so you want to fix existing data rather than prevent bad entries.
Without going into the detail, the approach you suggested should work.

It would probably be possible to do the job by executing a couple of UPDATE
queries as well.
 
I wish I could fix the problem at the front end, but an outside source
maintains the programs and data. I am just using ODBC to link to it and
retreive the data.

I decided to put the data into a table then sql it with order by to get the
data that I need. Thanks all!
 
Back
Top