Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-18-2018, 12:30 AM   #1
DavidCon
Newly Registered User
 
Join Date: Apr 2011
Posts: 19
Thanks: 4
Thanked 0 Times in 0 Posts
DavidCon is on a distinguished road
Date Query

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

DavidCon is offline   Reply With Quote
Old 05-18-2018, 12:36 AM   #2
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,131
Thanks: 83
Thanked 1,508 Times in 1,408 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Date Query

Try something like
Code:
DMin("paymentRun","YourTableName")>[date due]
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Old 05-18-2018, 12:57 AM   #3
DavidCon
Newly Registered User
 
Join Date: Apr 2011
Posts: 19
Thanks: 4
Thanked 0 Times in 0 Posts
DavidCon is on a distinguished road
Re: Date Query

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?

DavidCon is offline   Reply With Quote
Old 05-18-2018, 01:43 AM   #4
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,131
Thanks: 83
Thanked 1,508 Times in 1,408 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Date Query

As you have spaces you need to enclose items in [] brackets
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
DavidCon (05-18-2018)
Old 05-18-2018, 02:08 AM   #5
DavidCon
Newly Registered User
 
Join Date: Apr 2011
Posts: 19
Thanks: 4
Thanked 0 Times in 0 Posts
DavidCon is on a distinguished road
Re: Date Query

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?
DavidCon is offline   Reply With Quote
Old 05-18-2018, 02:17 AM   #6
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,131
Thanks: 83
Thanked 1,508 Times in 1,408 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Date Query

Quote:
Originally Posted by DavidCon View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
DavidCon (05-18-2018)
Old 05-18-2018, 02:31 AM   #7
DavidCon
Newly Registered User
 
Join Date: Apr 2011
Posts: 19
Thanks: 4
Thanked 0 Times in 0 Posts
DavidCon is on a distinguished road
Re: Date Query

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

DavidCon is offline   Reply With Quote
Old 05-18-2018, 03:00 AM   #8
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 192 Times in 179 Posts
static will become famous soon enough static will become famous soon enough
Re: Date Query

Code:
select a.duedate,
	(select top 1 paymentdate from tblpaymentdates where paymentdate > a.duedate order by paymentdate asc) as paymentdate
from tblDueDates a
static is offline   Reply With Quote
The Following User Says Thank You to static For This Useful Post:
DavidCon (05-18-2018)
Old 05-18-2018, 03:13 AM   #9
DavidCon
Newly Registered User
 
Join Date: Apr 2011
Posts: 19
Thanks: 4
Thanked 0 Times in 0 Posts
DavidCon is on a distinguished road
Re: Date Query

Excellent, that did the perfectly. I can't pretend to understand how it works though but thank you both for your input.
DavidCon is offline   Reply With Quote
Old 05-18-2018, 03:34 AM   #10
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,531
Thanks: 53
Thanked 1,869 Times in 1,819 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Date Query

@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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 05-18-2018, 04:31 AM   #11
DavidCon
Newly Registered User
 
Join Date: Apr 2011
Posts: 19
Thanks: 4
Thanked 0 Times in 0 Posts
DavidCon is on a distinguished road
Re: Date Query

Thank you for this, very useful.
DavidCon is offline   Reply With Quote
Old 05-18-2018, 09:18 AM   #12
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,131
Thanks: 83
Thanked 1,508 Times in 1,408 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Date Query

Quote:
Originally Posted by DavidCon View Post
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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to format date values in query criteria to search for short date MilaK Queries 16 04-19-2017 12:01 AM
Query Table1 date to Table2 column1 begin date range to column2 end date range shardez Queries 10 03-10-2014 12:14 PM
Query criteria Date() not accurate for fields set as General Date (date/time) isaacski Queries 3 05-24-2013 06:51 PM
need help making a query(s) to categorize time between ship date and repair date weeblebiker Queries 1 04-04-2013 10:05 AM
Calculating a review date based on a date field and a non date field in a query Sweetnuff38 Queries 4 10-15-2010 03:49 PM




All times are GMT -8. The time now is 07:16 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World