Having problems with using DCOUNT

[Damob9K]

Hello all,

I have this little bit of VBA that essentially:
A) checks to see if the user has actually entered a value into the input box
B) then uses DCOUNT to check the query used, that there are any records that match and if so :
C) Apply a filter to the subform datasheet to show only the records set by A.

Now A and C work when either no criteria is entered or the correct criteria is entered, but the check to see if there are any records that exist, using dcount does not work.

It's probably something wrong with my syntax or i'm using it the wrong way.
Can someone take a quick look ?

Many thanks

Damob

Private Sub Search_Click()
Dim strQuery As String
Dim PCNumber As String
strQuery = "RepairHistoryQuery"
If IsNull(Me![PCNumberIn]) Then
MsgBox "You Must enter a PC number, or click cancel to exit", vbExclamation, "Search For PC"
Me.PCNumberIn.SetFocus
    Else
    PCNumber = Me!PCNumberIn
        If DCount("PCNumber", "RepairHistoryQuery") > 0 Then
        MsgBox "PC not Found.", vbInformation, "Search For PC"
        Else
        With Forms!RepairHistoryForm!RepairHistorySubForm.Form
        .Filter = "PCNumber='" & Me!PCNumberIn & "'"
        .FilterOn = True
        End With
        
    End If
DoCmd.Close acForm, "RepairHistorySearchForm"
End If


End Sub

 

[MarkK]

DCount() takes three parameters . . .

DCount(field, table, criteria)

. . . and you can just use "*" for field, since we are counting records, not fields, so it doesn't matter . . .

If DCount("*", "RepairHistoryQuery", "PCNumber = " & me.PCNumberIn) > 0 Then

You should also work on your indents. A routine like this should look like . . .

Private Sub Search_Click()
   Dim strQuery As String
   Dim PCNumber As String

   strQuery = "RepairHistoryQuery"
   If IsNull(Me![PCNumberIn]) Then
      MsgBox "You Must enter a PC number, or click cancel to exit", vbExclamation, "Search For PC"
      Me.PCNumberIn.SetFocus
   Else
      PCNumber = Me!PCNumberIn
      If DCount("PCNumber", "RepairHistoryQuery") > 0 Then
         MsgBox "PC not Found.", vbInformation, "Search For PC"
      Else
         With Forms!RepairHistoryForm!RepairHistorySubForm.Form
            .Filter = "PCNumber='" & Me!PCNumberIn & "'"
            .FilterOn = True
         End With
      End If
      DoCmd.Close acForm, "RepairHistorySearchForm"
   End If
End Sub

Do you see the pattern? This makes your code much more readable that each block is nested in its containing block.

Hope this helps,

 

[smig]

DCount() takes three parameters . . .

. . . and you can just use "*" for field, since we are counting records, not fields, so it doesn't matter . . .

I think I read somewhere it will perform faster counting *

 

[Damob9K]

Thanks for the tips, it does help me to see an example of code and how it is supposed to be formulated, to understand it better.

And I thought my indents were not bad !! must do better

Right onto the VBA ... I'm getting an error ..

If I type in a value that I know does not exist i.e. PC56789 then I get an error message box pop-up that says:-

"Run-time error '2471': 
The expression you entered as a query parameter produced this error:
'PC56789'

Would it matter that the query I'm referencing has a criteria set on one of the fields ? it's a filter that only allows this subform to display historical faults and not PC's that are currently in repair.

Looking on the internet run-time error 2471 seems to relate to the data type being wrong, but I don't see where the issue is it is a string not a numerical value because of being preceded by PC , and the data type in the table is text, and the query itself runs fine on it's own ?

Sorry for being a numpty !!

Actually I get this error even if I type in a value that does exist in the query ??

 

[smig]

as your PCNumber is a text string you must use the single quotation:
put this as your DCount criteria:
"PCNumber='" & Me.PCNumberIn & "'"

This will result as error:
"PCNumber=" & Me!PCNumberIn

 

[Galaxiom]

I think I read somewhere it will perform faster counting *

Yes because even without an explicit Where condition a count on a field will test for Nulls and exclude them from the count.

Domain functions work much faster on indexed fields.

 

[Damob9K]

Thanks guys, that fixed the error I was getting, but it still wasn't working as I expected .... that's until I was staring at it thinking my logics all F'd up somewhere, and then I saw it !! :banghead:

Private Sub Search_Click()

If IsNull(Me![PCNumberIn]) Then
MsgBox "You Must enter a PC number, or click cancel to exit", vbExclamation, "Search For PC"
Me.PCNumberIn.SetFocus
    Else
        If [B]DCount("*", "RepairHistoryQuery", "PCNumber='" & Me.PCNumberIn & "'") > 0 Then
        With Forms!RepairHistoryForm!RepairHistorySubForm.Form
        .Filter = "PCNumber='" & Me!PCNumberIn & "'"
        .FilterOn = True
        End With[/B]
        Else
        [B]MsgBox "PC not Found.", vbInformation, "Search For PC"[/B]
        
    End If
DoCmd.Close acForm, "RepairHistorySearchForm"
End If


End Sub

The Else / IF conditions were the wrong way round !!

I think sometimes you can look at a problem for too long, and stop seeing the obvious problem / solution !

Thanks for all your help ... again

Damob

 

[Damob9K]

Re: Having problems with using DCOUNT (Fixed)

One last question about this routine ..

