Reportdate?

[nisa]

Hey!

Now my DB finally is almost up and running, so now I only have one (maybe 2) question(s) left...

How do I create a reportdate-field?

What I tinhk of, is that instead of printing out the entire database, then I only want to print, for example, from the 1st of May 2006 untill 23rd of August 2007, instead of getting the entire record from way back into the middleages...

 

[KeithG]

you need to add criteria to the date field in the reports recordsource. something like below

between [Start Date] and [End Date]

 

[rainman89]

ok this can be done

first create a form that you will use to input the dates that you want to print the report for. put a start date and end date field on there. and also a button to open your report

then in the criteria of your query for the report put

between forms!yourdateselectionformname.startdate AND forms!yourdateselectionformname.enddate

hope this gives you a start

 

[nisa]

I got this already:

"For arbejde udført mellem: " & [Forms]![Rapportdatoområde]![Startdato] & " og " & [Forms]![Rapportdatoområde]![Slutdato]

(It's in Danish sorry )

Translated it would look sometinhg like this: "For work done between: " & [Forms]![Reportdatearea]![startdate] & "and" & [Forms]![Reportdatearea]![enddate]

But when I try open the report all it shows is a #name? error-message... and yes I've already created the "start and end-date" form

 

[rainman89]

where do you have that located? on the report itself? youll need to make sure the criteria for the report has changed

 

[nisa]

Ok... now you got me confused... (I'm totaaly noob with this)... how do I change the criteria for the report?

 

[rainman89]

Ok... now you got me confused... (I'm totaaly noob with this)... how do I change the criteria for the report?

first off, your report is based on a query correct?

 

[nisa]

yup, that is correct...

 

[rainman89]

in the query criteria for the dates you have to put


between [Forms]![Rapportdatoområde]![Startdato] AND [Forms]![Rapportdatoområde]![Slutdato]

 

[nisa]

in the query criteria for the dates you have to put


between [Forms]![Rapportdatoområde]![Startdato] AND [Forms]![Rapportdatoområde]![Slutdato]

But where do I find the query criteria? That was more my question... sorry for the utterly crappy phrasing of question...

 

[Michael J Ross]

If you go into the design view of your query, under the date field you are querying enter the
between [Forms]![Rapportdatoområde]![Startdato] AND [Forms]![Rapportdatoområde]![Slutdato]

on the Criteria line which is located under Show in the query grid.

 

[nisa]

Ok I've tried now to put it under the workdate, and placing the line you guys have given me under tha criteria, but apparently I've done something wrong, cuz now it doesn't give me any data, no matter what I put in under reportdatearea...

I've also tried to add it under all the other columns, but still it doesn't give me anything...

I'm totally confused!?!

What I got so far in SQL looks like this:

1st report:

SELECT DISTINCTROW Arbejdsseddeltimer.ProjektId, Projekter.Projektbeskrivelse, Arbejdsseddeltimer.ArbejdsseddelId, Arbejdssedler.MedarbejderId, Sum(Arbejdsseddeltimer.Faktureringstimer) AS SumOfFaktureringstimer1, Sum(Arbejdsseddeltimer.Antal_Rek) AS [Sum Of Antal_Rek]
FROM Projekter INNER JOIN (Arbejdsseddeltimer LEFT JOIN Arbejdssedler ON Arbejdsseddeltimer.ArbejdsseddelId = Arbejdssedler.ArbejdsseddelId) ON Projekter.ProjektId = Arbejdsseddeltimer.ProjektId
GROUP BY Arbejdsseddeltimer.ProjektId, Projekter.Projektbeskrivelse, Arbejdsseddeltimer.ArbejdsseddelId, Arbejdssedler.MedarbejderId;


2nd report:

SELECT DISTINCTROW Arbejdsseddeltimer.ProjektId, Projekter.Projektnavn, Arbejdsseddeltimer.ProduktionskodeId, Produktionskoder.Produktionskode, First(Projekter.Projektbeskrivelse) AS [Første Of Projektbeskrivelse], Sum(Arbejdsseddeltimer.Faktureringstimer) AS SumOfFaktureringstimer, Sum(Arbejdsseddeltimer.Antal_Rek) AS [Sum Of Antal_Rek]
FROM Projekter INNER JOIN (Produktionskoder INNER JOIN Arbejdsseddeltimer ON Produktionskoder.ProduktionskodeId = Arbejdsseddeltimer.ProduktionskodeId) ON Projekter.ProjektId = Arbejdsseddeltimer.ProjektId
GROUP BY Arbejdsseddeltimer.ProjektId, Projekter.Projektnavn, Arbejdsseddeltimer.ProduktionskodeId, Produktionskoder.Produktionskode;


where should I place the date request?

 

[rainman89]

SELECT DISTINCTROW Arbejdsseddeltimer.ProjektId, Projekter.Projektbeskrivelse, Arbejdsseddeltimer.ArbejdsseddelId, Arbejdssedler.MedarbejderId, Sum(Arbejdsseddeltimer.Faktureringstimer) AS SumOfFaktureringstimer1, Sum(Arbejdsseddeltimer.Antal_Rek) AS [Sum Of Antal_Rek]
FROM Projekter INNER JOIN (Arbejdsseddeltimer LEFT JOIN Arbejdssedler ON Arbejdsseddeltimer.ArbejdsseddelId = Arbejdssedler.ArbejdsseddelId) ON Projekter.ProjektId = Arbejdsseddeltimer.ProjektId 
GROUP BY Arbejdsseddeltimer.ProjektId, Projekter.Projektbeskrivelse, Arbejdsseddeltimer.ArbejdsseddelId, Arbejdssedler.MedarbejderId;
[b]HAVING (your datefield) between [Forms]![Rapportdatoområde]![Startdato] AND [Forms]![Rapportdatoområde]![Slutdato]) [/b]

i believe its something like that

 

[nisa]

unfortunately not...

SELECT DISTINCTROW Arbejdsseddeltimer.ProjektId, Projekter.Projektbeskrivelse, Arbejdsseddeltimer.ArbejdsseddelId, Arbejdssedler.MedarbejderId, Sum(Arbejdsseddeltimer.Faktureringstimer) AS SumOfFaktureringstimer1, Sum(Projekter.Antal_Rek) AS [Sum Of Antal_Rek]
FROM Projekter INNER JOIN (Arbejdsseddeltimer LEFT JOIN Arbejdssedler ON Arbejdsseddeltimer.ArbejdsseddelId = Arbejdssedler.ArbejdsseddelId) ON Projekter.ProjektId = Arbejdsseddeltimer.ProjektId
WHERE (((Arbejdsseddeltimer.Arbejdsdato) Between [forms]![rapportdatoområde].[startdate] And [forms]![rapportdatoområde].[enddate]))
GROUP BY Arbejdsseddeltimer.ProjektId, Projekter.Projektbeskrivelse, Arbejdsseddeltimer.ArbejdsseddelId, Arbejdssedler.MedarbejderId;

That's how it looks now, and still it doesn't give any sense... it still asks for the parameter values, and when those are entered, then it complains that the values are to complex to handle.