query conversion

  • Thread starter Thread starter davidstevans
  • Start date Start date
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.
 
David, the DCount would work like this:
= DCount("CURRENT", "MyDdata", _
"(MyDdata.CURRENT Like " & "'714*'" _
& " AND (MyDdata.days Between 0 And 10) " _
& " AND MyDdata.OPER Is Not Null)")

If you need to put this in the datasource for a control on a form, you can
avoid having to reconstruct the string by pasting the expression into a
function like this:

Function fcnCount()
fcnCount = DCount("CURRENT", "MyData", _
"(MyData.CURRENT Like " & "'714*'" _
& " AND (MyData.days Between 0 And 10) " _
& " AND MyData.OPER Is Not Null)")
End Function

Then make the datasource for the control: =fcnCount()

HTH, UpRider
 
Back
Top