need to create a query result and cannot determine correct syntax

[Randy]

I have two tables
TblData
TblG4Lookup

TBLData
14 columns, but really only two column matters
PSID
TBTP

TblG4Lookup
paycode (key field)
G4 (all records have a value)
BalanceSheet (only about 10% of the records have values)

The values in TblData fields PSID&TBTP are the same values as in TblG4Lookup field paycode i.e. Tbldata has the information in two fields, and TblG4Lookup has the combined value in one field. This is not an issue for me, I can handle this.

It seems simple but but I cannot figure out how to "check" the field BalanceSheet and if a value is there return that value, if not return the value in field G4

The requirement:
Take the PSID&TBTP value in TblData and look for the Paycode in TblG4Lookup. If there is a value in BalanceSheet then return that value, if NO value in field balanceSheet (i.e. null/empty) return the value in field G4

The basic SQL statement is this

SELECT TblData.psid, TblData.tbtp, tblG4Lookup.paycode, tblG4Lookup.G4, tblG4Lookup.BalanceSheet, 1 AS InfiniumString, [debit]+([credit]*-1) AS AMT
FROM TblData LEFT JOIN tblG4Lookup ON TblData.psid&TblData.tbtp = tblG4Lookup.paycode;

The SQL statement above returns the G4 value for all lines, It is not checking the balancesheet field first.

I cannot figure out how to update this to include a verification of the TblG4Lookup field BalanceSheet

1 as InfiniumString is the field I want to have bring back the correct G4 or balancesheet value from TblG4Lookup.

Any help is appreciated. thank you.

example data for TblG4Lookup
S1B0003 3115 null
M2A0440 3435 680.110.4532.9902
B2D0097 3672 680.210.1101.6578
B1D0562 3378 Null

 

[CJ_London]

try using the nz function

nz(tblG4Lookup.BalanceSheet,tblG4Lookup.G4) AS ReturnedValue