IIF([Or/And/Else])

[Tezdread]

I'm probably missing funamental understanding I've tried various options but by chance can't get any of them to work. Well that and the complexity of the queries I need running...

I'll try and explain the whole thing and if some one can give me some pointers on how I can achive this that would be great...

There are 3 queries that I need:

Query 1, This is to give a value of Yes or No, which will be used in the second query.

This is the criteria that needs to be matched in order to get a Yes

Customer = True
Purchases = False
Contacted = False
NumPurchases1 <= 0
NumPurchases2 <= 0
NumPurchases3 <= 3
MainScore >= 800

("Yes" would be added to the same table in field "ValuedCustomer")

So, if the above gets a Yes the following will be added and queried. The below needs to be matched

ValuedCustomer = Yes
MainScore is between 800 And 849 = VC5
MainScore is between 850 And 909 = VC4
MainScore is between 910 And 939 = VC3
MainScore is between 940 And 1048 = VC2
MainScore is > 1049 = VC1

Here the VC score would be added to the table under VCScore and then used in the next query.

(This is the worst of them...)

The VCScore and another field 'Expenditure' from the table will be used:

If VCScore = VC5 Or VC4 And Expenditure Between 15 and 32, Then this is the value 7000
If VCScore = VC5 Or VC4 And Expenditure = 40, Then this is the value 8500
If VCScore = VC5 Or VC4 And Expenditure = 50, Then this is the value 10500
If VCScore = VC5 Or VC4 And Expenditure = 60, Then this is the value 8500
If VCScore = VC5 Or VC4 And Expenditure = 65, Then this is the value 13500

If VCScore = VC3 And Expenditure Between = 15 And 25, Then this is the value 9000
If VCScore = VC3 And Expenditure = 32, Then this is the value 9500
If VCScore = VC3 And Expenditure = 40, Then this is the value 11500
If VCScore = VC3 And Expenditure = 50, Then this is the value 14500
If VCScore = VC3 And Expenditure Between 60 And 65, Then this is the value 16000

If VCScore = VC2 Or VC1 And Expenditure Between 15 And 17, Then this is the value 8500
If VCScore = VC2 Or VC1 And Expenditure Between 25 And 32, Then this is the value 13500
If VCScore = VC5 Or VC4 And Expenditure = 40, Then this is the value 16500
If VCScore = VC5 Or VC4 And Expenditure Between 60 And 65, Then this is the value 20000
If none of the above is matched the value should be "Declined"

Can someone explain the best way to get this query sorted (apart from paying a pro) ;-)

If you can point me to any info on the web that will help me get started on VB that might help

 

[Mile-O]

This is the criteria that needs to be matched in order to get a Yes

Customer = True
Purchases = False
Contacted = False
NumPurchases1 <= 0
NumPurchases2 <= 0
NumPurchases3 <= 3
MainScore >= 800

There's your problem. Your table structure is not normalised. You should not have fields called NumPurchases1, NumPurchases2, NumPurchases3 as this is indicative of a repeating group which means that your database does not even reach first normal form, never mind third normal form.

 

[Tezdread]

sorry should have said, these names are only for example.
Can't post the 'real' field names etc due to company policies yada yada yada...

If we can ignore the names as I'll change all this later, it's just the way to code it, that's causing the headaches.

 

[Tezdread]

Now armed with a VB Book for begineers the battle goes on...

Ok, thinking about this differently, as the first two querie parts work fine using the iif statement in the query would it be better / possible to have the third part as the coded query (like that you suggested SJ?) so they all work together?

That way I was thinking that the VB part would contain three if then else statements and then the outcome would be picked up by another query?

 

[Mile-O]

Can't post the 'real' field names etc due to company policies

Field names? Are you sure. I would understand if you couldn't post field contents due to their sensitive nature but the names of fields? What could be sensitive about them?

 

[Tezdread]

lol yeah right

Unless this is a "news group" which it technically isn't I shouldn't even be posting 'anything'

Policy excerpt:

6.2 Contributions to News Groups may not be used to convey technical information or assist third parties by providing opinions or advice that could adversely affect the company.

6.3 Participation in Chat Rooms and Message Boards is not permitted.

They give me tasks to do that leave me with a big headache and no way of getting help...but obviously if my posting was raised as an isssue the work would get done at all...

 

[Tezdread]

One step forward one step back...

I thought I'd be able to get there with the full part (3rd query) but not so and when I broke it down to the most basic parts it still doesn't work??

Public Function QueryValues(ByVal VPScore As String, ByVal Spenditure As Long) As String
    On Error GoTo Err_QueryValues
    If VPScore = RB5 Then
        Select Case Spenditure
            Case Is <= 32
                QueryValues = "200"
        End Select
            Else
                QueryValues = "Decline"
                    End If
    Exit Function
Err_QueryValues:
    QueryValues = "Decline"
End Function

In my query I have this - MaxGift: QueryValues([VPScore],[Spenditure])

Now I can see why some of the query results show #Error and this is because there are blank fields, so how can I overcome this?

As for why it's not showing the "200" I don't know? I've checked the tables and at least one of them matches the criteria: VPScore = RB5 & Spenditure = 30.

Can you see what's going wrong?

 

[DALeffler]

How about this:

Public Function QueryValues(ByVal VPScore As String, ByVal Spenditure As Long) As String

QueryValues = "Decline"

On Error GoTo Err_QueryValues
    If VPScore = "RB5" Then
        Select Case Spenditure
            Case Is <= 32
                QueryValues = "200"
         End Select
    End If

Exit Function

Err_QueryValues:
End Function

 

[Tezdread]

thanks doug,

I tried it but got a compile error: Function call on left-hand side of assignment must return Variant or Object?

I did notice one of the problems with the first code though because before the RB5 didn't have quotes but when I added them, one of the records gave the right results.

Can you explain what I need to put into the other code so that it doesn't throw an error when it comes to a blank field?

I thought I could simply add an Or to the first line but this didn't work either:

If VPScore = RB5 Or RB4 Then

 

[Mile-O]

I still think your problem is based upon a poor design.

 

[Tezdread]

SJ that's fair enough, you may well be right and I don't doubt you know a heck of a lot more than me, however you haven't seen the database design so why do you assume it is this that is causing the problems?

The first bit of code you gave me works fine but I just don't know where to start in changing it to include everything I need, so as far as I'm concerned the problem is with my lack of knowledge in VB and not the DB design, but I could obviously be wrong.

I could post the database after making all the name changes but in the past it seems people don't want to download the DB to look at it....If I do this and the design is all wrong and I first sort out the design will you help me sort this query?

I know I'm asking a lot and my appreciation doesn't pay the bills but I don't have any other way of doing this.