Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 853
  • Last Modified:

Ms Access, Linked SQL, Date Filter

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
bslattery
Asked:
bslattery
  • 8
  • 5
  • 2
  • +4
1 Solution
 
jdlambert1Commented:
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
 
peter57rCommented:
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
 
jdlambert1Commented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
bslatteryAuthor Commented:
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
 
jdlambert1Commented:
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
 
jdlambert1Commented:
Oh, it also lets you use BETWEEN in the GUI:

Between #1/1/2004# And #6/1/2004#
0
 
bslatteryAuthor Commented:
Hi jd,

Yeah, tried it, also returns dates outside of the criteria range.  
0
 
jdlambert1Commented:
How far outside? Is it like there is no criteria?
0
 
jdlambert1Commented:
Which database brand/version is the back-end?
0
 
bslatteryAuthor Commented:
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
 
bslatteryAuthor Commented:
SQL2000 SP3a
0
 
jdlambert1Commented:
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
 
bslatteryAuthor Commented:
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
 
jdlambert1Commented:
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
 
acidburn2003Commented:
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
 
MichaelSFullerCommented:
Is there time values in your dates?
0
 
willcode4coffeeCommented:
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
 
willcode4coffeeCommented:
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
 
MichaelSFullerCommented:
Couldn't you just add the time value, and do a passthrough?
0
 
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 8
  • 5
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now