SQL structure wrong?

  • Thread starter perryclisbee via AccessMonster.com
  • Start date
P

perryclisbee via AccessMonster.com

I have a table where a person will manually enter multiple patient numbers(
up to 30), a facility code and start/end dates per patient number. I am
trying to have a SQL statement pull from the entries on this form, and give
me results, but the structure isn't quite right. There are only eight
criteria lines in the query, so I thought I would need to switch to SQL to
accomodate. I copied the code directly over from the query, but there are
some IIF statements that I don't know how to restructure, and I am getting an
error stating SQl Command not properly ended (#933). Any help would be
appreciated.

Thanks,

Perry (see below for SQL code)




SELECT

SPIDER_V_CLAIM_FACT.BILLING_OFFICE,
SPIDER_V_CLAIM_FACT.DATASET_NAME,
SPIDER_V_CLAIM_FACT.PATIENT_MPI,
SPIDER_V_CLAIM_FACT.LAST_NAME,
SPIDER_V_CLAIM_FACT.FIRST_NAME,
SPIDER_V_CLAIM_FACT.INSURANCE_CODE,
SPIDER_V_CLAIM_FACT.INSURANCE_NAME,
SPIDER_V_CLAIM_FACT.FACILITY_CODE,
SPIDER_V_CLAIM_FACT.FACILITY_NAME,
SPIDER_V_CLAIM_FACT.SERVICE_DATE

FROM SPIDER_V_CLAIM_FACT

IIf(InStr([spider_v_claim_fact.claim_no],"-")=0,[spider_v_claim_fact.claim_no]
,Mid([spider_v_claim_fact.claim_no],1,InStr([spider_v_claim_fact.claim_no],"-
")-1)) AS [CLAIM NO], SPIDER_V_CLAIM_FACT.PRIMARY_SPA, Date() AS [Report Date]
, IIf([Voided]="","Karla","Karla") AS [Drap Span]



GROUP BY SPIDER_V_CLAIM_FACT.BILLING_OFFICE,
SPIDER_V_CLAIM_FACT.DATASET_NAME,
SPIDER_V_CLAIM_FACT.PATIENT_MPI,
SPIDER_V_CLAIM_FACT.LAST_NAME,
SPIDER_V_CLAIM_FACT.FIRST_NAME,
SPIDER_V_CLAIM_FACT.INSURANCE_CODE,
SPIDER_V_CLAIM_FACT.INSURANCE_NAME,
SPIDER_V_CLAIM_FACT.FACILITY_CODE,
SPIDER_V_CLAIM_FACT.FACILITY_NAME,
SPIDER_V_CLAIM_FACT.SERVICE_DATE

IIf(InStr([spider_v_claim_fact.claim_no],"-")=0,[spider_v_claim_fact.claim_no]
,Mid([spider_v_claim_fact.claim_no],1,InStr([spider_v_claim_fact.claim_no],"-
")-1)), SPIDER_V_CLAIM_FACT.PRIMARY_SPA, Date(), IIf([Voided]="","Karla",
"Karla"), SPIDER_V_CLAIM_FACT.COVERAGE_TYPE, SPIDER_V_CLAIM_FACT.VOIDED

HAVING (((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient
Search_Karla]![txtMPI1]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]!
[Multi-Patient Search_Karla]![txtFacNo1]) AND ((SPIDER_V_CLAIM_FACT.
SERVICE_DATE) Between [Forms]![Multi-Patient Search_Karla]![txtStart1] And
[Forms]![Multi-Patient Search_Karla]![txtEnd1]) AND ((SPIDER_V_CLAIM_FACT.
COVERAGE_TYPE)="P") AND ((SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI2]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo2]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart2] And [Forms]![Multi-Patient
Search_Karla]![txtEnd2]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI3]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo3]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart3] And [Forms]![Multi-Patient
Search_Karla]![txtEnd3]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI4]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo4]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart4] And [Forms]![Multi-Patient
Search_Karla]![txtEnd4]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI5]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo5]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart5] And [Forms]![Multi-Patient
Search_Karla]![txtEnd5]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI6]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo6]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart6] And [Forms]![Multi-Patient
Search_Karla]![txtEnd6]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI7]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo7]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart7] And [Forms]![Multi-Patient
Search_Karla]![txtEnd7]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI8]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo8]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart8] And [Forms]![Multi-Patient
Search_Karla]![txtEnd8]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
 
S

Sylvain Lafontaine

This newsgroup is about ADP and SQL-Server and the syntaxe of T-SQL is quite
different from the syntaxe of SQL used in Access, so you're not at the right
place. You should try m.p.access.queries.

