I'm stumped. Could someone please look at my code and see why the WHERE
clause fails when I try to restrict the records to a particular date range?

I'm trying to convert a Totals query to code, with parameters for client
(NameID), type of service (TreatmentService), and date of service
(DateProvSrv).

[I gave up trying to keep the parameters in the original query and then
trying to resolve those parameters within the module].

SQL of the original query (which works fine) :

SELECT ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText,
Sum([ProvUnits]*[HrsPerUnit]) AS Hours
FROM ServicesProvided INNER JOIN ServiceCodes_lkp ON
ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID
WHERE (((ServicesProvided.NameID)=[Forms]![frmTreatmentPlans_main]![NameID])
AND ((ServicesProvided.DateProvSrv) Between
DateAdd("d",-90,[Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubf
rm]![TxPlanDate]) And
[Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubfrm]![TxPlanDate]
) AND ((ServiceCodes_lkp.TxService)=Yes))
GROUP BY ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText;

In trying to convert to code, I have: (just the relevant portion)

Dim strSQL As String
Dim IntervalType As String
Dim ClientID As Long
Dim ToDate As Date
Dim FromDate As Date

IntervalType = "d"
ClientID = Forms!frmTreatmentPlans_main!NameID
ToDate =
Forms!frmTreatmentPlans_main!frmTreatmentPlans_mainsubfrm!TxPlanDate
FromDate = DateAdd(IntervalType, -90, ToDate)

[each of the above variables prints out correctly in the debug window]

strSQL = "SELECT NameID, SrvCodeText, Sum(ProvUnits*HrsPerUnit) AS Hours"
strSQL = strSQL & " FROM ServicesProvided INNER JOIN ServiceCodes_lkp ON
ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID"
strSQL = strSQL & " WHERE NameID = " & ClientID
strSQL = strSQL & " AND DateProvSrv Between " & FromDate & " AND " & ToDate
strSQL = strSQL & " AND TxService = Yes"
strSQL = strSQL & " GROUP BY NameID,SrvCodeText;"

.... If I comment-out the line "strSQL = strSQL & " AND DateProvSrv Between "
& FromDate & " AND " & ToDate" I get the correct recordset (correct NameID,
SrvCodeText, Hours, and TxService), but of course this includes *all* dates
of service.

I also tried (unsuccessfully):
strSQL = strSQL & " AND DateProvSrv IN (SELECT DateProvSrv FROM
ServicesProvided WHERE DateProvSrv Between " & FromDate & " And " & ToDate &
" AND NameID = " & ClientID & ")"

Could sure use some help.
Thank you,
Mark

Dates can be a bit fickle in SQL statements. It's best to explicitly format
and delimit them for ACCESS to work without fail:

