rewrite queries

[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.

 

[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

 

[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.

 

[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

 

[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