Filter and where clause for OpenReport

  • Thread starter Thread starter R
  • Start date Start date
R

R

Hi,
I want to open a report with it's data filtered only
displayind dates from 6months ago til now.
Also I want to take only those projects whose project name
is choosen from a combo box.

the part of my code looks like this:

strFilter = "SELECT * FROM tblMain " _
& "WHERE [Von] Between DateAdd(m; -6; Now()) And Now()" _
& " ORDER BY [Von] DESC;"

strSQL = "ProjectName Like Forms!frmFilters!ctlProject"

DoCmd.OpenReport stDocName, acViewPreview, strFilter,
strSQL

---- It do opens only for the selected project name, but
shows all dates for that project and not shows only for
the past 6 months. So I assume the Between DateAdd ... is
not working. Any ideas why ?? What I'm missing? ---

thx,
r
 
Specify the order in the Sorting And Grouping dialog of the report.

Combine the project name and date into the WhereCondition of the OpenReport
action:

strFilter = "[ProjectName] = """ & Me.ctlProject & """) AND ([Von] Between
DateAdd(m; -6; Now()) And Now())"
DoCmd.OpenReport stDocName, acViewPreview,, strFilter

Note that this assumes the ProjectName is text. Drop the extra quotes if
it's a numeric field.
 
thank you, but now showing a syntax error first I think
because of the () but fixed them and still the error
message... any other idea?
-----Original Message-----
Specify the order in the Sorting And Grouping dialog of the report.

Combine the project name and date into the WhereCondition of the OpenReport
action:

strFilter = "[ProjectName] = """ & Me.ctlProject & """) AND ([Von] Between
DateAdd(m; -6; Now()) And Now())"
DoCmd.OpenReport stDocName, acViewPreview,, strFilter

Note that this assumes the ProjectName is text. Drop the extra quotes if
it's a numeric field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

R said:
Hi,
I want to open a report with it's data filtered only
displayind dates from 6months ago til now.
Also I want to take only those projects whose project name
is choosen from a combo box.

the part of my code looks like this:

strFilter = "SELECT * FROM tblMain " _
& "WHERE [Von] Between DateAdd(m; -6; Now()) And Now ()" _
& " ORDER BY [Von] DESC;"

strSQL = "ProjectName Like Forms!frmFilters!ctlProject"

DoCmd.OpenReport stDocName, acViewPreview, strFilter,
strSQL

---- It do opens only for the selected project name, but
shows all dates for that project and not shows only for
the past 6 months. So I assume the Between DateAdd ... is
not working. Any ideas why ?? What I'm missing? ---

thx,
r
Click to expand...


.
Click to expand...
 
thx Allen, now working perfect I was just missing double ""
worked with the original statement:

strFilter = "SELECT * FROM tblMain " _
& "WHERE Von Between DateAdd(""m"", -6, Now()) And Now()"
strSQL = "ProjectName Like Forms!frmFilters!ctlProject"


DoCmd.OpenReport stDocName, acViewPreview, strFilter,
strSQL
 
Back
Top