On the search form for this routine there are 3 buttons, the one that runs the above VBA (which now works perfectly) , one button that is called "Clear" and one that is called "Cancel"

Cancel just exits out of the search input box.
Now clear is supposed to reset the input box so that the user can re enter a value.

the onclick vba is dead simple :

Private Sub Clear_Click()
Me.PCNumberIn.Value = ""
Me.PCNumberIn.SetFocus
End Sub

But as usual it doesn't do what I want it to do, or more accurately it behaves differently to what I expected !

As if I click on the clear button and then was to click on the search button, rather than the VBA code:

If IsNull(Me![PCNumberIn]) Then
MsgBox "You Must enter a PC number, or click cancel to exit", vbExclamation, "Search For PC"
Me.PCNumberIn.SetFocus
    Else

Coming up with the message "You Must enter a PC number ..." , it instead allows it to run through the next stage:

    Else
        If DCount("*", "RepairHistoryQuery", "PCNumber='" & Me.PCNumberIn & "'") > 0 Then
        With Forms!RepairHistoryForm!RepairHistorySubForm.Form
        .Filter = "PCNumber='" & Me!PCNumberIn & "'"
        .FilterOn = True
        End With
        Else
        MsgBox "PC not Found.", vbInformation, "Search For PC"
        
    End If

Which then correctly says "PC not Found"

So it would appear that by setting Me.PCNumberIn.Value = "" , the "" is not clearing it of a value but setting some value that the search is then applying ?

Again I guess i'm doing it wrong ! is there another way of setting a null value ?

This is not overly important but a) I am still learning a great deal and would like to try to understand why things work the way they do, and b) I like to cover all possibilities at the user input end, and make sure that every possible combination of user input results with either a message to say they've done something wrong or just does what it is supposed to do.

Thanks

Damob

 

[mh123]

You are checking to see if the textbox is null not an empty string? Perhaps change this line here;
If IsNull(Me![PCNumberIn]) Then
to
If IsNull(Me![PCNumberIn]) or Me![PcNumberIn] = "" Then ' this will check for the value of an empty string (which your clear is setting it to) and halt the procedure asking the user to input a correct value

HTH

 

[Damob9K]

Ah yes .. I see, yes thank you mh123 that did help indeed !

Cheers

Damob

 

[smig]

Isnull() will only look for nulls, not empty strings ""

Use:
If trim(testString & "") = ""
This will test both for nulls and empty strings.

 

[Damob9K]

=If trim(testString & "") = ""

Huh ! now your just messing with my head

I will look up the Trim function later and see if I can get my head around it.
As at the moment looking at that, it looks to me that the logic is saying

if something & "" = "" then ..

will always equal "" ?

Yeah .. I'll read up on it first !!

Cheers

Damob

 

[MarkK]

While you are looking up functions, also check out Nz(), which is another common way to handle nulls.

Nz(<expression>, <valueIfNull>)
- checks <expression> and if it is not null, returns <expression>, otherwise, returns <valueIfNull>

Usage in code might be . . .

If Nz(Me.SomeField, "") = "" then
[COLOR="Green"]   'this block runs if SomeField is null, or if it contains an empty string[/COLOR]
Else
[COLOR="Green"]   'this block runs in all other cases[/COLOR]
End If

To me the advantage of Nz() is it makes it very clear you are checking for nulls.

 

[smig]

Trim() is just to remove extra spaces.

Something & "" will be equal to "" only if something is null or ""

"Danob" & "" = "Danob" not ""


As MarkK said nz() is also good for looking nulls, but it too wont look for an empty string.

 

[Damob9K]

@smig right i'm with you, I think I understand the basics of Trim now.

@MarkK yes I have used Nz as well , in fact now that I have had the time to look back through my code, I have used if Nz (field) <> 0 then , as well as if Len (field) = 0 .. it's all a bit of a mess !!

I guess this is the pitfall of learning on the fly, and then not being able to spend any time on it for months on end !!

but I have managed to spend a lot of the Christmas holiday on it, so it's pretty close to being a functional database

Thanks again for your help and guidance chaps.

Damob

 

[smig]

NZ() return true and false. False = 0, True = -1
Len(field) = 0 will not catch empty strings "" too.

 

[Galaxiom]

NZ() return true and false. False = 0, True = -1

No. Nz() without a second parameter to define the Null replacement will return the variant Empty. This implicitly casts to zero for a numeric variable or field or the NullString for text.

Len(field) = 0 will not catch empty strings "" too.

It will catch empty strings. It won't catch a Null.

 

[smig]

No. Nz() without a second parameter to define the Null replacement will return the variant Empty. This implicitly casts to zero for a numeric variable or field or the NullString for text.

I alwsys use nz(variable) = true or false
Are you saing its wrong ?

It will catch empty strings. It won't catch a Null.

Oops. My mistake

 

[Galaxiom]

I alwsys use nz(variable) = true or false
Are you saing its wrong ?

Nz(Null), Nz(vbNullString) and Nz(0) will all implicitly cast to False when tested against a Boolean.

So provided you a happy with that than it is a fair test. It is potentially slightly illogical since a value of zero may be valid data where a Null invariably means no data.

However one should understand that Nz() returns the variant Empty not a Boolean.

BTW Microsoft's documentation about Nz() is incorrect since it claims that Nz() returns either zero or a null string depending on where it is used, which of course is complete nonsense.

 

[smig]

But what nz() will return for a non null, or non empty string ?