Date form and Report

[artist02 via AccessMonster.com]

Hello,

I have 2 queries qryTotal_Production and
qryDowntime:

qryDowntime:

SELECT qryDowntimeUnion.Session, qryDowntimeUnion.Machine,
qryDowntimeUnion.Shift, qryDowntimeUnion.Downtime_Code,
Sum(qryDowntimeUnion.Downtime) AS SumOfDowntime,
Format([Date],"dd-mmm-yyyy") AS [Downtime Date]
FROM qryDowntimeUnion
GROUP BY qryDowntimeUnion.Session, qryDowntimeUnion.Machine,
qryDowntimeUnion.Shift, qryDowntimeUnion.Downtime_Code,
Format([Date],"dd-mmm-yyyy")
HAVING (((qryDowntimeUnion.Downtime_Code) Is Not Null) AND
((Sum(qryDowntimeUnion.Downtime)) Not Like "0"));

qryTotal_Production:

SELECT qryScrapUnion.Session, qryScrapUnion.Machine,
qryScrapUnion.Shift, Sum(qryScrapUnion.Scrap_Kgs) AS SumOfScrap_Kgs,
Format([Date],"dd-mmm-yyyy") AS [Scrap Date]
FROM qryScrapUnion
GROUP BY qryScrapUnion.Session, qryScrapUnion.Machine,
qryScrapUnion.Shift, Format([Date],"dd-mmm-yyyy")
HAVING (((Sum(qryScrapUnion.Scrap_Kgs)) Not Like "0"));

I have an unbound report called "test" which has some of these
text boxes: i) txtE1 ii) txtProduction. The controlsource of is like this and

works perfectly:
=Nz(DLookUp("[SumOfDowntime]","qryDowntime","[Downtime_Code] = '" & 3 & "'AND
[Machine]= '" & 1 & "'"),0)

1. How can i create a form where a user can input a date and the DLookUp
above creates the
correct info for that particular date on the report.
2. How can i create a DLookUp on the controlsource of txtProduction based on
qryTotal_Production
where it presents [SumOfScrap_Kgs] from qryTotal_Production where
[Scrap Date] = to the the date entered by a user on the form created above.


Thank you in advance!

PS. Iam an access 2002 novice.

 

[Jeff L]

1. How can i create a form where a user can input a date and the
DLookUp above creates the correct info for that particular date on the
report.

Your qryDowntime needs to have a date field that can be filtered. Is
it [Date]? Then your DLookup would look like the following. I made it
look a little neater:
=Nz(DLookUp("[SumOfDowntime]","qryDowntime","[Downtime_Code] = '3' AND
[Machine]= '1' And [Date] = #" & Forms!YourFormName!DateFieldOnForm &
"#"),0)

2. How can i create a DLookUp on the controlsource of txtProduction
based on qryTotal_Production where it presents [SumOfScrap_Kgs] from
qryTotal_Production where
[Scrap Date] = to the the date entered by a user on the form created
above.

I'm not sure how you are using txtProduction to do a lookup from
qryTotal_Production, so here's a kinda generic DLookup for you:

=Nz(DLookUp("[SumOfScrap_Kgs]","qryTotal_Production", "SomeField = '" &
[txtProduction] & "' And [Date] = #" &
Forms!YourFormName!DateFieldOnForm & "#"),0)

I did notice some potential problems:
1. HAVING Sum(qryScrapUnion.Scrap_Kgs) Not Like "0" should be
HAVING Sum(qryScrapUnion.Scrap_Kgs) > 0. Like is used to compare
strings and not numbers.

2. Having a field called Date in your table is not a good idea. Date
is a reserved word in Access and reserved words should not be used as a
field name.

Hope that helps!

Hello,

I have 2 queries qryTotal_Production and
qryDowntime:

qryDowntime:

SELECT qryDowntimeUnion.Session, qryDowntimeUnion.Machine,
qryDowntimeUnion.Shift, qryDowntimeUnion.Downtime_Code,
Sum(qryDowntimeUnion.Downtime) AS SumOfDowntime,
Format([Date],"dd-mmm-yyyy") AS [Downtime Date]
FROM qryDowntimeUnion
GROUP BY qryDowntimeUnion.Session, qryDowntimeUnion.Machine,
qryDowntimeUnion.Shift, qryDowntimeUnion.Downtime_Code,
Format([Date],"dd-mmm-yyyy")
HAVING (((qryDowntimeUnion.Downtime_Code) Is Not Null) AND
((Sum(qryDowntimeUnion.Downtime)) Not Like "0"));

qryTotal_Production:

SELECT qryScrapUnion.Session, qryScrapUnion.Machine,
qryScrapUnion.Shift, Sum(qryScrapUnion.Scrap_Kgs) AS SumOfScrap_Kgs,
Format([Date],"dd-mmm-yyyy") AS [Scrap Date]
FROM qryScrapUnion
GROUP BY qryScrapUnion.Session, qryScrapUnion.Machine,
qryScrapUnion.Shift, Format([Date],"dd-mmm-yyyy")
HAVING (((Sum(qryScrapUnion.Scrap_Kgs)) Not Like "0"));

I have an unbound report called "test" which has some of these
text boxes: i) txtE1 ii) txtProduction. The controlsource of is like this and

works perfectly:
=Nz(DLookUp("[SumOfDowntime]","qryDowntime","[Downtime_Code] = '" & 3 & "'AND
[Machine]= '" & 1 & "'"),0)

1. How can i create a form where a user can input a date and the DLookUp
above creates the
correct info for that particular date on the report.
2. How can i create a DLookUp on the controlsource of txtProduction based on
qryTotal_Production
where it presents [SumOfScrap_Kgs] from qryTotal_Production where
[Scrap Date] = to the the date entered by a user on the form created above.


Thank you in advance!

PS. Iam an access 2002 novice.