Solved Date filter on report (1 Viewer)

[nashaz]

Hi

I have spent half a day looking at various solutions with no luck. I have a report based off a query which shows expired training courses for each employee. Everything works fine until I try to filter the report with date ranges. Mainly, I have tried working with the following 2 ways:

*Solved. Cannot delete thread for some reason*

Apologies

 

[The_Doc_Man]

First, not a problem. You can leave threads. But...

Second, if you solve a problem, we would LIKE it if you posted back and told us how you solved it, so that someone else can benefit from your efforts later. This is not REQUIRED of you. It is just something that we would LIKE done so that other readers can learn from your efforts.

 

[Pat Hartman]

Welcome to the forum. Rather than delete the question, why not post your solution. That helps everyone

 

[nashaz]

Thank you, both. You are right. However, at this point I am not sure myself what I was doing wrong. I was trying to use following two ways:

1. Method #2 here http://allenbrowne.com/casu-08.html
2. Using docmd.openreport and passing date requirements

With the first option, the code was reading the dates input from the unbound form but was not applying the filter to the report. I decided to one last time type every line myself and make sure I was making no errors, and viola it worked!

With the second option, I was struggling with syntax, predominantly single and double quotations. Perhaps some afterthought, if possible, from experienced people like you on these. Specially the syntax for second option, for my and other people's future reference?

Regards

 

[Pat Hartman]

It is difficult for ME sometimes to get the syntax typed correctly for a string that contains other strings. The standard suggestion is to build the string into a variable because that makes displaying the string after it is built easier and once you see the string printed, you can usually see your syntax error. The other thing I do is in all my apps I create a module that includes application wide constants and common code. One of the constants is named QUOTE and it allows me to easily embed a double quote character without blinding my self trying to count the instances of double quote characters to make sure the count is correct in each place.

Option Compare Database
Option Explicit


Public Const QUOTE = """"

So, the code then becomes something like:

Dim strWHERE as String

strWHERE = "CustomerName Like " & QUOTE & Me.CustomerName & "*" & QUOTE & " AND Status = 'Active'"

debug.Print strWHERE
DoCmd.OpenReport "ReportName", acViewPreview, , strWHERE

You can comment out the debug.Print once you see that the syntax is working.

 

[nashaz]

It is difficult for ME sometimes to get the syntax typed correctly for a string that contains other strings. The standard suggestion is to build the string into a variable because that makes displaying the string after it is built easier and once you see the string printed, you can usually see your syntax error. The other thing I do is in all my apps I create a module that includes application wide constants and common code. One of the constants is named QUOTE and it allows me to easily embed a double quote character without blinding my self trying to count the instances of double quote characters to make sure the count is correct in each place.

Option Compare Database
Option Explicit


Public Const QUOTE = """"

So, the code then becomes something like:

Dim strWHERE as String

strWHERE = "CustomerName Like " & QUOTE & Me.CustomerName & "*" & QUOTE & " AND Status = 'Active'"

debug.Print strWHERE
DoCmd.OpenReport "ReportName", acViewPreview, , strWHERE

You can comment out the debug.Print once you see that the syntax is working.

Much appreciated, Pat. Will bookmark this thread for future reference. I specially like the idea about the Quotes. It drives me crazy sometimes!

 

[Josef P.]

I like to recommend using helper functions for these string concatenations, then you don't have to worry about formatting/masking in your daily work.

e. g.:

strWHERE = "CustomerName Like " & TextToSqlText(Me.CustomerName & "*") & " AND Status = 'Active'"

.. also for numeric, date or boolean values.

I use a class for several conditions, which does the job for me.

With New FilterStringBuilder
   .ConfigSqlFormat SqlDateFormat:="\#yyyy-mm-dd\#", _
                    SqlBooleanTrueString:="True", _
                    SqlWildCardString:="*"

   .Add "CustomerName", SQL_Text, SQL_Like + SQL_Add_WildCardSuffix, Me.CustomerName.Value
   .Add "Status", SQL_Text, SQL_Equal, "Active"

   strWhere = .ToString

End With

=> (CustomerName Like '.....*') And (Status = 'Active')

or for ADODB/T-SQL: only set new ConfigSqlFormat

With New FilterStringBuilder
   .ConfigSqlFormat SqlDateFormat:="'yyyymmdd'", _
                    SqlBooleanTrueString:="1", _
                    SqlWildCardString:="%"

   .Add "CustomerName", SQL_Text, SQL_Like + SQL_Add_WildCardSuffix, Me.CustomerName.Value
   .Add "Status", SQL_Text, SQL_Equal, "Active"

   strWhere = .ToString

End With

=> (CustomerName Like '.....%') And (Status = 'Active')

 

[Pat Hartman]

You never want to use single quote delimiters for name fields. Names can contain single quotes and if you run into one, the expression will fail with an error.

 

[Josef P.]

That is not an argument. If there is a double quote in the string, it would not work with these as a delimiter (if use it wrong).
You just have to do it right, and that's what you do with a help function.
Note: You never want to use double quote delimiters in T-SQL for strings.

Extract from SqlTools.cls:

Public Function TextToSqlText(ByVal Value As Variant, _
                     Optional ByVal Delimiter As String = "'") As String

   Dim Result As String

   If IsNull(Value) Then
      TextToSqlText = ResultTextIfNull
      Exit Function
   End If

   Result = Replace$(Value, Delimiter, Delimiter & Delimiter)
   Result = Delimiter & Result & Delimiter

   TextToSqlText = Result

End Function

This approach is especially important when processing user input and not wanting to have SQL injection.
e. g.:

strWHERE = "CustomerName Like " & QUOTE & Me.CustomerName & "*" & QUOTE & " AND Status = 'Active'"

+ CustomerName with value: abc" or 1=1 or "x"="