I have a ACCESS 2010 subreport with the report source property something like:
Select * from tblNotes where NoteDate > getStartDate()
The function getStartDate() gets date from a form text box:
Public Function getWorkloadStartDate() As Date
'function pair used in srptWorkload, srptCurrentBid etc record source
getWorkloadStartDate = Format(Forms!frmWorkLoadPrint_Option.dtpFrom.Value, "yyyy/mm/dd")
I have also tried ...
Public Function getWorkloadEndDate() As Date
getWorkloadEndDate = Format(Forms!frmWorkLoadPrint_Option.dtpTo.Value, "yyyy/mm/dd")
Public Function getWorkloadStartDate2() As Date
'function pair used in srptCurrentBid etc record source
getWorkloadStartDate2 = Format(Forms!frmWorkLoadPrint_Option.dtpFrom.Value, "\#yyyy\-mm\-dd\#")
Public Function getWorkloadEndDate2() As Date
getWorkloadEndDate2 = FormatForms!frmWorkLoadPrint_Option.dtpTo.Value, "\#yyyy\-mm\-dd\#")
Public Function getdCutOffDate() As Date
getdCutOffDate = CDate(dCutOffDate)
My report is almost correct, however one user has noted that it does not pick up one of his notes. I did some testing and although it works perfectly for most notes, it has an issue with a few notes.
If I hard code the record source to be:
Select * from tblNotes where NoteDate > #20/02/2014#
it collects the correct number of notes, however, if the form textbox is 20/02/2014 it misses one or two notes.
If this was VBA know I could use :
"Select * from Notes where noteDate > #" & format(FromDate "yyyy/mm/dd") & "#"
but how do I replicate this in report record source property box? (remember it is a SUB report, not a report, so I can't set me.recordsource using VBA on the report open event since master child won't work)
Please note I am in Australia where date format is dd/mm/yyyy, not mm/dd/yyyy.
Also note that I have used yyyy/mm/dd after testing dd/mm/yyyy and mm/dd/yyyy unsuccessfully.
I HATE working with dates!