Conditional Criteria for a Date Field in a Query

I

ions

Access User
Local time
Today, 13:55
Joined
May 23, 2004
Messages
875
Dear Query Expert.

I have a query with a date field and I am trying to implement the following logic in the criteria. Date Criteria = CustomerMonthlyWasteReportDate()

Both Functions cause and error in the Criteria when I run the query.

Error Message: DataType Mismatch in the Criteria Expression.

The interesting fact is when I manually put in the results of these functions it works. I used the Variant as the return type.

How do I achieve what I want. Thanks.

Code: 
Public Function CustomerMonthlyWasteReportDate() As Variant
      
      Dim strDate As Variant
      
      If Forms![CustomerMonthlyWasteReport]![ReportType] = 1 Then
            strDate = "Between #" & CDate(Forms![CustomerMonthlyWasteReport]![FromMonth]) & "# And #" & CDate(Forms![CustomerMonthlyWasteReport]![ToMonth]) & "#"
      Else
            strDate = "Between " & Forms![CustomerMonthlyWasteReport]![FromMan] & " and " & Forms![CustomerMonthlyWasteReport]![ToMan]
      End If
      
      CustomerMonthlyWasteReportDate = strDate

End Function

Public Function CustomerMonthlyWasteReportDate2() As Variant
      
      Dim strDate As Variant
      
      If Forms![CustomerMonthlyWasteReport]![ReportType] = 1 Then
            strDate = ">= #" & CDate(Forms![CustomerMonthlyWasteReport]![FromMonth]) & "# And <= #" & CDate(Forms![CustomerMonthlyWasteReport]![ToMonth]) & "#"
      Else
            strDate = "Between " & Forms![CustomerMonthlyWasteReport]![FromMan] & " and " & Forms![CustomerMonthlyWasteReport]![ToMan]
      End If
      
      CustomerMonthlyWasteReportDate2 = strDate

End Function
 
Hi -

What is contained in [FromMonth] and [ToMonth] and what is the data type of the underlying fields?

Same question re Forms![CustomerMonthlyWasteReport]![FromMan] and [ToMan].

Bob
 
THey are all Medium Date Format, Unbound Controls

The functions produce the following results.

CustomerMonthlyWasteReportDate()
returns -> Between #01/06/2008# And #30/06/2008#

CustomerMonthlyWasteReportDate2()
returns -> >= #01/06/2008# And <= #30/06/2008#

When I put both results into the Query... it works.

When I call the function I receive the DataType Mismatch Error.

Thanks.
 
I think that part of your problem is that you are trying to concatenate a date to a string, and I do not think that is allowed. Change the Date to a string using the cstr( function before concatenating and that problem should go away.

NOTE that the example shows changes for one function only. Both of the functions need to be changed in a similar manner for the same reason.

ions said:
Code: 
Public Function CustomerMonthlyWasteReportDate() As Variant
 
Dim strDate As Variant
 
If Forms![CustomerMonthlyWasteReport]![ReportType] = 1 Then
strDate = [COLOR=sandybrown][B]"Between #"[/B][/COLOR] & [B][COLOR=royalblue]cstr([/COLOR][COLOR=yellowgreen]CDate(Forms![CustomerMonthlyWasteReport]![FromMonth])[/COLOR][COLOR=royalblue])[/COLOR][/B] & [COLOR=sandybrown][B]"# And #"[/B][/COLOR] & [COLOR=yellowgreen][B][COLOR=royalblue]cstr([/COLOR]CDate(Forms![CustomerMonthlyWasteReport]![ToMonth])[COLOR=royalblue])[/COLOR][/B][/COLOR] & [COLOR=sandybrown][B]"#"[/B][/COLOR]
Else
[COLOR=sandybrown][B]strDate = "Between " & Forms![CustomerMonthlyWasteReport]![FromMan] & " and " & Forms![CustomerMonthlyWasteReport]![ToMan][/B][/COLOR]
End If
 
CustomerMonthlyWasteReportDate = strDate
 
End Function
Click to expand...
 
Hi MSAccess.

You are right I need to change both... Just testing the first condition at the moment.

Changed CDate to Cstr .... same error.

Thanks
 
strDate = "Between #" & CDate(Forms![CustomerMonthlyWasteReport]![FromMonth]) & "# And #" & CDate(Forms![CustomerMonthlyWasteReport]![ToMonth]) & "#"
Else
strDate = "Between " & Forms![CustomerMonthlyWasteReport]![FromMan] & " and " & Forms![CustomerMonthlyWasteReport]![ToMan]
Click to expand...



Note that you're treating the two possibilities differently:

1) Dates surrounded with ## in one situation, not in the other.
2) CDate() -- which is used to convert a string to a date -- in one situation, not in the other.

There should be no problem outputting your SQL as a string. If in fact these fields are in date/time data type, try including the ##s in both instances and eliminating both CDate and CStr(). Note: CStr(CDate is like saying "...convert a string, which had been converted to a date with CDate(), back to a string with CStr().

The field names may be misleading but are you sure [FromMonth] and [ToMonth] contain complete dates?

Bob
 
