Nz(DCount...)

[Navyguy]

A few days ago Wayne helped me out (thanks alot again!!) using a Nz(DCount..) to search for null fields. Now I am trying to modify the code to do the same but using multiple fields. I have read my references and searched but have not had too much luck. I think I am supposed to use the AND function in the code but now I get compile errors.

Here's the code, and as always, appreciate the help

If Nz(DCount("[Incident Report Number]", _
"[Tbl-Resident Surnames]", _
"[Incident Report Number] = '" & Me.[Incident Report Number] & "'"), 0) = 0 AND ("[Surname]", _
"[Tbl-Resident Surnames]", _
"[Surname] = '" & Me.[Surname] & "'"), 0) = 0
AND ("[Residence Location]", _
"[Tbl-Incident Details Resident]", _
"[Residence Location] = '" & Me.[Residence Location] & "'"), 0) = 0 Then
MsgBox "There Is No Incidents That Match Your Entries", vbOKOnly, "Invalid Search Criterion!"
Me.[Incident Report Number].SetFocus
Exit Sub
End If

Navyguy

 

[billyr]

A few days ago Wayne helped me out (thanks alot again!!) using a Nz(DCount..) to search for null fields. Now I am trying to modify the code to do the same but using multiple fields. I have read my references and searched but have not had too much luck. I think I am supposed to use the AND function in the code but now I get compile errors.

Here's the code, and as always, appreciate the help

If Nz(DCount("[Incident Report Number]", _
"[Tbl-Resident Surnames]", _
"[Incident Report Number] = '" & Me.[Incident Report Number] & "'"), 0) = 0 AND ("[Surname]", _
"[Tbl-Resident Surnames]", _
"[Surname] = '" & Me.[Surname] & "'"), 0) = 0
AND ("[Residence Location]", _
"[Tbl-Incident Details Resident]", _
"[Residence Location] = '" & Me.[Residence Location] & "'"), 0) = 0 Then
MsgBox "There Is No Incidents That Match Your Entries", vbOKOnly, "Invalid Search Criterion!"
Me.[Incident Report Number].SetFocus
Exit Sub
End If

Navyguy

Oops
" AND ("[Residence Location]", _

 

[Rich]

Why do you need all that criteria, surely Incident Report Number should be unique?

 

[Navyguy]

Hi Rich
The criteria is based on several items. You are right the Incident Report Number is unique, but several surnames could be attached to the incident which are in a separate table. I added the Residence Location just as a thrid criteria to eliminate user errors when finding the required record.

An example might be Incident #1 with Jones at Location A.
With a similar entry Incident #2 with Jones at Location B, they will get a totally different Jones is they mistype the incident number, and have no location criteria but to mistype the incident number and select the wrong location... I am hoping is pretty remote!!!

Navyguy

 

[WayneRyan]

NavyGuy,

You can't use the DCount on multiple tables. It's syntax
is DCount("Column", "Table", "Criteria"). You can have
complex criteria, but not multiple tables.

You will need multiple DCounts to do this:

If (Nz(DCount("[Column]", _
              "[Table1]", _
              "[Column] = 'Something'), 0) +
    Nz(DCount("[Column]", _
              "[Table2]", _
              "[Column] = 'Something'), 0) +
    Nz(DCount("[Column]", _
              "[Table3]", _
              "[Column] = 'Something'), 0)) = 0 Then
    MsgBox("Not found.")
Else
    MsgBox("Found")
End If

Wayne

 

[Navyguy]

Hi Wayne

This is what I did...

If (Nz(DCount("[Incident Report Number]", _
"[Tbl-Resident Surnames]", _
"[Incident Report Number] = '& Me.[Incident Report Number]&'), 0) + _
Nz(DCount("[Residence Location]", _
"[Tbl-Incident Details Resident]", _
"[Residence Location] = '& Me.[Residence Location]&'), 0) + _
Nz(DCount("[Surname]", _
"[Tbl-Resident Surnames]", _
"[Surname] = '& Me.[Surname]&'), 0)) = 0 Then
MsgBox ("Not found.")
Else
MsgBox ("Found")
End If

I had to add the underscore after the + sign otherwise the code just stopped. I am not sure if I have the right 'something' in the 'something' area either. Now I get the Complie Error: Syntax Error message, the first line is highlighted and the code that I pasted in turns red. Of course the first line is the Private Sub line.

What I tried earlier was that I pasted you earlier code in three times individually and it worked OK on the individual fields (if there was no criteria present in the table), but when all the criteria was met (regardless if it was related) it loaded the blank edit form. No text, no boxes, nothing.

Example: If there was a Loacation A in Table A and a Jones in Table A and Report Number in Table B, if all that criteria was present but not related to each other the edit form would came up blank.

When I typed in known related criteria the edit form loaded no problem and no error message was raised.

Hope this makes sense...

Navyguy

 

[Mile-O]

DCount, if it doesn't find anything, returns 0 - you don't need the Nz.

 

[Navyguy]

Hi Mile

So I deleted the Nz as you suggested with the only difference being the error message now displays "Expected list separator or ")"" on the first "(" of the DCount line. I searched my books and nothing about list separators...

When I stop the debugger then I get the same error as posted earlier regarding the syntax and the highlight of the first line. Also now when I put in known linked criteria I get the error also.

Can't understand why this is so tough...seems simple enough//sigh//

Navyguy

 

[Mile-O]

That's because you've only taken one bracket off the Nz function

You'd have had:

Nz(DCount(blablabla))

If you take of the Nz( then you are left with the closing bracket at the end.

 

[Navyguy]

I think I got the brackets right???

This is what I have...I thought is was right...(that was the problem I was thinking!!!)

If DCount("[Incident Report Number]", _
"[Tbl-Resident Surnames]", _
"[Incident Report Number] = '& Me.[Incident Report Number]&'), 0 + _
DCount("[Residence Location]", _
"[Tbl-Incident Details Resident]", _
"[Residence Location] = '& Me.[Residence Location]&'), 0 + _
DCount("[Surname]", _
"[Tbl-Resident Surnames]", _
"[Surname] = '& Me.[Surname]&'), 0 = 0 Then

MsgBox ("Not found.")
Else
MsgBox ("Found")
End If

Please show me the err of my ways....

Navyguy

 

[Navyguy]

I am almost there!!!

After playing around for quite awhile this is what I come up with...

If DCount("[Incident Report Number]", _
"[Tbl-Incident Details Resident]", _
"[Incident Report Number] = '" & Me.[Incident Report Number] & "'") + _
DCount("[Residence Location]", _
"[Tbl-Incident Details Resident]", _
"[Residence Location] = '" & Me.[Residence Location] & "'") + _
DCount("[Surname]", _
"[Tbl-Resident Surnames]", _
"[Surname] = '" & Me.[Surname] & "'") = 0 Then

MsgBox ("Not found.")
Else
MsgBox ("Found")
End If

Now the code runs and indicates "Found" regardless of the criteria in the fields. What does happen though, same as before, only a blank edit form shows if the criteria is not related.

So how Do I get the code to check to see if the criteria is related and answer the appropriate Found/Not found response?

Navyguy

Edit:

I changed the "+" sign to "AND" and now it checks for related the records only and the proper message comes up.

Navyguy