Date Query (1 Viewer)

DavidCon

Registered User.
Local time
Today, 06:12
Joined
Apr 14, 2011
Messages
23
Hi All,

I’ve researched this but could not find anything suitable so i’m here to beg for your help!

This might be a simple thing but it has got be stumped.

So here’s the scenario....

I have a table with a list of dates [PaymentRun] and a query which basically calculates [due date]. What I would like to do is to compare the [due date] to the [PaymentRun] and return the value which is the closest date in the future.

i.e.

table 1

17/05/2018
26/05/2018

if [Due Date] = 18/05/2018 then 26/05/18 is returned.

Hope this make sense about would appreciate any feedback.

Many thanks
 

isladogs

MVP / VIP
Local time
Today, 13:12
Joined
Jan 14, 2017
Messages
18,186
Try something like
Code:
DMin("paymentRun","YourTableName")>[date due]
 

DavidCon

Registered User.
Local time
Today, 06:12
Joined
Apr 14, 2011
Messages
23
Thanks for the suggestion, i've tried it but it just returns #Error

This is what I tried

Code:
test: DMin("payment run","payment dates">[due date])

both fields are date/time format.

any suggestions please?
 

isladogs

MVP / VIP
Local time
Today, 13:12
Joined
Jan 14, 2017
Messages
18,186
As you have spaces you need to enclose items in [] brackets
 

DavidCon

Registered User.
Local time
Today, 06:12
Joined
Apr 14, 2011
Messages
23
Thanks, I tried that but no luck. I even removed the spaces in the field names and still no luck.

This is what I have at the moment and I just get #Error
Code:
test: DMin("paymentdates","paymentrun">[duedate])

any ideas please?
 

isladogs

MVP / VIP
Local time
Today, 13:12
Joined
Jan 14, 2017
Messages
18,186
Thanks for the suggestion, i've tried it but it just returns #Error



This is what I tried



Code:
test: DMin("payment run","payment dates">[due date])



both fields are date/time format.



any suggestions please?



Missing ) before >


Sent from my iPhone using Tapatalk
 

DavidCon

Registered User.
Local time
Today, 06:12
Joined
Apr 14, 2011
Messages
23
Thank you that does work now but it's not really what i was looking for as it is finding the min date in the paymentdate table and comparing it against the due date.

The query i'm looking for would look at each of the due dates and then look in the paymentrun table to find the closest future dated date. Is this even possible?

i.e.

TblPaymentDates

20/05/2018
29/05/2018
17/06/2018

Due dates:

01/05/2018 should return 20/05/2018
21/05/2018 should return 29/05/2018
30/05/2018 should return 17/06/2018

regards
 

static

Registered User.
Local time
Today, 13:12
Joined
Nov 2, 2015
Messages
823
Code:
select a.duedate,
	(select top 1 paymentdate from tblpaymentdates where paymentdate > a.duedate order by paymentdate asc) as paymentdate
from tblDueDates a
 

DavidCon

Registered User.
Local time
Today, 06:12
Joined
Apr 14, 2011
Messages
23
Excellent, that did the perfectly. I can't pretend to understand how it works though but thank you both for your input.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:12
Joined
Jan 23, 2006
Messages
15,361
@DavidCon,

Regarding 'I can't pretend to understand how it works though'

Here is a link to info on Database Planning and Design.

The link has a variety of info. I highly recommend the video series on VBA by Steve Bishop.

Good luck with your project.
 

isladogs

MVP / VIP
Local time
Today, 13:12
Joined
Jan 14, 2017
Messages
18,186
Excellent, that did the perfectly. I can't pretend to understand how it works though but thank you both for your input.

Sorry I was away from my computer all day & unable to check the code I had suggested
Static's solution uses a subquery and as you say it works perfectly

The subquery part gets the first record in tblPaymentDates that is later than the due date.
By sorting the dates in ascending order, the lowest date that fits the criteria is selected
 

Users who are viewing this thread

Top Bottom