strSQL = strSQL & " AND DateProvSrv Between " & _
Format(FromDate, "\#mm\/dd\/yyyy\#) & " AND " & _
Format(ToDate, "\#mm\/dd\/yyyy\#)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"Mark" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> I'm stumped. Could someone please look at my code and see why the WHERE
> clause fails when I try to restrict the records to a particular date
> range?
>
> I'm trying to convert a Totals query to code, with parameters for client
> (NameID), type of service (TreatmentService), and date of service
> (DateProvSrv).
>
> [I gave up trying to keep the parameters in the original query and then
> trying to resolve those parameters within the module].
>
> SQL of the original query (which works fine) :
>
> SELECT ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText,
> Sum([ProvUnits]*[HrsPerUnit]) AS Hours
> FROM ServicesProvided INNER JOIN ServiceCodes_lkp ON
> ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID
> WHERE
> (((ServicesProvided.NameID)=[Forms]![frmTreatmentPlans_main]![NameID])
> AND ((ServicesProvided.DateProvSrv) Between
> DateAdd("d",-90,[Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubf
> rm]![TxPlanDate]) And
> [Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubfrm]![TxPlanDate]
> ) AND ((ServiceCodes_lkp.TxService)=Yes))
> GROUP BY ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText;
>
> In trying to convert to code, I have: (just the relevant portion)
>
> Dim strSQL As String
> Dim IntervalType As String
> Dim ClientID As Long
> Dim ToDate As Date
> Dim FromDate As Date
>
> IntervalType = "d"
> ClientID = Forms!frmTreatmentPlans_main!NameID
> ToDate =
> Forms!frmTreatmentPlans_main!frmTreatmentPlans_mainsubfrm!TxPlanDate
> FromDate = DateAdd(IntervalType, -90, ToDate)
>
> [each of the above variables prints out correctly in the debug window]
>
> strSQL = "SELECT NameID, SrvCodeText, Sum(ProvUnits*HrsPerUnit) AS Hours"
> strSQL = strSQL & " FROM ServicesProvided INNER JOIN ServiceCodes_lkp ON
> ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID"
> strSQL = strSQL & " WHERE NameID = " & ClientID
> strSQL = strSQL & " AND DateProvSrv Between " & FromDate & " AND " &
> ToDate
> strSQL = strSQL & " AND TxService = Yes"
> strSQL = strSQL & " GROUP BY NameID,SrvCodeText;"
>
> ... If I comment-out the line "strSQL = strSQL & " AND DateProvSrv Between
> "
> & FromDate & " AND " & ToDate" I get the correct recordset (correct
> NameID,
> SrvCodeText, Hours, and TxService), but of course this includes *all*
> dates
> of service.
>
> I also tried (unsuccessfully):
> strSQL = strSQL & " AND DateProvSrv IN (SELECT DateProvSrv FROM
> ServicesProvided WHERE DateProvSrv Between " & FromDate & " And " & ToDate
> &
> " AND NameID = " & ClientID & ")"
>
> Could sure use some help.
> Thank you,
> Mark
>
>

Thanks, Ken, but when I copy/past the expression -- and remove the "_ >"
stuff -- I get "Compile Error: expected expression" with the cursor placed
over the next-to-last #.

I assume the backward and forward slashes (\/) between the mm, dd, and yyyy
markers are supposed to be there?

Mark

"Ken Snell [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Dates can be a bit fickle in SQL statements. It's best to explicitly
format
> and delimit them for ACCESS to work without fail:
>
> strSQL = strSQL & " AND DateProvSrv Between " & _
> Format(FromDate, "\#mm\/dd\/yyyy\#) & " AND " & _
> Format(ToDate, "\#mm\/dd\/yyyy\#)
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
>
> "Mark" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > I'm stumped. Could someone please look at my code and see why the WHERE
> > clause fails when I try to restrict the records to a particular date
> > range?
> >
> > I'm trying to convert a Totals query to code, with parameters for client
> > (NameID), type of service (TreatmentService), and date of service
> > (DateProvSrv).
> >
> > [I gave up trying to keep the parameters in the original query and then
> > trying to resolve those parameters within the module].
> >
> > SQL of the original query (which works fine) :
> >
> > SELECT ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText,
> > Sum([ProvUnits]*[HrsPerUnit]) AS Hours
> > FROM ServicesProvided INNER JOIN ServiceCodes_lkp ON
> > ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID
> > WHERE
> > (((ServicesProvided.NameID)=[Forms]![frmTreatmentPlans_main]![NameID])
> > AND ((ServicesProvided.DateProvSrv) Between
> >
DateAdd("d",-90,[Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubf
> > rm]![TxPlanDate]) And
> >
[Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubfrm]![TxPlanDate]
> > ) AND ((ServiceCodes_lkp.TxService)=Yes))
> > GROUP BY ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText;
> >
> > In trying to convert to code, I have: (just the relevant portion)
> >
> > Dim strSQL As String
> > Dim IntervalType As String
> > Dim ClientID As Long
> > Dim ToDate As Date
> > Dim FromDate As Date
> >
> > IntervalType = "d"
> > ClientID = Forms!frmTreatmentPlans_main!NameID
> > ToDate =
> > Forms!frmTreatmentPlans_main!frmTreatmentPlans_mainsubfrm!TxPlanDate
> > FromDate = DateAdd(IntervalType, -90, ToDate)
> >
> > [each of the above variables prints out correctly in the debug window]
> >
> > strSQL = "SELECT NameID, SrvCodeText, Sum(ProvUnits*HrsPerUnit) AS
Hours"
> > strSQL = strSQL & " FROM ServicesProvided INNER JOIN ServiceCodes_lkp ON
> > ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID"
> > strSQL = strSQL & " WHERE NameID = " & ClientID
> > strSQL = strSQL & " AND DateProvSrv Between " & FromDate & " AND " &
> > ToDate
> > strSQL = strSQL & " AND TxService = Yes"
> > strSQL = strSQL & " GROUP BY NameID,SrvCodeText;"
> >
> > ... If I comment-out the line "strSQL = strSQL & " AND DateProvSrv
Between
> > "
> > & FromDate & " AND " & ToDate" I get the correct recordset (correct
> > NameID,
> > SrvCodeText, Hours, and TxService), but of course this includes *all*
> > dates
> > of service.
> >
> > I also tried (unsuccessfully):
> > strSQL = strSQL & " AND DateProvSrv IN (SELECT DateProvSrv FROM
> > ServicesProvided WHERE DateProvSrv Between " & FromDate & " And " &
ToDate
> > &
> > " AND NameID = " & ClientID & ")"
> >
> > Could sure use some help.
> > Thank you,
> > Mark
> >
> >
>
>

Do not remove the trailing " _" on each line. That is a continuation
character, telling ACCESS that the code line continues on the next line.

Use these lines exactly as shown:

strSQL = strSQL & " AND DateProvSrv Between " & _
Format(FromDate, "\#mm\/dd\/yyyy\#) & " AND " & _
Format(ToDate, "\#mm\/dd\/yyyy\#)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Mark" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks, Ken, but when I copy/past the expression -- and remove the "_ >"
> stuff -- I get "Compile Error: expected expression" with the cursor
> placed
> over the next-to-last #.
>
> I assume the backward and forward slashes (\/) between the mm, dd, and
> yyyy
> markers are supposed to be there?
>
> Mark
>
> "Ken Snell [MVP]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Dates can be a bit fickle in SQL statements. It's best to explicitly
> format
>> and delimit them for ACCESS to work without fail:
>>
>> strSQL = strSQL & " AND DateProvSrv Between " & _
>> Format(FromDate, "\#mm\/dd\/yyyy\#) & " AND " & _
>> Format(ToDate, "\#mm\/dd\/yyyy\#)
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>> http://www.accessmvp.com/KDSnell/
>>
>>
>>
>> "Mark" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > I'm stumped. Could someone please look at my code and see why the WHERE
>> > clause fails when I try to restrict the records to a particular date
>> > range?
>> >
>> > I'm trying to convert a Totals query to code, with parameters for
>> > client
>> > (NameID), type of service (TreatmentService), and date of service
>> > (DateProvSrv).
>> >
>> > [I gave up trying to keep the parameters in the original query and then
>> > trying to resolve those parameters within the module].
>> >
>> > SQL of the original query (which works fine) :
>> >
>> > SELECT ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText,
>> > Sum([ProvUnits]*[HrsPerUnit]) AS Hours
>> > FROM ServicesProvided INNER JOIN ServiceCodes_lkp ON
>> > ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID
>> > WHERE
>> > (((ServicesProvided.NameID)=[Forms]![frmTreatmentPlans_main]![NameID])
>> > AND ((ServicesProvided.DateProvSrv) Between
>> >
> DateAdd("d",-90,[Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubf
>> > rm]![TxPlanDate]) And
>> >
> [Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubfrm]![TxPlanDate]
>> > ) AND ((ServiceCodes_lkp.TxService)=Yes))
>> > GROUP BY ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText;
>> >
>> > In trying to convert to code, I have: (just the relevant portion)
>> >
>> > Dim strSQL As String
>> > Dim IntervalType As String
>> > Dim ClientID As Long
>> > Dim ToDate As Date
>> > Dim FromDate As Date
>> >
>> > IntervalType = "d"
>> > ClientID = Forms!frmTreatmentPlans_main!NameID
>> > ToDate =
>> > Forms!frmTreatmentPlans_main!frmTreatmentPlans_mainsubfrm!TxPlanDate
>> > FromDate = DateAdd(IntervalType, -90, ToDate)
>> >
>> > [each of the above variables prints out correctly in the debug window]
>> >
>> > strSQL = "SELECT NameID, SrvCodeText, Sum(ProvUnits*HrsPerUnit) AS
> Hours"
>> > strSQL = strSQL & " FROM ServicesProvided INNER JOIN ServiceCodes_lkp
>> > ON
>> > ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID"
>> > strSQL = strSQL & " WHERE NameID = " & ClientID
>> > strSQL = strSQL & " AND DateProvSrv Between " & FromDate & " AND " &
>> > ToDate
>> > strSQL = strSQL & " AND TxService = Yes"
>> > strSQL = strSQL & " GROUP BY NameID,SrvCodeText;"
>> >
>> > ... If I comment-out the line "strSQL = strSQL & " AND DateProvSrv
> Between
>> > "
>> > & FromDate & " AND " & ToDate" I get the correct recordset (correct
>> > NameID,
>> > SrvCodeText, Hours, and TxService), but of course this includes *all*
>> > dates
>> > of service.
>> >
>> > I also tried (unsuccessfully):
>> > strSQL = strSQL & " AND DateProvSrv IN (SELECT DateProvSrv FROM
>> > ServicesProvided WHERE DateProvSrv Between " & FromDate & " And " &
> ToDate
>> > &
>> > " AND NameID = " & ClientID & ")"
>> >
>> > Could sure use some help.
>> > Thank you,
>> > Mark
>> >
>> >
>>
>>
>
>

Sorry, typo:

strSQL = strSQL & " AND DateProvSrv Between " & _
Format(FromDate, "\#mm\/dd\/yyyy\#") & " AND " & _
Format(ToDate, "\#mm\/dd\/yyyy\#")


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"Ken Snell [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Do not remove the trailing " _" on each line. That is a continuation
> character, telling ACCESS that the code line continues on the next line.
>
> Use these lines exactly as shown:
>
> strSQL = strSQL & " AND DateProvSrv Between " & _
> Format(FromDate, "\#mm\/dd\/yyyy\#) & " AND " & _
> Format(ToDate, "\#mm\/dd\/yyyy\#)
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
> "Mark" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thanks, Ken, but when I copy/past the expression -- and remove the "_ >"
>> stuff -- I get "Compile Error: expected expression" with the cursor
>> placed
>> over the next-to-last #.
>>
>> I assume the backward and forward slashes (\/) between the mm, dd, and
>> yyyy
>> markers are supposed to be there?
>>
>> Mark
>>
>> "Ken Snell [MVP]" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> Dates can be a bit fickle in SQL statements. It's best to explicitly
>> format
>>> and delimit them for ACCESS to work without fail:
>>>
>>> strSQL = strSQL & " AND DateProvSrv Between " & _
>>> Format(FromDate, "\#mm\/dd\/yyyy\#) & " AND " & _
>>> Format(ToDate, "\#mm\/dd\/yyyy\#)
>>>
>>> --
>>>
>>> Ken Snell
>>> <MS ACCESS MVP>
>>> http://www.accessmvp.com/KDSnell/
>>>
>>>
>>>
>>> "Mark" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>> > I'm stumped. Could someone please look at my code and see why the
>>> > WHERE
>>> > clause fails when I try to restrict the records to a particular date
>>> > range?
>>> >
>>> > I'm trying to convert a Totals query to code, with parameters for
>>> > client
>>> > (NameID), type of service (TreatmentService), and date of service
>>> > (DateProvSrv).
>>> >
>>> > [I gave up trying to keep the parameters in the original query and
>>> > then
>>> > trying to resolve those parameters within the module].
>>> >
>>> > SQL of the original query (which works fine) :
>>> >
>>> > SELECT ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText,
>>> > Sum([ProvUnits]*[HrsPerUnit]) AS Hours
>>> > FROM ServicesProvided INNER JOIN ServiceCodes_lkp ON
>>> > ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID
>>> > WHERE
>>> > (((ServicesProvided.NameID)=[Forms]![frmTreatmentPlans_main]![NameID])
>>> > AND ((ServicesProvided.DateProvSrv) Between
>>> >
>> DateAdd("d",-90,[Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubf
>>> > rm]![TxPlanDate]) And
>>> >
>> [Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubfrm]![TxPlanDate]
>>> > ) AND ((ServiceCodes_lkp.TxService)=Yes))
>>> > GROUP BY ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText;
>>> >
>>> > In trying to convert to code, I have: (just the relevant portion)
>>> >
>>> > Dim strSQL As String
>>> > Dim IntervalType As String
>>> > Dim ClientID As Long
>>> > Dim ToDate As Date
>>> > Dim FromDate As Date
>>> >
>>> > IntervalType = "d"
>>> > ClientID = Forms!frmTreatmentPlans_main!NameID
>>> > ToDate =
>>> > Forms!frmTreatmentPlans_main!frmTreatmentPlans_mainsubfrm!TxPlanDate
>>> > FromDate = DateAdd(IntervalType, -90, ToDate)
>>> >
>>> > [each of the above variables prints out correctly in the debug window]
>>> >
>>> > strSQL = "SELECT NameID, SrvCodeText, Sum(ProvUnits*HrsPerUnit) AS
>> Hours"
>>> > strSQL = strSQL & " FROM ServicesProvided INNER JOIN ServiceCodes_lkp
>>> > ON
>>> > ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID"
>>> > strSQL = strSQL & " WHERE NameID = " & ClientID
>>> > strSQL = strSQL & " AND DateProvSrv Between " & FromDate & " AND " &
>>> > ToDate
>>> > strSQL = strSQL & " AND TxService = Yes"
>>> > strSQL = strSQL & " GROUP BY NameID,SrvCodeText;"
>>> >
>>> > ... If I comment-out the line "strSQL = strSQL & " AND DateProvSrv
>> Between
>>> > "
>>> > & FromDate & " AND " & ToDate" I get the correct recordset (correct
>>> > NameID,
>>> > SrvCodeText, Hours, and TxService), but of course this includes *all*
>>> > dates
>>> > of service.
>>> >
>>> > I also tried (unsuccessfully):
>>> > strSQL = strSQL & " AND DateProvSrv IN (SELECT DateProvSrv FROM
>>> > ServicesProvided WHERE DateProvSrv Between " & FromDate & " And " &
>> ToDate
>>> > &
>>> > " AND NameID = " & ClientID & ")"
>>> >
>>> > Could sure use some help.
>>> > Thank you,
>>> > Mark
>>> >
>>> >
>>>
>>>
>>
>>
>
>

You're the man, Ken!
Thank you so much.
Mark

"Ken Snell [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sorry, typo:
>
> strSQL = strSQL & " AND DateProvSrv Between " & _
> Format(FromDate, "\#mm\/dd\/yyyy\#") & " AND " & _
> Format(ToDate, "\#mm\/dd\/yyyy\#")
>
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
> "Ken Snell [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Do not remove the trailing " _" on each line. That is a continuation
> > character, telling ACCESS that the code line continues on the next line.
> >
> > Use these lines exactly as shown:
> >
> > strSQL = strSQL & " AND DateProvSrv Between " & _
> > Format(FromDate, "\#mm\/dd\/yyyy\#) & " AND " & _
> > Format(ToDate, "\#mm\/dd\/yyyy\#)
> >
> > --
> >
> > Ken Snell
> > <MS ACCESS MVP>
> > http://www.accessmvp.com/KDSnell/
> >
> >
> > "Mark" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> Thanks, Ken, but when I copy/past the expression -- and remove the "_
>"
> >> stuff -- I get "Compile Error: expected expression" with the cursor
> >> placed
> >> over the next-to-last #.
> >>
> >> I assume the backward and forward slashes (\/) between the mm, dd, and
> >> yyyy
> >> markers are supposed to be there?
> >>
> >> Mark
> >>
> >> "Ken Snell [MVP]" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >>> Dates can be a bit fickle in SQL statements. It's best to explicitly
> >> format
> >>> and delimit them for ACCESS to work without fail:
> >>>
> >>> strSQL = strSQL & " AND DateProvSrv Between " & _
> >>> Format(FromDate, "\#mm\/dd\/yyyy\#) & " AND " & _
> >>> Format(ToDate, "\#mm\/dd\/yyyy\#)
> >>>
> >>> --
> >>>
> >>> Ken Snell
> >>> <MS ACCESS MVP>
> >>> http://www.accessmvp.com/KDSnell/
> >>>
> >>>
> >>>
> >>> "Mark" <(E-Mail Removed)> wrote in message
> >>> news:%(E-Mail Removed)...
> >>> > I'm stumped. Could someone please look at my code and see why the
> >>> > WHERE
> >>> > clause fails when I try to restrict the records to a particular date
> >>> > range?
> >>> >
> >>> > I'm trying to convert a Totals query to code, with parameters for
> >>> > client
> >>> > (NameID), type of service (TreatmentService), and date of service
> >>> > (DateProvSrv).
> >>> >
> >>> > [I gave up trying to keep the parameters in the original query and
> >>> > then
> >>> > trying to resolve those parameters within the module].
> >>> >
> >>> > SQL of the original query (which works fine) :
> >>> >
> >>> > SELECT ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText,
> >>> > Sum([ProvUnits]*[HrsPerUnit]) AS Hours
> >>> > FROM ServicesProvided INNER JOIN ServiceCodes_lkp ON
> >>> > ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID
> >>> > WHERE
> >>> >
(((ServicesProvided.NameID)=[Forms]![frmTreatmentPlans_main]![NameID])
> >>> > AND ((ServicesProvided.DateProvSrv) Between
> >>> >
> >>
DateAdd("d",-90,[Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubf
> >>> > rm]![TxPlanDate]) And
> >>> >
> >>
[Forms]![frmTreatmentPlans_main]![frmTreatmentPlans_mainsubfrm]![TxPlanDate]
> >>> > ) AND ((ServiceCodes_lkp.TxService)=Yes))
> >>> > GROUP BY ServicesProvided.NameID, ServiceCodes_lkp.SrvCodeText;
> >>> >
> >>> > In trying to convert to code, I have: (just the relevant portion)
> >>> >
> >>> > Dim strSQL As String
> >>> > Dim IntervalType As String
> >>> > Dim ClientID As Long
> >>> > Dim ToDate As Date
> >>> > Dim FromDate As Date
> >>> >
> >>> > IntervalType = "d"
> >>> > ClientID = Forms!frmTreatmentPlans_main!NameID
> >>> > ToDate =
> >>> > Forms!frmTreatmentPlans_main!frmTreatmentPlans_mainsubfrm!TxPlanDate
> >>> > FromDate = DateAdd(IntervalType, -90, ToDate)
> >>> >
> >>> > [each of the above variables prints out correctly in the debug
window]
> >>> >
> >>> > strSQL = "SELECT NameID, SrvCodeText, Sum(ProvUnits*HrsPerUnit) AS
> >> Hours"
> >>> > strSQL = strSQL & " FROM ServicesProvided INNER JOIN
ServiceCodes_lkp
> >>> > ON
> >>> > ServicesProvided.SrvCodeID = ServiceCodes_lkp.SrvCodeID"
> >>> > strSQL = strSQL & " WHERE NameID = " & ClientID
> >>> > strSQL = strSQL & " AND DateProvSrv Between " & FromDate & " AND " &
> >>> > ToDate
> >>> > strSQL = strSQL & " AND TxService = Yes"
> >>> > strSQL = strSQL & " GROUP BY NameID,SrvCodeText;"
> >>> >
> >>> > ... If I comment-out the line "strSQL = strSQL & " AND DateProvSrv
> >> Between
> >>> > "
> >>> > & FromDate & " AND " & ToDate" I get the correct recordset (correct
> >>> > NameID,
> >>> > SrvCodeText, Hours, and TxService), but of course this includes
*all*
> >>> > dates
> >>> > of service.
> >>> >
> >>> > I also tried (unsuccessfully):
> >>> > strSQL = strSQL & " AND DateProvSrv IN (SELECT DateProvSrv FROM
> >>> > ServicesProvided WHERE DateProvSrv Between " & FromDate & " And " &
> >> ToDate
> >>> > &
> >>> > " AND NameID = " & ClientID & ")"
> >>> >
> >>> > Could sure use some help.
> >>> > Thank you,
> >>> > Mark
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>