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,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: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,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
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    20th October 2019 - 07:01 AM