| Author |
Message |
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
   Topic: Month Day Parameter
Posted: 05 Jul 2017 at 4:18am |
|
Hi, I'm using CR2013. I am bringing in a SQL Command that will require a begin and end parameter, for begin month/day and end month/day. The field name is LAW_TAEEMASTER.MASTR_ENTRY.
I need the report to return only records where the month and day of LAW_TAEEMASTER.MASTR_ENTRY falls between the parameter. Year is not needed. For example, if my MASTR_ENTRY is 06/30/2017, and the parameter I enter is 6/25 to 7/4, this record should appear, regardless of year.
I am not sure how to construct this formula within the SQL. Your help is appreciated. I will post more code if needed. Once I know how to parse out month and day from the year and create a parameter for it though, I should be good to go. Thank you,
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Jul 2017 at 6:08am |
|
are you going to allow users to make the second param < the first param? Say 12-1 to 1-15?
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 05 Jul 2017 at 6:16am |
|
Ah, good point, and yes, that would be the preferred way to construct the parameter. Thank you for clarifying.
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Jul 2017 at 7:52am |
|
something to consider would be maybe using cascading parameters to make sure the user selects a valid month/day combination. Think you would have to exclude leap year date as an option.
Assuming you have month and day integer values for a start and end I THINK the this would do what you want but look it over carefully
(
date(year(today),{?Month Start},{?Day Start})<=date(year(today),{?Month End},{?Day End})
and
dateadd('yyyy',datediff('yyyy',{LAW_TAEEMASTER.MASTR_ENTRY},today),{LAW_TAEEMASTER.MASTR_ENTRY}) in date(year(today),{?Month Start},{?Day Start}) to date(year(today),{?Month End},{?Day End})
)
OR
(
date(year(today),{?Month Start},{?Day Start})>date(year(today),{?Month End},{?Day End})
and
(
dateadd('yyyy',datediff('yyyy',{LAW_TAEEMASTER.MASTR_ENTRY},today),{LAW_TAEEMASTER.MASTR_ENTRY}) <= date(year(today),{?Month End},{?Day End})
or
dateadd('yyyy',datediff('yyyy',{LAW_TAEEMASTER.MASTR_ENTRY},today),{LAW_TAEEMASTER.MASTR_ENTRY}) >= date(year(today),{?Month Start},{?Day Start})
)
)
Edited by DBlank - 05 Jul 2017 at 7:53am
|
|
IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 05 Jul 2017 at 8:44am |
|
Thank you! I thing this will work, but first, I get an error on this parameter "Failed to retrieve data from the database, 42000[Microsoft][OSBC SQL Server Driver]syntax error or access violation". If I take the parameter out, or change it to a simple date range parameter it works: LAW_TAEEMASTER.MASTR_ENTRY >= {?BegMasterEntryDate} and LAW_TAEEMASTER.MASTR_ENTRY <={?EndMasterEntryDate}. If you have any thoughts on that error please share. Not finding much on line.
Edited by thummel1 - 05 Jul 2017 at 8:48am
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Jul 2017 at 10:10am |
|
I was creating a crystal select using crystal parameters as compared to command parameters.
If you use command parameters you are going to run the risk of users entering non-date values.
|
|
IP Logged |
|
|
|
