formatnumber need help with module

S

smile

Registered User.
Local time
Today, 05:30
Joined
Apr 21, 2006
Messages
212
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

PHP: 
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:

PHP: 
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
 
Last edited:
Maybe somethig like this in a standard module:
Code: 
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
 
RuralGuy said:
Maybe somethig like this in a standard module:
Code: 
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
Click to expand...

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?
 
Last edited:
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?
 
RuralGuy said:
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?
Click to expand...

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
 
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.
 
RuralGuy said:
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.
Click to expand...

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.
 
RuralGuy said:
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.
Click to expand...

Thanks for your help.

I could use IIF up to 7 levels deep right:

PHP: 
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:

PHP: 
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?
 
Since nobody else posted any ideas I posted my question on

http://www.utteraccess.com/forums/s...=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:

PHP: 
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:

PHP: 
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?
 
Last edited:
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
 
gemma-the-husky said:
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
Click to expand...

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?
Code: 
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
 
You must log in or register to reply here.

Similar threads

A
Solved SQL query advise for inventory item and set
Replies
11
Views
1,171
atzdgreat
A
L
Need help for Concat issue on access report
Replies
5
Views
116
DHookom
DHookom
D
Solved If...then or loop help
Replies
13
Views
400
Pat Hartman
P
F
Your Query does not include the specified Expression ‘ID’ as part of an Aggregate Function
Replies
8
Views
586
June7
June7
A
Need VBA Help with Printer Status
Replies
1
Views
469
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom