UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help ... My Head Hurts. Finding Next Date With Dlookup, Access 2016    
 
   
3dzer0
post Feb 7 2019, 11:36 AM
Post#1



Posts: 7
Joined: 7-February 19



I hope this is the right place to post this ... but I am at my wits end after 2 days trying to figure out a small piece of code.

I have a Project table that records each project that comes in the office. I have a Tasks table that records each task for a given project with start dates and Due_Dates (Milestones).

What I am trying to do I thought was going to be simple.

When the Project Details form is opened there should be a textbox that will display the next Task Milestone date that is coming up. So I used the Dlookup to look for the next task date that was after the current date, that also matched the Project ID.

Field Project - Tasks TBL - Data Type-Number - Display Control - Combo Box
Field Due_Date - Tasks TBL - Data-Date/Time - Format - Short Date

Dim NextDate As Date or
Dim NextDate As Variant or
Dim NextDate As String

NextDate = DLookup("[Due_Date]", "Tasks", "Project=" & Me!Project) --- This works and returns back the first (Due_Date) for project (3/31/2019 but should be 2/14/2019 which is the next milestone)


NextDate = DLookup("[Due_Date]", "Tasks", "Due_Date >= " & Date & "") Works but wrong value - (1/24/2019 but should be 2/14/2019) returns back the first (Due_Date) for Tasks Table


NextDate = DLookup("[Due_Date]", "Tasks", "Due_Date >= " & Date) Works but wrong value - (1/24/2019 but should be 2/14/2019) returns back the first (Due_Date) for Tasks Table


I have went through each iteration that I could come up with and have had no luck at all


NextDate = DLookup("[Due_Date]", "Tasks", Project = Me!Project And "[Due_Date] >= " & Date & "") ------- Gives me a Data Type mismatch

NextDate = DLookup("[Due_Date]", "Tasks", "Project= & Me!Project And [Due_Date] >= " & Date) ---- Gives me a Syntax error

NextDate = DLookup("[Due_Date]", "Tasks", Project = Me!Project And [Due_Date] >= Date) ------- Gives me a Data Type mismatch



There has got to be something simple I am missing and at the moment I have several knots on my head from banging it against the desk.

Thanks !!!

Glen







Go to the top of the page
 
theDBguy
post Feb 7 2019, 11:47 AM
Post#2


Access Wiki and Forums Moderator
Posts: 74,446
Joined: 19-June 07
From: SunnySandyEggo


Hi Glen,

Welcome to UtterAccess!
welcome2UA.gif

The next task should have a later date than the current due date, correct? If so, you could try something like:

DLookup("Due_Date","Tasks","Project=" & Me.Project & " AND Due_Date>=#" & Me.Due_Date & "#")

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
3dzer0
post Feb 7 2019, 11:59 AM
Post#3



Posts: 7
Joined: 7-February 19



Thank you for the quick reply ...

NextDate = DLookup("Due_Date", "Tasks", "Project=" & Me.Project & " AND Due_Date>=#" & Me.Due_Date & "#")

this works with no errors but returns 3/31/2019 rather than 2/14/2019

Due Dates for my 5 records for this one project are:

ID3 - 3/31/2019
ID4 - 4/11/2019
ID13 - 2/28/2019
ID14 - 2/15/2019
ID15 - 2/14/2019
This post has been edited by 3dzer0: Feb 7 2019, 12:00 PM
Go to the top of the page
 
3dzer0
post Feb 7 2019, 12:13 PM
Post#4



Posts: 7
Joined: 7-February 19



!!!!!!!!!!!!!!!!!!!!!

I figured it out .... And thank you so much for your help !!! You gave me the basis of what I needed.

I used Elookup by Allen Browne and then sorted the records by ascending date ... and now it is pulling the next date after today which is the 14th ..

Thank you Thank you Thank you

NextDate = ELookup("Due_Date", "Tasks", "Project=" & Me.Project & " AND Due_Date>=#" & Me.Due_Date & "#", "Due_Date ASC")
This post has been edited by 3dzer0: Feb 7 2019, 12:14 PM
Go to the top of the page
 
theDBguy
post Feb 7 2019, 12:16 PM
Post#5


Access Wiki and Forums Moderator
Posts: 74,446
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2019 - 02:30 AM