Web Development

Ask something
Discussion Locked

SQL within Access

0 helpful
7 replies

by john_wills 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

INSERT INTO Report_Intermed ( ProvName, ProvSSN, Claim, DateOfSvc, Payment, Something )
SELECT dbo_PROVIDER.NAME, dbo_PROVIDER.SS_NUMBER, zstblClaimsAdjStatus.CLAIM_NUM, zstblClaimsAdjStatus.DATE_FROM, zstblClaimsAdjStatus.PAY_DOLL, IIf(Forms!frmProcessAdjClaims.fraMode = 1, ([VendorID] is not null AND [VendorID] > ' '),([VendorID] is null or [VendorID] = ' ')) AS CTLFI
FROM zstblClaimsAdjStatus INNER JOIN dbo_PROVIDER ON zstblClaimsAdjStatus.PROV_ID = dbo_PROVIDER.PROV_ID
WHERE CTLFI AND zstblClaimsAdjStatus.BILLED_FLG<>0
ORDER BY dbo_PROVIDER.NAME;

I am expecting CTLFI as a boolean. When I run the query from VBA in form frmProcessAdjClaims I get asked for the value of CTLFI. When I run the query by itself I get asked for fraMode, which is fair enough, but then also for CTLFI, which appears to be defined already.

7 replies

by nikki96

Access has a yes/no datatype, but I'm not sure it is a true boolean and will compare properly. Yet in this statement, you are inserting CTFL1, a boolean, into Something. Is Something defined as a Yes/No? If not, that may be the problem.

Is VendorID a field in the DB or on the form? I can't tell from this. If it is on the form, it isn't qualified properly with Forms!formname! etc and if it is in the DB, you may want to qualify with its table name. I.E zstblClaimsAdjStatus.VendorID.
If it is a variable, make sure it is spelled properly esp. with case (VendorID vendorId VendorId [Vendor ID]). Make sure it is defined. If you are using brackets because there is a space in the name, you didn't put the space there.

That's all I can see fornow...

by chris.lewis

First of all -
You can't use a calc'd field (CTLFI) in a WHERE clause...

Use:
WHERE (((IIf(Forms!frmProcessAdjClaims.fraMode=1,([VendorID] Is Not Null And [VendorID]>' '),([VendorID] Is Null Or [VendorID]=' ')))=True) AND ((zstblClaimsAdjStatus.BILLED_FLG)<>0))
ORDER BY dbo_PROVIDER.NAME;

by john_wills

May you have a long life, many children and a high pension. But I'm keeping my account with Bank One.

by adel_ibrahim

Use the follwing:

INSERT INTO Report_Intermed ( ProvName, ProvSSN, Claim, DateOfSvc, Payment, Something )
SELECT dbo_PROVIDER.NAME, dbo_PROVIDER.SS_NUMBER, zstblClaimsAdjStatus.CLAIM_NUM, zstblClaimsAdjStatus.DATE_FROM, zstblClaimsAdjStatus.PAY_DOLL, IIf([Forms]![frmProcessAdjClaims].[fraMode]=1,([VendorID] Is Not Null And [VendorID]>' '),([VendorID] Is Null Or [VendorID]=' ')) AS CTLFI
FROM zstblClaimsAdjStatus INNER JOIN dbo_PROVIDER ON zstblClaimsAdjStatus.PROV_ID = dbo_PROVIDER.PROV_ID
WHERE (((IIf([Forms]![frmProcessAdjClaims].[fraMode]=1,([VendorID] Is Not Null And [VendorID]>' '),([VendorID] Is Null Or [VendorID]=' ')))<>False) AND ((zstblClaimsAdjStatus.BILLED_FLG)<>0))
ORDER BY dbo_PROVIDER.NAME;

This discussion is closed to new replies.