Data Type Mismatch Hell

  • Thread starter Thread starter Alfonso Edgardo
  • Start date Start date
A

Alfonso Edgardo

Why does this code give a data type mismatch right before
the report runs, is there a problem with setting years in
this manner? Any other potential solutions?

CmbReportYear is set to either "2004" or "2005".

If Not IsNull(Me.CmbReportYear) And CmbReportYear.Value
<> "All" Then
strWhere = strWhere & strLink & "[Activity Date]="""
& "Between #01/01/" & CmbReportYear & "#" & " " & "and"
& " " & "#12/31/" & CmbReportYear & "#" & """"

Thanks!
 
Alfonso said:
Why does this code give a data type mismatch right before
the report runs, is there a problem with setting years in
this manner? Any other potential solutions?

CmbReportYear is set to either "2004" or "2005".

If Not IsNull(Me.CmbReportYear) And CmbReportYear.Value
<> "All" Then
strWhere = strWhere & strLink & "[Activity Date]="""
& "Between #01/01/" & CmbReportYear & "#" & " " & "and"
& " " & "#12/31/" & CmbReportYear & "#" & """"
Click to expand...


Don't know what effect strLink might be having, but you
definitely have to get rid of the = sign before the Between
 
Try:

strWhere = strWhere & strLink & "[Activity Date] "
& "Between TimeSerial(CmbReportYear, 1, 1) & " and "
& TimeSerial(CmbReportYear, 12, 31)
 
Alfonso said:
Why does this code give a data type mismatch right before
the report runs, is there a problem with setting years in
this manner? Any other potential solutions?
CmbReportYear is set to either "2004" or "2005".
If Not IsNull(Me.CmbReportYear) And CmbReportYear.Value
<> "All" Then
strWhere = strWhere & strLink & "[Activity Date]="""
& "Between #01/01/" & CmbReportYear & "#" & " " & "and"
& " " & "#12/31/" & CmbReportYear & "#" & """"
Click to expand...

Because you have unwanted quotes and also an equals in front of the between.
When building strings like this gives a problem, try using a MsgBox or
Debug.Print to see what you have built, the problems are then much easier to spot.

Your code should say:

If Not IsNull(Me.CmbReportYear) And CmbReportYear.Value <> "All" Then _
strWhere = strWhere & strLink & "[Activity Date] Between #01/01/" _
& CmbReportYear & "# and #12/31/" & CmbReportYear & "#"

HTH
John
 
Back
Top