Filter not to be happen (1 Viewer)

Reshmi mohankumar

Registered User.
Local time
Today, 22:50
Joined
Dec 5, 2017
Messages
101
hi,
i wish to filter date column where checkbox is false, if checkbox is true the filter should not be happen (or) all dates to be select .
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 13:20
Joined
Apr 27, 2015
Messages
6,285
A little more info if you please...are filtering a form, report or a table?
 

Reshmi mohankumar

Registered User.
Local time
Today, 22:50
Joined
Dec 5, 2017
Messages
101
A little more info if you please...are filtering a form, report or a table?
yes, im filtering a query which collecting data from main table.
date is column , which some times no need to filter and sometimes to be filter in a range depending upon a checkbox selection.


i already using below
DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , "[pending]=true and [trandate]= between '" & fromdate & "' and '" & todate & "'", acDialog


is it possible??
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 13:20
Joined
Apr 27, 2015
Messages
6,285
Absolutely! The WHERE condition on your OpenReport statement is essentially your query.

If you want to use a query instead, design the query and then use it as the record source for your report

OR

Use this:
Code:
DoCmd.OpenReport "rpt_pend_issue", acViewPreview, "qryYourQuery", , , acDialog
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:20
Joined
Sep 21, 2011
Messages
14,044
yes, im filtering a query which collecting data from main table.
date is column , which some times no need to filter and sometimes to be filter in a range depending upon a checkbox selection.


i already using below
DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , "[pending]=true and [trandate]= between '" & fromdate & "' and '" & todate & "'", acDialog


is it possible??

You will need # enclosing your dates.?
I'd construct a string strWhere so you can debug.print it to check the syntax then use that as the Where parameter in your OpenReport command.

https://www.access-programmers.co.uk/forums/showthread.php?t=227204

HTH
 

Dreamweaver

Well-known member
Local time
Today, 17:20
Joined
Nov 28, 2005
Messages
2,466
use this for your dates Format(Your Date Here, "\#mm/dd/yyyy\#")
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 13:20
Joined
Apr 27, 2015
Messages
6,285
Guess I misinterpreted what the OP was asking...wouldn't be the first time!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:20
Joined
Feb 28, 2001
Messages
26,999
Reshmi mohankumar

Absolutely, there are MANY ways to achieve that result. The problem is, TOO many ways. I'm going to walk through what I think you are asking for and explain as I go.

You appear to be opening a report. From your question, you are doing so from a form that has a checkbox on it. It is not 100% clear to me what that checkbox is named but if it is pending then you are using it incorrectly in the filter. I'll show you the simplest and most straight-forward way.

The simplest way is to choose to design your report without filtration. That is, you presume that your checkbox is TRUE when you build the report.

Then in your code that launches the report,

Code:
IF Me.pending Then
    DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , , acDialog
Else
    DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , "[trandate]= between '" & fromdate & "' and '" & todate & "'", acDialog
End If

That is, if the checkbox is true then don't apply the filter. If the checkbox is false, apply the filter. Note, however, that I am NOT addressing the date format in the sample code here. I'll explain that separately.

When dealing with the dates as VBA variables you can compare dates to dates easily enough. SQL can also directly compare dates that are fields in a recordset. However, if your range dates are controls on a form, then they are inherently in a text format and SQL will want them in a date format. Date STRINGS are usually bracketed by the # sign, which alerts SQL that the string bracketed that way needs conversion before use. For one possible example,

Code:
DoCmd.OpenReport "rpt_pend_issue", acViewPreview, , "[trandate]= between '#" & fromdate & "#' and '#" & todate & "#'", acDialog

Note that if you need more than this, you have to ask a bit more explicitly where you need this to go. I took a simplistic view.
 

Users who are viewing this thread

Top Bottom