Ask for advise

  • Thread starter Thread starter Michael Lam
  • Start date Start date
M

Michael Lam

Dear All expert

I have modify the query as follow but dont work Please advise

SELECT DISTINCTROW RequestForm.[Approve Status], RequestForm.Department,
Format$([RequestForm].[Created Date],'Short Date') AS [Created Date/¤é],
Sum([RequestForm].[Estimate Effort]) AS [Á`­p ¤§ Estimate Effort], Count(*)
AS RequestForm¤§µ§¼Æ
FROM RequestForm
GROUP BY RequestForm.[Approve Status], RequestForm.Department,
Format$([RequestForm].[Created Date],'Short Date')
HAVING (((RequestForm.[Approve
Status])=[Forms]![ReportPrint]![ApproveStatusSelect]) AND
((Format$([RequestForm].[Created Date],'Short Date')) Between
[Forms]![ReportPrint]![CreatDateFrom] And
[Forms]![ReportPrint]![CreateDateTo]))
ORDER BY RequestForm.Department;

Michael Lam

"Michael Lam" <[email protected]> ¦b¶l¥ó ¤¤¼¶¼g...
I have using the wizard to create the following query and will generate a
report base on this query


SELECT DISTINCTROW RequestForm.[Approve Status], RequestForm.Department,
Format$([RequestForm].[Created Date],'Long Date') AS [Created Date/¤é],
Sum([RequestForm].[Estimate Effort]) AS [Á`­p ¤§ Estimate Effort], Count(*)
AS RequestForm¤§µ§¼Æ
FROM RequestForm
GROUP BY RequestForm.[Approve Status], RequestForm.Department,
Format$([RequestForm].[Created Date],'Long Date')
HAVING (((RequestForm.[Approve Status])="Accept"))
ORDER BY RequestForm.Department;

How can I modify this query so that the query able to select the record base
on the following criteria

[Approve status] = Accept or Reject based on the value from the form
[Create Date] between the date range enter in the form i.e (From
11-02-2004 to 11-02-2004)
[Department] (optional)

Do I just create the entry in the parameter like [Approve
Status]=[Forms]![NewForm]![combo32]

[Create Date] between [Forms]![NewForm]![FieldA]and
[Forms]![NewForm]![FieldB]

Please adviese
If not, Am I need to write two query in order to do this funciton?

THANKS
 
Hi


