Feebly trying to evaluate several fields by VBA

[Gingertrees]

Trying to classify businesses by a number of factors put together. I'd like to do this in a query; number of variable factors leads me to believe some of this needs to be a UDF in VBA. The classification system would be similar to the packages you can buy at a car wash (except pretend you can order each option separately, e.g. wash, undercarriage, but not tire-bright):

Good: wash, hot wax, hot dry
Better: wash, undercarriage flush, tire wash, hot wax, hot dry
Best: wash, undercarriage flush, tire-bright, Premium wax, spot-free rinse, soft-cloth

So the table would be wax (hot/Premium), undercarriage (yes/no), tires (NA/tirewash/tirebright), spotfree (yes/no), dry (hot/softcloth)

I need to figure out how to say
"if wax=premium, undercarriage=yes, tires=tire-bright, spotfree=yes, dry=softcloth, THEN type=BEST
Else if wax=hot OR premium, undercarriage=yes, tires=tirewash OR tire-bright, spotfree=no, dry=hot, THEN type=BETTER
Else if wax=hot OR premium, dry=hot, undercarriage=no, tires=no, spotfree=no, THEN GOOD
Otherwise type = NA"

Option Compare Database

Public Function TypeAchieved() As Variant
Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb

Set rs = db.Recordsets("CarPkgs")

‘pseudocode…
‘If (And(rs!wax=”premium”, rs!undercarriage=”yes”, rs!tires=”tire-bright”, rs!spotfree=”yes”, ‘rs!dry=”softcloth”)) Then TypeAchieved = “Best”
 
‘Else if(And(rs!undercarriage=”yes”, rs!tires/=”NA”,rs!spotfree=”no”)) Then TypeAchieved = “Better”
 
‘Else if(And(rs!wax=”hot”,rs!dry=”hot”)) Then TypeAchieved = “Good”
 
‘Else
‘End if

End Function

 

[xenou]

Good. It seems you learned a lot from this project too.

FYI, if you are not familiar with all of the vba debugging tools then you will find this page useful:
A Day in the Life: Programming: Excel VBA Debugging for Beginners

I usually walk through all of my code line by line - even if it isn't crashing - and check everything one last time.

 

[Gingertrees]

Cool link! That should help in writing better code, too.

And yes, I learned a lot, and can already think of 3 other ways to use the code we put together. It was a pain, but worthwhile!