Am I doing something wrong? Query problem

[3Mark3]

Ok I'm trying to run a query. I'm formatting a certain field as follows:

Audit Date: Format([audited],"Short Date")

In the criteria, I have the following:
>=[Enter start date to report]

Now the problem is, no matter what date I put in the criteria, I still get all records. I have even tried removing completly the >= and just putting in a single date. Still get all records. I am confused as to why this isn't working properly. Can anyone suggest what I may be doing wrong? Thank you!

Mark

 

[Golom]

Try inserting a PARAMETERS statement in the SQL view of the query as in

PARAMETERS [Enter start date to report] DateTime;
Select ... etc.

Access sometimes has problems converting entered text to a date if you are not explicit about its being a date.

 

[PHV]

Anyway, Format([audited],"Short Date") is a string, not a DateTime ...

 

[3Mark3]

Golom...forgive my limited code knowledge....how would I include the parameters? In SQL, my query looks like this (I removed the format part):

SELECT audit_findings_all.auditor, Count(audit_findings_all.loan) AS CountOfloan, audit_findings_all.exception_type1, audit_findings_all.audited
FROM audit_findings_all
GROUP BY audit_findings_all.auditor, audit_findings_all.exception_type1, audit_findings_all.audited
HAVING (((audit_findings_all.exception_type1) Like "*missing note") AND ((audit_findings_all.audited)=[enter date]));



PHV, I've done this format before, but it obviously wasn't the way I just set it. What would be the way to format that field so I could only see the date as mm/dd/yy?

 

[Golom]

Like this

[COLOR=red]PARAMETERS [enter date] DateTime;[/color]
SELECT auditor, Count(loan) AS CountOfloan, exception_type1, audited

FROM audit_findings_all

WHERE exception_type1 Like "*missing note" 
      AND audited=[enter date]

GROUP BY auditor, exception_type1, audited

Your HAVING clause should be a WHERE clause (as I have shown) since it references individual fields rather than aggregates.

 

[PHV]

PARAMETERS [enter date] DateTime;
SELECT auditor, Count(loan) AS CountOfloan, exception_type1, audited
FROM audit_findings_all
WHERE exception_type1 Like '*missing note' AND audited=[enter date]
GROUP BY auditor, exception_type1, audited

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Although it looks like it ... PHV and I really didn't copy each other's code.

 

[PHV]

You was faster this time Golom ;-)

 

[3Mark3]

Thank you for both of your imputs. I have one problem that comes up though.

That code works well, but the problem remains that I need to enter the entire date (3/2/2006 9:48:06 AM for example) as opposed to entering just 03/02/06, to pull up any data. Is there a way to format this so I don't have to enter the hour, minute, etc.?

Also, how would it change if I needed to do a between criteria? (eg. from 02/01/06 to 02/28/06)?

 

[PHV]

PARAMETERS [enter start date] DateTime, [enter end date] DateTime;
SELECT auditor, Count(loan) AS CountOfloan, exception_type1, Format([audited],"Short Date") As AuditDate
FROM audit_findings_all
WHERE exception_type1 Like '*missing note' AND (Int(audited) Beetween [enter start date] And [enter end date])
GROUP BY auditor, exception_type1, Format([audited],"Short Date")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[3Mark3]

Excellent!!! You guys are the best. Thank you very very much for your efforts with this problem.

Mark