help needed with Nz() and vba coding

[Misiek]

Good day all,

I have been trying to resolve those 2 problems, but so far unsuccessful, probably didn't asked the right question. Would appreciate your kind help.

Problem 1.
The following code was showing #num! if [cboDeptStats] was left blank, after I added the iff(Nz.. function to what I used to had, it works ok, but still shows #num! if there isn't any records for specific month in the query. Can you please advice, how to edit this line.

=IIf(Nz([cboDeptStats],0)=0,0,DCount("tTaskPK","Q_task_CompStats","tTaskDueDate>=tTaskComplDate AND Month([tTaskDueDate]) = 7 AND qaDeptFK=[Forms]![F_CompLvl]![cboDeptStats]")/DCount("tTaskPK","Q_task_CompStats","Month([tTaskDueDate]) = 7 AND qaDeptFK=[Forms]![F_CompLvl]![cboDeptStats]"))

Problem 2.
I have a form with a print button, What I want it to do, is run the query and check if there is any matching records, if so, print the report, if there isn't any, show a message box.

Field name in current form: qaQAPK
matching field in the query: tQAFK
query name: Q_printTask

Private Sub cmdPrintActions_Click()
'Print Actions for current record
'using R_printQA.
If IsNull([tQAFK,Q_printTask]) Then
MsgBox "No Records available for print", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "R_printTask", acPreview, , _
"tQAFK = " & Me!qaQAPK
End Sub

 

[JHB]

For problem #2 use DCount, instead of IsNull.
Only for info: The way you've setup IsNull is total wrong, look in the Helpfile how to use it. In the Helpfile you'll also find how you setup and use DCount.

 

[smig]

Your False result for the iif looks like a mess
Try to use some variables to make it more readable.

its better to use If command rather the IIf() function

 

[Misiek]

Thanks JHB, used Dcount and it works like a dream!

Private Sub cmdPrintActions_Click()
'Print Actions for current record using R_printQA.
Dim i As Integer
i = DCount("*", "Q_printTask", "tQAFK=qaQAPK")
'MsgBox "The count of rows is " & i
If i = 0 Then
MsgBox "No Records available for print", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "R_printTask", acPreview, , _
"tQAFK = " & Me!qaQAPK
End Sub

Problem #2 sorted!


Smig, no not really as my IIF is a control source expression. IF is a structural programming element and in this case I don't need to use it, there will no no ELSE or THEN, I just simply need to calculate number of records matching criteria.

Still need some help with Problem #1 please.

 

[namliam]

after making it more readable...

