I've tried your code, and it works OK for me.
Are you sure it's really an endless loop? It will prompt for the three
input boxes for every record that your query is returning, and only puts
each row into the query results after the function calculates. I tested
with a small recordset (3 records), and got nine input boxes before the
query completed (as expected). So if your query has a large recordset,
you'll feel like it's an endless loop, even though it's not ;-)
A couple of other points:
When you declare a function, you should specify the type of data which it is
to return. In this case, since you're generating a string, your declaration
would be:
Function MyBins(fldDepth As Integer) As String
You can pass all your input values into your function (if you know them, as
I assume you do), when you call it, as follows:
Function MyBins(fldDepth As Integer, BinStart As Integer, BinEnd As
Integer, BinStep As Integer) As String
and remove the Dim statements and BinXXX = Inputbox ... lines from the code
in the function. This would eliminate the endless input boxes - you can
simply include the start, end and step values as other fields in your query,
and call the function using:
BinRange: MyBins([DepthField], [StartField]], [EndField], [StepField])
If fldDepth is less than BinStart, your function returns a zero-length
string, because MyBins never gets anything assigned to it. Is this what you
are expecting? As an aside, it's good practice to ensure that your
functions always return something - here, it could be a string such as
"Sorry, can't calculate what you asked!", or it could be a zero-length
string ("") or it could be vbNullString. This can be important if you are
doing any subsequent processing of your returned values - "" and Null will
give different results (and require different tests) if you are later using
them in other expressions.
I would have expected a "bin range" somewhere between the Start and End
values, modified by the fldDepth, but the results look strange (to me - they
may be exactly what you want). For example, with BinStart = 1, BinEnd = 24,
and BinStep = 4, calling MyBins with various values of fldDepth gives the
following:
fldDepth | MyBins
1 | 1 - 5
2 | 1 - 5
3 | 1 - 5
4 | 1 - 5
5 | 5 - 8
....
9 | 9 - 12
....
HTH,
Rob
Jessica said:
Doug,
Thanks for the info, but that generated another problem. When I run the
following code through the query I get an endless loop on my input boxes.
Any idea why this happens? When I put actual values in for these integers it
works perfectly, but not when I use an input box. Here's the code I am using
(fldDepth is being pulled straight from the query):
Function MyBins(fldDepth As Integer)
Dim BinStart As Integer
Dim BinEnd As Integer
Dim binStep As Integer
BinStart = Inputbox("Enter Start of Bin Range (Integers only)", "Bin
Starting Range")
BinEnd = Inputbox("Enter End of Bin Range (Integers only)", "Bin Ending
Range")
binStep = Inputbox("Enter Increment (Integers only)", "Bin Increment")
If fldDepth >= BinStart And fldDepth <= binStep Then
MyBins = BinStart & " - " & BinStart + binStep
End If
BinStart = binStep + 1
Do While BinStart < BinEnd
If fldDepth >= BinStart And fldDepth <= (BinStart + binStep - 1) Then
MyBins = BinStart & " - " & (BinStart + binStep - 1)
End If
BinStart = BinStart + binStep
Loop
End Function
Thanks!
Jessica
Douglas J. Steele said:
If it's in fact a function (i.e.: it returns a value), then yes, you can use
it in a query. Simply use it in an unused field in the query definition. Put
a name you want to refer to the result by (followed by a colon) in front to
make it easier on yourself:
CalculatedValue: MyFunction([Field1], [Field2])
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Greetings!
Is it possible to run a module function through a query as an output
field?
I have a function that looks at a field in the query, and then determines
a
category for that field based on its numerical value (categories change
frequently, so I can't use a table). I was wondering if I could run the
the
module directly in the query. Otherwise, I will use the query as
make-table
and update the field in the table through the module.
Any help would be great.
Thanks!
Jessica
