Filtering Form using Unbound Date fields

Question

I have a "CheckBookReg" table that I am trying to use "From" and "To" date fields to filter the data. I've written the following VB procedure, but keep getting an error code "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." Where did I go wrong???

Public Function ToDate_AfterUpdate(FormName As Form) As String
On Error GoTo Err_ToDate_AfterUpdate

Dim strQuery As String

If IsNull([Forms]![CheckRegister]![FromDate]) Then  
  
    strQuery = "SELECT CheckBookregister.\*, CheckBookregister.TransDate, CheckBookregister.[Credit]-[Debit] AS Amount," &\_

                       "IIf([IsCleared],[Amount],0) AS ClearedAmt" & \_  
                       "FROM CheckBookregister " & \_  
                       "WHERE [CheckBookregister].[TransDate] Between [Forms]![CheckRegister]![FromDate] And [Forms]![CheckRegister]![ToDate])));"  
Else  

    strQuery = "SELECT CheckBookregister.\*, CheckBookregister.TransDate, CheckBookregister.[Credit]-[Debit] AS Amount,"

                       "IIf([IsCleared],[Amount],0) AS ClearedAmt" & \_  
                       "FROM CheckBookregister;"  
End If  

[Forms]![CheckRegister].RecordSource = strQuery  

' FormName![CheckRegister].Form.RecordSource = strQuery

Exit_ToDate_AfterUpdate:
Exit Function

Err_ToDate_AfterUpdate:
MsgBox Err.Description
Resume Exit_ToDate_AfterUpdate
End Function

Answer 1

It is not clear how you use or call this function, but you could simplify the process a bit like this:

Public Function ToDate_AfterUpdate(FormName As String) As String

Dim Form        As Access.Formdim strQuery    As StringDim strFilter   As StringSet Form = Forms(FormName)' (Re)set record source.strQuery = _"SELECT *, [Credit] - [Debit] AS Amount, " & _"IIf([IsCleared], [Credit] - [Debit], 0) AS ClearedAmt " & _"FROM CheckBookregister;"If Form.RecordSource <> strQuery ThenForm.RecordSource = strQueryEnd IfIf IsNull(Form!FromDate.Value + Form!ToDate.Value) Then' Ignore filter.Form.FilterOn = FalseElse' Set filter.strFilter = "TransDate Between #" & Format(Form!FromDate.Value, "yyyy/mm/dd") & "# And #" & Format(Form!ToDate.Value, "yyyy/mm/dd") & "#"Form.Filter = strFilterForm.FilterOn = TrueEnd If

    Set Form = NothingEnd Function

Answer 2

You can also restrict the rows returned in a form or report by making its RecordSource a query which references one or more parameters, in this case references to unbound controls in the form.  The following explains how.  The final section (italicized below) describes how to use date parameters to define a range. To implement the restrictions requery the form with:

Me.Requery

either in the Click event procedure of a separate button, or in the AfterUpdate event procedures of each parameter control to restrict the results progressively.

The basis for restricting a query on multiple parameters where the parameters might be used singly or in combination, allowing one or more to be Null, is that in the WHERE clause each parameter is tested in this way:

WHERE (SomeColumn = [some parameter]

  OR [some parameter] IS NULL)

AND (SomeOtherColumn = [some other parameter]

  OR [some other parameter] IS NULL)

AND etc

The following can of course be used:

    (SomeColumn LIKE "*" & [some parameter] & "*"

      OR [some parameter] IS NULL)

where it is appropriate to use pattern matching rather than testing for equality.  Bear in mind, however, that even when applied to a single column this can result in specious mismatches.  Moreover, the LIKE operator does not allow use of the indexes, so can reduce performance significantly.  In most cases it is better to reference a combo box in an unbound dialogue form, or in a bound form's header, as the parameter.  The user can then select a value from a fixed set of known values, or by entering the first few characters in the combo box, progressively go to the first match by virtue of the control's AutoExpand property.  The following query is an example:

SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region,

Country, Employer, LastName, FirstName, Contacts.ContactID

FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID)

INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts

ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers

ON Contacts.ContactID = ContactEmployers.ContactID)

ON Employers.EmployerID = ContactEmployers.EmployerID)

ON Regions.RegionID = Cities.RegionID

WHERE (Cities.CityID = Forms!frmReportDialogue!cboCity

    OR Forms!frmReportDialogue!cboCity IS NULL)

AND (Employers.EmployerID = Forms!frmReportDialogue!cboEmployer

    OR Forms!frmReportDialogue!cboEmployer IS NULL);

This example is taken from the section on 'Retrieving data from the database' in my DatabaseBasics demo file, which can be found in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

The logic behind this approach is extremely simple and consequently, given good indexing in the table, very efficient.  It also has the advantage of not having to bother about the data type of the column in question, so unlike when building an SQL statement in code, consideration does not need to be given as to whether the values need delimiting or not. 

Each OR operation is enclosed in parentheses to force it to evaluate independently.  These parenthesized expressions are then tacked together with AND operations.  The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter or, if the parameter is left empty (NULL), for every row.  By virtue of the AND operations  the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

Note that when you do this, parameters should only be declared in a PARAMETERS clause if they are of DateTime data type.  If other types were declared they could never be Null.  DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.

When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view.  This applies to the initial saving of the query, and if any subsequent amendments are made.  If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open.  It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.

Note BTW that if searching on the basis of a date range this can be made a closed range or open ended in either direction by treating the start and end date parameters independently, rather than within a BETWEEN….AND operation:

WHERE (DateColumn >= [start date parameter]

OR [start date parameter] IS NULL)

AND (DateColumn < [end date parameter]+1

OR [end date parameter] IS NULL)

This also allows for date/time values in the table where the time of day element is not zero.  Any rows with such values would not be returned by a BETWEEN....AND operation if the value is on the final day of the range.

Answer 3

Rather than change the RecordSource, why not just apply a Filter for the form?

I.E.

strCriteria = "Transdate BETWEEN #" & Me.FromDate & "# AND # " & Me.ToDate & "#"

Me.Filter = strCriteria

DoCmd.RunCommand acCmdApplyFilterSort

Answer 4

Edited the original post to show the SQL better. The SQLs work if I just use them raw. The problem is in these statements. I must be referencing it wrong thus getting the error code

For Statement 1. - "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

For Statement 2. - "Microsoft Access can't find the field 'CheckRegister' referred to in your expression

1. [Forms]![CheckRegister].RecordSource = strQuery
2. FormName![CheckRegister].Form.RecordSource = strQuery

Answer 5

Hmm, where is the rest of the this IIf statement?

IIf([IsCleared],[Amount],0) AS ClearedAmt"

And why are there 3 closing parenthesis in the first part? You need to fix that first part. up. Write it in a query first and then translate to VBA.