Using a form to supply DCount Criteria

B

Brian Smith

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.

Thanks.

Brian
 
A

Arvin Meyer [MVP]

Use a branch in your code:

If Len(DateTo & vbNullString) = 0 Then
' Do one thing
ElseIf Len(ExpenseType & vbNullString) = 0 Then
' Do something else
Else
' Run your DCount code
End If
 
A

Allen Browne

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.
 
B

Brian Smith

I've actually done that concerning the date fields by using DMin and DMax to
find the first and last dates. What I'm totally stumped on is what to do if
a category is not selected. How do I indicate that

Forms![Print Expenses Dialog]![Expenses]=* (where * indicates to include all
expense types)?

I'm assuming this is possible but I've searched high and low for examples
and so far no luck.

Thanks.

Brian

Arvin Meyer said:
Use a branch in your code:

If Len(DateTo & vbNullString) = 0 Then
' Do one thing
ElseIf Len(ExpenseType & vbNullString) = 0 Then
' Do something else
Else
' Run your DCount code
End If
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Brian Smith said:
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.

Thanks.

Brian
 
T

Tom Wickerath

Hi Brian,
I'm assuming this is possible but I've searched high and low for examples
and so far no luck.

Perhaps this sample will be helpful for you:

http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Brian Smith said:
I've actually done that concerning the date fields by using DMin and DMax to
find the first and last dates. What I'm totally stumped on is what to do if
a category is not selected. How do I indicate that

Forms![Print Expenses Dialog]![Expenses]=* (where * indicates to include all
expense types)?

I'm assuming this is possible but I've searched high and low for examples
and so far no luck.

Thanks.

Brian
 
B

Brian Smith

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

Allen Browne said:
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.

Brian Smith said:
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.
 
A

Allen Browne

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 Browne - Microsoft MVP. Perth, Western Australia

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

Brian Smith said:
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

Allen Browne said:
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.

Brian Smith said:
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.
 
B

Brian Smith

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

Allen Browne said:
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 Browne - Microsoft MVP. Perth, Western Australia

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

Brian Smith said:
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

Allen Browne said:
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.
 
A

Allen Browne

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 Browne - Microsoft MVP. Perth, Western Australia

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

Brian Smith said:
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

Allen Browne said:
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

Brian Smith said:
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.
 
B

Brian Smith

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

Allen Browne said:
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 Browne - Microsoft MVP. Perth, Western Australia

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

Brian Smith said:
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

Allen Browne said:
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.
 
A

Allen Browne

That's not enough for me to pin down anything specific for you Brian.

The message typically means that Access is trying to match one data type
against a value of a different type, but the specifics could be varied.

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

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

Brian Smith said:
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

Allen Browne said:
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.)

Brian Smith said:
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
 
B

Brian Smith

Not sure why this is happening but using the Switch Function seems to
convert the data type to Text which is why I can't do any calculations.

Given the above, I want to try something else to get around the problem and
build in some flexibility. I have a table that keeps track of expenses but I
need to keep track of the GST (goods and services tax in Canada) paid
separately. So essentially I want to record the amount paid before taxes,
the amount of taxes and the total amount paid. I figure setting up a GST
Rates table would provide a lot of flexibility but can't figure out how to
get the DLookup function to work when you have a rate that can last for
years but need to match it up with dates within those years. Hopefully that
makes sense.

The GSTRates table would have two fields: DateEffective and GSTRate. The
records would be

01/01/1990 7.0%
07/01/2006 6.0%
01/01/2008 5.0%

If I have a table Expenses with fields [TransactionDate], [Amount Paid
Before Taxes] and [Total Paid] (along with other fields) how would I use the
DLookup function in a query to calculate what the GSTRate should be for a
given transaction? Unless I'm reading the help file incorrectly this
function does not work the same in Access as in Excel. I'm using Access 2003
if that matters.

Thanks.

Brian

Allen Browne said:
That's not enough for me to pin down anything specific for you Brian.

The message typically means that Access is trying to match one data type
against a value of a different type, but the specifics could be varied.

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

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

Brian Smith said:
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

Allen Browne said:
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
 
A

Allen Browne

