I use formatnumber formula to calculate in my query

This is what I use now.

My fieldsize is double, decimal places 3, Format general number.

I use this to convert a value I enter into table to a proper case like

1 is for a pieces I enter number like 1 and get 1
2 is for a kilogram I enter 0.5 and get 0,500
3 is for a kilowatt-hour I enter 1 and get 1
4 is for Cubic metre I enter 1 and get 1

Kiekis2: IIf(1=[pagrindinis]![Tipas];FormatNumber([pagrindinis]![Kiekis];0);
IIf(3=[pagrindinis]![Tipas];FormatNumber([pagrindinis]![Kiekis];0);
IIf(4=[pagrindinis]![Tipas];FormatNumber([pagrindinis]![Kiekis];0);FormatNumber([pagrindinis]![Kiekis];3))))

I need to add one more type that is the same as 1 can I write:

Kiekis2: IIf(1 or 5=[pagrindinis]![Tipas];FormatNumber([pagrindinis]![Kiekis];0);
IIf(3=[pagrindinis]![Tipas];FormatNumber([pagrindinis]![Kiekis];0);
IIf(4=[pagrindinis]![Tipas];FormatNumber([pagrindinis]![Kiekis];0);FormatNumber([pagrindinis]![Kiekis];3))))

Someone please help me to make a module with above information so I could add statements like

if case1 then this
if case2 t this etc.

Thanks

Maybe somethig like this in a standard module:
Public Function Decode(InCode As Integer, InField As Variant) As Double
'-- Used in a query like:
'-- Kiekis2:=Decode([Tipas],[Kiekis])

Select Case InCode
Case 1, 3, 4, 5
Decode = FormatNumber(InField, 0)
Case Else
Decode = FormatNumber(InField, 3)
End Select

End Function

Maybe somethig like this in a standard module:
Public Function Decode(InCode As Integer, InField As Variant) As Double
'-- Used in a query like:
'-- Kiekis2:=Decode([Tipas],[Kiekis])

Select Case InCode
Case 1, 3, 4, 5
Decode = FormatNumber(InField, 0)
Case Else
Decode = FormatNumber(InField, 3)
End Select

End Function

I tried your code, created module and pasted the code.
Then In my query I added a field Kiekis3:=Decode([Tipas],[Kiekis])
Then I got error: The expression you entered contains invalid syntax

If I changed to Kiekis3:=Decode("[Tipas],[Kiekis]")
I get error: wrong number of arguments used with function in query express ion

Please help.

Did you add the field in the query builder? The syntax provided will not work directly in SQL. In SQL I would think something like:
Decode([Tipas],[Kiekis]) As Kiekis3
...assuming [Tipas] and [Kiekis] are both fields in the current query.

I just added the code Kiekis3:=Decode([Tipas],[Kiekis]) in my query in design view. I changed filed name from Kiekis2 to Kiekis3 because I still left my old field named Kiekis2 in the query and I can't have two fields have the same name.

Still I get: The expression you entered contains invalid syntax. What did I do wrong?

I just put the equivilent of:
Kiekis3: Decode([Tipas],[Kiekis])
...in a querybuilder Field Area and it worked just fine. What version of Access are you using?

I just put the equivilent of:
Kiekis3: Decode([Tipas],[Kiekis])
...in a querybuilder Field Area and it worked just fine. What version of Access are you using?

I'm using access 2007 but my database is in access 2000 *.mdb file format.

I have a table named: "tipas"

with fields:
ID autonumber
Tipas Text

Then on table named: "pagrindinis"

I have lookup field "Tipas", data type Number to get data form table "Tipas".

I then designed a query using wizard from table "pagrindinis" that has fields:

Kiekis
Tipas

So field Tipas is looked up in table Tipas that stores ID and text value like


ID Tipas
--------------
1 pcs
2 Kg
3 KW
4 M3

You're not using Evil Lookup Fields (http://www.mvps.org/access/lookupfields.htm) are you?

You're not using Evil Lookup Fields (http://www.mvps.org/access/lookupfields.htm) are you?

I must admit I am using them :rolleyes:

Not a good plan. It is OK to use Lookup's *but* with ComboBoxes at the Forms level and not the table level. It also seems you have a field named "Tipas" in a table named "Tipas". Another bad idea. Name the table "tblTipas" and see what happens.

Not a good plan. It is OK to use Lookup's *but* with ComboBoxes at the Forms level and not the table level. It also seems you have a field named "Tipas" in a table named "Tipas". Another bad idea. Name the table "tblTipas" and see what happens.