For now please forget the conditional statements. I should of omitted them from the question as they seem to be a distraction.

Please focus on the following.

Code: 
 strDate = "Between #" & CDate(Forms![CustomerMonthlyWasteReport]![FromMonth]) & "# And #" & CDate(Forms![CustomerMonthlyWasteReport]![ToMonth]) & "#"

Please note I have also tried.

Code: 
 strDate = "Between #" & Forms![CustomerMonthlyWasteReport]![FromMonth] & "# And #" & Forms![CustomerMonthlyWasteReport]![ToMonth] & "#"

I have also attempted a direct IIF statement in the criteria. None of which work.

The work around for this is to call two different type of queries based on the ReportType selection. I didn't want to do this as it creates too much overhead (Duplication of Queries)

However, there must be a solution.

Thanks
 
Read Allen Browne's article here, in particular:
In VBA code, delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#.
Click to expand...

Since you're inserting dates in European format (e.g. #30/06/2008#) suspect that could well be the problem.

Bob
 
Hi Bob,

I changed the date format from

Between #01/06/2008# And #30/06/2008#

to

Between #6/1/2008# And #6/30/2008#

I still get the same error. When I hard code the result of the function Between #6/1/2008# And #6/30/2008# the correct result is produced.

I will read Allen's Article.

Thanks.
 
Hi -

Is it possible for you to provide a very small sample of your database?

Bob
 
Interesting that you coded the hard dates both in European and American formats and it worked, but to your problem.

I think , and wait to be corrected, that you cannot create dynamically a string containing the Between And commands, I think that you will need to call 2 functions to return the 2 dates having coded

Between Function1 And Function2

Just my 2 pennies worth.

Brian
 
Even this doesn't work. Hmmm Strange stuff

Code: 
Public Function CustomerMonthlyWasteReportDate() As Variant
      
      Dim strDate As Variant
      
       strDate = "#" & Forms![CustomerMonthlyWasteReport]![FromMonth] & "#"
      
      
      CustomerMonthlyWasteReportDate = strDate

End Function


Function Produces -> ? strDate
#6/1/2008#


Error Mesage: Data type mismatch in criteria expression.
 
Its not at all strange, if you are quoting dates in a form to be used in criteria you do not include # #, I suspect that when used in criteria the system makes the necessary "adjustments", it thinks that you are passing a string to be used to compare against dates, a no can do.

Brian
 
Got fed up so did a little test 2 functions fromdate() and todate()
coded Fromdate=#02/01/08# and todate = #2/28/08#
criteria Between fromdate() and todate() pulled all of my data for Feb.

Brian
 
Some Progress:

Without the Quotes

Code: 
strDate = "6/2/2008"
Works without the # Good Stuff


Code: 
strDate = "Between " & Forms![CustomerMonthlyWasteReport]![FromMonth] & " And " & Forms![CustomerMonthlyWasteReport]![ToMonth]

Function Result Between 6/1/2008 And 6/30/2008 No # symbols.

Same Data Mismatch Error.

Hmmmm interesting
 
Nice Work Brian.

Have to seperate it the Between into Two Functions I guess.

Between fromdate() and todate().

I'll give it a try but it should work.

Once again Good Work!
 
Good Work Brian.

Between fromdate() and todate() should work.

Thanks.
 
When I tried to use a form to provide date got the error so I coded
flddate= Forms! etc
todate=Cdate(flddate)


and it worked.

Brian

Hmmhadn't formatted the textbox will look at that.

Yes now that the text box on the form is short date works without that fiddle.
 
My forms worked Brian

Code: 
Public Function CustomerMonthlyWasteReportFromDate() As String
      
      Dim strDate As String
      
      If Forms![CustomerMonthlyWasteReport]![ReportType] = 1 Then
            strDate = Forms![CustomerMonthlyWasteReport]![FromMonth]
      Else
            strDate = Forms![CustomerMonthlyWasteReport]![FromMan]
      End If
      
      CustomerMonthlyWasteReportFromDate = strDate

End Function


Public Function CustomerMonthlyWasteReportToDate() As String
      
      Dim strDate As String
      
      If Forms![CustomerMonthlyWasteReport]![ReportType] = 1 Then
            strDate = Forms![CustomerMonthlyWasteReport]![ToMonth]
      Else
            strDate = Forms![CustomerMonthlyWasteReport]![ToMan]
      End If
      
      CustomerMonthlyWasteReportToDate = strDate
      

End Function

Query Date Criteria: Between CustomerMonthlyWasteReportFromDate() And CustomerMonthlyWasteReportToDate()

Works Great.

Much Appreciated.
 
You must log in or register to reply here.

Similar threads

Gasman
Solved What is going on here?
Replies
18
Views
1,779
The_Doc_Man
The_Doc_Man
S
Solved Copy to Documents\Backups
Replies
7
Views
683
isladogs
isladogs
C
Matching vba recordset to form recordset
Replies
12
Views
707
Gasman
Gasman
J
Textbox as source criteria for multiple queries
Replies
10
Views
431
JPR
J
jwcolby54
The message class - RAISING events
2 3
Replies
46
Views
3,452
jwcolby54
jwcolby54

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom