type mismatch opening report to base on date field

  • Thread starter Thread starter boris
  • Start date Start date
B

boris

I have this code opening a reports and get type mismatch even do I see
that the code is looking at the right data type

strWhere4 = "[InvoiceDate] >= #" & Nz(Me!txtFrom.Value, Date) & _
"# And [InvoiceDate] <= #" & Nz(Me!txtTo.Value, Date) & "#"

can someboby help

thenl
 
You are not necessaryl looking at a date. if you encounter a Null, then the #
before and after the date does not evaluate to a date type.


strWhere4 = "[InvoiceDate] >= " & CDate(Nz(Me!txtFrom, Date)) & _
"# And [InvoiceDate] <= " & CDate(Nz(Me!txtTo, Date))"

This will work as long as your text boxes have an input mask or are somehow
edited to produce a string that the CDate function can convert to a date.
Also, the .Value is not required. It is the default property returned for
text boxes.
 
I copy your code and it tell me Expected End of Statement, I have a
date field with dates on it. What doed that mean
 
If a take the last quote out it does not give a expected end of
statement put I get
Syntax erro in date in query expression'([PaymentDate]>=1/1/2005# and
[PaymentDate]<=7/27/2005)'
 
Sorry, I left a # in the string and quote on the end of the statement that
don't belong there:
strWhere4 = "[InvoiceDate] >= " & CDate(Nz(Me!txtFrom, Date)) & _
"# And [InvoiceDate] <= " & CDate(Nz(Me!txtTo, Date))"
Should be:
strWhere4 = "[InvoiceDate] >= " & CDate(Nz(Me!txtFrom, Date)) & _
" And [InvoiceDate] <= " & CDate(Nz(Me!txtTo, Date))

The data type in your field may be a date. That is not a problem. It is
also not a problem if your text box has a value that can be converted to a
date. It is a problem if your text box is empty because then your original
statement would put a # before and after the date which will not evaluate to
a date.
 
If a take the last quote out it does not give a expected end of
statement put I get
Syntax erro in date in query expression'([PaymentDate]>=1/1/2005# and
[PaymentDate]<=7/27/2005)'
Click to expand...

Include the # delimiters in the string:

strWhere4 = "[InvoiceDate] >= #" & CDate(Nz(Me!txtFrom, Date)) & _
"# And [InvoiceDate] <= #" & CDate(Nz(Me!txtTo, Date)) & "#"

John W. Vinson[MVP]
 
Argh!!
Sorry, I don't have a way to test it. My apologies. Try this:

strWhere4 = "[InvoiceDate] >= " & Nz(Me!txtFrom.Value, Date) & _
" And [InvoiceDate] <= " & Nz(Me!txtTo.Value, Date)
 
I still get a Type mismatch (Error 13) I also added dim strWhere4 as
Date also tried as string
I don't get it

Here is code


Dim strSQLE As String
Dim strSQLy As String
Dim sWhere As String
Dim strWhere4 As Date
Dim varSelected As Variant

'Select from List Box if it is base on the PUB field PubNumber

For Each varSelected In Me!list.ItemsSelected
strSQL = strSQL & Me!list.ItemData(varSelected) & ","
Next varSelected
If strSQL <> "" Then
End If
strSQL = "[Pub] IN (" & Left(strSQL, Len(strSQL) - 1) & ")"


Select Case Forms!reportCriteriaAccounting!lstReports.Value
Case "rptAccoutingPublicationBalance_Acc"
DoCmd.OpenReport
"rptAccoutingPublicationBalance_Acc", acPreview, , strSQL
DoCmd.Maximize
Case "AR Aging Report_Acc"
DoCmd.OpenReport "AR Aging Report_Acc", acPreview,
, strSQL
DoCmd.Maximize
Case "QryOutstanding_Acc"
DoCmd.OpenReport "QryOutstanding_Acc", acPreview, ,
strSQL
DoCmd.Maximize
Case "QryBalance_Acc"
DoCmd.OpenReport "QryBalance_Acc", acPreview, ,
strSQL
DoCmd.RunCommand acCmdFitToWindow
Case "SalesCommission_Acc"
DoCmd.OpenReport "SalesCommission_Acc", acPreview,
, strSQL
DoCmd.Maximize
Case "RevenueRec_Acc"
strWhere4 = "[PaymentDate] >= #" &
CDate(Nz(Me!txtDatefrom, Date)) & _
"# And [PaymentDate] <= #" &
CDate(Nz(Me!txtDateTo, Date)) & "#"
DoCmd.OpenReport "RevenueRec_Acc", acPreview, , strWhere4
' DoCmd.RunCommand acCmdFitToWindow

End Select


End Sub
At this moment I am asking only for dates fields(strWHERE)
I will need to add this values to a second string later base on a
number field. (strSQL=StrSQL & StrWhere4)
I have try this already with 2 number filed and it works

Your Help is greatly appreciated
 
I still get a Type mismatch (Error 13) I also added dim strWhere4 as
Date also tried as string
Click to expand...

strWhere4 should be a String not a Date. You're building a SQL string.
I don't get it

Here is code


Dim strSQLE As String
Dim strSQLy As String
Dim sWhere As String
Dim strWhere4 As Date
Dim varSelected As Variant
Click to expand...
<snip>
Case "RevenueRec_Acc"
strWhere4 = "[PaymentDate] >= #" &
CDate(Nz(Me!txtDatefrom, Date)) & _
"# And [PaymentDate] <= #" &
CDate(Nz(Me!txtDateTo, Date)) & "#"
DoCmd.OpenReport "RevenueRec_Acc", acPreview, , strWhere4
' DoCmd.RunCommand acCmdFitToWindow

End Select


End Sub
At this moment I am asking only for dates fields(strWHERE)
I will need to add this values to a second string later base on a
number field. (strSQL=StrSQL & StrWhere4)
I have try this already with 2 number filed and it works

Your Help is greatly appreciated
Click to expand...

What is the actual value assigned to strWhere4 in the code?


John W. Vinson[MVP]
 
Back
Top