The program collects a BEGIN date and an END date from the user;
these are stored in variables as dates: mdPeriodBEG and mdPeriodEND;
no problem.

I now need to construct a string variable that incorporates all the
monthly periods in between, regardless of start and end dates given,
in a certain format. Examples:

1. Example: Given BEG: 10-2007; END: 02-2008; *

I need a string variable loaded with:
'Oct-07', 'Nov-07', 'Dec-07', 'Jan-08', 'Feb-08'

*Please note: crosses a calendar year

2. Example: Given BEG: 02-2008; END: 05-2008;

I need a string variable loaded with:
'Feb-08', 'Mar-08', 'Apr-08', 'May-08'

This format is required so I can ultimately feed this string variable of
dates into a SQL string. I know how to concatenate a string variable,
but I need an efficient method of doing so using variable start and end
periods.

Please note that due to the datatype being used in Oracle, I cannot
simply use BETWEEN in my SQL string, rather I must feed it each
individual period as shown in my example using the IN keyword.

I am at a loss on how to do this efficiently, but perhaps a select
case or two?

Thanks much in advance for your kind assistance.

Of course, 10-2007 and 02-2008 are not **real** VB dates; however, this
function should return the SQL string you are looking for whether you input
your dates like you showed in your message or as real VB dates...

Function GetSQLDates(mdPeriodBEG As Date, mdPeriodEND As Date) As String
Dim X As Long
GetSQLDates = "'" & Format(mdPeriodBEG, "mmm-yy") & "'"
For X = 1 To DateDiff("m", mdPeriodBEG, mdPeriodEND)
GetSQLDates = GetSQLDates & ", '" & _
Format(DateAdd("m", X, mdPeriodBEG), "mmm-yy") & "'"
Next
End Function

Rick


"XP" <(E-Mail Removed)> wrote in message
news:3A369ADA-537D-47A8-B0D8-(E-Mail Removed)...
> The program collects a BEGIN date and an END date from the user;
> these are stored in variables as dates: mdPeriodBEG and mdPeriodEND;
> no problem.
>
> I now need to construct a string variable that incorporates all the
> monthly periods in between, regardless of start and end dates given,
> in a certain format. Examples:
>
> 1. Example: Given BEG: 10-2007; END: 02-2008; *
>
> I need a string variable loaded with:
> 'Oct-07', 'Nov-07', 'Dec-07', 'Jan-08', 'Feb-08'
>
> *Please note: crosses a calendar year
>
> 2. Example: Given BEG: 02-2008; END: 05-2008;
>
> I need a string variable loaded with:
> 'Feb-08', 'Mar-08', 'Apr-08', 'May-08'
>
> This format is required so I can ultimately feed this string variable of
> dates into a SQL string. I know how to concatenate a string variable,
> but I need an efficient method of doing so using variable start and end
> periods.
>
> Please note that due to the datatype being used in Oracle, I cannot
> simply use BETWEEN in my SQL string, rather I must feed it each
> individual period as shown in my example using the IN keyword.
>
> I am at a loss on how to do this efficiently, but perhaps a select
> case or two?
>
> Thanks much in advance for your kind assistance.
>

Hi Rick,

I ultimately came up with a solution, but it's not nearly as cohesive and
efficient as yours; I am adding your solution to my code library, thanks much
for the help!



"Rick Rothstein (MVP - VB)" wrote:

> Of course, 10-2007 and 02-2008 are not **real** VB dates; however, this
> function should return the SQL string you are looking for whether you input
> your dates like you showed in your message or as real VB dates...
>
> Function GetSQLDates(mdPeriodBEG As Date, mdPeriodEND As Date) As String
> Dim X As Long
> GetSQLDates = "'" & Format(mdPeriodBEG, "mmm-yy") & "'"
> For X = 1 To DateDiff("m", mdPeriodBEG, mdPeriodEND)
> GetSQLDates = GetSQLDates & ", '" & _
> Format(DateAdd("m", X, mdPeriodBEG), "mmm-yy") & "'"
> Next
> End Function
>
> Rick
>
>
> "XP" <(E-Mail Removed)> wrote in message
> news:3A369ADA-537D-47A8-B0D8-(E-Mail Removed)...
> > The program collects a BEGIN date and an END date from the user;
> > these are stored in variables as dates: mdPeriodBEG and mdPeriodEND;
> > no problem.
> >
> > I now need to construct a string variable that incorporates all the
> > monthly periods in between, regardless of start and end dates given,
> > in a certain format. Examples:
> >
> > 1. Example: Given BEG: 10-2007; END: 02-2008; *
> >
> > I need a string variable loaded with:
> > 'Oct-07', 'Nov-07', 'Dec-07', 'Jan-08', 'Feb-08'
> >
> > *Please note: crosses a calendar year
> >
> > 2. Example: Given BEG: 02-2008; END: 05-2008;
> >
> > I need a string variable loaded with:
> > 'Feb-08', 'Mar-08', 'Apr-08', 'May-08'
> >
> > This format is required so I can ultimately feed this string variable of
> > dates into a SQL string. I know how to concatenate a string variable,
> > but I need an efficient method of doing so using variable start and end
> > periods.
> >
> > Please note that due to the datatype being used in Oracle, I cannot
> > simply use BETWEEN in my SQL string, rather I must feed it each
> > individual period as shown in my example using the IN keyword.
> >
> > I am at a loss on how to do this efficiently, but perhaps a select
> > case or two?
> >
> > Thanks much in advance for your kind assistance.
> >
>
>