×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How te filter by date ?

How te filter by date ?

How te filter by date ?

(OP)

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

RE: How te filter by date ?

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

RE: How te filter by date ?

(OP)
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?

RE: How te filter by date ?

Why the “As RCDet”? Try remove it.

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

RE: How te filter by date ?

(OP)
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.

RE: How te filter by date ?

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

RE: How te filter by date ?

This is an SQL Server Programing forum, but how about:

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
 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: How te filter by date ?

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

RE: How te filter by date ?

No wonder your SQL did not work sad

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" 

An extra comma before FROM, around 'dates' (?), and an Alias in WHERE part
WHERE Datum between 'DD-MM-YYYY' and 'DD-MM-YYYY'
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:
WHERE Datum between '01-01-2020' and '02/02/2022'
as long as you can convert Text '01-01-2020' to a Date.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: How te filter by date ?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close