null parameter between two dates in access 2007

Question

I have a query as below :

select column_date, field1, field2, sum(field3) from table1
where field1 like '*xyz' and 
column_date between [please enter start date] and [please enter end date]
group by column_date, field1, field2

When I leave blank both the parameter boxes, the output is blank. But I want them in output as below

I want to display all records when I leave blank both parameter box
if I put date in any one of the parameter boxes, it should display only records for that date
If i put dates in both the parameter boxes, it should display all records between those dates.

This is my asp code to display the output. It works when I insert values in both the text boxes but if I left blanks any of them or both, it shows error.

<html>
<body>
<%
dim startdate, enddate
startdate = Request.Form ("startdate")
enddate = Request.Form("enddate")
set conn = Server.CreateObject ("ADODB.Connection")
conn.open "connectionname"
set rs = Server.CreateObject ("ADODB.Recordset")
Sqlquery = "queryname '" & startdate & "', '" & enddate &'" "
rs.open sql, conn %>
<table>
<tr>
<%
For each x in rs.fields
response.write ("<th>" & x.name & "</th>") 
next %> </tr>
<tr><% Do Until rs.EOF %>
<% For each x in rs.Fields %>
<td>Response.write (x.value)</td>
<%next
rs.movenext %>
</tr>
<% loop
rs.close
conn.close %>
</table>
</body>
</html>

I reckon you should be using a form to get your parameters: office.microsoft.com/en-us/access-help/… — Fionnuala

HansUp HansUp · Accepted Answer · 2012-07-08T23:50:10

I would start with a plain SELECT query to figure out the WHERE clause. Once you get that working correctly, convert it to a GROUP BY query.

So see if this one targets the correct records:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT t.field1, t.field2, t.field3, t.column_date
FROM table1 AS t
WHERE
       t.field1 Like '*xyz'
    AND (         
       (t.column_date Between [Start Date] And [End Date])
    OR ([Start Date] Is Null And t.column_date = [End Date])
    OR (t.column_date = [Start Date] And [End Date] Is Null)
    OR ([Start Date] Is Null And [End Date] Is Null)
       );

Assuming that first query returns the correct rows, I think this GROUP BY query may give you what you want.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT t.column_date, t.field1, t.field2, sum(t.field3)
FROM table1 AS t
WHERE
       t.field1 Like '*xyz'
    AND (         
       (t.column_date Between [Start Date] And [End Date])
    OR ([Start Date] Is Null And t.column_date = [End Date])
    OR (t.column_date = [Start Date] And [End Date] Is Null)
    OR ([Start Date] Is Null And [End Date] Is Null)
       )
GROUP BY t.column_date, t.field1, t.field2;

If running this query from classic ASP, you will need to substitute the ANSI wild card character % for the Access-style * wild card.

t.field1 Like '%xyz'

Or consider using ALike and the ANSI wild card instead of Like. That way the query will always operate the same without needing to switch wild cards.

t.field1 ALike '%xyz'

Also, with classic ASP, run the query from an ADO Command object, and supply values for the parameters.

null parameter between two dates in access 2007

1 Answers