Help With DCount

[chrisguk]

Hi,

I have the following code on my form which works as desired:

Private Sub Form_Open(Cancel As Integer)
    qrycounter = 0
    Counter_txt = qrycounter
    qrycounter = DCount("[Logid]", "tbllog", "[tbllog].[sitesid] = [forms]![frmsites]![sitesid]")
    Counter_txt = qrycounter
    End Sub

The problem I am having is the following related code which keeps returning the "Else" statement "Msgbox..........":

Private Sub open_openqueries_Click()
If qrycounter > 0 Then
    
    DoCmd.OpenQuery "qryview_all_queries", acViewNormal, acReadOnly
    Else
        MsgBox ("There are no Queries on this Site")
End If
End Sub

So basically it will not open my query. Any ideas why?

 

[spikepl]

Because
qrycounter <=0

 

[chrisguk]

Because
qrycounter <=0

That doesnt work because if the counter is 0 then its still true. I tried:

qrycounter < 0 and that produces false no matter whether is 0, 1, 2,3 etc ect

 

[spikepl]

You clause qrycounter>0 is false, which is what I answered.

Look at what is the value of your qrycounter!

Where is it supposed to get its value from?

 

[chrisguk]

You clause qrycounter>0 is false, which is what I answered.

Look at what is the value of your qrycounter!

Where is it supposed to get its value from?

Okay Im officially lost ! ........ I cant work it out

 

[smig]

qrycounter = DCount("[Logid]", "tbllog", "[tbllog].[sitesid] = " & [forms]![frmsites]![sitesid] & " ")

 

[spikepl]

If I write

If A<B Then
something
Else
something else
End if

Then A and B have to contain a value for this to execute.

So I can say
A=1
B=2
and then the code runs, and the "something" executes

if I say
A=3 and B=1 then the code runs and "Something else" executes

In your clause you have set the value for B but not for A.

What is the value of qrycounter?

 

[spikepl]

Gee thanks smig, that sure helps the guy to see the errors of his ways

 

[chrisguk]

Thanks guys I am learning and not been doing Access for long. So every little helps. I understand what you mean about the A=1 and B=2 thing.

My qrycounter is random as it reflects the amount of queries open on that site. It is displayed via Dcount query on the frmsites page.

 

[chrisguk]

Thanks guys,

I appreciate your help. I am a real beginner and trying to learn what I can.

qrycounter is random as it reflects the amount of queries open on that site that is displayed.

 

[thhui]

To run a dynamic query under dcount,

the sql filter query should be written as smig stated
qrycounter = DCount("[Logid]", "tbllog", "[tbllog].[sitesid] = " & [forms]![frmsites]![sitesid] & " ")

or (if sitesid is a textfield)
qrycounter = DCount("[Logid]", "tbllog", "[tbllog].[sitesid] = '" & [forms]![frmsites]![sitesid] & "'")

 

[vbaInet]

I believe the code is being run from same form so:

qrycounter = DCount("Logid", "tbllog", "sitesid = " & Me!sitesid)

OR

qrycounter = DCount("Logid", "tbllog", "sitesid = " & Me.sitesid)

Notice that the square brackets aren't needed in this case and qualifying the field name with the table name is also not necessary because we're dealing with one table.

Advice from others are still valid points.

 

[smig]

just an explanation how it works.
what you did:
DCount("[Logid]", "tbllog", "[tbllog].[sitesid] = [forms]![frmsites]![sitesid]")
the function will read this (Anything inside the quaes) [tbllog].[sitesid] = [forms]![frmsites]![sitesid] as single text string.

correct way:
qrycounter = DCount("[Logid]", "tbllog", "[tbllog].[sitesid] = " & [forms]![frmsites]![sitesid] & " ")
the function will read [tbllog].[sitesid] = as string and will add the value of [forms]![frmsites]![sitesid]

the & " " part is not realy required, as it just add a space (You do must close the function with ) )

as thhui said: in case of text string you must add a single quate, and in this case you do need the last quates (to put the single one inside )

what vbaInet said is also valid