Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How te filter by date ?

Status
Not open for further replies.

leifoet

Technical User
Jan 31, 2016
203
BE

Code:
SQL="SELECT Datum, Number, Action, BInVKP, BUitAKP FROM journal WHERE Datum between 'DD-MM-YYYY' and 'DD-MM-YYYY'" &_
"UNION ALL SELECT Datum, Number, Action, BInVKP, BUitAKP, FROM bnkbk WHERE Datum between ''DD-MM-YYYY' and 'DD-MM-YYYY'' AS RCDet ORDER BY Datum ASC"

Filtering the date field between 01-01-YYYY and any date in the year is not possible.
The website database is Access - field : short date as DD/MM/YYYY (starting from year beginning 01-01)
I also tried for example date<=30/04/YYYY or date<=YYYY/04/30... without success.

Where am I wrong?
Thanks for tips
 
Access database dates use # as the delimiter and values should be entered as mm/dd/yyyy or yyyy/mm/dd not dd/mm/yyyy. There are other acceptable formats.

I think the Access Queries forum would more appropriate for this question since I don’t see where SQL Server is involved.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
dhoocom : I applied your recommendations => the results below.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Datum between #01/01/2023# and #09/30/2023# AS RCDet'.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Datum between #1/1/2023# and #9/30/2023# AS RCDet'.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Datum between #2023/1/1# and #2023/9/30# AS RCDet'.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Datum between #2023/01/01# and #2023/09/30# AS RCDet'

WHERE Datum<=#2023/09/30# gives the same error message as above.

Can I test other options?
Thanks for tips.

Also this:
Indeed, there is no SQL Server involved - but Windows MS SQL
Which forum section should I contact?
 
I need "RCDet" further in the queries.
Just for the sake of completeness: the entire query has been running well for about three years.
the only disadvantage that I would like to finally solve is the insertion of a date filter (so the code only after 'WHERE ...)
I hope a solution comes from somewhere...
In any case, thanks for the tips.
 
Your code is also missing a space before UNION.

Maybe you should provide more complete information since Access might not support what you are attempting.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
This is an SQL Server Programing forum, but how about:

[pre]
Select Datum AS RCDet, Number, Action, BInVKP, BUitAKP From
(SELECT Datum, Number, Action, BInVKP, BUitAKP FROM journal
UNION ALL
SELECT Datum, Number, Action, BInVKP, BUitAKP FROM bnkbk)
WHERE RCDet between #01/01/2023# and #05/05/2023#
ORDER BY RCDet ASC
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Be careful, if there is a time component of the date field you may not get all of the records for the ending date.
Found this on another websie.
keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'
To get around this i use the following for the second date, @CheckDate2 is a parameter for the second date.
Code:
dateadd(day, 1, @CheckDate2)'

Auguy
Sylvania/Toledo Ohio
 
No wonder your SQL did not work :-(

Code:
SQL="SELECT Datum, Number, Action, BInVKP, BUitAKP FROM journal WHERE Datum between 'DD-MM-YYYY' and 'DD-MM-YYYY'" &_
"UNION ALL SELECT Datum, Number, Action, BInVKP, BUitAKP[highlight #FCE94F],[/highlight] FROM bnkbk WHERE Datum between [highlight #8AE234]'[/highlight]'DD-MM-YYYY' and 'DD-MM-YYYY'[highlight #8AE234]'[/highlight][highlight #FCE94F] AS RCDet[/highlight] ORDER BY Datum ASC"

An extra comma before FROM, around 'dates' (?), and an Alias in WHERE part
[tt]WHERE Datum between 'DD-MM-YYYY' and 'DD-MM-YYYY'[/tt]
that will not work, there are no 'Dates' in this part. 'DD-MM-YYYY' is not a date, it is a format of a date.
SQL expects something like:
[tt]WHERE Datum between '01-01-2020' and '02/02/2022'[/tt]
as long as you can convert Text '01-01-2020' to a Date.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Andy, good catch on the extra comma. This is why I always use Debug.Print SQL so I can troubleshoot any issues with the statement.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top