Date range parameter, force results if no dates match (1 Viewer)

TB11

Member
Local time
Today, 00:01
Joined
Jul 7, 2020
Messages
78
Hi.

I have a query based on 2 tables. Table 1: IDProject; Table2: IDProjectInfo, fkProject, fkTask, TaskDate. Left join on table 1 IDProject.

I want to use a date range parameter based on a form with user providing the start and end dates: Between [Forms]![f_Basic]![StartDate] And [Forms]![f_Basic]![EndDate]

Wish: if no date range match table 1 IDProject, then return 0

Any thoughts?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:01
Joined
Oct 29, 2018
Messages
21,322
Can you post your SQL statement?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 28, 2001
Messages
26,946
Well, without seeing the SQL, I can say that it is more likely than not that if nothing matches, you would get back NOTHING, not zero. HOWEVER, I am going to make a wild guess that you might wish to look up the DCount function as a way to decide if you were about to get back nothing. Without knowing a little more about what you are trying to do, it would be hard to say more.
 

TB11

Member
Local time
Today, 00:01
Joined
Jul 7, 2020
Messages
78
@theDBguy

SELECT Project.IDProject, ProjectInfo.TaskDate
FROM Project LEFT JOIN ProjectInfo ON Project.IDProject = ProjectInfo.fkProject
WHERE (((ProjectInfo.TaskDate) Between [Forms]![f_Basic]![StartDate] And [Forms]![f_Basic]![EndDate]));
 

TB11

Member
Local time
Today, 00:01
Joined
Jul 7, 2020
Messages
78
@The_Doc_Man I know there are times there will be no results. I want to be able to get something back, like with a nz function. That's where I'm stuck.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 28, 2001
Messages
26,946
The question is, how is this running? Let me explain how I mean this question.

You say you get your dates from a form. OK, should be possible and quite simple to manage. But then you want to do something with a query. What are you doing? Populating a sub-form? Performing an update? Changing the form's content? How does this query relate to this form?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:01
Joined
Oct 29, 2018
Messages
21,322
@theDBguy

SELECT Project.IDProject, ProjectInfo.TaskDate
FROM Project LEFT JOIN ProjectInfo ON Project.IDProject = ProjectInfo.fkProject
WHERE (((ProjectInfo.TaskDate) Between [Forms]![f_Basic]![StartDate] And [Forms]![f_Basic]![EndDate]));
Thanks. You can't mix dates and numbers in one column. If there's no match, you have to pick a designated date for that. If you use 0, you will get 12/31/1899 back. Since you mentioned it, did you actually try using Nz()?
 

TB11

Member
Local time
Today, 00:01
Joined
Jul 7, 2020
Messages
78
@theDBguy I did try Nz. Just got errors. I can work with the picking a designated date (12/31/1899 would work). How would I do this?
 

plog

Banishment Pending
Local time
Today, 00:01
Joined
May 11, 2011
Messages
11,597
Code:
...WHERE (((ProjectInfo.TaskDate) Between [Forms]![f_Basic]![StartDate] And [Forms]![f_Basic]![EndDate]));...

This effectively undoes your LEFT JOIN and turns it into an INNER JOIN. When you put criteria on a datasource that is in a LEFT JOIN only records that meet the criteria will be returned--ergo only records that have data in the LEFT JOIN datasource can be returned.

I know there are times there will be no results. I want to be able to get something back, like with a nz function. That's where I'm stuck.

You can't create records where there are none. The best you can do is do a DCOUNT() into your query before running it to see how many records there are. If none, then you have your 0 and can do what you want.

For a specific answer we need more context. What are you doing with this data? What did you expect to happen when that 0 was returned--what was the user going to experience?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:01
Joined
Feb 19, 2002
Messages
42,872
Wish: if no date range match table 1 IDProject, then return 0
Queries return a recordset NOT a value.

Your query returns project and date. There is no way to make it return 0.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:01
Joined
Oct 29, 2018
Messages
21,322
@theDBguy I did try Nz. Just got errors. I can work with the picking a designated date (12/31/1899 would work). How would I do this?
Hi. Sorry for the delay. I just got in front of a computer. What do you get with this?
SQL:
SELECT Project.IDProject, Nz(SQ.TaskDate,#1/1/1900#) As DateOfTask
FROM Project 
LEFT JOIN (SELECT ProjectInfo.fkProject, ProjectInfo.TaskDate
    FROM ProjectInfo 
    WHERE ProjectInfo.TaskDate Between [Forms]![f_Basic]![StartDate] And [Forms]![f_Basic]![EndDate]) AS SQ
ON Project.IDProject = SQ.fkProject
(untested)
Hope that helps...
 

Users who are viewing this thread

Top Bottom