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.
