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
> 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
 
PhilS
post Aug 9 2019, 09:49 AM
Post#2



Posts: 626
Joined: 26-May 15
From: The middle of Germany


If Due_Date is of type Date/Time in the table you don't need (and shouldn't!) add the #-signs. You can just assign it to your variable as it is.

--------------------
Go to the top of the page
 
nuclear_nick
post Aug 9 2019, 09:50 AM
Post#3



Posts: 1,789
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:56 AM
Post#4



Posts: 24
Joined: 7-February 19



QUOTE
If Due_Date is of type Date/Time in the table you don't need (and shouldn't!) add the #-signs. You can just assign it to your variable as it is.


I have tried that and either get the same error 13 or a Null value.
This post has been edited by 3dzer0: Aug 9 2019, 09:56 AM
Go to the top of the page
 
3dzer0
post Aug 9 2019, 09:58 AM
Post#5



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#6



Posts: 1,789
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#7



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#8



Posts: 1,789
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#9



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#10



Posts: 1,789
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#11


UtterAccess VIP
Posts: 10,097
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#12



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#13


UtterAccess VIP
Posts: 10,097
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#14



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#15


UtterAccess VIP
Posts: 10,097
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
 
dale.fye
post Aug 9 2019, 12:08 PM
Post#16



Posts: 161
Joined: 28-March 18
From: Virginia


For the next date, I would use:
CODE
NextDate = DMin("[Due_Date]", "Tasks", "(Project = " & vProject & ") And ([Due_Date] > #" & Date() & "#)")

When doing this in VBA, I prefer to use:
CODE
Dim strCriteria as string
strCriteria = "([Project] = " & vProject & ") And ([Due_Date] > #" & Date() & "#)"
Debug.print strCriteria
NextDate = DMin("[Due_Date]", "Tasks", strCriteria)

Which allows me to examine the value of strCriteria before executing the DMIN.

DLOOKUP will return the value of the first record in the recordset which meets your criteria. Since the "Tasks" table is probably not sorted by the [Due_Date] field, then it is unlikely that DLOOKUP will actually return the minimum value for [Due_Date] for the selected project.

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
3dzer0
post Aug 12 2019, 06:44 AM
Post#17



Posts: 24
Joined: 7-February 19



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

CODE
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


Joe this worked great and put me on the right track. I honestly have never heard of Dmin until your post ... I also now know there is a Dmax also.

I had to make some minor changes to the code you provided but I am getting exactly the results I have been looking for. Thank you so much.

CODE
NextDate = DMin("[Due_Date]", "[Tasks]", "[Project]=" & vProject & " And Not IsNull([Due_Date]) And [Due_Date]>Date()")


Your code as written gave me "Compile Error: Statement invalid outside Type Block. " So I just moved the variable to the front with a = sign and it worked immediately.


I see you are on the Gulf Coast ... I live in the Mobile Area ..


Thanks Again,

Glen
This post has been edited by 3dzer0: Aug 12 2019, 06:44 AM
Go to the top of the page
 
RJD
post Aug 12 2019, 09:26 AM
Post#18


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


Hi Glen: You are very welcome. We are all happy to assist.

Yes, I posted the DMax in SQL syntax, giving the field name after the As. I wasn't sure how you were going to use it, so did it that way as in the demo I attached. Typically, I try to do all these things in a query unless it is required to be in a VBA procedure. Glad you got that sorted out for your usage.

Incidentally, I live about 30+ miles west of the Mobile limits, and get to Mobile occasionally.

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
 


Custom Search


RSSSearch   Top   Lo-Fi    21st October 2019 - 10:49 PM