Cannot pass value of date to a report that uses a query as its sou

[mc]

I have a form that is running a command button to print an invoice. For some
reason, I am getting no data, even though I think I am passing the date
correctly. Here is my coding:

Private Sub cmdPrintInvoices_Click()
Dim stDocName, strWhere As String, dtNow As Date

dtNow = Format(Date, "mm-dd-yyyy")
strWhere = "(Format([dtCreatedDate]," & Chr$(34) & "mm/dd/yyyy" &
Chr$(34) & "))=#" & dtNow & "#"
stDocName = "rpt_Create_Invoice"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Sub

Report "rpt_Create_Invoice" is using as its source a query called
qry_tblAllocation_Hist. When I run this query,
data is returned. When I change the query and add a where condition of
(Format([dtCreatedDate],"mm/dd/yyyy"))=#7/18/2008#
it works

in the debug window, I dumped the contents of strWhere:
?strWhere
(Format([dtCreatedDate],"mm/dd/yyyy"))=#7/18/2008#

I looked in this discussion group and I thought I found my solution but have
struggled trying to fix this. It is probably something small, any help would
be appreciated.

 

[mc]

Thanks, that worked. I also realized that I was populating dtCreatedDate
incorrectly, I was using Now(), which put the time in. I am now using dtNow
= Format(Date, "mm-dd-yyyy") to populate it.

Try just:
strWhere = "[dtCreatedDate] = #" & dtNow & "#"

I have a form that is running a command button to print an invoice. For some
reason, I am getting no data, even though I think I am passing the date
correctly. Here is my coding:

Private Sub cmdPrintInvoices_Click()
Dim stDocName, strWhere As String, dtNow As Date

dtNow = Format(Date, "mm-dd-yyyy")
strWhere = "(Format([dtCreatedDate]," & Chr$(34) & "mm/dd/yyyy" &
Chr$(34) & "))=#" & dtNow & "#"
stDocName = "rpt_Create_Invoice"
DoCmd.OpenReport stDocName, acPreview, , strWhere

End Sub

Report "rpt_Create_Invoice" is using as its source a query called
qry_tblAllocation_Hist. When I run this query,
data is returned. When I change the query and add a where condition of
(Format([dtCreatedDate],"mm/dd/yyyy"))=#7/18/2008#
it works

in the debug window, I dumped the contents of strWhere:
?strWhere
(Format([dtCreatedDate],"mm/dd/yyyy"))=#7/18/2008#

I looked in this discussion group and I thought I found my solution but have
struggled trying to fix this. It is probably something small, any help would
be appreciated.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com