We help IT Professionals succeed at work.

Access SQL Between Query does not work

210 Views
Last Modified: 2013-11-28
Hello

I have a database and I have a form with a Calendar control that users click to populate 2 text boxes. The form's control source is set to a query with the following code

SELECT count (*) AS RangeCount
FROM Ttable
WHERE iDate between Forms!RangeQueryForm!Date1 And
Forms!RangeQueryForm!Date2;

The table has records with dates from Nov 1 thru Nov 12, if I run the query on [for example] NOv 2 thru Nov 12, it returns the count total for Nov 2 and Nov 12 only and skips the dates in between


Can someone tell me what I am doing wrong?

Thank you
Comment
Watch Question

Author

Commented:
Also, I forgot to add that the form has a 3rd text box bound to RangeCount that displays the count

Commented:
Try

SELECT count (*) AS RangeCount
FROM Ttable
WHERE iDate between #Forms!RangeQueryForm!Date1# And
#Forms!RangeQueryForm!Date2#;

Author

Commented:
That produces a Run-time error 3075

Syntax error in date in query expresion

Author

Commented:
Even if I bypass the form and write it as

SELECT count (*) AS RangeCount
FROM Ttable
WHERE iDate between [date1] And [date2]

It still only counts the records for Date1 and Date2 and excludes the records between the 2 dates.

Author

Commented:
Also, if I choose continuous dates, it works with no problem

So, if there are records in the db for each date from Nov 2 - Nov 6, it returns the correct count but when I choose a date range that includes dates that have no matching records in the db, it returns only the first and last date count, as long as records exist with those 2 dates

.

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I was able to switch the format of IDate to Date/Time and it works now, which is awesome! It's been driving me crazy and it was so easy, I just overlooked the field data type setting.

I did not see an option for Short Date, I am in Access 2007, is that possibly an option in Access 2003 but not 2007?

Thanks
CERTIFIED EXPERT
Top Expert 2010

Commented:
DosDedosMisAmigos,

Short Date is a display format, not a data type.  Getting things into the date data type was the key.

Patrick

Author

Commented:
Thanks to both of you, it works now!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.