rewrite queries

D

davidstevans

hi all & thanks for you your help.

I have the following SQL query & I am trying to create a report using
the dcount statement listed below the 1st SQL statement, but I am
getting errors.please look at the Dcount statement and tell me where I
went wrong

SELECT Count(*)
FROM mydata
WHERE (((mydata.CURRENT) Like "714*") And ((myddata.OPER) Is Not Null)
And ((mydata.DAYS) Between 0 And 10));

=DCount("[CURRENT] like ''714*' ")","myddata","[OPER] is Not Null and
[days] Between 0 And 10 ")

Additionally I have the following sql statements that I would like to
convert also to the same format as above for use in a report.

SELECT Count(IIf(Current Like "714*",1,Null))/Count(IIf(Current Not
Like "714*",1,Null)) AS result
FROM myddata;
=========================================================

SELECT TOP 3 Error1
FROM myddata
WHERE Current like "714*"
GROUP BY Error1
ORDER BY Count(Error1);

=======================================================================
SELECT myddata.CURRENT, myddata.SSNFEI
FROM myddata
WHERE myddata.MAILDATE<DateAdd("yyyy",-6,Date());



thanks for all your help.
 
P

Pieter Wijnen

1) No Reason why you can't use the Query (rather a recordset based on it) in
the approperiate report section to populate Control(s).
That said:

=DCount("*", "myddata", "[CURRENT] like ''714*' AND [OPER] is Not Null")

For the seccond one you either have to use method 1) or a Subreport

HTH

Pieter
 
B

Bob Quintal

(e-mail address removed) wrote in
hi all & thanks for you your help.

I have the following SQL query & I am trying to create a report
using the dcount statement listed below the 1st SQL statement, but
I am getting errors.please look at the Dcount statement and tell
me where I went wrong

SELECT Count(*)
FROM mydata
WHERE (((mydata.CURRENT) Like "714*") And ((myddata.OPER) Is Not
Null) And ((mydata.DAYS) Between 0 And 10));

=DCount("[CURRENT] like ''714*' ")","myddata","[OPER] is Not Null
and [days] Between 0 And 10 ")

=DCount("*","myddata","[CURRENT] like ""714*"" AND [OPER] is Not
Null and [days] Between 0 And 10 ")


Additionally I have the following sql statements that I would like
to convert also to the same format as above for use in a report.

SELECT Count(IIf(Current Like "714*",1,Null))/Count(IIf(Current
Not Like "714*",1,Null)) AS result
FROM myddata;
=========================================================

SELECT TOP 3 Error1
FROM myddata
WHERE Current like "714*"
GROUP BY Error1
ORDER BY Count(Error1);

================================================================== =
==== SELECT myddata.CURRENT, myddata.SSNFEI
FROM myddata
WHERE myddata.MAILDATE<DateAdd("yyyy",-6,Date());



thanks for all your help.
 
D

davidstevans

1) No Reason why you can't use the Query (rather a recordset based on it) in
the approperiate report section to populate Control(s).
That said:

=DCount("*", "myddata", "[CURRENT] like ''714*' AND [OPER] is Not Null")

For the seccond one you either have to use method 1) or a Subreport

HTH

Pieter


hi all & thanks for you your help.
I have the following SQL query & I am trying to create a report using
the dcount statement listed below the 1st SQL statement, but I am
getting errors.please look at the Dcount statement and tell me where I
went wrong
SELECT Count(*)
FROM mydata
WHERE (((mydata.CURRENT) Like "714*") And ((myddata.OPER) Is Not Null)
And ((mydata.DAYS) Between 0 And 10));
=DCount("[CURRENT] like ''714*' ")","myddata","[OPER] is Not Null and
[days] Between 0 And 10 ")
Additionally I have the following sql statements that I would like to
convert also to the same format as above for use in a report.
SELECT Count(IIf(Current Like "714*",1,Null))/Count(IIf(Current Not
Like "714*",1,Null)) AS result
FROM myddata;
=========================================================
SELECT TOP 3 Error1
FROM myddata
WHERE Current like "714*"
GROUP BY Error1
ORDER BY Count(Error1);
=======================================================================
SELECT myddata.CURRENT, myddata.SSNFEI
FROM myddata
WHERE myddata.MAILDATE<DateAdd("yyyy",-6,Date());
thanks for all your help.

I get #Error when I run the above statement that you gave me also what
about the last section of the SQL namely Between 0 And 10 ?

thanks again
 
P

Pieter Wijnen

Replication of you typo , extra ' ;-)

=DCount("*", "myddata", "[CURRENT] like '714*' AND [OPER] is Not Null And
[DAYS] Between 0 And 10")

DCount(Field,TableOrQuery,WhereCondition)

Pieter

1) No Reason why you can't use the Query (rather a recordset based on it)
in
the approperiate report section to populate Control(s).
That said:

=DCount("*", "myddata", "[CURRENT] like ''714*' AND [OPER] is Not Null")

For the seccond one you either have to use method 1) or a Subreport

HTH

Pieter


hi all & thanks for you your help.
I have the following SQL query & I am trying to create a report using
the dcount statement listed below the 1st SQL statement, but I am
getting errors.please look at the Dcount statement and tell me where I
went wrong
SELECT Count(*)
FROM mydata
WHERE (((mydata.CURRENT) Like "714*") And ((myddata.OPER) Is Not Null)
And ((mydata.DAYS) Between 0 And 10));
=DCount("[CURRENT] like ''714*' ")","myddata","[OPER] is Not Null and
[days] Between 0 And 10 ")
Additionally I have the following sql statements that I would like to
convert also to the same format as above for use in a report.
SELECT Count(IIf(Current Like "714*",1,Null))/Count(IIf(Current Not
Like "714*",1,Null)) AS result
FROM myddata;
=========================================================
SELECT TOP 3 Error1
FROM myddata
WHERE Current like "714*"
GROUP BY Error1
ORDER BY Count(Error1);
=======================================================================
SELECT myddata.CURRENT, myddata.SSNFEI
FROM myddata
WHERE myddata.MAILDATE<DateAdd("yyyy",-6,Date());
thanks for all your help.

I get #Error when I run the above statement that you gave me also what
about the last section of the SQL namely Between 0 And 10 ?

thanks again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top