You may be able to explicitly typecast the results of your Switch(), so
Access knows the data type. Use CLng() around the expression to get a Long
Integer, CCur() for Currency, CDbl() for Double, etc - whatever type you
need. These conversion functions don't handle null, so use Nz() inside,
e.g.:
CLng(Nz(Switch( ... ),0))

DLookup() is somewhat different than VLookup() in Excel, and one of it's
limits is that you can't specify the ordering of the records. If
DateEffective is the primary key, you may be able to use an expression such
as:
=DLookup("GSTRate", "GSTRates", "[InvoiceDate] >= " &
Format(Nz([DateEffective], #1/1/1900#), "\#mm\/dd\/yyyy\#"))

Alternatively, you could use the ELookup() function here:
http://allenbrowne.com/ser-42.html
This one does let you specify the sort order, so you would use:
=DLookup("GSTRate", "GSTRates", "[InvoiceDate] >= " &
Format(Nz([DateEffective], #1/1/1900#), "\#mm\/dd\/yyyy\#"), "DateEffective)

If there are many records, Tom Ellision has a much more efficient
alternative:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Brian Smith said:
Not sure why this is happening but using the Switch Function seems to
convert the data type to Text which is why I can't do any calculations.

Given the above, I want to try something else to get around the problem
and build in some flexibility. I have a table that keeps track of expenses
but I need to keep track of the GST (goods and services tax in Canada)
paid separately. So essentially I want to record the amount paid before
taxes, the amount of taxes and the total amount paid. I figure setting up
a GST Rates table would provide a lot of flexibility but can't figure out
how to get the DLookup function to work when you have a rate that can last
for years but need to match it up with dates within those years. Hopefully
that makes sense.

The GSTRates table would have two fields: DateEffective and GSTRate. The
records would be

01/01/1990 7.0%
07/01/2006 6.0%
01/01/2008 5.0%

If I have a table Expenses with fields [TransactionDate], [Amount Paid
Before Taxes] and [Total Paid] (along with other fields) how would I use
the DLookup function in a query to calculate what the GSTRate should be
for a given transaction? Unless I'm reading the help file incorrectly this
function does not work the same in Access as in Excel. I'm using Access
2003 if that matters.

Thanks.

Brian

Allen Browne said:
That's not enough for me to pin down anything specific for you Brian.

The message typically means that Access is trying to match one data type
against a value of a different type, but the specifics could be varied.

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

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

Brian Smith said:
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
 
M

Mark

Allen,
I was taught some years ago to avoid the use of some functions, DLookUp
being one. I was hoping that ELookUp would be an alternative without the
restrictions. When I attempt to use this function I get a compile error, “Sub
or function to defined.†I have MS DAO 3.6 in my references. I suspect it’s a
library issue but can’t define which one I should have. Can libraries
conflict and cancel out one another?
I’m using Access 2002 mdb.
Thanks,
M


Allen Browne said:
You may be able to explicitly typecast the results of your Switch(), so
Access knows the data type. Use CLng() around the expression to get a Long
Integer, CCur() for Currency, CDbl() for Double, etc - whatever type you
need. These conversion functions don't handle null, so use Nz() inside,
e.g.:
CLng(Nz(Switch( ... ),0))

DLookup() is somewhat different than VLookup() in Excel, and one of it's
limits is that you can't specify the ordering of the records. If
DateEffective is the primary key, you may be able to use an expression such
as:
=DLookup("GSTRate", "GSTRates", "[InvoiceDate] >= " &
Format(Nz([DateEffective], #1/1/1900#), "\#mm\/dd\/yyyy\#"))

Alternatively, you could use the ELookup() function here:
http://allenbrowne.com/ser-42.html
This one does let you specify the sort order, so you would use:
=DLookup("GSTRate", "GSTRates", "[InvoiceDate] >= " &
Format(Nz([DateEffective], #1/1/1900#), "\#mm\/dd\/yyyy\#"), "DateEffective)

If there are many records, Tom Ellision has a much more efficient
alternative:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia

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

Brian Smith said:
Not sure why this is happening but using the Switch Function seems to
convert the data type to Text which is why I can't do any calculations.

Given the above, I want to try something else to get around the problem
and build in some flexibility. I have a table that keeps track of expenses
but I need to keep track of the GST (goods and services tax in Canada)
paid separately. So essentially I want to record the amount paid before
taxes, the amount of taxes and the total amount paid. I figure setting up
a GST Rates table would provide a lot of flexibility but can't figure out
how to get the DLookup function to work when you have a rate that can last
for years but need to match it up with dates within those years. Hopefully
that makes sense.

The GSTRates table would have two fields: DateEffective and GSTRate. The
records would be

01/01/1990 7.0%
07/01/2006 6.0%
01/01/2008 5.0%

If I have a table Expenses with fields [TransactionDate], [Amount Paid
Before Taxes] and [Total Paid] (along with other fields) how would I use
the DLookup function in a query to calculate what the GSTRate should be
for a given transaction? Unless I'm reading the help file incorrectly this
function does not work the same in Access as in Excel. I'm using Access
2003 if that matters.

Thanks.

Brian

Allen Browne said:
That's not enough for me to pin down anything specific for you Brian.

The message typically means that Access is trying to match one data type
against a value of a different type, but the specifics could be varied.

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

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

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"
 
T

Tom Wickerath

Hi Mark,

One should avoid using domain aggregrate functions in queries, as this can
lead to really slow performance. My experience suggests that using these
functions on forms and reports is generally not so bad. You should index any
fields used in the optional criteria statement for a domain aggregrate
function.
When I attempt to use this function I get a compile error, “Sub or function to defined.â€

Did you paste the ELookup function into a new stand-alone module (ie. not
into a module associated with a form or report)? Also, did you give the
module a unique name (not ELookup)? Which line of code is flagged by the
compiler? Please show exactly how you are using it.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
M

Mark

I have the code in a stand alone module as follows:

Public Function CallELookup()
Dim FCN_Number As Variant
FCN_Number = ELookup("[Bates]", "tbl-all-new-BIT-fees-numerical", "[fcn]
= 373817")
MsgBox FCN_Number
End Function

It stops on ELookup with “sub or function not definedâ€.

Thanks,
M
 
D

Douglas J. Steele

Did you remember to copy the ELookup function from Allen's site into your
application? http://www.allenbrowne.com/ser-42.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mark said:
I have the code in a stand alone module as follows:

Public Function CallELookup()
Dim FCN_Number As Variant
FCN_Number = ELookup("[Bates]", "tbl-all-new-BIT-fees-numerical",
"[fcn]
= 373817")
MsgBox FCN_Number
End Function

It stops on ELookup with "sub or function not defined".

Thanks,
M


Tom Wickerath said:
Hi Mark,

One should avoid using domain aggregrate functions in queries, as this
can
lead to really slow performance. My experience suggests that using these
functions on forms and reports is generally not so bad. You should index
any
fields used in the optional criteria statement for a domain aggregrate
function.


Did you paste the ELookup function into a new stand-alone module (ie. not
into a module associated with a form or report)? Also, did you give the
module a unique name (not ELookup)? Which line of code is flagged by the
compiler? Please show exactly how you are using it.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
T

Tom Wickerath

Hi Mark,

As Doug suggests, did you copy the ELookup function from Allen's web page
and paste it into your application? Position your mouse pointer so that it is
blinking anywhere within the name "ELookup" in your code. Then press
<Shift><F2>. Focus should move to the ELookup function, if it is present in a
location where Access can find it.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have the code in a stand alone module as follows:

Public Function CallELookup()
Dim FCN_Number As Variant
FCN_Number = ELookup("[Bates]", "tbl-all-new-BIT-fees-numerical", "[fcn]
= 373817")
MsgBox FCN_Number
End Function

It stops on ELookup with “sub or function not definedâ€.

Thanks,
M
 
D

Douglas J. Steele

Will Access find it even if he mistakenly named the module ELookup as well?
I wouldn't expect the editor to be able to find it if Access can't find it
at execution time. It might be necessary to use Find, looking all through
the database.
 
T

Tom Wickerath

Hi Doug,
Will Access find it even if he mistakenly named the module ELookup as well?

No. In that case, the error message as shown in Access 2002 (which I'm using
at the moment) would be:

Compile error:
Expected variable or procedure, not module

If one positions their mouse cursor within the call to the ELookup function,
and hits <shift><F2>, and one had forgotten to import this function into the
database, the resulting error message (again in A2002) is:

Identifier under cursor is not recognized

You'd get the same errors if the ELookup function was mistakenly added to a
form or report class module, instead of a stand-alone module.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top