|
 |
access thread: Checkboxes
Message #1 by msavoy@h... on Mon, 21 May 2001 14:48:26
|
|
I am creating a report from a query that I created within Access2000.
However, I've learned that I cannot use values to determine the value of a
checkbox but can only use bit values. Can someone help me by telling me
what the appropriate syntax is for adding a CASE statement to a SQL
statement within Access? Please not the SELECT CASE subquery that I tried
to add but am getting a SYNTX ERROR from Access.
Thanks in advance for any help or direction. Here is my code:
SELECT DISTINCT [THR Non_CG_Physicians].HD_ID,
dbo_Organization.Organization, [THR Non_CG_Physicians].LastName,
[THR Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].MiddleName,
[THR Non_CG_Physicians].DegreeCode,
[THR Non_CG_Physicians].PrimaryOfcAddr1Text,
[THR Non_CG_Physicians].PrimaryOfcAddr2Text,
[THR Non_CG_Physicians].PrimaryOfcCityName,
[THR Non_CG_Physicians].PrimaryOfcStateCode,
[THR Non_CG_Physicians].PrimaryOfcZipCode,
[THR Non_CG_Physicians].PrimaryOfcPhnNum,
[THR Non_CG_Physicians].PrimaryOfcFaxNum,
[THR Non_CG_Physicians].EmailAddrText,
[THR Non_CG_Physicians].SpecltyDescr, dbo_Service_Components.ServiceID,
dbo_Service.Service,
(SELECT CASE [THR Non_CG_Physicians].EnterprsFacilityCode WHEN "amh"
then "1" else "0")
FROM (([THR Non_CG_Physicians] INNER JOIN dbo_Customer ON [THR
Non_CG_Physicians].HD_ID = dbo_Customer.ClientID) INNER JOIN
(dbo_Service_Components INNER JOIN dbo_Service ON
dbo_Service_Components.ServiceID = dbo_Service.Serviceid) ON
dbo_Customer.ClientID = dbo_Service_Components.ClientID) INNER JOIN
dbo_Organization ON dbo_Customer.OrgID = dbo_Organization.OrgID
WHERE (((dbo_Service_Components.ServiceID)=110) AND (([THR
Non_CG_Physicians].EnterprsFacilityCode)="amh"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="amsmc"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccfw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccsp"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmec"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmfw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmheb"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmnw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmsw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmwal"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="mrmc"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="pha"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phc"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phd"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phg"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phk"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="php"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phvn"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phw"))
ORDER BY [THR Non_CG_Physicians].LastName, [THR
Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].EnterprsFacilityCode;
Message #2 by "Simon Woollard" <simon.woollard@b...> on Mon, 21 May 2001 15:53:09 +0100
|
|
Hi:
The JET engine doesn't support T-SQL like you have done.
What you need is the conditional IF function, IIF -
Replace:
(SELECT CASE [THR Non_CG_Physicians].EnterprsFacilityCode WHEN "amh"
then "1" else "0")
With:
IIF([THR Non_CG_Physicians].EnterprsFacilityCode = "amh", 1, 0)
It should work.
Cheers,
Simon
-----Original Message-----
From: msavoy@h... [mailto:msavoy@h...]
Sent: 21 May 2001 14:48
To: Access
Subject: [access] Checkboxes
I am creating a report from a query that I created within Access2000.
However, I've learned that I cannot use values to determine the value of a
checkbox but can only use bit values. Can someone help me by telling me
what the appropriate syntax is for adding a CASE statement to a SQL
statement within Access? Please not the SELECT CASE subquery that I tried
to add but am getting a SYNTX ERROR from Access.
Thanks in advance for any help or direction. Here is my code:
SELECT DISTINCT [THR Non_CG_Physicians].HD_ID,
dbo_Organization.Organization, [THR Non_CG_Physicians].LastName,
[THR Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].MiddleName,
[THR Non_CG_Physicians].DegreeCode,
[THR Non_CG_Physicians].PrimaryOfcAddr1Text,
[THR Non_CG_Physicians].PrimaryOfcAddr2Text,
[THR Non_CG_Physicians].PrimaryOfcCityName,
[THR Non_CG_Physicians].PrimaryOfcStateCode,
[THR Non_CG_Physicians].PrimaryOfcZipCode,
[THR Non_CG_Physicians].PrimaryOfcPhnNum,
[THR Non_CG_Physicians].PrimaryOfcFaxNum,
[THR Non_CG_Physicians].EmailAddrText,
[THR Non_CG_Physicians].SpecltyDescr, dbo_Service_Components.ServiceID,
dbo_Service.Service,
(SELECT CASE [THR Non_CG_Physicians].EnterprsFacilityCode WHEN "amh"
then "1" else "0")
FROM (([THR Non_CG_Physicians] INNER JOIN dbo_Customer ON [THR
Non_CG_Physicians].HD_ID = dbo_Customer.ClientID) INNER JOIN
(dbo_Service_Components INNER JOIN dbo_Service ON
dbo_Service_Components.ServiceID = dbo_Service.Serviceid) ON
dbo_Customer.ClientID = dbo_Service_Components.ClientID) INNER JOIN
dbo_Organization ON dbo_Customer.OrgID = dbo_Organization.OrgID
WHERE (((dbo_Service_Components.ServiceID)=110) AND (([THR
Non_CG_Physicians].EnterprsFacilityCode)="amh"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="amsmc"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccfw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccsp"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmec"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmfw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmheb"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmnw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmsw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmwal"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="mrmc"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="pha"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phc"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phd"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phg"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phk"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="php"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phvn"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phw"))
ORDER BY [THR Non_CG_Physicians].LastName, [THR
Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].EnterprsFacilityCode;
Message #3 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 21 May 2001 08:25:08 -0700
|
|
You can use the primitive values TRUE and FALSE as a more readable
substitute for the literal values of -1 and 0.
As for getting SELECT CASE type functionality in jet SQL, have a look at the
IIf(), Switch() and Choose() functions. If I understand the bit you've
written below, you can use CBool() to convert a logical expression to it's
boolean value, e.g.,
CBool([THR Non_CG_Physicians].EnterprsFacilityCode = "amh")
Gratuitous advice: consider using the In() operator instead of your long
list of OR's below:
[THR Non_CG_Physicians].EnterprsFacilityCode) in ("amh", "amsmc", "hccfw",
"hccsp", ...)
HTH,
-Roy
-----Original Message-----
From: msavoy@h... [mailto:msavoy@h...]
Sent: Monday, May 21, 2001 7:47 AM
To: Access
Subject: [access] Checkboxes
I am creating a report from a query that I created within Access2000.
However, I've learned that I cannot use values to determine the value of a
checkbox but can only use bit values. Can someone help me by telling me
what the appropriate syntax is for adding a CASE statement to a SQL
statement within Access? Please not the SELECT CASE subquery that I tried
to add but am getting a SYNTX ERROR from Access.
Thanks in advance for any help or direction. Here is my code:
SELECT DISTINCT [THR Non_CG_Physicians].HD_ID,
dbo_Organization.Organization, [THR Non_CG_Physicians].LastName,
[THR Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].MiddleName,
[THR Non_CG_Physicians].DegreeCode,
[THR Non_CG_Physicians].PrimaryOfcAddr1Text,
[THR Non_CG_Physicians].PrimaryOfcAddr2Text,
[THR Non_CG_Physicians].PrimaryOfcCityName,
[THR Non_CG_Physicians].PrimaryOfcStateCode,
[THR Non_CG_Physicians].PrimaryOfcZipCode,
[THR Non_CG_Physicians].PrimaryOfcPhnNum,
[THR Non_CG_Physicians].PrimaryOfcFaxNum,
[THR Non_CG_Physicians].EmailAddrText,
[THR Non_CG_Physicians].SpecltyDescr, dbo_Service_Components.ServiceID,
dbo_Service.Service,
(SELECT CASE [THR Non_CG_Physicians].EnterprsFacilityCode WHEN "amh"
then "1" else "0")
FROM (([THR Non_CG_Physicians] INNER JOIN dbo_Customer ON [THR
Non_CG_Physicians].HD_ID = dbo_Customer.ClientID) INNER JOIN
(dbo_Service_Components INNER JOIN dbo_Service ON
dbo_Service_Components.ServiceID = dbo_Service.Serviceid) ON
dbo_Customer.ClientID = dbo_Service_Components.ClientID) INNER JOIN
dbo_Organization ON dbo_Customer.OrgID = dbo_Organization.OrgID
WHERE (((dbo_Service_Components.ServiceID)=110) AND (([THR
Non_CG_Physicians].EnterprsFacilityCode)="amh"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="amsmc"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccfw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccsp"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmec"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmfw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmheb"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmnw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmsw"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmwal"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="mrmc"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="pha"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phc"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phd"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phg"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phk"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="php"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phvn"
Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phw"))
ORDER BY [THR Non_CG_Physicians].LastName, [THR
Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].EnterprsFacilityCode;
Message #4 by msavoy@h... on Mon, 21 May 2001 20:43:50
|
|
Thanks to both of you for your help. Your suggestions worked. But if you
guys do not mind answering one more question. As I stated below, I have
created this report to pull from a query. The report has multiple
checkboxes on one page that should be checked depending on the facility
field where a doctor can practice. Currently my query displays a record
for each facility he/she can practice at and I am trying to get it to one
field. How can I do this so that all the checkboxes are checked on one
report not a report for each record? Any suggestions would be
appreciated.
Thank you.
> You can use the primitive values TRUE and FALSE as a more readable
> substitute for the literal values of -1 and 0.
>
> As for getting SELECT CASE type functionality in jet SQL, have a look at
the
> IIf(), Switch() and Choose() functions. If I understand the bit you've
> written below, you can use CBool() to convert a logical expression to
it's
> boolean value, e.g.,
>
> CBool([THR Non_CG_Physicians].EnterprsFacilityCode = "amh")
>
> Gratuitous advice: consider using the In() operator instead of your long
> list of OR's below:
> [THR Non_CG_Physicians].EnterprsFacilityCode) in
("amh", "amsmc", "hccfw",
> "hccsp", ...)
>
> HTH,
>
> -Roy
>
> -----Original Message-----
> From: msavoy@h... [mailto:msavoy@h...]
> Sent: Monday, May 21, 2001 7:47 AM
> To: Access
> Subject: [access] Checkboxes
>
>
> I am creating a report from a query that I created within Access2000.
> However, I've learned that I cannot use values to determine the value of
a
> checkbox but can only use bit values. Can someone help me by telling me
> what the appropriate syntax is for adding a CASE statement to a SQL
> statement within Access? Please not the SELECT CASE subquery that I
tried
> to add but am getting a SYNTX ERROR from Access.
> Thanks in advance for any help or direction. Here is my code:
>
> SELECT DISTINCT [THR Non_CG_Physicians].HD_ID,
> dbo_Organization.Organization, [THR Non_CG_Physicians].LastName,
> [THR Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].MiddleName,
> [THR Non_CG_Physicians].DegreeCode,
> [THR Non_CG_Physicians].PrimaryOfcAddr1Text,
> [THR Non_CG_Physicians].PrimaryOfcAddr2Text,
> [THR Non_CG_Physicians].PrimaryOfcCityName,
> [THR Non_CG_Physicians].PrimaryOfcStateCode,
> [THR Non_CG_Physicians].PrimaryOfcZipCode,
> [THR Non_CG_Physicians].PrimaryOfcPhnNum,
> [THR Non_CG_Physicians].PrimaryOfcFaxNum,
> [THR Non_CG_Physicians].EmailAddrText,
> [THR Non_CG_Physicians].SpecltyDescr, dbo_Service_Components.ServiceID,
> dbo_Service.Service,
> (SELECT CASE [THR Non_CG_Physicians].EnterprsFacilityCode WHEN "amh"
> then "1" else "0")
>
> FROM (([THR Non_CG_Physicians] INNER JOIN dbo_Customer ON [THR
> Non_CG_Physicians].HD_ID = dbo_Customer.ClientID) INNER JOIN
> (dbo_Service_Components INNER JOIN dbo_Service ON
> dbo_Service_Components.ServiceID = dbo_Service.Serviceid) ON
> dbo_Customer.ClientID = dbo_Service_Components.ClientID) INNER JOIN
> dbo_Organization ON dbo_Customer.OrgID = dbo_Organization.OrgID
>
> WHERE (((dbo_Service_Components.ServiceID)=110) AND (([THR
> Non_CG_Physicians].EnterprsFacilityCode)="amh"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="amsmc"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccfw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccsp"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmec"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmfw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmheb"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmnw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmsw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmwal"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="mrmc"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="pha"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phc"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phd"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phg"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phk"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="php"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phvn"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phw"))
>
> ORDER BY [THR Non_CG_Physicians].LastName, [THR
> Non_CG_Physicians].FirstName, [THR
Non_CG_Physicians].EnterprsFacilityCode;
Message #5 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 21 May 2001 14:02:28 -0700
|
|
I'm not totally sure I understand what you're trying to do here, but it
sounds like you want a cross-tab query--practitioner names down the side of
the page, facility codes accross the top of the page and yes/no that doc can
practice in that facility in the cells. If that's right, try the cross-tab
query wizard.
HTH,
-Roy
-----Original Message-----
From: msavoy@h... [mailto:msavoy@h...]
Sent: Monday, May 21, 2001 1:43 PM
To: Access
Subject: [access] RE: Checkboxes
Thanks to both of you for your help. Your suggestions worked. But if you
guys do not mind answering one more question. As I stated below, I have
created this report to pull from a query. The report has multiple
checkboxes on one page that should be checked depending on the facility
field where a doctor can practice. Currently my query displays a record
for each facility he/she can practice at and I am trying to get it to one
field. How can I do this so that all the checkboxes are checked on one
report not a report for each record? Any suggestions would be
appreciated.
Thank you.
> You can use the primitive values TRUE and FALSE as a more readable
> substitute for the literal values of -1 and 0.
>
> As for getting SELECT CASE type functionality in jet SQL, have a look at
the
> IIf(), Switch() and Choose() functions. If I understand the bit you've
> written below, you can use CBool() to convert a logical expression to
it's
> boolean value, e.g.,
>
> CBool([THR Non_CG_Physicians].EnterprsFacilityCode = "amh")
>
> Gratuitous advice: consider using the In() operator instead of your long
> list of OR's below:
> [THR Non_CG_Physicians].EnterprsFacilityCode) in
("amh", "amsmc", "hccfw",
> "hccsp", ...)
>
> HTH,
>
> -Roy
>
> -----Original Message-----
> From: msavoy@h... [mailto:msavoy@h...]
> Sent: Monday, May 21, 2001 7:47 AM
> To: Access
> Subject: [access] Checkboxes
>
>
> I am creating a report from a query that I created within Access2000.
> However, I've learned that I cannot use values to determine the value of
a
> checkbox but can only use bit values. Can someone help me by telling me
> what the appropriate syntax is for adding a CASE statement to a SQL
> statement within Access? Please not the SELECT CASE subquery that I
tried
> to add but am getting a SYNTX ERROR from Access.
> Thanks in advance for any help or direction. Here is my code:
>
> SELECT DISTINCT [THR Non_CG_Physicians].HD_ID,
> dbo_Organization.Organization, [THR Non_CG_Physicians].LastName,
> [THR Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].MiddleName,
> [THR Non_CG_Physicians].DegreeCode,
> [THR Non_CG_Physicians].PrimaryOfcAddr1Text,
> [THR Non_CG_Physicians].PrimaryOfcAddr2Text,
> [THR Non_CG_Physicians].PrimaryOfcCityName,
> [THR Non_CG_Physicians].PrimaryOfcStateCode,
> [THR Non_CG_Physicians].PrimaryOfcZipCode,
> [THR Non_CG_Physicians].PrimaryOfcPhnNum,
> [THR Non_CG_Physicians].PrimaryOfcFaxNum,
> [THR Non_CG_Physicians].EmailAddrText,
> [THR Non_CG_Physicians].SpecltyDescr, dbo_Service_Components.ServiceID,
> dbo_Service.Service,
> (SELECT CASE [THR Non_CG_Physicians].EnterprsFacilityCode WHEN "amh"
> then "1" else "0")
>
> FROM (([THR Non_CG_Physicians] INNER JOIN dbo_Customer ON [THR
> Non_CG_Physicians].HD_ID = dbo_Customer.ClientID) INNER JOIN
> (dbo_Service_Components INNER JOIN dbo_Service ON
> dbo_Service_Components.ServiceID = dbo_Service.Serviceid) ON
> dbo_Customer.ClientID = dbo_Service_Components.ClientID) INNER JOIN
> dbo_Organization ON dbo_Customer.OrgID = dbo_Organization.OrgID
>
> WHERE (((dbo_Service_Components.ServiceID)=110) AND (([THR
> Non_CG_Physicians].EnterprsFacilityCode)="amh"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="amsmc"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccfw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccsp"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmec"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmfw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmheb"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmnw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmsw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmwal"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="mrmc"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="pha"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phc"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phd"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phg"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phk"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="php"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phvn"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phw"))
>
> ORDER BY [THR Non_CG_Physicians].LastName, [THR
> Non_CG_Physicians].FirstName, [THR
Non_CG_Physicians].EnterprsFacilityCode;
Message #6 by "Simon Woollard" <simon.woollard@b...> on Mon, 21 May 2001 22:58:55 +0100
|
|
I concur with Roy's suggestion regarding the Crosstab option.
Regards,
Simon
-----Original Message-----
From: msavoy@h... [mailto:msavoy@h...]
Sent: 21 May 2001 20:44
To: Access
Subject: [access] RE: Checkboxes
Thanks to both of you for your help. Your suggestions worked. But if you
guys do not mind answering one more question. As I stated below, I have
created this report to pull from a query. The report has multiple
checkboxes on one page that should be checked depending on the facility
field where a doctor can practice. Currently my query displays a record
for each facility he/she can practice at and I am trying to get it to one
field. How can I do this so that all the checkboxes are checked on one
report not a report for each record? Any suggestions would be
appreciated.
Thank you.
> You can use the primitive values TRUE and FALSE as a more readable
> substitute for the literal values of -1 and 0.
>
> As for getting SELECT CASE type functionality in jet SQL, have a look at
the
> IIf(), Switch() and Choose() functions. If I understand the bit you've
> written below, you can use CBool() to convert a logical expression to
it's
> boolean value, e.g.,
>
> CBool([THR Non_CG_Physicians].EnterprsFacilityCode = "amh")
>
> Gratuitous advice: consider using the In() operator instead of your long
> list of OR's below:
> [THR Non_CG_Physicians].EnterprsFacilityCode) in
("amh", "amsmc", "hccfw",
> "hccsp", ...)
>
> HTH,
>
> -Roy
>
> -----Original Message-----
> From: msavoy@h... [mailto:msavoy@h...]
> Sent: Monday, May 21, 2001 7:47 AM
> To: Access
> Subject: [access] Checkboxes
>
>
> I am creating a report from a query that I created within Access2000.
> However, I've learned that I cannot use values to determine the value of
a
> checkbox but can only use bit values. Can someone help me by telling me
> what the appropriate syntax is for adding a CASE statement to a SQL
> statement within Access? Please not the SELECT CASE subquery that I
tried
> to add but am getting a SYNTX ERROR from Access.
> Thanks in advance for any help or direction. Here is my code:
>
> SELECT DISTINCT [THR Non_CG_Physicians].HD_ID,
> dbo_Organization.Organization, [THR Non_CG_Physicians].LastName,
> [THR Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].MiddleName,
> [THR Non_CG_Physicians].DegreeCode,
> [THR Non_CG_Physicians].PrimaryOfcAddr1Text,
> [THR Non_CG_Physicians].PrimaryOfcAddr2Text,
> [THR Non_CG_Physicians].PrimaryOfcCityName,
> [THR Non_CG_Physicians].PrimaryOfcStateCode,
> [THR Non_CG_Physicians].PrimaryOfcZipCode,
> [THR Non_CG_Physicians].PrimaryOfcPhnNum,
> [THR Non_CG_Physicians].PrimaryOfcFaxNum,
> [THR Non_CG_Physicians].EmailAddrText,
> [THR Non_CG_Physicians].SpecltyDescr, dbo_Service_Components.ServiceID,
> dbo_Service.Service,
> (SELECT CASE [THR Non_CG_Physicians].EnterprsFacilityCode WHEN "amh"
> then "1" else "0")
>
> FROM (([THR Non_CG_Physicians] INNER JOIN dbo_Customer ON [THR
> Non_CG_Physicians].HD_ID = dbo_Customer.ClientID) INNER JOIN
> (dbo_Service_Components INNER JOIN dbo_Service ON
> dbo_Service_Components.ServiceID = dbo_Service.Serviceid) ON
> dbo_Customer.ClientID = dbo_Service_Components.ClientID) INNER JOIN
> dbo_Organization ON dbo_Customer.OrgID = dbo_Organization.OrgID
>
> WHERE (((dbo_Service_Components.ServiceID)=110) AND (([THR
> Non_CG_Physicians].EnterprsFacilityCode)="amh"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="amsmc"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccfw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccsp"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmec"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmfw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmheb"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmnw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmsw"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmwal"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="mrmc"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="pha"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phc"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phd"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phg"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phk"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="php"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phvn"
> Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phw"))
>
> ORDER BY [THR Non_CG_Physicians].LastName, [THR
> Non_CG_Physicians].FirstName, [THR
Non_CG_Physicians].EnterprsFacilityCode;
Message #7 by msavoy@h... on Tue, 22 May 2001 15:39:05
|
|
Gentlemen,
Thank you both very much for your time. This worked great. Other than
slow as molasses but that is another issue.
Have a great day.
> I concur with Roy's suggestion regarding the Crosstab option.
>
> Regards,
> Simon
>
> -----Original Message-----
> From: msavoy@h... [mailto:msavoy@h...]
> Sent: 21 May 2001 20:44
> To: Access
> Subject: [access] RE: Checkboxes
>
>
> Thanks to both of you for your help. Your suggestions worked. But if
you
> guys do not mind answering one more question. As I stated below, I have
> created this report to pull from a query. The report has multiple
> checkboxes on one page that should be checked depending on the facility
> field where a doctor can practice. Currently my query displays a record
> for each facility he/she can practice at and I am trying to get it to
one
> field. How can I do this so that all the checkboxes are checked on one
> report not a report for each record? Any suggestions would be
> appreciated.
>
> Thank you.
>
>
> > You can use the primitive values TRUE and FALSE as a more readable
> > substitute for the literal values of -1 and 0.
> >
> > As for getting SELECT CASE type functionality in jet SQL, have a look
at
> the
> > IIf(), Switch() and Choose() functions. If I understand the bit you've
> > written below, you can use CBool() to convert a logical expression to
> it's
> > boolean value, e.g.,
> >
> > CBool([THR Non_CG_Physicians].EnterprsFacilityCode = "amh")
> >
> > Gratuitous advice: consider using the In() operator instead of your
long
> > list of OR's below:
> > [THR Non_CG_Physicians].EnterprsFacilityCode) in
> ("amh", "amsmc", "hccfw",
> > "hccsp", ...)
> >
> > HTH,
> >
> > -Roy
> >
> > -----Original Message-----
> > From: msavoy@h... [mailto:msavoy@h...]
> > Sent: Monday, May 21, 2001 7:47 AM
> > To: Access
> > Subject: [access] Checkboxes
> >
> >
> > I am creating a report from a query that I created within Access2000.
> > However, I've learned that I cannot use values to determine the value
of
> a
> > checkbox but can only use bit values. Can someone help me by telling
me
> > what the appropriate syntax is for adding a CASE statement to a SQL
> > statement within Access? Please not the SELECT CASE subquery that I
> tried
> > to add but am getting a SYNTX ERROR from Access.
> > Thanks in advance for any help or direction. Here is my code:
> >
> > SELECT DISTINCT [THR Non_CG_Physicians].HD_ID,
> > dbo_Organization.Organization, [THR Non_CG_Physicians].LastName,
> > [THR Non_CG_Physicians].FirstName, [THR Non_CG_Physicians].MiddleName,
> > [THR Non_CG_Physicians].DegreeCode,
> > [THR Non_CG_Physicians].PrimaryOfcAddr1Text,
> > [THR Non_CG_Physicians].PrimaryOfcAddr2Text,
> > [THR Non_CG_Physicians].PrimaryOfcCityName,
> > [THR Non_CG_Physicians].PrimaryOfcStateCode,
> > [THR Non_CG_Physicians].PrimaryOfcZipCode,
> > [THR Non_CG_Physicians].PrimaryOfcPhnNum,
> > [THR Non_CG_Physicians].PrimaryOfcFaxNum,
> > [THR Non_CG_Physicians].EmailAddrText,
> > [THR Non_CG_Physicians].SpecltyDescr,
dbo_Service_Components.ServiceID,
> > dbo_Service.Service,
> > (SELECT CASE [THR Non_CG_Physicians].EnterprsFacilityCode WHEN "amh"
> > then "1" else "0")
> >
> > FROM (([THR Non_CG_Physicians] INNER JOIN dbo_Customer ON [THR
> > Non_CG_Physicians].HD_ID = dbo_Customer.ClientID) INNER JOIN
> > (dbo_Service_Components INNER JOIN dbo_Service ON
> > dbo_Service_Components.ServiceID = dbo_Service.Serviceid) ON
> > dbo_Customer.ClientID = dbo_Service_Components.ClientID) INNER JOIN
> > dbo_Organization ON dbo_Customer.OrgID = dbo_Organization.OrgID
> >
> > WHERE (((dbo_Service_Components.ServiceID)=110) AND (([THR
> > Non_CG_Physicians].EnterprsFacilityCode)="amh"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="amsmc"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccfw"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hccsp"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmec"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmfw"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmheb"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmnw"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmsw"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="hmwal"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="mrmc"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="pha"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phc"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phd"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phg"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phk"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="php"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phvn"
> > Or ([THR Non_CG_Physicians].EnterprsFacilityCode)="phw"))
> >
> > ORDER BY [THR Non_CG_Physicians].LastName, [THR
> > Non_CG_Physicians].FirstName, [THR
> Non_CG_Physicians].EnterprsFacilityCode;
|
|
|
View Cart
