Mr. Walsh,
First of all, thank you for all of your efforts. I think it's obvious
that
I need to brush up on my VBA knowledge if I am to continue along this
tack.
You have been a great help. I am, however, under the gun to finish at
least
this part of what I started. Since you have helped me this far, perhaps
you
are willing to take me the rest of the way?
Anyway, here goes...
The reason I am trying to extract the longest of the dimensions from the
[Size] field is that we use it to determine the pricing structure for
certain
processes. I've got the form to produce the longest dimension [Length],
now
I need to take this information and apply it to a pricing structure.
Here's
my code minus the error handling. I'm not getting any results. That is,
my
[Price] text box remains blank.
Private Sub Form_Load()
Dim dimension1 As Integer
Dim dimension2 As Integer
Dim dimension3 As Integer
Dim Length As Integer
Dim Price1 As Integer
Dim Price2 As Integer
Dim Price3 As Integer
Dim Price4 As Integer
Dim Process_Code As Integer
Me.dimension1 = Split([Size 1], "X")(0)
Me.dimension2 = Split([Size 1], "X")(1)
Me.dimension3 = Split([Size 1], "X")(2)
Me.Length = MaxOfList(Eval(Me.dimension1), Eval(Me.dimension2),
Eval(Me.dimension3))
If Me.Process_Code = "SLA*" Then
If Eval(Me.Length) <= 4 Then Me.Price = 0.64
If Me.Length <= 10 Then Me.Price = 0.77
If Me.Length <= 20 Then Me.Price = 1.66
If Me.Length <= 30 Then Me.Price = 2.32
If Me.Length <= 40 Then Me.Price = 3.57
If Me.Length <= 50 Then Me.Price = 5.23
If Me.Length <= 60 Then Me.Price = 9.14
End If
I can't promise you I won't bug you again, but if you can clear the clouds
a
little bit, I will sure try not to!
--
Why are you asking me? I don''''t know what I''m doing!
Jaybird
Michel Walsh said:
If a VBA function does not work in a query, you can define it into one of
your own public standard module:
-----------------
Public Function MySplit( Arg AS String) As String()
MySplit=Split(Arg)
End Function
-----------------
and now, you can use MySplit in your query. (instead of Split), when
executed within Access (won't work when executed from outside Access).
Hoping it may help,
Vanderghast, Access MVP
Well, I guess I'm going to have to figure out how to handle the
errors...
You are right, of course. Examining the table behind the form reveals
that
there is data that doesn't use the same format. This was a huge
breakthrough, though. Many thanks. Any Idea how to use Split in a
query?
When I try it, I get a message saying that it isn't defined.
--
Why are you asking me? I don't know what I''m doing!
Jaybird
:
Probably because the initial string does not have 3 'parts', so Split
cannot
access the third part ( the first part is 0, so 2 is the third
part).
If
that is an error in your data, you can only ask who is responsible for
it
to
correct it. Otherwise, you can fail to trap the error with a
on-error-resume-next, or check the limit:
Dim upperLimit = UBound(Split( .... ) )
if(upperLimit>=0) dimension1=Split(...)(0)
if(upperLimit>=1) dimension2=Split(...)(1)
if(upperLimit>=2) dimension3=Split(...)(2)
Hoping it may help,
Vanderghast, Access MVP
I would like to post a follow up question, if I may... I'm a VBA
moron
or
close to it, and the code I submitted earlier was created on a VBA
laboratory
environment as an experiment. When I go to use this code in my
actual
application, I run into all kinds of issues like "object 'Split'
does
not
exist, and errors that tell me my variables are out of range.
What's
different? I copy and pasted the codes, changed the appropriate
field
names,
etc.
I would also like to know how I get the results of a function to
become
my
value for a textbox. My code doesn't seem to work in the
ControlSource
property either.
--
Why are you asking me? I don''''t know what I''m doing!
Jaybird
:
Max, on String, is like a dictionary: "ABCD" comes before "Z"
so
"1234" comes before "9", while 1234, numerical comes after 9.
Try:
Lengthofpart = MaxOfList(eval(dimension3), eval(dimension2),
eval(dimension1))
Hoping it may help,
Vanderghast, Access MVP
I've attempted to use Allen Brown's "MaxofList" function to
determine
the
greatest of three variables: Dimension1, Dimension2, and
Dimension3.
These
were all derived from a goofy text field that I inherited called
Size
(LxWxH). Length is by default the longest dimension. Anyhow, I
used
the
Split function to separate my numberical values from my delimiter
(x).
The
thing is, I can't get MaxofList to select the highest value. Any
Ideas?
Here's my code:
Option Compare Database
Option Explicit
Private Sub Dimensions_MouseDown(Button As Integer, Shift As
Integer, X
As
Single, Y As Single)
Dim dimension1 As String
Dim dimension2 As String
Dim dimension3 As String
Dim Lengthofpart As Integer
Dim Widthofpart As Integer
Dim IntMsg As Integer
dimension1 = Split([Dimensions], "x")(0)
dimension2 = Split([Dimensions], "x")(1)
dimension3 = Split([Dimensions], "x")(2)
Lengthofpart = MaxOfList(dimension3, dimension2, dimension1)
Widthofpart = MinofList(dimension3, dimension2, dimension1)
IntMsg = MsgBox("The dimensions are " & dimension1 & " " &
dimension2 &
" " & dimension3 & " The Length is " & Lengthofpart & ". The
width
is
"
&
Widthofpart)
End Sub
Allen's code is saved as a module called modMaxofList and defines
the
MaxofList function.
