MonkeyPie
asked on
Access report source with date range
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:
...
I have also tried ...
...
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!
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")
End Function
I have also tried ...
Public Function getWorkloadEndDate() As Date
getWorkloadEndDate = Format(Forms!frmWorkLoadPrint_Option.dtpTo.Value, "yyyy/mm/dd")
End Function
Public Function getWorkloadStartDate2() As Date
'function pair used in srptCurrentBid etc record source
getWorkloadStartDate2 = Format(Forms!frmWorkLoadPrint_Option.dtpFrom.Value, "\#yyyy\-mm\-dd\#")
End Function
Public Function getWorkloadEndDate2() As Date
getWorkloadEndDate2 = FormatForms!frmWorkLoadPrint_Option.dtpTo.Value, "\#yyyy\-mm\-dd\#")
End Function
Public Function getdCutOffDate() As Date
getdCutOffDate = CDate(dCutOffDate)
End Function
...
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have done more testing and found this:
If I change the hardcoded recordsource to :
Select * from xx where NoteDate > #03/01/2014#
(switching dd/mm to mm/dd)
I then get the same BAD result as I get with my function.
So, it is obviously to do with swtiching mm and dd around, but I can't work out how to fix it! Any ideas?
Can I say:
datevalue(format(NoteDate, "dd/mm/yyyy")) > datevalue(format(formDate, "dd/mm/yyyy")?
Seems overly complicated.
If I change the hardcoded recordsource to :
Select * from xx where NoteDate > #03/01/2014#
(switching dd/mm to mm/dd)
I then get the same BAD result as I get with my function.
So, it is obviously to do with swtiching mm and dd around, but I can't work out how to fix it! Any ideas?
Can I say:
datevalue(format(NoteDate,
Seems overly complicated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks, Gustav.
I hope sometime you will have time to write an article about date values in Access
I hope sometime you will have time to write an article about date values in Access
Yes, I've tried a couple of times but too much real work blocks.
/gustav
/gustav
ASKER
Thank you everyone. Both DateSerial (yy, mm, dd) and
DateValue(Forms!frmWorkLoa dPrint_Opt ion!dtpFro m.Value)
worked.
DateValue(Forms!frmWorkLoa
worked.
You are welcome!
/gustav
/gustav
ASKER
getWorkloadStartDate = Format(Forms!frmWorkLoadPr
I also tried "dd/mm/yyyy" again just to check. Also, only getting 55 out of the 56 notes it finds if I hard code in date.
I added some debug.print lines to check form date "01/03/2014" and resulting date from function (also "01/03/2014") which are both correct.
So, just to confirm if function returns 01/03/2014 as type DATE, this gives a different result to hardcoded record source:
Select * from xx where NoteDate > #01/03/2014#
- where I expect the result to be the same.