What those sign means ?
1.RequestForm¤§µ§¼Æ , AS [Created Date/¤é] ,AS [Á`­p ¤§ Estimate Effort],
2.[Forms]![ReportPrint]![CreatDateFrom] And
[Forms]![ReportPrint]![CreateDateTo]))
Click to expand...
Has your form control named "CreatDateFrom" and not "CreateDateFrom"
3.Try Convert to Date function( CDate)

CDate([Forms]![ReportPrint]![CreatDateFrom]) And
CDate([[Forms]![ReportPrint]![CreateDateTo])))

Is your RequestForm a table?

More real
SELECT DISTINCTROW RequestForm.[Approve Status], RequestForm.Department,
Format([RequestForm].[Created Date],'Short Date') AS [Created Date/¤é(New
maybe ?)],
Sum([RequestForm].[Estimate Effort]) AS [(New maybe ?)Á`­p ¤§ Estimate
Effort],
Count(*) ???What???All fields??? AS RequestForm¤§µ§¼Æ(New maybe ?)
FROM RequestForm
GROUP BY RequestForm.[Approve Status], RequestForm.Department,[Created
Date/¤é]
HAVING (((RequestForm.[Approve
Status])=[Forms]![ReportPrint]![ApproveStatusSelect]) AND
( CDate([Created Date/¤é] )>=
CDate([Forms]![ReportPrint]![CreatDateFrom] )And
CDate([Created Date/¤é] )<= CDate([[Forms]![ReportPrint]![CreateDateTo]))))
ORDER BY RequestForm.Department;
----------------------------------------------------------------------------
----------------
Or
Most real
maybe like this
SELECT DISTINCTROW RequestForm.[Approve Status], RequestForm.Department,
Format([RequestForm].[Created Date],'Short Date') AS [Formated Date],
Sum([RequestForm].[Estimate Effort]) AS [(Summed Estimate Effort],
Count(*) ???What???All fields??? AS CountedRequestForm
Maybe:
Count(([RequestForm].[Created Date]) AS CountedDate
FROM RequestForm
GROUP BY RequestForm.[Approve Status], RequestForm.Department,[Formated
Date]
HAVING (((RequestForm.[Approve
Status])=[Forms]![ReportPrint]![ApproveStatusSelect]) AND
( CDate([Formated Date]) >=
CDate([Forms]![ReportPrint]![CreateDateFrom] )And
CDate([Formated Date]) <= CDate([[Forms]![ReportPrint]![CreateDateTo]))))
ORDER BY RequestForm.Department;
----------------------------------------------------------------------------
---------------







SELECT DISTINCTROW RequestForm.[Approve Status], RequestForm.Department,
CDate([RequestForm].[Created Date])AS [Created Date/¤é],

Michael Lam said:
Dear All expert

I have modify the query as follow but dont work Please advise

SELECT DISTINCTROW RequestForm.[Approve Status], RequestForm.Department,
Click to expand...
CDate([RequestForm].[Created Date])AS [Created Date/¤é],
not this
Format$([RequestForm].[Created Date],'Short Date') AS [Created Date/¤é],
Click to expand...
Sum([RequestForm].[Estimate Effort]) AS [Á`­p ¤§ Estimate Effort], Count(*)
AS RequestForm¤§µ§¼Æ
FROM RequestForm
GROUP BY RequestForm.[Approve Status], RequestForm.Department,[Created
Click to expand...
Date/¤é]
not this> Format$([RequestForm].[Created Date],'Short Date')
HAVING (((RequestForm.[Approve
Status])=[Forms]![ReportPrint]![ApproveStatusSelect]) AND [Created Date/¤é] Between
[Forms]![ReportPrint]![CreateDateFrom] And
[Forms]![ReportPrint]![CreateDateTo]))
ORDER BY RequestForm.Department;
Click to expand...

or rather
HAVING (((RequestForm.[Approve
Status])=[Forms]![ReportPrint]![ApproveStatusSelect]) AND
Click to expand...
[Created Date/¤é] >= CDate([Forms]![ReportPrint]![CreatDateFrom] )And
[Created Date/¤é] <= CDate([[Forms]![ReportPrint]![CreateDateTo])))
ORDER BY RequestForm.Department;
not
((Format$([RequestForm].[Created Date],'Short Date')) Between
[Forms]![ReportPrint]![CreatDateFrom] And
[Forms]![ReportPrint]![CreateDateTo]))
ORDER BY RequestForm.Department;

Michael Lam

"Michael Lam" <[email protected]> ¦b¶l¥ó ¤¤¼¶¼g...
I have using the wizard to create the following query and will generate a
report base on this query


SELECT DISTINCTROW RequestForm.[Approve Status], RequestForm.Department,
Format$([RequestForm].[Created Date],'Long Date') AS [Created Date/¤é],
Sum([RequestForm].[Estimate Effort]) AS [Á`­p ¤§ Estimate Effort], Count(*)
AS RequestForm¤§µ§¼Æ
FROM RequestForm
GROUP BY RequestForm.[Approve Status], RequestForm.Department,
Format$([RequestForm].[Created Date],'Long Date')
HAVING (((RequestForm.[Approve Status])="Accept"))
ORDER BY RequestForm.Department;

How can I modify this query so that the query able to select the record base
on the following criteria

[Approve status] = Accept or Reject based on the value from the form
[Create Date] between the date range enter in the form i.e (From
11-02-2004 to 11-02-2004)
[Department] (optional)

Do I just create the entry in the parameter like [Approve
Status]=[Forms]![NewForm]![combo32]

Cdate([Create Date]) between CDate([Forms]![NewForm]![FieldA])and
CDate([[Forms]![NewForm]![FieldB])



Please adviese
If not, Am I need to write two query in order to do this funciton?

THANKS
Click to expand...
 
Back
Top