Solved

Ms Access, Linked SQL, Date Filter

Posted on 2004-09-28
21
832 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
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…

763 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