How te filter by date ?

[leifoet]

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

 

[dhookom]

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

 

[leifoet]

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?

 

[dhookom]

Why the “As RCDet”? Try remove it.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[leifoet]

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.

 

[dhookom]

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

 

[Andrzejek]

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

 

[Auguy]

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.

dateadd(day, 1, @CheckDate2)'

Auguy
Sylvania/Toledo Ohio

 

[Andrzejek]

No wonder your SQL did not work :-(

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

 

[dhookom]

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