Can we use Case statement in ADP forms.

[indraT]

Hi There,

I have lot of iff statement in MS ACCESS mdb forms, which i converted to adp file. Some of the if statement i converted to case statment in query, but some of them i canot convert to query as i am using in forms. I change iff statment with case statement but its giving me error message, just wondering wheather we can use case statement in adp form or not, if yes how.

Thanks for help in advance.

Indra.

 

[cmmrfrds]

Yes, the Case statement will replace the iif in an SQL Server sql statement. If you need more help show an example of what you are doing.

 

[indraT]

Hi Cmmrfrds,

I have lots of iff statement in my form and report, which i could not able to use after i converted to adp from access db.

here is the formula.

=IIf([QuoteTarget]=0,0,[QuoteTarget]-[QuoteItemsPriced])

Where QuoteTarget is a field in the form where user enter the some price value to calculate the discounted value which is independent of query and QuoteItemsPriced is a sum of the expression from the query. when i try to convert this to case statement its display an error message. Could you please help me.

=Case when ([QuoteTarget]=0) then 0 else ([QuoteTarget] - [QuoteItemspriced]) end,

when i try to enter , its say the expression you have enter contain invalid syntax.

Thanks in advance.

Indra.

 

[cmmrfrds]

Is QuoteTarget a prompted field? That is not a field in the sql select statement. I am not sure I understand. Can you paste in your select statement so I can see the full syntax.

 

[indraT]

Actually now i included that field in table and as well as in query. [QuoteTarger] comming from query. and [quoteItemsprice] is equal to =Sum[DP], WHERE DP is the calculate field in query from other two field in query like DP is quantity * Price;. but i still having problem using case statement. Your help will be highly appriciated.

=Case when ([QuoteTarget]=0) then 0 else ([QuoteTarget] - [QuoteItemspriced]) end


Thanks again.

Indra.

 

[cmmrfrds]

The syntax looks okay provided the fields are from tables.

Are you trying to do something that references an alias? Like so.
Select
(quantity * Price) as QuoteItemsPriced,
Case when ([QuoteTarget]=0) then 0 else ([QuoteTarget] - [QuoteItemspriced]) end
From yourtable

If so, you cannot reference a alias in the same select statement. You can reference an alias from a subquery or derived table. In the same select you need to repeat the calculated field.

Case when ([QuoteTarget]=0) then 0 else ([QuoteTarget] - (quantity * Price)) end

 

[indraT]

Thanks for your help, but i just wanted to know how can i write case statement in the adp forms or report suppose i have a field in form which is not directly comming from the query but its using field from the query and calculating it in the control as =Case when ([QuoteTarget]=0) then 0 else ([QuoteTarget] - [QuoteItemspriced]) end

but its always give me error message saying syntax error, if i give one space in the starting of the = sign its does not dispay error message but when i view the form no output of that field come. I donot understand if you really understand what i mean or not, but i just wanted to know how to write case statement in the forms or report not in query. If you have any example please let me know.


Thanks a lot for all help.

Indra.

 

[cmmrfrds]

I have not done that type of syntax in a control on a Form, but you can reference a function in a control.

=yourfunction()

'- simple example.
Public yourfunction(apassedfield as integer) as long
Select Case apassedfield '
Case 1
yourfunction = 1
Case 2
yourfunction = 2
Case 100
yourfunction = 100
Case Else
yourfunction = 999
End Select
End Function

 

[indraT]

Thanks for the help. I will try this.

Indra.