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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Capturing Date Into A Variable, Access 2016    
 
   
3dzer0
post Aug 9 2019, 09:40 AM
Post#1



Posts: 24
Joined: 7-February 19



I am working on a database for projects and tasks. A Single Project can have multiple tasks and due dates. I am trying to capture the next due date after today's date to find out what the next deadline will be.

For some reason Dates have always been the bane of my existence, I don't know if I over think it or can't quite grasp the data format.

I have a field in a table called "Due_Date" It is formatted as Short Date - mm/dd/yyyy in a table called Tasks. The table also has a field named "Projects".

CODE
NextDate = DLookup("#" & [Due_Date] & "#", "Tasks", "Project = " & vProject)

This works and returns the first task Due_Date for a particular project. (Returns 7/8/2018 as expected) When I step through the code and mouse over each portion it shows a value.

CODE
NextDate = DLookup("#" & [Due_Date] & "#", "Tasks", "Project = " & vProject And "#" & [Due_Date] & "#" >= "#" & Date & "#")

This returns an error "Run-time error '13': Type mismatch". (Should return 9/16/2019) When I step through the code and mouse over each portion it shows a value.

So thinking that maybe it is the Date field that is causing the mismatch I tried:
CODE
NextDate = DLookup("#" & [Due_Date] & "#", "Tasks", "Project = " & vProject And "#" & [Due_Date] & "#" >= Date)

This returns an error "Run-time error '13': Type mismatch". (Should return 9/16/2019) When I step through the code and mouse over each portion it shows a value.

And from there I have tried every possible combination I can come up with, including not using the Octothorp which then leads to the same "error 13"


Thanks Glen
Go to the top of the page
 
 
Start new topic
Replies
nuclear_nick
post Aug 9 2019, 09:50 AM
Post#2



Posts: 1,782
Joined: 5-February 06
From: Ohio, USA


I think I see some incorrect syntax?
CODE
NextDate = DLookup("#" & [Due_Date] & "#", "Tasks", "Project = " & vProject And "#" & [Due_Date] & "#" >= "#" & Date & "#")

… should be...
CODE
NextDate = DLookup("#" & [Due_Date] & "#", "Tasks", "Project = " & vProject " And #" & [Due_Date] & "# >= #" & Date & "#")


--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
3dzer0
post Aug 9 2019, 09:58 AM
Post#3



Posts: 24
Joined: 7-February 19



QUOTE
… should be...
CODE
NextDate = DLookup("#" & [Due_Date] & "#", "Tasks", "Project = " & vProject " And #" & [Due_Date] & "# >= #" & Date & "#")



I tried this and it gives me: "compile error expected: list separator or )"
Go to the top of the page
 
nuclear_nick
post Aug 9 2019, 10:01 AM
Post#4



Posts: 1,782
Joined: 5-February 06
From: Ohio, USA


That would be my fault... dropped an '&'... try it this way...
CODE
NextDate = DLookup("#" & [Due_Date] & "#", "Tasks", "Project = " & vProject & " And #" & [Due_Date] & "# >= #" & Date & "#")

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
3dzer0
post Aug 9 2019, 10:06 AM
Post#5



Posts: 24
Joined: 7-February 19



QUOTE
That would be my fault... dropped an '&'... try it this way...

CODE
NextDate = DLookup("#" & [Due_Date] & "#", "Tasks", "Project = " & vProject & " And #" & [Due_Date] & "# >= #" & Date & "#")


That worked with no errors at all, but it returned a "Null" for "NextDate"

Now there are tasks that do not have a due_date but I would think those records would be bypassed ... ??
Go to the top of the page
 
nuclear_nick
post Aug 9 2019, 10:12 AM
Post#6



Posts: 1,782
Joined: 5-February 06
From: Ohio, USA


Why do you think it would be bypassed?

'Null' does not mean 'Skip me'... it means 'I can't get the value of this, it's non-existant.' That distinction trips up a lot of people. There are ways of handling null values.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
3dzer0
post Aug 9 2019, 10:19 AM
Post#7



Posts: 24
Joined: 7-February 19



QUOTE
Why do you think it would be bypassed?

'Null' does not mean 'Skip me'... it means 'I can't get the value of this, it's non-existent.' That distinction trips up a lot of people. There are ways of handling null values.


So as it is looking for the date greater than today, and it hits a null value it stops ?

Go to the top of the page
 
nuclear_nick
post Aug 9 2019, 10:34 AM
Post#8



Posts: 1,782
Joined: 5-February 06
From: Ohio, USA


Not stops.

Breaks, most likely because you've set 'NextDate' to be a date, and when it's not, it breaks. You should be able to check it in the immediate window, using values in place of the variables...
CODE
?DLookup([Due_Date], "Tasks", "Project = 7 And Due_Date >= #8/9/2019#")

… or whatever, just as long you know the correct answer beforehand so you know if it's correct or not.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
RJD
post Aug 9 2019, 11:07 AM
Post#9


UtterAccess VIP
Posts: 10,077
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but why not use DMin instead of DLookup? ...

DMin("[Due_Date]","[Tasks]","[Project]=" & [Project] & " And Not IsNull([Due_Date]) And [Due_Date]>Date()") AS NextDueDate

(Assumes Project is Numeric)

DMin gives you the next Due_Date that is not Null and is after today's date. You can change that to >= Date() if that better represents your intent.

See the demo attached. See if this fits with what you are trying to do.

HTH
Joe
Attached File(s)
Attached File  NextDueDate.zip ( 20.87K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
3dzer0
post Aug 9 2019, 11:51 AM
Post#10



Posts: 24
Joined: 7-February 19



QUOTE
Hi: PMFJI, but why not use DMin instead of DLookup? ...

DMin("[Due_Date]","[Tasks]","[Project]=" & [Project] & " And Not IsNull([Due_Date]) And [Due_Date]>Date()") AS NextDueDate

(Assumes Project is Numeric)

DMin gives you the next Due_Date that is not Null and is after today's date. You can change that to >= Date() if that better represents your intent.

See the demo attached. See if this fits with what you are trying to do.


Thank you so much .. I will most certainly check it out. I'm away from work now but first thing Monday morning I will try the code and check out the demo.

Go to the top of the page
 
RJD
post Aug 9 2019, 11:55 AM
Post#11


UtterAccess VIP
Posts: 10,077
Joined: 25-October 10
From: Gulf South USA


You are welcome. After you check it out, let us know if this meets your requirements, or if you need more assistance.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
3dzer0
post Aug 9 2019, 12:01 PM
Post#12



Posts: 24
Joined: 7-February 19



I most certainly will Joe ... !!
Go to the top of the page
 
RJD
post Aug 9 2019, 12:03 PM
Post#13


UtterAccess VIP
Posts: 10,077
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    14th October 2019 - 05:33 AM