Tried it got same error: The expression you entered contains invalid syntax

I think that access don't know what to do with field Tipas as it is being pulled by lookup from another table. IIf function worked OK but now I need more options to choose from that is why I'm looking how to get a working module sollution without changing my database alot.

Perhaps you can update your code to reflect that Tipas is pulled by lookup field? Or is that not possible?

found some sample code with function switch anyone could give a hand if possible to make it work?

I'm sorry but I have no experience using Lookup Fields and would prefer not to fix your problem with a BandAid. Maybe someone else will jump in here.

I'm sorry but I have no experience using Lookup Fields and would prefer not to fix your problem with a BandAid. Maybe someone else will jump in here.

Thanks for your help.

I could use IIF up to 7 levels deep right:

IIf (1=[pagrindinis]![Tipas]; FormatNumber([pagrindinis]![Kiekis];0);IIf (2=[pagrindinis]![Tipas]; FormatNumber([pagrindinis]![Kiekis];3);IIf (3=[pagrindinis]![Tipas]; FormatNumber([pagrindinis]![Kiekis];0);IIf (4=[pagrindinis]![Tipas]; FormatNumber([pagrindinis]![Kiekis];0);IIf (5=[pagrindinis]![Tipas]; FormatNumber([pagrindinis]![Kiekis];0); ) ) ) ) )

Then I taught to reverse the logic and get the same with 1 IIF
I made my IIF modification like this:

IIf (2=[pagrindinis]![Tipas];FormatNumber([pagrindinis]![Kiekis];3);FormatNumber([pagrindinis]![Kiekis];0))

Still I have aproblem that I must edit each query that uses this function and that takes time and effort.

How can I call this IIF function from a module with code perhaps that would work?

For example I would put this in my query Kiekis3: my_custom_iif_module

and what should I enter into the module?

I could use IIF up to 7 levels deep rightThat is correct.

Since nobody else posted any ideas I posted my question on

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=82&Number=1601446&Zf=&Zw=&Zg=0&Zl=a&Main=1601446&Search=true&where=&Zu=136576&Zd=l&Zn=&Zt=c&Zs=&Zy=#Post1601446&Zp=

Got some replies that I should not use FormatNumber in module, etc. I don't get it is the syntax wrong or it is not possible to do?

Still I have aproblem that I must edit each query that uses this function and that takes time and effort.

So if you know how to call IIF function from module I would appreciate it. Thanks.

I use the code you gave me:

Public Function Decode(InCode As Integer, InField As Variant) As String
'-- Used in a query like:
'-- Kiekis2:=Decode([Tipas],[Kiekis])

Select Case InCode
Case 1, 3, 4, 5
Decode = FormatNumber(InField, 0)
Case Else
Decode = FormatNumber(InField, 3)
End Select

End Function

and I changed the call to Kiekis2:decode([Tipas];[Kiekis])

It started to work :)

However if I encaunter a case that is not specified I would like to get a popup that would say case 6 is undefined please add it to Tipas table. Instead I now get an error.

however I have another question:

On utteraccess I got a suggestion that I should use Format instead
but the code won't work:

Option Compare Database
Option Explicit

Public Function fncKeksas(kiekis as Variant, tipas as Variant) As Variant
If isNull(kiekis) = True Then
fncKeksas = Null
ElseIf isNull(tipas) = True Then
fncKeksas = kiekis
Else
fncKeksas = Format(kiekis, Choose(tipas, "0", "0.000", "0", "0"))
End If
End Function

What implementation is better?

in your table for units, cant you store a scale factor (ie 1,1,1000,1 etc) depending on the unit

then you dont need any cases/ifs etc, just a multiply in whatever query you are using

in your table for units, cant you store a scale factor (ie 1,1,1000,1 etc) depending on the unit

then you dont need any cases/ifs etc, just a multiply in whatever query you are using

The Case table that I call Tipas has
autonumber field and Unit field
---------------------------------------
1 Unit Name
2 Unit Name
3 Unit Name
4 Unit Name

Is this what you had in mind?
Public Function Decode(InCode As Integer, InField As Variant) As String
'-- Used in a query like:
'-- Kiekis2:Decode([Tipas],[Kiekis])

Select Case InCode
Case 1, 3, 4, 5
Decode = FormatNumber(InField, 0)
Case 2
Decode = FormatNumber(InField, 3)
Case Else
MsgBox "[" & InCode & "] is undefined, please add it to Tipas table", vbCritical
End Select

End Function