WHERE clause

M

Mark

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
 
K

Ken Snell [MVP]

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 said:
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
Click to expand...
 
M

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 said:
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 said:
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
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

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 said:
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 said:
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 said:
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/



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
Click to expand...
Click to expand...
Click to expand...
 
M

Mark

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

Ken Snell said:
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 said:
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 said:
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

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/



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form-Entered Parameters for Pass-thru query 1
Split Forms and ADP issues 2

Top