=IIf(Nz([cboDeptStats],0)=0
    ,0
    ,DCount("tTaskPK"
                ,"Q_task_CompStats"
                ,"tTaskDueDate>=tTaskComplDate 
              AND Month([tTaskDueDate]) = 7 
              AND qaDeptFK=[Forms]![F_CompLvl]![cboDeptStats]")/DCount("tTaskPK"
                                                                 ,"Q_task_CompStats"
                                                                 ,"Month([tTaskDueDate]) = 7 
                                                               AND qaDeptFK=[Forms]![F_CompLvl]![cboDeptStats]"))

If there is no data you end up /0 which is nonsence which is your error...
So either you need to repeat your dcount insert an IIF to prevent the /0

Or you need to stick it into a function that does this for you.

 

[Misiek]

I don't know how to do it. Can you help me please.

 

[namliam]

You build the other IIF didnt you, then surely you can build another? if not what are you stuck on?

 

[Misiek]

Well, yes you are right. I am stuck here: I don't understand what to do.

Is it:

IIf(Nz()Dcount())/IIf(Nz()Dcount()) ??


I tried this:

=IIf(Nz([cboDeptStats],0)=0,0,DCount("tTaskPK","Q_task_CompStats","tTaskDueDate>=tTaskComplDate AND Month([tTaskDueDate]) = 6 AND qaDeptFK=cboDeptStats"))/IIf(Nz([tTaskDueDate],0)=0,0,DCount("tTaskPK","Q_task_CompStats","Month([tTaskDueDate]) = 6 AND qaDeptFK=cboDeptStats"))

but gives me #Name? error :/

 

[namliam]

Guess you didnt get my more subtle hint, so let me be a little more clear
Dont just splash a lengthy IIF (or anything lengthy) on a forum, it is frigging unreadable and people are donating their time freely.... they may want to help but are not always inclined to go for what may be a complex puzzle. any lengthy unreadable garbage adds to that puzzle and will reduce your chances of finding any help.... Hence the reason why you did get an anwers to your second but not your first question to start with.

What I was suggesting is to add an IIF that does the trick for you...

=IIf(Nz([cboDeptStats],0)=0
    ,0
[b]    ,Iif(DCount("tTaskPK"
               ,"Q_task_CompStats"
               ,"Month([tTaskDueDate]) = 7 
             AND qaDeptFK=[Forms]![F_CompLvl]![cboDeptStats]") = 0
         , -99[/b]
         , DCount("tTaskPK"
                ,"Q_task_CompStats"
                ,"tTaskDueDate>=tTaskComplDate 
              AND Month([tTaskDueDate]) = 7 
              AND qaDeptFK=[Forms]![F_CompLvl]![cboDeptStats]")/DCount("tTaskPK"
                                                                 ,"Q_task_CompStats"
                                                                 ,"Month([tTaskDueDate]) = 7 
                                                               AND qaDeptFK=[Forms]![F_CompLvl]![cboDeptStats]"))
[B])[/B]

Offcourse you are layering DCounts here which generaly speaking is a bad idea, because each DCount is a query on your database, with more and more records each dcount will take longer and longer... Maybe now each DCount takes 0.1 seconds thus not really noticable....
In the future they may grow to 1 second each and it does become noticable.

 

[smig]

Thanks JHB, used Dcount and it works like a dream!

Private Sub cmdPrintActions_Click()
'Print Actions for current record using R_printQA.
Dim i As Integer
i = DCount("*", "Q_printTask", "tQAFK=qaQAPK")
'MsgBox "The count of rows is " & i
If i = 0 Then
MsgBox "No Records available for print", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "R_printTask", acPreview, , _
"tQAFK = " & Me!qaQAPK
End Sub

Problem #2 sorted!


Smig, no not really as my IIF is a control source expression. IF is a structural programming element and in this case I don't need to use it, there will no no ELSE or THEN, I just simply need to calculate number of records matching criteria.

Still need some help with Problem #1 please.

I gave you my 2 cents tips. You decided to ignore.
Good luck.

 

[Misiek]

I didn't know, Dcount will slow me down. I have approx 100 dcount expressions on my form. I've done a little read and others say even if I replace them with something else it won't make any difference in time as it's a single form.
Obviously I don't want users to wait 20 seconds or longer for it to reload.
What would be my best option in this case?

Smig, I didn't ignore you. I just didn't understand the difference and results behind it.

Thanks.

 

[namliam]

a dcount is essentially a query, if you dcount 100 times on 100 different tables... then yes it doesnt matter.

If you danything on 100 times on 1 table, you will do better using SQL... SQL will only trigger 1 query, where each danything will execute a seperate query...

 

[Misiek]

I use 2 queries, so I believe I would benefit if i convert to SQL (which I never used before). Would you please be able to point me to some kind of HOWTO, or give me any instructions on where to start.

Thank you

 

[namliam]

depends on what you are trying to do... if you are doing all kinds of dcounts it is a matter of translating those into a single query and retrieving the recordset from there...

Research CURRENTDB.OPENRECORDSET, as it allows you to open a query and read it in VBA, the standard MSDN reference is a good place to start.

 

[smig]

I gave you 2 tips which you where ignored:
1. Simplify things to make them readable.
2. Iif is a function and will alwayes run slower then if.

Even in your iif you have if/then/else. You have value for true result and another one for the false result.

If you use if it will look like:

If Nz([cboDeptStats],0)=0 then
X = 0
Else
X = DCount("tTaskPK","Q_task_CompStats","tTaskDueDate>=tTaskComplDate AND Month([tTaskDueDate]) = 7 AND qaDeptFK=[Forms]![F_CompLvl]![cboDeptStats]")/DCount("tTaskPK","Q_task_CompStats","Month([tTaskDueDate]) = 7 AND qaDeptFK=[Forms]![F_CompLvl]![cboDeptStats]"))
End if

 

[Misiek]

smig,
where do I insert it? in an event of the textbox?
Feeling confused :/

 

[smig]

Where do you use it now ?
If it's the rowsource of a text box you can use the AfteUpdate events of other text boxes to update this one.
Another option will be to create a function with this code and assign the function to the text box row source (Iif is also a function)

 

[Misiek]

Yes, it's in the rowsource.
But if we create a function we going to go back to where we started. It's going to be slow. I want to speed it all up like namliam suggested. Shall we use a query then?
I don't know what's better or faster. And don't know how to start on either of them.

 

[smig]

Yes, if we are going back to function we are going back to the issue of time, but you will be able to make it more readable using variables.
Not sure if you can use a query, as you do need the IF part.

 

[Misiek]

Ok. I am willing to try anything. As long as it works correctly and quickly.
What shall I do next then?