[X]My Assistant

[3dzer0]

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

[theDBguy]

Hi Glen,

Welcome to UtterAccess!


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

[3dzer0]

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

[3dzer0]

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

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

[theDBguy]

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