selecting record based on months

[zhockz]

ne1 can help me? i created a report based on selected month & year, but i
want to view the selected month's records including the last month records...
tnx

 

[Stefan Hoffmann]

hi,

ne1 can help me? i created a report based on selected month & year, but i
want to view the selected month's records including the last month records...

E.g.

SELECT *
FROM [yourTable]
WHERE Year([dateField]) = Year([yourDate])
AND Month([dateField]) >= Month([yourDate]) - 1
AND Month([dateField]) <= Month([yourDate])


mfG
--> stefan <--

 

[zhockz via AccessMonster.com]

tnx stefan..but, here's my example of my report....

february 2009 january 2009
remittance remittance
100.00 200.00
50.00 100.00
200.00 300.00

i placed a [Enter Date] in my criteria query so i can view my records on feb
2009, but i want also to view my last month record (i.e. jan 2009) on my
report...hope that u can help on this..tnx a lot

 

[Stefan Hoffmann]

hi,

i placed a [Enter Date] in my criteria query so i can view my records on feb
2009, but i want also to view my last month record (i.e. jan 2009) on my
report...hope that u can help on this..tnx a lot

Can you post your reports query?



mfG
--> stefan <--

 

[zhockz via AccessMonster.com]

hi,

i placed a [Enter Date] in my criteria query so i can view my records on feb
2009, but i want also to view my last month record (i.e. jan 2009) on my
report...hope that u can help on this..tnx a lot

Can you post your reports query?

mfG
--> stefan <--

elo stefan...sorry im 2 busy this couple of days...nwy hrz my report query

SELECT Company.CompanyName, Company.CompanyAddress, Company.Telephone,
Company.GSISEmployerIDNo, UCase$(Format$([Date],"mmmm yyyy",0,0)) AS Date1,
Employees.PolicyNumber, Employees.UOLIPolicyNo1, Employees.UOLIPolicyNo2,
[LastName] & ", " & [FirstName] & " " & Left$([Middle Initial],1) & "." AS
Employee, Title.Title, Sum(Earnings.Salary) AS SumOfSalary, Sum(Earnings.
GSISUOLICont1) AS SumOfGSISUOLICont1, Sum(Earnings.GSISUOLICont2) AS
SumOfGSISUOLICont2, Sum(Earnings.GSISCEAPCont) AS SumOfGSISCEAPCont, Sum
(Earnings.GSISHIPCont) AS SumOfGSISHIPCont, Employees.LastName, Employees.
FirstName, Employees.[Middle Initial], Earnings.Remarks, Sum([Salary]*0.09)
AS Personal, Sum([Salary]*0.12) AS Govt, Sum(Earnings.GSISCont) AS
SumOfGSISCont, UCase$(Format$([Date]-31,"mmm yyyy",0,0)) AS Date2
FROM Title RIGHT JOIN ((Company RIGHT JOIN Employees ON Company.EmployerID =
Employees.EmployerID) RIGHT JOIN Earnings ON Employees.EmployeeID = Earnings.
EmployeeID) ON Title.TitleID = Employees.TitleID
GROUP BY Company.CompanyName, Company.CompanyAddress, Company.Telephone,
Company.GSISEmployerIDNo, UCase$(Format$([Date],"mmmm yyyy",0,0)), Employees.
PolicyNumber, Employees.UOLIPolicyNo1, Employees.UOLIPolicyNo2, [LastName] &
", " & [FirstName] & " " & Left$([Middle Initial],1) & ".", Title.Title,
Employees.LastName, Employees.FirstName, Employees.[Middle Initial], Earnings.
Remarks, UCase$(Format$([Date]-31,"mmm yyyy",0,0))
HAVING (((UCase$(Format$([Date],"mmmm yyyy",0,0)))=[Enter Date (exp: January
2009)]))
ORDER BY [LastName] & ", " & [FirstName] & " " & Left$([Middle Initial],1) &
".";

 

[Stefan Hoffmann]

hi,

elo stefan...sorry im 2 busy this couple of days...nwy hrz my report query

No problem. First of all, use simpler queries. Create a query with joins
only. Create a second query based on it doing the grouping.

Place the filter in the first query, something like e.g.

PARAMETER [Enter Month] LONG;
PARAMETER [Enter Year] LONG;
SELECT *
FROM Title T RIGHT JOIN
(
(Company C RIGHT JOIN Employees Emp ON C.EmployerID = Emp.EmployerID)
RIGHT JOIN Earnings Ear ON Emp.EmployeeID = Ear.EmployeeID
) ON T.TitleID = Emp.TitleID
WHERE [Date]
BETWEEN DateSerial([Enter Year], [Enter Month] - 1, 1)
AND DateSerial([Enter Year], [Enter Month] + 1, 1) - 1


btw, rename your [Date] field. This is a really, really bad name for a
date field.

mfG
--> stefan <--