General discussion

Locked

SQL within Access

By john_wills ·
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.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL within Access

by nikki96 In reply to SQL within Access

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...

Collapse -

SQL within Access

by john_wills In reply to SQL within Access

Poster rated this answer

Collapse -

SQL within Access

by Chris.Lewis In reply to SQL within Access

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;

Collapse -

SQL within Access

by john_wills In reply to SQL within Access

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

Collapse -

SQL within Access

by adel_ibrahim In reply to SQL within Access

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;

Collapse -

SQL within Access

by john_wills In reply to SQL within Access

This is effectively a duplicate of the B of A person's suggestion.

Collapse -

SQL within Access

by john_wills In reply to SQL within Access

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums