Solved

Ms Access, Linked SQL, Date Filter

Posted on 2004-09-28
21
801 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
  • 8
  • 5
  • 2
  • +4
21 Comments
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:bslattery
Comment Utility
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
Comment Utility
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
Comment Utility
Oh, it also lets you use BETWEEN in the GUI:

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

Author Comment

by:bslattery
Comment Utility
Hi jd,

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

Expert Comment

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

Expert Comment

by:jdlambert1
Comment Utility
Which database brand/version is the back-end?
0
 

Author Comment

by:bslattery
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:bslattery
Comment Utility
SQL2000 SP3a
0
 
LVL 15

Expert Comment

by:jdlambert1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Is there time values in your dates?
0
 
LVL 4

Expert Comment

by:willcode4coffee
Comment Utility
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
Comment Utility
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
Comment Utility
Couldn't you just add the time value, and do a passthrough?
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now