I think I found what my real problem is but have no idea of how to solve
it. I had this all working before for years but hadn't done any updates
for a long time. In the query that is the data source of the report I had
some calculations which I had to change. To handle increased complexity I
decided to use the Switch function which worked great in the criteria.
However, in the report when I use one of these calculated fields with the
Sum function I get the "Data type mismatch in criteria expression." Any
ideas of why this is the case or what I can do for a work around?
Thanks.
Brian
There is a bad closing bracket in the expression:
... AND ([Date Amount Spent] ) < #01/12/2008#)
If the Immediate Window returns nothing when there's nothing in the
boxes, then there will be no filter applied when you OpenReport. If no
filter is applied all records will be returned. Therefore something
else must be interferring (e.g. some criteria still in the report's
query.)
Allen, I did explain before what is not working. I'll copy the text
here so it's upfront.
This is what was happening when I was using MY code.
If I select an expense type and leave the Date fields blank in the
dialog box everything works fine. Same goes for every combination
except for when
I leave everything blank. In this case I end up getting an error
message that reads:
"Data type mismatch in criteria expression."
When I leave all fields blank in the dialog box the strWhere field is
equal to the following:
([Date Amount Spent] >= #07/07/1993#) AND ([Date Amount Spent]) <
#01/12/2008#)
I've now changed my code to what you provided below. Now the only way
I can get any data returned is if I actually enter dates. If I don't
enter dates I still get the error message above. "Data type mismatch
in criteria expression." Why am I getting this error when I enter no
criteria?
Also, nothing shows up in the Immediate Window unless I put in dates.
When I put in dates I get the correct records returned.
Basically the puzzling/annoying aspect is why am I getting the "Data
type mismatch in criteria expression." error message when I do not
enter dates?
Thanks.
Brian
Can you explain 'not working'?
Does it error?
Does it return the wrong records? No records?
There is no need to lookup the min/max date values.
The procedure just ignores any boxes that are not used.
Have striped it down (omitted comments) as follows.
If it still does not work, remove the single quote from this line:
'Debug.Print strWhere
After running it open the Immediate Window (Ctrl+G), and look at what
came out. This has to be the same as the WHERE clause in a query.
Private Sub cmdPrintPreview_Click()
On Error GoTo Err_cmdPrintPreview_Click
Dim strWhere As String
Dim lngLen As Long
Dim DocName As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Me.DateFrom > Me.DateTo Then
MsgBox "The From date should be the same or later than the To
date.", vbOKCancel, "Print--Expenses"
Me.DateFrom.SetFocus
Else
If Not IsNull(Me.ExpenseType) Then
strWhere = strWhere & "([Purchase] = """ & _
Me.ExpenseType & """) AND "
End If
If Not IsNull(Me.DateFrom) Then
strWhere = strWhere & "([Date Amount Spent] >= " & _
Format(Me.DateFrom, conJetDate) & ") AND "
End If
If Not IsNull(Me.DateTo) Then 'Less than the next day.
strWhere = strWhere & "([Date Amount Spent] < " & _
Format(Me.DateTo + 1, conJetDate) & ") AND "
End If
Me.Visible = False
lngLen = Len(strWhere) - 5
DocName = "Expenses"
If lngLen <= 0 Then
DoCmd.OpenReport DocName, acViewPreview
Else
strWhere = Left$(strWhere, lngLen)
'Debug.Print strWhere
DoCmd.OpenReport DocName, acViewPreview, , strWhere
End If
End If
Exit_cmdPrintPreview_Click:
Exit Sub
Err_cmdPrintPreview_Click:
MsgBox Error$
Resume Exit_cmdPrintPreview_Click
End Sub
Allen, I've been studying your code for two weeks now and continue
to run into problems. I think I understand what you are doing but
I'm obviously screwing up somewhere.
Here's the code I'm using including what you provided. Some of the
MsgBoxes are for testing/verification purposes only.
"Expenses" is the name of a table.
Private Sub cmdPrintPreview_Click()
On Error GoTo Err_cmdPrintPreview_Click
'********************************************************
'Added 3/2/2008
'Purpose: Build up the criteria string form the non-blank
search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so
you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.
'********************************************************
'Added on 3/17/2008
If Me.DateFrom > Me.DateTo Then
MsgBox "The From date should be the same or later than the To
date.", vbOKCancel, "Print--Expenses"
DoCmd.GoToControl "DateFrom"
DoCmd.CancelEvent
GoTo bigdog
End If
'********************************************************
'********************************************************
'Added 3 / 1 / 2008
If IsNull(Me.DateFrom) Then
Me.DateFrom = DMin("[Date Amount Spent]", "Expenses")
MsgBox Me.DateFrom, vbOKOnly, "Test" 'to be removed later
End If
If IsNull(Me.DateTo) Then
Me.DateTo = DMax("[Date Amount Spent]", "Expenses")
MsgBox Me.DateTo, vbOKOnly, "Test" 'to be removed later
End If
'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.ExpenseType) Then
strWhere = strWhere & "([Purchase] = """ & Me.ExpenseType &
""") AND "
End If
'Date field example. Use the format string to add the #
delimiters and get the right international format.
If Not IsNull(Me.DateFrom) Then
strWhere = strWhere & "([Date Amount Spent] >= " &
Format(Me.DateFrom, conJetDate) & ") AND "
End If
'Another date field example. Use "less than the next day" since
this field has times as well as dates.
If Not IsNull(Me.DateTo) Then 'Less than the next day.
strWhere = strWhere & "([Date Amount Spent] < " &
Format(Me.DateTo + 1, conJetDate) & ") AND "
End If
'********************************************************
Me.Visible = False
lngLen = Len(strWhere) - 5
Dim DocName As String
DocName = "Expenses"
If lngLen <= 0 Then
MsgBox lngLen, vbOKOnly, "Test"
MsgBox strWhere, vbOKOnly, "Test"
DoCmd.OpenReport DocName, acViewPreview
Else
strWhere = Left$(strWhere, lngLen)
MsgBox lngLen, vbOKOnly, "Test"
MsgBox strWhere, vbOKOnly, "Test"
DoCmd.OpenReport DocName, acViewPreview, , strWhere
End If
Exit_cmdPrintPreview_Click:
Exit Sub
Err_cmdPrintPreview_Click:
MsgBox Error$
Resume Exit_cmdPrintPreview_Click
bigdog: End Sub
If I select an expense type and leave the Date fields blank in the
dialog box everything works fine. Same goes for every combination
except for when I leave everything blank. In this case I end up
getting an error message that reads:
"Data type mismatch in criteria expression."
When I leave all fields blank in the dialog box the strWhere field
is equal to the following:
([Date Amount Spent] >= #07/07/1993#) AND ([Date Amount Spent]) <
#01/12/2008#)
Any ideas of why this is not working? I know I can force the issue
by requiring dates be entered but I've invested so much time on this
that I want it to work just to say I've conquered the problem!
Thanks.
Brian
Brian, what I prefer to do here is to start with the form where the
user enters the criteria, and then clicks a button to open the
report. In the button's Click event procedure, you build the
WhereCondition for OpenReport so the report opens with the correct
filter. It's quite straightforward to simply omit the controls that
the user left blank so they are not part of the filter.
For an example of how to build such a filter string, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example builds the filter for a form, but it's identical to
building the WhereCondition for OpenReport.
Download the example, pull it apart, and see how it works. It shows
how to work with different field types (Text, Number, Date), and
even with a range of dates. It's certainly a technique worth
learning.
Once you have built the filter string, you can use it as the
Criteria for your DCount() if you wish. I don't usually bother with
that: I just cancel the report's NoData event if there's nothing to
show.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I have a form (dialog box) where you can supply a range of dates
and an expense category in order to customize a report. It's
possible to not supply dates or an expense category in which case I
want all expense records to be printed in the report. I want to use
the DCount function to determine if the selections made will result
in 0 records. The problem I having is how to handle the situations
where either no dates and/or no category is selected.
Right now here is what I have.
DCount("*","[Expenses]", "[Date Amount Spent] Between Forms![Print
Expenses Dialog]![DateFrom] and Forms![Print Expenses
Dialog]![DateTo] And Forms![Print Expenses Dialog]![ExpenseType]")
What do I need to do to handle the cases where dates and/or
categories are not selected? In other words tell the DCount
function to select all records
