Err in Query to Report

[Guest_imported]

Hi,

I got a query named "qta query1" which ontain a date field and a report "rpt report1".
If I use the query and use the criteria between some period of the date field, it is ok when open my report. However when I change the criteria of the query to parameter query, the report will got error, hoping some of you can help ~ thx.

Here are the details:
ok mode :
Query [date field]: criteria --> between #8/1/99# and # 10/1/99#
report : no error for using DFirst and DLast function

error mode:
Query[date field]: criteria --> between [start date:] and [end date:]
report : #Error for using DFirst and DLast

Thanks in advance to anybody who can help me!

Kenneth

 

[Steve101]

Ken,
Suggest you publish a copy of the full SQL associated with your query, to allow us to help you. Select Query, Design, View SQL options, and cut and past the content of the SQL into your next post,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Guest_imported]

Hi All,

Here is the full SQL Query which didn't have any problem:

Query (qry Inspection Record Monthly) :
SELECT DISTINCTROW [Inspection Record].[No], Format$([Inspection Record].[Date],'yyyy mm') AS [Date By Month], [Inspection Record].Date, [Inspection Record].[Inspected by], [Inspection Record].[Ship Name], Vessel.Flag, Vessel.[Ship Type], Vessel.[Trading Area], [Inspection Record].Location, [Inspection Record].Agent, [Inspection Record].DOC, [Inspection Record].[DG List], [Inspection Record].[Stowage Plan], [Inspection Record].Manifest, [Inspection Record].MP, [Inspection Record].Declaration, [Inspection Record].[IMDG Code], [Inspection Record].[Packing Cert], [Inspection Record].Stowage, [Inspection Record].Label, [Inspection Record].[DG Onboard], [Inspection Record].[Pre-Arrival Manifest], [Inspection Record].Notification, Sum([Inspection Record].[N-DD]) AS [Sum Of N-DD], Sum([Inspection Record].[N-MPD]) AS [Sum Of N-MPD], Sum([Inspection Record].[N-DGD]) AS [Sum Of N-DGD], Sum([Inspection Record].[N-CPC]) AS [Sum Of N-CPC], Sum([Inspection Record].[N-SS]) AS [Sum Of N-SS], Sum([Inspection Record].[N-LM]) AS [Sum Of N-LM], Sum([Inspection Record].[N-PAM]) AS [Sum Of N-PAM], Sum([Inspection Record].[N-DGC]) AS [Sum Of N-DGC], Count(*) AS [Count Of Inspection Record]
FROM [Inspection Record] LEFT JOIN Vessel ON [Inspection Record].[Ship Name] = Vessel.[Ship Name]
GROUP BY [Inspection Record].[No], Format$([Inspection Record].[Date],'yyyy mm'), [Inspection Record].Date, [Inspection Record].[Inspected by], [Inspection Record].[Ship Name], Vessel.Flag, Vessel.[Ship Type], Vessel.[Trading Area], [Inspection Record].Location, [Inspection Record].Agent, [Inspection Record].DOC, [Inspection Record].[DG List], [Inspection Record].[Stowage Plan], [Inspection Record].Manifest, [Inspection Record].MP, [Inspection Record].Declaration, [Inspection Record].[IMDG Code], [Inspection Record].[Packing Cert], [Inspection Record].Stowage, [Inspection Record].Label, [Inspection Record].[DG Onboard], [Inspection Record].[Pre-Arrival Manifest], [Inspection Record].Notification, Year([Inspection Record].[Date])*12+DatePart('m',[Inspection Record].[Date])-1
HAVING ((([Inspection Record].Date) Between #8/1/1999# And #10/1/1999#));

However, when I change the last sentence to
Between [a] and

the function from the report of
=DFirst("[Date By Month]","qry Inspection Record Monthly",[No]>0) & " - " & DLast("[Date By Month]","qry Inspection Record Monthly",[No]>0)
will return #Error

hoping it can give you all some idea of my problem. Thx again...

Kenneth

 

[Steve101]

Ouch!

Kenneth; You might just need to ensure that the query parameters [a] and are defined as type Date/Type. To do this, go into the query in Design View, and select the Query, Parameters option, and enter [a] and as two separate parameters, each with DataType of Date/Time. This should then make them be equivalent to their #dd/mm/yy# eqivalents, which you say work, and hopefully solve the problem you're having. Otherwise get back.

Another thing; your query can be made MUCH shorter if you add an alias to the Inspection Record table, of say IR. To do this, expose the query properties, point to the Inspection Record table, and in the Alias Property, type in IR. This will then use IR in the query, instead of the longer Inspection Record each time.
Good luck,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Guest_imported]

Hi,

Thanks for your suggestion first, however, I have try that method before and it still got the same error as I said before. I have try to execute the parameter and it really return the exactly same result as the normal query. I just wonder if I add a parameter in a query , will the query result will change the properties and then I can't use all Domina function like DFirst, DCount....

Kenneth

 

[Guest_imported]

Hi all,

For the problem I mentioned above, I found out that if I use the SQL Aggregate function of max or min for the date field from the query, there will be no error if I put them in the Report Header, however, if I move them to the page header, error will occur again. Hoping that it will give all of you some idea of what problem I am facing.

Thanks in advance to anybody who can help me!
Rgds,
Kenneth

 

[Steve101]

You cannot use the max, min, or other aggregate functions within the page header or footer; you can use them within the report header or footer. From the sound of things, you are trying to use something like '=max(.....)' as the controlsource of one of your report controls. Am I right?

What exactly is it that you are trying to do within the report??? Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Guest_imported]

Hi,

right, as I need to calculate the grouping value for each group, eg, sum up or count for the record from the query. However, if I am not using parameter query, I can use domain aggregate inside page header and detail. So, I just wonder why I can use them if I use parameter query. Hoping it can give you more detail. Thx

Rgds,
Kenneth

 

[Steve101]

Ken,
Have you defined Groups within your report. It is on the group Footer's that you need to put the aggregations; not on the Page Header/Footer sections. In fact, if you define the Group footers properly, you should be able to replace your aggregage query with a standard select query; have the option of making the report Detail line not visible, and achieve what you want via your Group Header/Footer lines.
You may need a direction shift to your current approach to simplify things.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Guest_imported]

Hi,

Sorry, I really cannot get what you mean.

Rgds,
Kenneth

 

[Steve101]

Ken,
Check online help; the section about 'Sorting and Grouping Records in a Report'. I think that this is relavent to what you're trying to do, and should simplify things somewhat. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au