As to your problem with IIF(), you should begin with something simple. I
don't know if anyone will seriously take a look at your code because of its
size.

Also, for the fact that you want to have up to 30 values, you should ask
about how to normalize your database and solve this kind of problems in a
clean way.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


perryclisbee via AccessMonster.com said:
I have a table where a person will manually enter multiple patient numbers(
up to 30), a facility code and start/end dates per patient number. I am
trying to have a SQL statement pull from the entries on this form, and
give
me results, but the structure isn't quite right. There are only eight
criteria lines in the query, so I thought I would need to switch to SQL to
accomodate. I copied the code directly over from the query, but there are
some IIF statements that I don't know how to restructure, and I am getting
an
error stating SQl Command not properly ended (#933). Any help would be
appreciated.

Thanks,

Perry (see below for SQL code)




SELECT

SPIDER_V_CLAIM_FACT.BILLING_OFFICE,
SPIDER_V_CLAIM_FACT.DATASET_NAME,
SPIDER_V_CLAIM_FACT.PATIENT_MPI,
SPIDER_V_CLAIM_FACT.LAST_NAME,
SPIDER_V_CLAIM_FACT.FIRST_NAME,
SPIDER_V_CLAIM_FACT.INSURANCE_CODE,
SPIDER_V_CLAIM_FACT.INSURANCE_NAME,
SPIDER_V_CLAIM_FACT.FACILITY_CODE,
SPIDER_V_CLAIM_FACT.FACILITY_NAME,
SPIDER_V_CLAIM_FACT.SERVICE_DATE

FROM SPIDER_V_CLAIM_FACT

IIf(InStr([spider_v_claim_fact.claim_no],"-")=0,[spider_v_claim_fact.claim_no]
,Mid([spider_v_claim_fact.claim_no],1,InStr([spider_v_claim_fact.claim_no],"-
")-1)) AS [CLAIM NO], SPIDER_V_CLAIM_FACT.PRIMARY_SPA, Date() AS [Report
Date]
, IIf([Voided]="","Karla","Karla") AS [Drap Span]



GROUP BY SPIDER_V_CLAIM_FACT.BILLING_OFFICE,
SPIDER_V_CLAIM_FACT.DATASET_NAME,
SPIDER_V_CLAIM_FACT.PATIENT_MPI,
SPIDER_V_CLAIM_FACT.LAST_NAME,
SPIDER_V_CLAIM_FACT.FIRST_NAME,
SPIDER_V_CLAIM_FACT.INSURANCE_CODE,
SPIDER_V_CLAIM_FACT.INSURANCE_NAME,
SPIDER_V_CLAIM_FACT.FACILITY_CODE,
SPIDER_V_CLAIM_FACT.FACILITY_NAME,
SPIDER_V_CLAIM_FACT.SERVICE_DATE

IIf(InStr([spider_v_claim_fact.claim_no],"-")=0,[spider_v_claim_fact.claim_no]
,Mid([spider_v_claim_fact.claim_no],1,InStr([spider_v_claim_fact.claim_no],"-
")-1)), SPIDER_V_CLAIM_FACT.PRIMARY_SPA, Date(), IIf([Voided]="","Karla",
"Karla"), SPIDER_V_CLAIM_FACT.COVERAGE_TYPE, SPIDER_V_CLAIM_FACT.VOIDED

HAVING (((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient
Search_Karla]![txtMPI1]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]!
[Multi-Patient Search_Karla]![txtFacNo1]) AND ((SPIDER_V_CLAIM_FACT.
SERVICE_DATE) Between [Forms]![Multi-Patient Search_Karla]![txtStart1] And
[Forms]![Multi-Patient Search_Karla]![txtEnd1]) AND ((SPIDER_V_CLAIM_FACT.
COVERAGE_TYPE)="P") AND ((SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI2]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo2]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart2] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd2]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI3]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo3]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart3] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd3]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI4]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo4]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart4] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd4]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI5]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo5]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart5] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd5]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI6]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo6]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart6] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd6]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI7]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo7]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart7] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd7]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI8]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo8]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart8] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd8]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
Click to expand...
 
A

Aaron Kempf

if you've got a comma seperated list of patient numbers, you can google for
a TSQL SPLIT function that will give you what you're looking for



