Forum Discussion
Last & Next Dates
Thanks for the reply.
Only get back in bits.
I think the SQL would be along the lines of
SELECT Min(JobDate) as NextDate
From SchedDates
Where RefNum = RefNum
AND SchedNum = SchedNum
And JobDate > JobDate
or similar as Detail line of report, as Job or Invoice.
RefNum = Customer
SchedNum = Job
JobDate = Date being run for
Where do I put it?
How do I pass these values and retrieve 'NextDate' or 'LastDate'
Many Thanks.
Jim.
"I think the SQL would be along the lines of ..."
So, this is not something you actually tried yet?
How do you determine the criteria in the WHERE clause?
RefNum, SchedNum, and JobDate need to be specified some way. Most commonly, they would refer to controls on a a form.
Perhaps:
SELECT Min(JobDate) as NextDate
From SchedDates
Where RefNum = Forms!YourFormNameGoesHere.cboYourControlNameforRefNumGoesHere
AND SchedNum = Forms!YourFormNameGoesHere.cboYourControlNameforSchedNumGoesHere
And JobDate > Forms!YourFormNameGoesHere.cboYourControlNameforJobDateGoesHere
That would return the earliest JobDate following the date selected in the form, which sounds like it could be the "Next" one.
I have no idea what the "Last" JobDate means in this context. How do you define it? I.e. what makes it the "Last" one?
Hi George,
I dont know what happened to my last reply/plead for help.
I have been at this for months.
I have tried a hundred different ways, things to try to get this work
SELECT Max([Schedule_Dates].[JobDate]) AS LastJobDate
FROM Schedule_Dates
WHERE (((Schedule_Dates.JobDate)<[Reports]![PrRunSheet].[RunDate]) AND ((Schedule_Dates.RefNum)=[Reports]![PrRunSheet].[RefNum]) AND ((Schedule_Dates.SchedNum)=[Reports]![PrRunSheet].[SchedNum]))
ORDER BY Max([Schedule_Dates].[JobDate]);This is the SQL that returns the result.
How do I get LastJobDate onto my report?
Ive tried textbox, subquery, subreport, direct sql as per a form load.
nothing wants to link.
I know that it has to be something simple.
- I am a bit puzzled by this approach, to be honest. I was expecting to see control references to controls on a form, not a report.
So, now I need to try to understand how this should work.
Is LastJobDate supposed to be returned for multiple jobs in a single report?
Why not calculate it in the same query that returns records for the report itself?
Maybe you can provide a sample of the accdb, with any confidential data removed, so we can examine it.Hi George,
Please don't be puzzled by what I'm doing because it doesn't work.
Why I need help.
This is my original select, it works well, does all I need except for my next, last dates.
SELECT [FirstName] & ", " & [Surname] AS Name, Customer.RefNum, Schedule.SchedNum, Customer.Mobile, Address.AddressType, GroupRef.GroupName, Schedule_Dates.JobDate, Schedule_Dates.SortCode, Customer.MapCode, Customer.Balance, [Address1] & ", " & [Suburb] AS Address, Address.Address1, AddressSuburb.Suburb, Address.PostCode, Schedule.SchedMemo
FROM (((((Customer
INNER JOIN Address
ON Customer.RefNum = Address.RefNum)
INNER JOIN AddressSuburb
ON (Address.SuburbCode = AddressSuburb.SuburbCode)
AND (Address.PostCode = AddressSuburb.Postcode))
INNER JOIN Schedule
ON Customer.RefNum = Schedule.RefNum)
INNER JOIN [Schedule Line]
ON (Schedule.RefNum = [Schedule Line].RefNum)
AND (Schedule.SchedNum = [Schedule Line].SchedNum))
INNER JOIN Schedule_Dates
ON (Schedule.SchedNum = Schedule_Dates.SchedNum)
AND (Schedule.RefNum = Schedule_Dates.RefNum))
INNER JOIN GroupRef
ON Schedule_Dates.GroupCode = GroupRef.GroupCode
WHERE (((Address.AddressType)="Service")
AND ((GroupRef.GroupName)=[Enter Group Ref:])
AND ((Schedule_Dates.JobDate)=[Enter Job Date:])
AND ((Schedule.SchedStatus)=1)
AND ((Schedule_Dates.JobStatus)=1))
ORDER BY Schedule_Dates.SortCode;Does this give an idea of tables etc. or do you still want DB?
The concept I took this from was an application I used and had a sub_form somehow that passed the dates in.
I'm open to anything that works.
Thanks again.
