SQL query Swith function 3

[jeronimus1980]

Dear Reader,

Hopefully somebody can help me with the following"

(Apologies already in advance for the poor explanation of the problem due to the fact that I am still pretty unfamiliar with the access/sql terminology.)

part of my script: (tonnes and X are already assigned)

Switch([x]>=10,[tonnes],
[x]<010,0) AS Process1,

Switch([x]>=5 And [x]<10,[tonnes],
[x]<5 And [x]>=10,0.0) AS process2,

Switch([x]<5,[tonnes],
[x]>=5,0) AS Process3,

Questions:

1> The "and" function is not working correctly, where is my mistake in formatting this function?

2> I use the numbers 5 and 10 to determine the used process. These number can/will change. These numbers are also stored in the table. Can I somehow use a link to the other column instead of the real number (like you easily can do in Excel)?

3> Is there a way to do this switch function shown above more efficiently as this function increases the query time (got lots of data).

Thank you for your time!

Jeronimus

 

[dhookom]

I'm not sure how this could ever evaluate to true:
[blue][x]<5 And [x]>=10[/blue]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

A starting point:
IIf([x]>=[10 field],[tonnes],0) AS Process1,
IIf([x]>=[5 field] And [x]<[10 field],[tonnes],0) AS process2,
IIf([x]<[5 field],[tonnes],0) AS Process3,

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jeronimus1980]

Thanks for the above replies.

What is the difference between the "switch" and "IIF" function? I updated the function but still have trouble with the following: Why is the [table2.Y] not accepted in the IIF function as shown below:

SELECT

Table1.ton,
Table1.X,
Table2.Y,


IIf([X]>=[table2.Y],[table1],0) AS Process1
==========

FROM ...etc



Thanks again!!!

 

[PHV]

IIf(Table1.X>=Table2.Y,Table1.ton,0) AS Process1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Reviewing this, I would not use either Switch() or IIf() in a query expression. This looks to me like business logic that should be placed in either a lookup table of ranges or in a public function.

I would expect the values 5 and 10 will change at some future date. I would not want to hunt down every place I used an expression with hard-coded values of 5 and 10.

A start of a public function might look like the following. [red]When[/red] the 5 and 10 change, you open the module "modBusinessLogic" and you modify the code.

Public Function GetProcess(dblX As Double, _
        dblTonnes As Double, _
        intProcess As Integer) As Double
    GetProcess = 0
    Select Case intProcess
        Case 1
            If dblX >= 10 Then
                GetProcess = dblTonnes
            End If
        Case 2
    '.... etc ....
            
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeronimus1980]

Thanks again,

PHV: I tried your method but got an "#Error" in the access table under Process1. (When I changed the Table2.Y to a value it worked, but this is not what I wanted).

Dhookom: As I just started with Access and sql last weekend so I have a hard understanding your code recommendation. But I am working on it right now to get more familair with that.

Thanks!

Jeroen

 

[MajP]

Like DHookum I would avoid the switch and nested iif. These are inefficient and can be very slow. There are many articles on this subject, but the biggest reasons is that the functions do not short circuit and can give you a runtime error if not careful. DHookum's select case will exit once a criteria is met, not so in the switch or iif. They will test every case. If testing one case causes an error such as a division by zero the whole function errors.

 

[PHV]

PHV: I tried your method but got an "#Error"
I think it's now time to post the whole SQL code ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jeronimus1980]

IT WORKS!

Found out that I had the column of table2 formatted as text eventhough it looked otherwise as I populated already with numbers. Guess a standard beginners mistake.

Thanks you guys. made my life a lot easier today and got at least something done for a change

After I finished this I will start looking into the public function in more detail.

Have a good day all.

Jeronimus