DosDedosMisAmigos
asked on
Access SQL Between Query does not work
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
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
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
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
Try
SELECT count (*) AS RangeCount
FROM Ttable
WHERE iDate between #Forms!RangeQueryForm!Date
#Forms!RangeQueryForm!Date
SELECT count (*) AS RangeCount
FROM Ttable
WHERE iDate between #Forms!RangeQueryForm!Date
#Forms!RangeQueryForm!Date
ASKER
That produces a Run-time error 3075
Syntax error in date in query expresion
Syntax error in date in query expresion
ASKER
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.
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.
ASKER
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
.
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
.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
DosDedosMisAmigos,
Short Date is a display format, not a data type. Getting things into the date data type was the key.
Patrick
Short Date is a display format, not a data type. Getting things into the date data type was the key.
Patrick
ASKER
Thanks to both of you, it works now!
ASKER