Need to add 1 Field to query!

[Bob Vance]

How would I add this field to my query, tblAccountStatus.PayModePay (Text)

SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment
AS Description,(tblAccountStatus.PaidAmount * -1) AS Credit,
NULL,BillID,-1 AS Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo);

 

[blake7]

Hi, View the query in DESIGN view not SQL view, then click the 'add table
button' and add the table that contains your field you want to add, then
either drag the field name down or just double click it to add it to your
query.
Hope this helps
Tony

 

[KARL DEWEY]

Just add as below ---
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag, "" AS PayModePay
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment
AS Description,(tblAccountStatus.PaidAmount * -1) AS Credit,
NULL,BillID,-1 AS Flag, tblAccountStatus.PayModePay
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName And
(tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo);

 

[Bob Vance]

Thanks Karl, But I am getting Enter Parameter Values Errors
Forms!frmBillStatement!cbOwner
Forms!frmBillStatement!tbDate
Forms!frmBillStatement!tbDateTo

Thanks Bob Vance

 

[Bob Vance]

Sorry Karl "All Good" Thank you very much for your help, Fantastic.....Bob

 

[Bob Vance]

Karl I have another report that is a Batch print could you help me add
PayModePay into it Thanks Bob

SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag

FROM tblInvoice
WHERE tblInvoice.OwnerID=Reports!rptOwnerPaymentMethodBatch!tbOwnerID And
(tblInvoice.InvoiceDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo)
ORDER BY tblInvoice.InvoiceDate
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDate AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,tblAccountStatus.PaidAmount AS Credit, NULL,BillID,-1 AS Flag

FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Reports!rptOwnerPaymentMethodBatch!tbOwnerID
And (tblAccountStatus.BillDate Between Forms!frmBillStatement!tbDateFrom And
Forms!frmBillStatement!tbDateTo);