Access 2007 Date Query Glitch

[RFabert]

Hello,

I am using Access 2007 for a database. The query that I'm using works in MS SQL Server but I only have Access 2007 to use for this project. There is a glitch in Access 2007 that causes it fail to return all the necessary records.

Is there someone who can help with this problem? I am unable to upload the database so wonder if there is someone who will work with me via email.

Thank you!

 

[pr2-eugin]

Hello, Welcome to AWF..
Would help if we have a bit more information on the problem before actually having to look into your DB..
What is the SQL query you have in place? What is the 'glitch' you are having?

 

[RFabert]

Thank you for your reply.

As I said above. I feel that I need to share the actual DB in order to receive help with this question and am unable to upload it. If you are willing to help via email, please let me know.

Thanks again!

 

[pr2-eugin]

Look at this thread..

 

[RFabert]

Here is the problem:

Each start and end date on the Serials table represents the range of newspapers included on a single roll of microfilm.
One of the problems that arose with testing was that a search of Start year 1914 and End year 1915 (we’d like to be able to search the DB by year and also specific month day and year) failed to yield, record 439, which has a start year of 1913 and an end year of 1917. I also tried a search of 1914-1916 in case the year function was causing it to overlook a single year search but it also failed to bring up record 439.

Another record that fails the search is record 446 with start year 1911 and end year 1917.

 

[pr2-eugin]

Hmmm.. Well your Query Criteria is wrong.. Because you are using AND.. That means if you enter 1913 as start and 1914 as end then it is looking for a Journal that is between 1913 AND 1914.. I know what you are actually looking for.. Return the Journal that has the Start year as 1913 OR end date as 1914 however this will not return you the result you desire as you have many Journals that has a Start date >1913.. The only way to avoid is to actually use = but then your search functionality might not be flexible.. You might wanna look into redesigning your Query with VBA.. This link might be a good start for creating 'Query On The Fly'..

 

[RFabert]

Yes, I need to return the actual roll of microfilm that contains the newspapers between the dates requested in the search and I need to be able to search by year only. Maybe someone else has an idea of how to do it? VBA is a bit beyond my skill level right now and I'm not sure I have time to learn it.

 

[RFabert]

I've been looking through the SQL Cookbook by Anthony Molinaro and wonder if the following is relevant. It uses a self-join. If so, how can I apply it to my project?

Identifying Overlapping date Ranges:

Select *
from emp_project a,
emp_project b

where a.empno = b.empno
and b.proj_start >= a.proj_start
and b.proj_start <= a.proj_end
and a.proj_id = b.proj_id

 

[RFabert]

Since I've not received replies that are immediately applicable to my project, can you recommend other forums?

 

[John Big Booty]

I believe the logic in your query is faulty.


Have a look at the adjustments I've made to your query, it should now return the results you are looking for.

 

[RFabert]

Hello Mr. Booty,

Thank you for your efforts!

You just knocked my socks off. Yes, this does work for the examples given, however, when tested further yielded the following problem:

When I did a search for the years 1929-1935, it returned zero results when the following records should have been returned:

440 1927-1930
441 1931-1936
445 1928-1932
586 1930-1931
427 1932-1934
428 1934-1936

Can you see a reason why this might have happened?

 

[nanscombe]

In SerialsSearchQuery did you really want ..

Year([Start_Date])   <=[Forms]![SearchForm]![FormStart]
Year([End_Date])   >=[Forms]![SearchForm]![FormEnd]

Or did you actually want

Year([Start_Date])   >=[Forms]![SearchForm]![FormStart]
Year([End_Date])   <=[Forms]![SearchForm]![FormEnd]

 

[RFabert]

Hi Nigel,

The solution you have suggested didn't work before so that John, above, suggested switching the signs around. This actually did resolve the problem as presented but didn't meet the entire range of needs for the search query.

Maybe some combination of the two will resolve the entire range?

Thanks for your reply,
Rhonda

 

[nanscombe]

Using your database from post 5 ...

Here is the problem:

Each start and end date on the Serials table represents the range of newspapers included on a single roll of microfilm.
One of the problems that arose with testing was that a search of Start year 1914 and End year 1915 (we’d like to be able to search the DB by year and also specific month day and year) failed to yield, record 439, which has a start year of 1913 and an end year of 1917. I also tried a search of 1914-1916 in case the year function was causing it to overlook a single year search but it also failed to bring up record 439.

Record with Serial_ID 439 has an end date of 29/10/1920 so it won't be before 1915, 1916 or 1917.

Another record that fails the search is record 446 with start year 1911 and end year 1917.

Serial_ID 446 does appear as Record_No = i13901114


Possibly time for a tea break?

 

[RFabert]

Hi Nigel,

I'm not sure how this helps to find the solution? The formula doesn't work with the signs in either direction, from what I can see and from where others have seen, as well. If you have a suggestion for a solution that works for the entire range as posted before, please share it.

When I did a search for the years 1929-1935, it returned zero results when the following records should have been returned:

440 1927-1930
441 1931-1936
445 1928-1932
586 1930-1931
427 1932-1934
428 1934-1936

 

[nanscombe]

But 1927 is not after the form start year of 1929? And 1930 if after the form end year?

Eh?

Are you actually saying ...

The film actually exists during the period defined by the form start year and end year?

The film start year is before (<=) the form end year
AND
The film end year is after (>=) the form start year

Rather than
The film start year is after (>=) the form start year
AND
The film end year is before (<=) the form end year

 

[nanscombe]

Try this?

 

[RFabert]

Hi Nigel,

That seems to work with both tests. I'm thrilled. Thank you!

 

[nanscombe]

A similar old logic problem I had to solve came to mind.

 

[RFabert]

You know, it seemed like the type of problem that might have been seen before. Thanks again!