writing the
custom function is something that I'm not familliar with but I'd love to
learn.
ok then. custom functions are easy to set up, and you're going to love
writing them, because they're so useful. the beauty of a custom function is
that, when you make it a public function, you can call it from other
procedures, AND you can also call it from event property lines, from
customer menus and toolbars, from calculated fields in queries and
calculated controls in forms and reports - basically anywhere that you could
call a built-in function, such as Date().
let's look at the structure first. you need to be able to pass the values of
the Shipped, Cancelled, and Rated fields into this function, and per your
post the fields are Yes/No - which is to say, they're Boolean values. and
you want the status as a return value, which per your post is Text - that
is, a String value. so write the function as
Public Function isGetStatus(ByVal blnShipped As Boolean, _
ByVal blnCancelled As Boolean, _
ByVal blnRated As Boolean) As String
<code will go here>
End Function
note, if you write the above into a module window, then place your cursor
anywhere in the word Function and press F1, it'll take you to the relevant
VBA Help topic so you can read up on functions and how they work.
when you call the function from the query, make sure the three fields are
included in the query, and set up the calculated field as
MyStatus: isGetStatus([Shipped],[Cancelled],[Rated])
to analyse the conditions, i typed them into an Excel spreadsheet (just to
have handy columns and rows) as columns headed
Condition Shipping Cancelled Rated Status
and filled in the rows per your post.
set up that way, it's easy to see that if Cancelled = True, the status =
Cancelled, period; it overrides the other two field values. so
If blnCancelled Then
isGetStatus = "Cancelled"
if Cancelled does not equal True, then if Rated = True, it overrides the
remaining field value. so
If blnCancelled Then
isGetStatus = "Cancelled"
ElseIf blnRated Then
isGetStatus = "Rated"
and then shipped or not shipped is the final consideration, as
If blnCancelled Then
isGetStatus = "Cancelled"
ElseIf blnRated Then
isGetStatus = "Rated"
ElseIf blnShipped Then
IsGetStatus = "Shipped"
Else
IsGetStatus = "Not Shipped"
End If
put it all together, and you have
Public Function isGetStatus(ByVal blnShipped As Boolean, _
ByVal blnCancelled As Boolean, _
ByVal blnRated As Boolean) As String
If blnCancelled Then
isGetStatus = "Cancelled"
ElseIf blnRated Then
isGetStatus = "Rated"
ElseIf blnShipped Then
IsGetStatus = "Shipped"
Else
IsGetStatus = "Not Shipped"
End If
End Function
as i said, just save it in a standard module, and make sure the module has a
different name than the function. easy as pie!
hth
Frank said:
Thanks for the quick response. I'm an intermediate user and writing the
custom function is something that I'm not familliar with but I'd love to
learn.
The feild names are... [Shipped], [Cancelled], & [Rated]
Condition 1 - [Shipped]=Yes, [Cancelled]=Yes, [Rated]=Yes. Status =
"Cancelled"
Condition 2 - [Shipped]=Yes, [Cancelled]=Yes, [Rated]=No. Status = "Cancelled"
Condition 3 - [Shipped]=Yes, [Cancelled]=No, [Rated]=No. Status = "Shipped"
Condition 4 - [Shipped]=No, [Cancelled]=No, [Rated]=No. Status = "Not Shipped"
Condition 5 - [Shipped]=No, [Cancelled]=No, [Rated]=Yes. Status = "Rated"
Condition 6 - [Shipped]=No, [Cancelled]=Yes, [Rated]=Yes. Status = "Cancelled"
Condition 7 - [Shipped]=Yes, [Cancelled]=No, [Rated]=Yes. Status = "Rated"
Condition 8 - [Shipped]=No, [Cancelled]=Yes, [Rated]=No. Status = "Cancelled"
tina said:
well, you can use nested IIf() functions in a query, but they can get
convoluted and pretty ugly. instead, you might want to write a custom
function to return the value, and then call the function in a calculated
field in the query. nested If statements are much easier to read and
maintain in VBA. just write the function as Public, in a standard module,
and it'll be available everywhere in your database.
if you need help with specifics, post back with the fieldnames, statuses,
and the 8 "combination = status" logical statements, and i'll have a look.
hth
I've created an Order Management database.
I would like to assign a Status to each Order based on the condition of
three seperate check box feilds.
For Example:
[Shipped]=Yes
[Cancelled]=No
[Rated]=Yes
Under these conditions, the status of the order should = "Rated"
My appoach was to do this in a querry and try to use an IIF(AND statment.
This is a teqnique that I use in Excell often however it doesn't appear
that
Access likes this kind of statement.
There are four possible order statuses based on 8 different combinations
of
these three feilds. Does anyone have a suggestion on how to solve this
problem?
