How to insert a date filter in a sql query ?

[leifoet]

The query below works (=displays the account totals from the complete tables A and B)

If I replace the bold code below with this 'date filter'
"Sum(RCDetails19.DT_V) WHERE RCDetails19.Date<=Enddate AS TTrDTV, "&_
"Sum(RCDetails19.CT_W) WHERE RCDetails19.Date<=Enddate AS TTrCTW, "&_
I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Sum(RCDetails19.DT_V) WHERE RCDetails19.Date<=Enddate'.

Thanks for tips to solve this.

Enddate=cdate(#2019/01/31#)

Dim Conn, rs1, sql1, rs2, sql2
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("_private/MY.mdb")
   
Set rs1 = Server.CreateObject("ADODB.Recordset")
Set rs2 = Server.CreateObject("ADODB.Recordset")

SQL1="SELECT Date, JBAccNR, Number, BInVKP, BUitAKP, ledenid, DT_V, CT_W  FROM TableA "&_
"UNION ALL SELECT Date, JBAccNR, Number, BInVKP, BUitAKP, ledenid, DT_V, CT_W FROM TableB AS RCDetails19 ORDER BY Date ASC"

SQL2="SELECT ACC18.AccID, ACC18.AccDate, ACC18.AccNR, ACC18.AccNM, ACC18.varAccNr, "&_
[b]"Sum(RCDetails19.DT_V) AS TTrDTV, "&_
"Sum(RCDetails19.CT_W) AS TTrCTW, "&_
[/b]"Sum(RCDetails19.CT_W)-Sum(RCDetails19.DT_V) AS SaldoBVD "&_ 
"FROM ACC18 "&_
"LEFT JOIN RCDetails19 ON ACC18.AccNR=RCDetails19.JBAccNR "&_
"WHERE ACC18.AccNR is not null GROUP BY ACC18.AccID, ACC18.AccDate, ACC18.AccNR, ACC18.AccNM, ACC18.varAccNr ORDER BY ACC18.AccNR ASC"

 

[Andrzejek]

That looks like VBA in Access, if so, you should be asking this question in forum701

Is [blue]that[/blue] what you are trying to do?

SQL2="SELECT ACC18.AccID, ACC18.AccDate, ACC18.AccNR, ACC18.AccNM, ACC18.varAccNr, "&_
"Sum(RCDetails19.DT_V) AS TTrDTV, "&_
"Sum(RCDetails19.CT_W) AS TTrCTW, "&_
"Sum(RCDetails19.CT_W)-Sum(RCDetails19.DT_V) AS SaldoBVD "&_ 
"FROM ACC18 "&_
"LEFT JOIN RCDetails19 ON ACC18.AccNR=RCDetails19.JBAccNR "&_
"WHERE ACC18.AccNR is not null " & _[blue]
" AND RCDetails19.Date <= " & Enddate & _
[/blue]" GROUP BY ACC18.AccID, ACC18.AccDate, ACC18.AccNR, ACC18.AccNM, ACC18.varAccNr " & _
" ORDER BY ACC18.AccNR ASC"

ESIT: Oops, make it once

---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

2x " AND RCDetails19.Date <= " & Enddate & _ ?

 

[Andrzejek]

OK, is that what you are trying to do with just one of these statements?


---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

Thanks Andrzejek for the working query SQL2.

However, the module (with this modified query) now crashes (immediately) after the query, with the following error :
ADODB.Recordset error '800a0bcd'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

This is the code that follows the query

… ORDER BY ACC18.AccNR ASC"

rs2.Open sql2, Conn

tot_DTV=0
tot_CTW=0
RekNr=0

rs2.moveFirst               'the error refers to this code line
Do While Not rs2.EOF
%>

    <%if RekNr = rs2("AccNR") then
      else%> 
      <tr><td align="right">
      ...

(The intention is to SUM (from tables A+B) only the records with fields like (ACC18.AccNR=RCDetails19.JBAccNR) AND with the correct date field restriction)

Thanks for help.

 

[Andrzejek]

Your sql2 returns no records.
You can see it by:

[blue]Debug.Print sql2[/blue]
rs2.Open sql2, Conn

tot_DTV=0
tot_CTW=0
RekNr=0
[blue]
If Not rs2.EOF Then[/blue]
    rs2.moveFirst    [blue]
End If[/blue]
...

---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

Result of the (complete) code =from 'Debug.' until 'End if'

1. without the time filter " AND RCDetails19.Date <= " &Enddate & _ (in sql2)
=> no errormessges
=> the programmodule displays the account totals from the complete tables A and B (=OK)

2. with " AND RCDetails19.Date <= " &Enddate & _
=> errormessage :
Microsoft VBScript runtime error '800a01b6'
Object doesn't support this property or method: 'Print'

=> if Debug.Print sql2 is 'deleted'
no errormessage but also no account total (=nothing) displayed

Should the problem be searched in the time filter or elsewhere.
Thanks for tips

 

[Andrzejek]

This forum is for SQL Server data base, but your code looks to me like a VBA in Access. Is that correct? Is that a VBA code from Access?


---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

Indeed an access database and asp code.
Sorry - do I have to change the forum?

 

[Andrzejek]

Next time you start a new question, post in the other more appropriate forum. This way, people with right knowledge would be able to help you.


---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

I think there is a problem with the time filter because the combination 1 does not work.

1. Enddate=cdate(#2019/01/31#)
" AND RCDetails19.Date <= " &Enddate & _ (in sql2)
=> no errormessges but also no account total (=nothing) displayed from the tables A and B

2. " AND [RCDetails19.Datum] <= cdate(#2019/01/31#) "&_
=> the programmodule displays the account totals from the tables A and B filtered on 2019/01/31 (=OK)

What could be wrong with the combination of the variable Enddate=cdate(#2019/01/31#) and the time filter " AND RCDetails19.Date <= " &Enddate & _ ?
Thanks for tips.

 

[Andrzejek]

You need to display and see what you have in [blue][tt]sql2[/tt][/blue] in order to know if you have a valid Select statement or not. Either in a MessageBox, of in the Immediate Window, or any other way.

And also we don't know how the [tt]Enddate[/tt] is defined... As String? As Date?

---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

This test

<body>
<%
Enddate=cdate(#31/01/2019#)

response.write(IsDate(#01/31/2019#))
response.write(Isdate(#2019/01/31#))
response.write(Isdate(Enddate))

response.write(Isdate(31/01/2019))
%>
</body>

gives as answers : TrueTrueTrueFalse

Is that a sufficient date check for this queryproblem?

 

[Andrzejek]

I would do:

<body>
<%
response.write(sql2)
%>
</body>

And see if the outcome makes sense and if it is Select that gives you want you need.


---- Andy

There is a great need for a sarcasm font.