Between dates query

[bobo962]

I did read JonK's post. The solution is what I am using now. I need a way of dealing with a enddate field that is blank (null). As a novice user, if there is something I'm missing, please explain.

Why you need blank enddate field anyway? I am new in VBA also, but I don't think it is possible to find the records (through query expression) between one fixed (startdate) and one blank (enddate) field...Most likely you will need a way (function) to check for null values and to combine query and function...
Sorry but I am not good enough in VBA to help you...

 

[boblarson]

The criteria could be:

=IIf(IsNull([Forms]![RPT_NAIC_AF1].[EndDate]),>=[Forms]![RPT_NAIC_AF1]![BeginDate],"Between #" & [Forms]![RPT_NAIC_AF1]![BeginDate] & "# And #" & [Forms]![RPT_NAIC_AF1].[EndDate] & "#")

 

[tbarkdull]

Query by Date

RPT_NAIC_AF1 is another query called from this query. I removed the [Forms]! references. I get an error that it is too complex. Ideally, I'd like to use today's date in place of a blank enddate. Thanks in advance for your help

 

[rainman89]

ok so set the default of the enddate to =Date()
or in the onclick of the button
if isnull(enddate)
enddate=Date()
else
endif

 

[boblarson]

The => without an end date would generate the same thing as using today's date. I was assuming you would be using a form to select the dates. Your way of referencing the other query will not work like you think.

 

[tbarkdull]

Query Between Dates

The query as is returns data with two entries missing. These are records of Officers of a company with their Titles and beginning and ending dates. The last BeginDate is 12/6/2006 with a blank EndDate because he is still an Officer. The two records missing are dated 12/29/2006 and 1/16/2007. If I change the "And" to "Or" it will return those two records, but other data is not correct. If I enter a date, today or after, the records show correctly.

 

[Riverburn]

I hope its no problem that Im bumping this thread, but I believe I know what the problem is with Access, why the BETWEEN doesn't work.

take my table for example

(UK format)

Assignment Hours Date Employee

1. 100110 | 4 | 28/04/2010 | 218
2. 011001 | 6 | 24/04/2010 | 243
3. 101111 | 3 | 22/04/2010 | 218
4. 001101 | 4 | 04/04/2010 | 218
5. 100110 | 4 | 25/04/2010 | 243

when you perform a SELECT on Date for dates BETWEEN 19/04 and 25/04 you'd expect to see the dates 24/04, 22/04 and 25/04. However, access sees it differently: He looks between the 19th and the 25th not on Date level, but on the table level, i.e. access will use whatever in the table is between the dates you've given. in this case 04/04 (4) is sitting between 24/04 (2) and 25/04 (5) so 04/04 (2<4<5) is selected as well. I've tried ordering the table on date. but 1) that's a really fragile way to solve it and 2) access knows betters and he automatically orders it by Primary key when you query it.

So, I had the same problem but thanks to the query given in this post I've solved it. but it is always good to know why things go wrong

 

[robina]

I realize this is an old post but I am working in access 2010, but any answers that work for 2007 would be great. I have a simple query using one table and I am using the BETWEEN AND criteria. It just returns random dates no matter what. Here is the SQL:

SELECT IIf([Join_tbl]![TaskAssignee] Is Null,[Join_tbl]![Administrator],[Join_tbl]![TaskAssignee]) AS Assignee, Join_tbl.ReqNum, Join_tbl.Status, Join_tbl.Approved, Join_tbl.TotalSLADays, Join_tbl.Region, Join_tbl.EmpID, Join_tbl.Description, Join_tbl.Category, Join_tbl.Vendor, Join_tbl.VP_Decision
FROM Join_tbl
WHERE (((Join_tbl.Approved) Between [Start Date] And [End Date]) AND ((Join_tbl.Administrator) Is Null) AND ((Join_tbl.TaskAssignee) Is Not Null)) OR (((Join_tbl.Administrator) Is Not Null) AND ((Join_tbl.TaskAssignee) Is Null));

To simplify it, in the Approved field I have as the criteria.