Forum Discussion
Last & Next Dates
Hi All,
new to Access.
I have a report, Invoice, - I need a 'LastDate' and 'NextDate', x3, from a table
where RefNum, SchedNum, Date <>(Approp) JobDate
I feel it should be simple but just seems everything I try cant quite get there.
Man
6 Replies
- Let's get some concrete details on the table. Show us, please, the actual query you tried.
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?
