Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Ms Access, Linked SQL, Date Filter

Posted on 2004-09-28
21
Medium Priority
?
850 Views
Last Modified: 2008-01-09
Hello Experts,

I have an Access database with linked SQL tables.  I have created a Select query based on one table which has a field called Transaction Date with a datatype of smalldatetime.  When I enter criteria for the field (i.e., >=#1/1/2004# AND <=#6/1/2004#), the query returns records outside the date range requested.  I have seen this before, but cannot remember the cure.

Assistance Sincerely Appreciated!!!

Respectfully,
bslattery
0
Comment
Question by:bslattery
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +4
21 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12171569
If you're in the graphical interface, you have to put the two dates on different lines
>=#1/1/2004#
<=#6/1/2004#

If you're in SQL, you have to use the column name twice:
DateColumn >=#1/1/2004# AND DateColumn<=#6/1/2004#),
0
 
LVL 77

Expert Comment

by:peter57r
ID: 12171590
Hello bslattery,

Your criteria asks for records with dates between 1 Jan 2004 and 1 Jun 2004 inclusive.

Is this what you meant?

Pete
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12171671
Oops, I was wrong about needing the dates on different lines if you're using the GUI, that's if you want OR instead of AND.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:bslattery
ID: 12171684
Hi jdlambert1,

In the graphical interface of the Acces query, placing the dates on separate lines provides an OR operation which is not what I was looking for.  I tried it and as expected it failed.  Thanks anyway.


peter57r,
You are correct, I am looking for dates that are inclusive of the criteria entered in the Access query grid.

Thanks
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12171726
Don't know why it's misbehaving, but since you want inclusive, you could try switching to SQL and using BETWEEN:

WHERE DateColumn BETWEEN #1/1/2004# AND #6/1/2004#

Perhaps the engine will use a different execution plan this way.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12171749
Oh, it also lets you use BETWEEN in the GUI:

Between #1/1/2004# And #6/1/2004#
0
 

Author Comment

by:bslattery
ID: 12171809
Hi jd,

Yeah, tried it, also returns dates outside of the criteria range.  
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12171837
How far outside? Is it like there is no criteria?
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12171875
Which database brand/version is the back-end?
0
 

Author Comment

by:bslattery
ID: 12171896
Also note that while I get records outside the criteria range, it does filter records (total records unfiltered=3000+, records with filter applied=367).  AND, that an Ascending sort does not produce the expected results.  The dates are sorted into the following order:

12/1/2002
10/1/2002
1/1/2002
11/13/2001
10/1/2001
11/1/2001
1/1/2002
3/1/2003

This doesn't make much sense, but those are the results.
0
 

Author Comment

by:bslattery
ID: 12171905
SQL2000 SP3a
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12172025
Random trouble-shooting thoughts... import the table (if it's small enough) and try the same query logic against it; Close & reopen Access; Compact & repair database; drop and recreate the link.
0
 

Author Comment

by:bslattery
ID: 12172659
Finally found the prob.....

The table is originally from a btrieve program using DDFs which had no primary keys.  The table was imported into SQL using a nightly DTS job.  With no primary key, none of the filtering and sorting would work.  I was able to create a compund promary key out of 3 of the fields and now the date filtering works correctly.

Thanks for your assistance.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12172718
Of course, there's a bug in there somewhere, 'cause it should have worked, even if very slowly.

Glad you got it working, and thanks for letting the rest of us know what the deal was!
0
 
LVL 1

Expert Comment

by:acidburn2003
ID: 12173936
I am not sure but i had this problem searching between two numbers.   So, i have done the following thing:

i have replaced the signs <= and >=  with   the BETWEEN method

for example:    

WHERE Transaction Date  BETWEEN 1/1/2004 AND 6/1/2004 . You should try this :)
0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12175750
Is there time values in your dates?
0
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12190275
Is this a date field or a string field? String comparisions work differently than date comparisions and it looks to me like the dates are being stored as strings, not date values. If this is the case you will need to convert the values to a date in your SELECT query before comparing them.

M@
0
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12190296
I see now where you said it was smalldatetime. You may want to try changing it to DateTime and see if it still acts weird.

M@
0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12190796
Couldn't you just add the time value, and do a passthrough?
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12747306
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question