No Data in Condition

[JUAN]

Hello,
using 97. and trying to do a condition in a macro, but
cant' seem to get it to work.
If there is no data in my query, then I want to display
message that NO Data and stop macro. So I test with data:
DCount("*","myqueryname")>0 and works so trying to use
same concept but only if my query doesn't contain data.
Can anyone provide any input?

Thanks,

Juan

 

[Ken Snell]

DCount("*","myqueryname")=0

The above condition willl be true if there are no records returned by the
query.

 

[JUAN]

Hello Ken,
thanks. Works fine but when I put a parameter in my query,
this doesn't work. So I'm assuming that this condition
doesn't work with parameters in query? This is what I have
in my field under Criteria in my query:
Like [ENTER A* FOR EU B* FOR NA AND * FOR ALL]
I have in the parameters Box ENTER A* FOR EU B* FOR NA AND
* FOR ALL

Would appreciate it if you have any ideas.

Thanks,

Juan

 

[Ken Snell]

No, if your query has parameters in it, then the way I posted DCount will
not work....it cannot resolve the parameters.

However, you can put your own parameters in the DCount function as the third
argument. For your situation, make a copy of the query and delete the
parameter from the query. Then put the parameter (as a WHERE string but
without the WHERE) as the third argument. For example,

DCount("*","myqueryname","[FieldName] Like 'A*'")=0

Or whichever criterion expression you want to use. Replace FieldName with
the actual name of the field for which the crierion applies.

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,
thanks. Works fine but when I put a parameter in my query,
this doesn't work. So I'm assuming that this condition
doesn't work with parameters in query? This is what I have
in my field under Criteria in my query:
Like [ENTER A* FOR EU B* FOR NA AND * FOR ALL]
I have in the parameters Box ENTER A* FOR EU B* FOR NA AND
* FOR ALL

Would appreciate it if you have any ideas.

Thanks,

Juan

-----Original Message-----
DCount("*","myqueryname")=0

The above condition willl be true if there are no records returned by the
query.

--

Ken Snell
<MS ACCESS MVP>




.

 

[juan]

Hello Ken,
thanks for the help u providing me. Can't seem to get it
to work when I add the extra field. Example I created new
sample query which contains data so I use:
DCount("*","myquery")or DCount("*","myquery")>0 and works
fine. But if I use:
DCount("*","myquery","[SALES_ORG] = 'A110' ")
doesn't work. I also put under SALES_ORG in my query in
the criteria A110. When I run the macro I get data. But my
condition doesn't work on the macro. So not sure what am I
doing wrong.
I'll keep playing around with this, but if you see any
minor thing I'm doing wrong please let me know. I really
do appreciate the time you are providing me with.
Thanks,
Juan

-----Original Message-----
No, if your query has parameters in it, then the way I posted DCount will
not work....it cannot resolve the parameters.

However, you can put your own parameters in the DCount function as the third
argument. For your situation, make a copy of the query and delete the
parameter from the query. Then put the parameter (as a WHERE string but
without the WHERE) as the third argument. For example,

DCount("*","myqueryname","[FieldName] Like 'A*'")=0

Or whichever criterion expression you want to use. Replace FieldName with
the actual name of the field for which the crierion applies.

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,
thanks. Works fine but when I put a parameter in my query,
this doesn't work. So I'm assuming that this condition
doesn't work with parameters in query? This is what I have
in my field under Criteria in my query:
Like [ENTER A* FOR EU B* FOR NA AND * FOR ALL]
I have in the parameters Box ENTER A* FOR EU B* FOR NA AND
* FOR ALL

Would appreciate it if you have any ideas.

Thanks,

Juan

-----Original Message-----
DCount("*","myqueryname")=0

The above condition willl be true if there are no

records
returned by the

query.

--

Ken Snell
<MS ACCESS MVP>

Hello,
using 97. and trying to do a condition in a macro, but
cant' seem to get it to work.
If there is no data in my query, then I want to display
message that NO Data and stop macro. So I test with data:
DCount("*","myqueryname")>0 and works so trying to use
same concept but only if my query doesn't contain data.
Can anyone provide any input?

Thanks,

Juan



.

.

 

[Ken Snell]

As I recall, you wanted to test for when the query doesn't return records.
If your query will return records when the SALES_ORG field's criterion is
'A110', then this DCount function expression will return a False as its
value:

DCount("*","myquery","[SALES_ORG] = 'A110' ") = 0

If you want a True statement if the DCount returns records, then use

DCount("*","myquery","[SALES_ORG] = 'A110' ") > 0

But if you just put this expression in the condition:

DCount("*","myquery","[SALES_ORG] = 'A110' ")

then the value returned will be a number equal to or greater than zero. As
zero is False, and -1 is True, any positive number is not going to mean
anything to the Condition process. So try one of the above that I've posted.

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,
thanks for the help u providing me. Can't seem to get it
to work when I add the extra field. Example I created new
sample query which contains data so I use:
DCount("*","myquery")or DCount("*","myquery")>0 and works
fine. But if I use:
DCount("*","myquery","[SALES_ORG] = 'A110' ")
doesn't work. I also put under SALES_ORG in my query in
the criteria A110. When I run the macro I get data. But my
condition doesn't work on the macro. So not sure what am I
doing wrong.
I'll keep playing around with this, but if you see any
minor thing I'm doing wrong please let me know. I really
do appreciate the time you are providing me with.
Thanks,
Juan

-----Original Message-----
No, if your query has parameters in it, then the way I posted DCount will
not work....it cannot resolve the parameters.

However, you can put your own parameters in the DCount function as the third
argument. For your situation, make a copy of the query and delete the
parameter from the query. Then put the parameter (as a WHERE string but
without the WHERE) as the third argument. For example,

DCount("*","myqueryname","[FieldName] Like 'A*'")=0

Or whichever criterion expression you want to use. Replace FieldName with
the actual name of the field for which the crierion applies.

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,
thanks. Works fine but when I put a parameter in my query,
this doesn't work. So I'm assuming that this condition
doesn't work with parameters in query? This is what I have
in my field under Criteria in my query:
Like [ENTER A* FOR EU B* FOR NA AND * FOR ALL]
I have in the parameters Box ENTER A* FOR EU B* FOR NA AND
* FOR ALL

Would appreciate it if you have any ideas.

Thanks,

Juan

-----Original Message-----
DCount("*","myqueryname")=0

The above condition willl be true if there are no records
returned by the
query.

--

Ken Snell
<MS ACCESS MVP>

Hello,
using 97. and trying to do a condition in a macro, but
cant' seem to get it to work.
If there is no data in my query, then I want to display
message that NO Data and stop macro. So I test with
data:
DCount("*","myqueryname")>0 and works so trying to use
same concept but only if my query doesn't contain data.
Can anyone provide any input?

Thanks,

Juan



.

.