perryclisbee via AccessMonster.com said:
I have a table where a person will manually enter multiple patient numbers(
up to 30), a facility code and start/end dates per patient number. I am
trying to have a SQL statement pull from the entries on this form, and give
me results, but the structure isn't quite right. There are only eight
criteria lines in the query, so I thought I would need to switch to SQL to
accomodate. I copied the code directly over from the query, but there are
some IIF statements that I don't know how to restructure, and I am getting an
error stating SQl Command not properly ended (#933). Any help would be
appreciated.

Thanks,

Perry (see below for SQL code)




SELECT

SPIDER_V_CLAIM_FACT.BILLING_OFFICE,
SPIDER_V_CLAIM_FACT.DATASET_NAME,
SPIDER_V_CLAIM_FACT.PATIENT_MPI,
SPIDER_V_CLAIM_FACT.LAST_NAME,
SPIDER_V_CLAIM_FACT.FIRST_NAME,
SPIDER_V_CLAIM_FACT.INSURANCE_CODE,
SPIDER_V_CLAIM_FACT.INSURANCE_NAME,
SPIDER_V_CLAIM_FACT.FACILITY_CODE,
SPIDER_V_CLAIM_FACT.FACILITY_NAME,
SPIDER_V_CLAIM_FACT.SERVICE_DATE

FROM SPIDER_V_CLAIM_FACT

IIf(InStr([spider_v_claim_fact.claim_no],"-")=0,[spider_v_claim_fact.claim_n
o],Mid([spider_v_claim_fact.claim_no],1,InStr([spider_v_claim_fact.claim_no],"
-
")-1)) AS [CLAIM NO], SPIDER_V_CLAIM_FACT.PRIMARY_SPA, Date() AS [Report Date]
, IIf([Voided]="","Karla","Karla") AS [Drap Span]



GROUP BY SPIDER_V_CLAIM_FACT.BILLING_OFFICE,
SPIDER_V_CLAIM_FACT.DATASET_NAME,
SPIDER_V_CLAIM_FACT.PATIENT_MPI,
SPIDER_V_CLAIM_FACT.LAST_NAME,
SPIDER_V_CLAIM_FACT.FIRST_NAME,
SPIDER_V_CLAIM_FACT.INSURANCE_CODE,
SPIDER_V_CLAIM_FACT.INSURANCE_NAME,
SPIDER_V_CLAIM_FACT.FACILITY_CODE,
SPIDER_V_CLAIM_FACT.FACILITY_NAME,
SPIDER_V_CLAIM_FACT.SERVICE_DATE

IIf(InStr([spider_v_claim_fact.claim_no],"-")=0,[spider_v_claim_fact.claim_n
o],Mid([spider_v_claim_fact.claim_no],1,InStr([spider_v_claim_fact.claim_no],"
-
")-1)), SPIDER_V_CLAIM_FACT.PRIMARY_SPA, Date(), IIf([Voided]="","Karla",
"Karla"), SPIDER_V_CLAIM_FACT.COVERAGE_TYPE, SPIDER_V_CLAIM_FACT.VOIDED

HAVING (((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient
Search_Karla]![txtMPI1]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]!
[Multi-Patient Search_Karla]![txtFacNo1]) AND ((SPIDER_V_CLAIM_FACT.
SERVICE_DATE) Between [Forms]![Multi-Patient Search_Karla]![txtStart1] And
[Forms]![Multi-Patient Search_Karla]![txtEnd1]) AND ((SPIDER_V_CLAIM_FACT.
COVERAGE_TYPE)="P") AND ((SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI2]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo2]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart2] And [Forms]![Multi-Patient
Search_Karla]![txtEnd2]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI3]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo3]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart3] And [Forms]![Multi-Patient
Search_Karla]![txtEnd3]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI4]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo4]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart4] And [Forms]![Multi-Patient
Search_Karla]![txtEnd4]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI5]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo5]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart5] And [Forms]![Multi-Patient
Search_Karla]![txtEnd5]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI6]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo6]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart6] And [Forms]![Multi-Patient
Search_Karla]![txtEnd6]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI7]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo7]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart7] And [Forms]![Multi-Patient
Search_Karla]![txtEnd7]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI8]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo8]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart8] And [Forms]![Multi-Patient
Search_Karla]![txtEnd8]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND (
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
Click to expand...
 
S

Susie Johnson

CORRECTION!

you shoudl stick with ADP and learn CASE WHEN THEN syntax



Sylvain Lafontaine said:
This newsgroup is about ADP and SQL-Server and the syntaxe of T-SQL is quite
different from the syntaxe of SQL used in Access, so you're not at the right
place. You should try m.p.access.queries.

As to your problem with IIF(), you should begin with something simple. I
don't know if anyone will seriously take a look at your code because of its
size.

Also, for the fact that you want to have up to 30 values, you should ask
about how to normalize your database and solve this kind of problems in a
clean way.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


perryclisbee via AccessMonster.com said:
I have a table where a person will manually enter multiple patient numbers(
up to 30), a facility code and start/end dates per patient number. I am
trying to have a SQL statement pull from the entries on this form, and
give
me results, but the structure isn't quite right. There are only eight
criteria lines in the query, so I thought I would need to switch to SQL to
accomodate. I copied the code directly over from the query, but there are
some IIF statements that I don't know how to restructure, and I am getting
an
error stating SQl Command not properly ended (#933). Any help would be
appreciated.

Thanks,

Perry (see below for SQL code)




SELECT

SPIDER_V_CLAIM_FACT.BILLING_OFFICE,
SPIDER_V_CLAIM_FACT.DATASET_NAME,
SPIDER_V_CLAIM_FACT.PATIENT_MPI,
SPIDER_V_CLAIM_FACT.LAST_NAME,
SPIDER_V_CLAIM_FACT.FIRST_NAME,
SPIDER_V_CLAIM_FACT.INSURANCE_CODE,
SPIDER_V_CLAIM_FACT.INSURANCE_NAME,
SPIDER_V_CLAIM_FACT.FACILITY_CODE,
SPIDER_V_CLAIM_FACT.FACILITY_NAME,
SPIDER_V_CLAIM_FACT.SERVICE_DATE

FROM SPIDER_V_CLAIM_FACT
Click to expand...
IIf(InStr([spider_v_claim_fact.claim_no],"-")=0,[spider_v_claim_fact.claim_n
o],Mid([spider_v_claim_fact.claim_no],1,InStr([spider_v_claim_fact.claim_no],"
-
")-1)) AS [CLAIM NO], SPIDER_V_CLAIM_FACT.PRIMARY_SPA, Date() AS [Report
Date]
, IIf([Voided]="","Karla","Karla") AS [Drap Span]



GROUP BY SPIDER_V_CLAIM_FACT.BILLING_OFFICE,
SPIDER_V_CLAIM_FACT.DATASET_NAME,
SPIDER_V_CLAIM_FACT.PATIENT_MPI,
SPIDER_V_CLAIM_FACT.LAST_NAME,
SPIDER_V_CLAIM_FACT.FIRST_NAME,
SPIDER_V_CLAIM_FACT.INSURANCE_CODE,
SPIDER_V_CLAIM_FACT.INSURANCE_NAME,
SPIDER_V_CLAIM_FACT.FACILITY_CODE,
SPIDER_V_CLAIM_FACT.FACILITY_NAME,
SPIDER_V_CLAIM_FACT.SERVICE_DATE
Click to expand...
IIf(InStr([spider_v_claim_fact.claim_no],"-")=0,[spider_v_claim_fact.claim_n
o],Mid([spider_v_claim_fact.claim_no],1,InStr([spider_v_claim_fact.claim_no],"
-
")-1)), SPIDER_V_CLAIM_FACT.PRIMARY_SPA, Date(), IIf([Voided]="","Karla",
"Karla"), SPIDER_V_CLAIM_FACT.COVERAGE_TYPE, SPIDER_V_CLAIM_FACT.VOIDED

HAVING (((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient
Search_Karla]![txtMPI1]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]!
[Multi-Patient Search_Karla]![txtFacNo1]) AND ((SPIDER_V_CLAIM_FACT.
SERVICE_DATE) Between [Forms]![Multi-Patient Search_Karla]![txtStart1] And
[Forms]![Multi-Patient Search_Karla]![txtEnd1]) AND ((SPIDER_V_CLAIM_FACT.
COVERAGE_TYPE)="P") AND ((SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI2]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo2]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart2] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd2]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI3]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo3]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart3] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd3]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI4]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo4]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart4] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd4]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI5]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo5]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart5] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd5]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI6]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo6]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart6] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd6]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI7]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo7]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart7] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd7]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
OR
(((SPIDER_V_CLAIM_FACT.PATIENT_MPI)=[Forms]![Multi-Patient Search_Karla]!
[txtMPI8]) AND ((SPIDER_V_CLAIM_FACT.FACILITY_CODE)=[Forms]![Multi-Patient
Search_Karla]![txtFacNo8]) AND ((SPIDER_V_CLAIM_FACT.SERVICE_DATE) Between
[Forms]![Multi-Patient Search_Karla]![txtStart8] And
[Forms]![Multi-Patient
Search_Karla]![txtEnd8]) AND ((SPIDER_V_CLAIM_FACT.COVERAGE_TYPE)="P") AND
(
(SPIDER_V_CLAIM_FACT.VOIDED)="N"))
Click to expand...
Click to expand...
 
Top