Using DCount to recognize blank

[Mel via AccessMonster.com]

My question runs fine to check if there are any rows. But it does check if
it's a new record and no rows exist yet. Here's my code:

Dim intX As Integer
Dim rs As DAO.Recordset
'****this runs to find if any records have "yes" in the flag field
intX = DCount ("*", "qryContgYes")
If intX > 0 Then
'if it does, then it's okay, exit the routine
exit sub
else
'if all the records have "No" in the flag field then open warning form
Docmd.OpenForm "frmWarning"
End if

This works fine until it meets the condition of a new record when there are
not records yet.

How would I code to check for no rcds?

I tried intX = -0, but that didn't work

Please help..

Thanks,
Mel

 

[Jeanette Cunningham]

Mel,
you can check for the new record condition first.
If it is a new record, then you don't perform the record count.

If Me.NewRecord = True Then
'skip record count
Else
intX = DCount ("*", "qryContgYes")

If intX > 0 Then

'etc
End If


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Damon Heron]

The dcount statement needs to check for null value, so if null then
alternative response:

intX = nz(DCount ("*", "qryContgYes"),"No")

what is the dim rs as dao.recordset line for?
Damon

 

[Mel via AccessMonster.com]

don't really need the DAO line. thanks.

I changed the intX line as you suggested.

Tried to test for null, but didn't work. here's my code....
intX = Nz(DCount("*", "qryContgYes"), "No")

If intX = Null Then
msgbox "no errors exist"
exit sub
end if

my query runs to find all those that have a "YES" as the flag. The flag can
be either "yes" or "no". so someting is always present. If all the records
are "NO" then it does a routine" If any of the records are "yes" it does a
routine"

But what I'm trying to catch is when I run the query and nothing is returned
because it's a new record.

Thanks,
Mel

The dcount statement needs to check for null value, so if null then
alternative response:

intX = nz(DCount ("*", "qryContgYes"),"No")

what is the dim rs as dao.recordset line for?
Damon

My question runs fine to check if there are any rows. But it does check
if

[quoted text clipped - 24 lines]

Thanks,
Mel

 

[Damon Heron]

Oops! I missed that you had intX as an integer. So, the null value should
be 0, likee this:
intX = Nz(DCount("*", "qryContgYes"),0)

Now when no records are returned, intX will be zero.

Damon

don't really need the DAO line. thanks.

I changed the intX line as you suggested.

Tried to test for null, but didn't work. here's my code....
intX = Nz(DCount("*", "qryContgYes"), "No")

If intX = Null Then
msgbox "no errors exist"
exit sub
end if

my query runs to find all those that have a "YES" as the flag. The flag
can
be either "yes" or "no". so someting is always present. If all the
records
are "NO" then it does a routine" If any of the records are "yes" it does a
routine"

But what I'm trying to catch is when I run the query and nothing is
returned
because it's a new record.

Thanks,
Mel

The dcount statement needs to check for null value, so if null then
alternative response:

intX = nz(DCount ("*", "qryContgYes"),"No")

what is the dim rs as dao.recordset line for?
Damon

My question runs fine to check if there are any rows. But it does check
if

[quoted text clipped - 24 lines]

Thanks,
Mel

 

[Damon Heron]

Just re-reading your original post. by new record, did you mean "no
records"? So the query tests for yes in a field in your table, and if there
is at least one record, then it returns the count. If there are no "yes"
values, the dCount should return 0, not null. So I don't see the need for a
nz statement. Maybe I am missing something. I have Access 2007, but I
believe it works the same in earlier versions.
Maybe there is something wrong with your query?

Damon

Oops! I missed that you had intX as an integer. So, the null value should
be 0, likee this:
intX = Nz(DCount("*", "qryContgYes"),0)

Now when no records are returned, intX will be zero.

Damon

don't really need the DAO line. thanks.

I changed the intX line as you suggested.

Tried to test for null, but didn't work. here's my code....
intX = Nz(DCount("*", "qryContgYes"), "No")

If intX = Null Then
msgbox "no errors exist"
exit sub
end if

my query runs to find all those that have a "YES" as the flag. The flag
can
be either "yes" or "no". so someting is always present. If all the
records
are "NO" then it does a routine" If any of the records are "yes" it does
a
routine"

But what I'm trying to catch is when I run the query and nothing is
returned
because it's a new record.

Thanks,
Mel

The dcount statement needs to check for null value, so if null then
alternative response:

intX = nz(DCount ("*", "qryContgYes"),"No")

what is the dim rs as dao.recordset line for?
Damon

My question runs fine to check if there are any rows. But it does
check
if
[quoted text clipped - 24 lines]
Thanks,
Mel

 

[Mel via AccessMonster.com]

Yes, you are correct. It returns 0. the problem i'm having is that when
there are no "Yes" it means there are "No"s in the field because my query
runs to check for "Yes". Then when it's not "yes" I will know that it must
be all "No".

But then how would I test for no "Yes" and no "No" which is a bank or
actually no row? right now they both are coming up 0. I need to make a
distinction between the two.

Maybe my query should be different. I just not sure.

Mel

Just re-reading your original post. by new record, did you mean "no
records"? So the query tests for yes in a field in your table, and if there
is at least one record, then it returns the count. If there are no "yes"
values, the dCount should return 0, not null. So I don't see the need for a
nz statement. Maybe I am missing something. I have Access 2007, but I
believe it works the same in earlier versions.
Maybe there is something wrong with your query?

Damon

Oops! I missed that you had intX as an integer. So, the null value should
be 0, likee this:

[quoted text clipped - 44 lines]

 

[Rick A.B.]

Yes, you are correct.  It returns 0.  the problem i'm having is that when
there are no "Yes" it means there are "No"s in the field because my query
runs to check for "Yes".  Then when it's not "yes" I will know that it must
be all "No".

But then how would I test for no "Yes" and no "No" which is a bank or
actually no row?  right now they both are coming up 0.  I need to make a
distinction between the two.

Maybe my query should be different.  I just not sure.  

Mel

Mel

Not sure what your query looks like but try.

If isNull (intX) Then
msgbox "no errors exist"
exit sub
end if

Hope that helps
Rick

 

[Damon Heron]

Sorry about that. Of course you are right, no "yeses" does not mean no
"nos". Maybe a solution would be to test the table first.
Heres some pseudo code:

dim AnyRecs as integer
AnyRecs = dcount("y/nfield", "yourtable")
if AnyRecs then
'do the query test
intX= dcount("*", .........
else
msgbox "no recs"
exit sub
end if
'do yes/ no routine

Damon

Yes, you are correct. It returns 0. the problem i'm having is that when
there are no "Yes" it means there are "No"s in the field because my query
runs to check for "Yes". Then when it's not "yes" I will know that it
must
be all "No".

But then how would I test for no "Yes" and no "No" which is a bank or
actually no row? right now they both are coming up 0. I need to make a
distinction between the two.

Maybe my query should be different. I just not sure.

Mel

Just re-reading your original post. by new record, did you mean "no
records"? So the query tests for yes in a field in your table, and if
there
is at least one record, then it returns the count. If there are no "yes"
values, the dCount should return 0, not null. So I don't see the need for
a
nz statement. Maybe I am missing something. I have Access 2007, but I
believe it works the same in earlier versions.
Maybe there is something wrong with your query?

Damon

Oops! I missed that you had intX as an integer. So, the null value
should
be 0, likee this:

[quoted text clipped - 44 lines]

Thanks,
Mel