Solved calculate textbox report (1 Viewer)

[Akai90]

hi,

i have report need to calculate how many room have. total room have 18 field,

below is the code i use

=IIf(IsNull([JENISBILIK1]),"0",IIf(IsNull([JENISBILIK2]),"1",IIf(IsNull([JENISBILIK3]),"2",IIf(IsNull([JENISBILIK4]),"3",IIf(IsNull([JENISBILIK5]),"4",IIf(IsNull([JENISBILIK6]),"5",IIf(IsNull([JENISBILIK7]),"6",IIf(IsNull([JENISBILIK8]),"7",IIf(IsNull([JENISBILIK9]),"8",IIf(IsNull([JENISBILIK10]),"9",IIf(IsNull([JENISBILIK11]),"10",IIf(IsNull([JENISBILIK12]),"11",IIf(IsNull([JENISBILIK13]),"12",IIf(IsNull([JENISBILIK14]),"13","14"))))))))))))))

it can only put until no 14, if i put ,IIf(IsNull([JENISBILIK15]),"14" it will got error

the expression is to complex

any idea ?

i need to put 4 more untul i get 18

 

[theDBguy]

You could use your own custom function.

 

[Akai90]

You could use your own custom function.

such as ?

 

[jdraw]

What exactly are you trying to achieve? Please provide a clear statement, preferably with an example.
If English is not your mother tongue, then please be precise in your own language, then use Google translate(or similar) and post.

 

[GPGeorge]

hi,

i have report need to calculate how many room have. total room have 18 field,

View attachment 98115
below is the code i use

=IIf(IsNull([JENISBILIK1]),"0",IIf(IsNull([JENISBILIK2]),"1",IIf(IsNull([JENISBILIK3]),"2",IIf(IsNull([JENISBILIK4]),"3",IIf(IsNull([JENISBILIK5]),"4",IIf(IsNull([JENISBILIK6]),"5",IIf(IsNull([JENISBILIK7]),"6",IIf(IsNull([JENISBILIK8]),"7",IIf(IsNull([JENISBILIK9]),"8",IIf(IsNull([JENISBILIK10]),"9",IIf(IsNull([JENISBILIK11]),"10",IIf(IsNull([JENISBILIK12]),"11",IIf(IsNull([JENISBILIK13]),"12",IIf(IsNull([JENISBILIK14]),"13","14"))))))))))))))

it can only put until no 14, if i put ,IIf(IsNull([JENISBILIK15]),"14" it will got error

the expression is to complex

any idea ?

i need to put 4 more untul i get 18

This has all the earmarks of a "Spreadsheet Style" table, not a relational database application table used in Access. You have, for example, JENISBILIK1, JENISBILIK2, JENISBILIK3, etc. If those are fields in a table, then that is a "spreadsheet" not a properly designed table. In such designs, queries are a real problem.

Here are some articles explaining the problem created by this table design and explaining how to correct it.

 

[June7]

Could use less characters with Switch() function, however, doubt will be enough to allow 4 more terms.

Really looks like a non-normalized data structure and that may be root of problem.

Build a VBA custom function that uses Switch() or SELECT CASE. Will probably have to open a recordset object.

Provide sample of table. If you want to provide db for analysis, follow instructions at bottom of post.

 

[Akai90]

here my example,
i can't share the real access due has very secret data

 

[oleronesoftwares]

Hi,
Its better you normalise your database
Fields like JENISBILIK1,JENISBILIK2,JENISBILIK3 etc can be a single field, then in your report it can be easier to perform a calculation on it.

 

[jdraw]

???? What is the purpose of this database? You should review and apply some database principles if you plan to use Access.

 

[June7]

Function like:

Function CountRooms(intID)
Dim rs As DAO.Recordset, x As Integer, c As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM butirips WHERE ID=" & intID)
For x = 19 To rs.Fields.Count - 1
    c = c + IIf(Not IsNull(rs(x)), 1, 0)
    Debug.Print rs(x).Name
Next
CountRooms = c
End Function

Call from textbox:
=CountRooms([ID])

 

[Akai90]

Function like:

Function CountRooms(intID)
Dim rs As DAO.Recordset, x As Integer, c As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM butirips WHERE ID=" & intID)
For x = 19 To rs.Fields.Count - 1
    c = c + IIf(Not IsNull(rs(x)), 1, 0)
    Debug.Print rs(x).Name
Next
CountRooms = c
End Function

Call from textbox:
=CountRooms([ID])

thanks sir.. it working, but if have add new field it will have error.

 

[arnelgp]

see also Query2 and your form.

 

[Akai90]

see also Query2 and your form.

thanks i understand and already run on live access and it work.

 

[Minty]

thanks sir.. it working, but if have add new field it will have error.

This is because your table design is wrong, as per posts #4 and #5

Fix that and your problems will be easily solved.