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

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 12:16 PM