Invoiceing / Invoice Payment Add Initial Payment Record?

G

Guest

godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records through the
frminvoicedetails subform, and don't enter a payment into the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to create a report
that will allow for me to show invoices that have a balance due with or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the tab for the
invoicepayments subform, I would like to be able to add a default first
record or automatically populate the following fields, but only do this once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
G

Guest

Not the way to do it. Carrying a current balance is storing a calculated
field which, if you have done any reading on this site, is always (no
exceptions, no excuses, no buts) a bad idea.
whether it be to display in a form or on a report, calculate the balance due
when you need it. My preferred method is to write a function that does the
calculation and call it from where I need it. That way, I only have to write
it once and if the business rules change, I only have to modify it once.
 
G

Guest

i'm not trying to store a calculated field, i'm trying to add a new record
only once to an invoice payment subform? Please see my post.

Brook
 
B

Brian Bastl

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do for the report
is join your invoice table to the invoice payments table, and then change
the join properties to show all invoices and any invoice payments (aka LEFT
JOIN).

Post back if you need help getting this right.

Brian
 
G

Guest

Well, the issue is, is that if the client has not made a payment and the
invoice is past due, the invoice payments table wouldn't work in this
situation b/c it would contain no data/payments so therefore I couldn't
create the report.

Do you understand?

Brook
 
B

Brian Bastl

Hi Brook,

Unless I've completely misunderstood your intent, I respectfully beg to
differ. Have you even tried creating a query as I suggested? This technique
works quite well. Below is the SQL I use in my invoicing system. It returns
the sum of payments if any are made, and returns $0.00 if none were made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID = tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd have to
change your table and field names appropriately.

HTH,

Brian
 
G

Guest

So, in your case, if no payment has been made, are there any entries in your
invoicepayment table? That is my case...

Brook

Brian Bastl said:
Hi Brook,

Unless I've completely misunderstood your intent, I respectfully beg to
differ. Have you even tried creating a query as I suggested? This technique
works quite well. Below is the SQL I use in my invoicing system. It returns
the sum of payments if any are made, and returns $0.00 if none were made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID = tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd have to
change your table and field names appropriately.

HTH,

Brian



Brook said:
Well, the issue is, is that if the client has not made a payment and the
invoice is past due, the invoice payments table wouldn't work in this
situation b/c it would contain no data/payments so therefore I couldn't
create the report.

Do you understand?

Brook
 
B

Brian Bastl

No, there are no payment entries in my payment table if none are made.

HTH,
Brian


Brook said:
So, in your case, if no payment has been made, are there any entries in your
invoicepayment table? That is my case...

Brook

Brian Bastl said:
Hi Brook,

Unless I've completely misunderstood your intent, I respectfully beg to
differ. Have you even tried creating a query as I suggested? This technique
works quite well. Below is the SQL I use in my invoicing system. It returns
the sum of payments if any are made, and returns $0.00 if none were made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID = tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd have to
change your table and field names appropriately.

HTH,

Brian



Brook said:
Well, the issue is, is that if the client has not made a payment and the
invoice is past due, the invoice payments table wouldn't work in this
situation b/c it would contain no data/payments so therefore I couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do for
the
report
is join your invoice table to the invoice payments table, and then change
the join properties to show all invoices and any invoice payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records through the
frminvoicedetails subform, and don't enter a payment into the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to create a report
that will allow for me to show invoices that have a balance due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the tab for the
invoicepayments subform, I would like to be able to add a default first
record or automatically populate the following fields, but only do this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
G

Guest

ok... I am working on trying to duplicate your SQL example with my data, but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a little more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

Brian Bastl said:
No, there are no payment entries in my payment table if none are made.

HTH,
Brian


Brook said:
So, in your case, if no payment has been made, are there any entries in your
invoicepayment table? That is my case...

Brook

Brian Bastl said:
Hi Brook,

Unless I've completely misunderstood your intent, I respectfully beg to
differ. Have you even tried creating a query as I suggested? This technique
works quite well. Below is the SQL I use in my invoicing system. It returns
the sum of payments if any are made, and returns $0.00 if none were made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID = tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment and the
invoice is past due, the invoice payments table wouldn't work in this
situation b/c it would contain no data/payments so therefore I couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do for the
report
is join your invoice table to the invoice payments table, and then
change
the join properties to show all invoices and any invoice payments (aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records through the
frminvoicedetails subform, and don't enter a payment into the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to create a
report
that will allow for me to show invoices that have a balance due with
or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the tab for the
invoicepayments subform, I would like to be able to add a default
first
record or automatically populate the following fields, but only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
B

Brian Bastl

Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


Brook said:
ok... I am working on trying to duplicate your SQL example with my data, but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a little more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

Brian Bastl said:
No, there are no payment entries in my payment table if none are made.

HTH,
Brian


Brook said:
So, in your case, if no payment has been made, are there any entries
in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I respectfully beg to
differ. Have you even tried creating a query as I suggested? This technique
works quite well. Below is the SQL I use in my invoicing system. It returns
the sum of payments if any are made, and returns $0.00 if none were made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID = tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment
and
the
invoice is past due, the invoice payments table wouldn't work in this
situation b/c it would contain no data/payments so therefore I couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do
for
the
report
is join your invoice table to the invoice payments table, and then
change
the join properties to show all invoices and any invoice
payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records
through
the
frminvoicedetails subform, and don't enter a payment into the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to create a
report
that will allow for me to show invoices that have a balance
due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the tab for the
invoicepayments subform, I would like to be able to add a default
first
record or automatically populate the following fields, but only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
B

Brian Bastl

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Brian Bastl said:
Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


Brook said:
ok... I am working on trying to duplicate your SQL example with my data, but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a little more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook
beg
to
differ. Have you even tried creating a query as I suggested? This
technique
works quite well. Below is the SQL I use in my invoicing system. It
returns
the sum of payments if any are made, and returns $0.00 if none were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID = tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment and
the
invoice is past due, the invoice payments table wouldn't work in this
situation b/c it would contain no data/payments so therefore I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do for
the
report
is join your invoice table to the invoice payments table, and then
change
the join properties to show all invoices and any invoice payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records through
the
frminvoicedetails subform, and don't enter a payment into the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to create a
report
that will allow for me to show invoices that have a balance due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the tab
for
 
B

Brian Bastl

needs a little more refining to eliminate any ambiguity:

SELECT tblinvoicedetails.invoiceid,
sum(nz([tblinvoicepayments].[paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;



Brian Bastl said:
SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Brian Bastl said:
Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


Brook said:
ok... I am working on trying to duplicate your SQL example with my
data,
but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a little more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

:

No, there are no payment entries in my payment table if none are made.

HTH,
Brian


So, in your case, if no payment has been made, are there any
entries
in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I respectfully
beg
to
differ. Have you even tried creating a query as I suggested? This
technique
works quite well. Below is the SQL I use in my invoicing system. It
returns
the sum of payments if any are made, and returns $0.00 if none were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID = tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a
payment
and
the
invoice is past due, the invoice payments table wouldn't work
in
this
situation b/c it would contain no data/payments so therefore I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd
do
for
the
report
is join your invoice table to the invoice payments table,
and
then
change
the join properties to show all invoices and any invoice payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records through
the
frminvoicedetails subform, and don't enter a payment into the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to create a
report
that will allow for me to show invoices that have a
balance
due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the tab
for
the
invoicepayments subform, I would like to be able to add a default
first
record or automatically populate the following fields, but only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
G

Guest

Brian...

Thank you for the quick post... however when I add the code, I am getting a
syntax error... any ideas? I'm taking your code and pasting it into my SQL
code?

BRook

Brian Bastl said:
SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Brian Bastl said:
Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


Brook said:
ok... I am working on trying to duplicate your SQL example with my data, but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a little more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

:

No, there are no payment entries in my payment table if none are made.

HTH,
Brian


So, in your case, if no payment has been made, are there any entries in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I respectfully
beg
to
differ. Have you even tried creating a query as I suggested? This
technique
works quite well. Below is the SQL I use in my invoicing system. It
returns
the sum of payments if any are made, and returns $0.00 if none were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID = tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment and
the
invoice is past due, the invoice payments table wouldn't work in this
situation b/c it would contain no data/payments so therefore I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do for
the
report
is join your invoice table to the invoice payments table, and then
change
the join properties to show all invoices and any invoice payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records through
the
frminvoicedetails subform, and don't enter a payment into the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to create a
report
that will allow for me to show invoices that have a balance due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the tab
for
the
invoicepayments subform, I would like to be able to add a default
first
record or automatically populate the following fields, but only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
G

Guest

Brian,

the error i'm getting is (missing operator) in query expression...

and when I click ok, it goes to the "AS" in the first line of code.

Any ideas?

Brook

Brian Bastl said:
SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Brian Bastl said:
Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


Brook said:
ok... I am working on trying to duplicate your SQL example with my data, but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a little more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

:

No, there are no payment entries in my payment table if none are made.

HTH,
Brian


So, in your case, if no payment has been made, are there any entries in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I respectfully
beg
to
differ. Have you even tried creating a query as I suggested? This
technique
works quite well. Below is the SQL I use in my invoicing system. It
returns
the sum of payments if any are made, and returns $0.00 if none were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID = tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment and
the
invoice is past due, the invoice payments table wouldn't work in this
situation b/c it would contain no data/payments so therefore I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do for
the
report
is join your invoice table to the invoice payments table, and then
change
the join properties to show all invoices and any invoice payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records through
the
frminvoicedetails subform, and don't enter a payment into the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to create a
report
that will allow for me to show invoices that have a balance due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the tab
for
the
invoicepayments subform, I would like to be able to add a default
first
record or automatically populate the following fields, but only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
B

Brian Bastl

Hi Brook,

I assumed that you couldn't just copy and paste what I wrote unless the
fields and tables names you posted were spelled exactly the same as in your
DB, which I suspected they weren't.

What you should do is create the query in the grid. That way there won't be
any mis-spellings. Capitalization and spelling are crucial, so perhaps your
invoicedetails table might actually be tblInvoiceDetails, as opposed to
tblinvoicedetails.

Brian


Brook said:
Brian...

Thank you for the quick post... however when I add the code, I am getting a
syntax error... any ideas? I'm taking your code and pasting it into my SQL
code?

BRook

Brian Bastl said:
SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Brian Bastl said:
Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


ok... I am working on trying to duplicate your SQL example with my data,
but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a
little
more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

:

No, there are no payment entries in my payment table if none are made.

HTH,
Brian


So, in your case, if no payment has been made, are there any entries
in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I
respectfully
beg
to
differ. Have you even tried creating a query as I suggested? This
technique
works quite well. Below is the SQL I use in my invoicing
system.
It
returns
the sum of payments if any are made, and returns $0.00 if none were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID =
tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd
have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment
and
the
invoice is past due, the invoice payments table wouldn't work in
this
situation b/c it would contain no data/payments so therefore I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do
for
the
report
is join your invoice table to the invoice payments table, and
then
change
the join properties to show all invoices and any invoice
payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records
through
the
frminvoicedetails subform, and don't enter a payment
into
the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to
create a
report
that will allow for me to show invoices that have a balance
due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the
tab
for
the
invoicepayments subform, I would like to be able to add a
default
first
record or automatically populate the following fields, but
only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
B

Brian Bastl

ok, I see it. I left off the second ')'

change sum(nz([paymentamount],0) As [Total Payments] to
sum(nz([paymentamount],0)) As [Total Payments]

It's getting late here. I'm getting sloppy.
Brian


Brook said:
Brian,

the error i'm getting is (missing operator) in query expression...

and when I click ok, it goes to the "AS" in the first line of code.

Any ideas?

Brook

Brian Bastl said:
SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Brian Bastl said:
Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


ok... I am working on trying to duplicate your SQL example with my data,
but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a
little
more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

:

No, there are no payment entries in my payment table if none are made.

HTH,
Brian


So, in your case, if no payment has been made, are there any entries
in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I
respectfully
beg
to
differ. Have you even tried creating a query as I suggested? This
technique
works quite well. Below is the SQL I use in my invoicing
system.
It
returns
the sum of payments if any are made, and returns $0.00 if none were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID =
tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd
have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment
and
the
invoice is past due, the invoice payments table wouldn't work in
this
situation b/c it would contain no data/payments so therefore I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do
for
the
report
is join your invoice table to the invoice payments table, and
then
change
the join properties to show all invoices and any invoice
payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records
through
the
frminvoicedetails subform, and don't enter a payment
into
the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to
create a
report
that will allow for me to show invoices that have a balance
due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed subforms.

What I would like to do is this, when I click the
tab
for
the
invoicepayments subform, I would like to be able to add a
default
first
record or automatically populate the following fields, but
only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
G

Guest

Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the erro is
coming from..

If you have any other ideas, please let me know..

Brook

Brian Bastl said:
Hi Brook,

I assumed that you couldn't just copy and paste what I wrote unless the
fields and tables names you posted were spelled exactly the same as in your
DB, which I suspected they weren't.

What you should do is create the query in the grid. That way there won't be
any mis-spellings. Capitalization and spelling are crucial, so perhaps your
invoicedetails table might actually be tblInvoiceDetails, as opposed to
tblinvoicedetails.

Brian


Brook said:
Brian...

Thank you for the quick post... however when I add the code, I am getting a
syntax error... any ideas? I'm taking your code and pasting it into my SQL
code?

BRook

Brian Bastl said:
SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


ok... I am working on trying to duplicate your SQL example with my data,
but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a little
more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

:

No, there are no payment entries in my payment table if none are made.

HTH,
Brian


So, in your case, if no payment has been made, are there any entries
in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I respectfully
beg
to
differ. Have you even tried creating a query as I suggested? This
technique
works quite well. Below is the SQL I use in my invoicing system.
It
returns
the sum of payments if any are made, and returns $0.00 if none
were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID =
tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd
have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment
and
the
invoice is past due, the invoice payments table wouldn't work in
this
situation b/c it would contain no data/payments so therefore I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do
for
the
report
is join your invoice table to the invoice payments table, and
then
change
the join properties to show all invoices and any invoice
payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records
through
the
frminvoicedetails subform, and don't enter a payment into
the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to
create a
report
that will allow for me to show invoices that have a balance
due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed
subforms.

What I would like to do is this, when I click the tab
for
the
invoicepayments subform, I would like to be able to add a
default
first
record or automatically populate the following fields, but
only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
G

Guest

Thanks.... so much...

That worked and its getting later here as well... I am going to work with
this a little more tomorrow and will let you know if I have any questions..
thanks for sticking with me!

Brook

Brian Bastl said:
ok, I see it. I left off the second ')'

change sum(nz([paymentamount],0) As [Total Payments] to
sum(nz([paymentamount],0)) As [Total Payments]

It's getting late here. I'm getting sloppy.
Brian


Brook said:
Brian,

the error i'm getting is (missing operator) in query expression...

and when I click ok, it goes to the "AS" in the first line of code.

Any ideas?

Brook

Brian Bastl said:
SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


ok... I am working on trying to duplicate your SQL example with my data,
but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a little
more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

:

No, there are no payment entries in my payment table if none are made.

HTH,
Brian


So, in your case, if no payment has been made, are there any entries
in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I respectfully
beg
to
differ. Have you even tried creating a query as I suggested? This
technique
works quite well. Below is the SQL I use in my invoicing system.
It
returns
the sum of payments if any are made, and returns $0.00 if none
were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID =
tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd
have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment
and
the
invoice is past due, the invoice payments table wouldn't work in
this
situation b/c it would contain no data/payments so therefore I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd do
for
the
report
is join your invoice table to the invoice payments table, and
then
change
the join properties to show all invoices and any invoice
payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform, and
frminvoicepayments subform.

When I initially create an invoice, I add the records
through
the
frminvoicedetails subform, and don't enter a payment into
the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to
create a
report
that will allow for me to show invoices that have a balance
due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed
subforms.

What I would like to do is this, when I click the tab
for
the
invoicepayments subform, I would like to be able to add a
default
first
record or automatically populate the following fields, but
only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
B

Brian Bastl

Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I don't have
your DB. I would suggest you do this in the query's Design View. After you
have your two tables in the grid, double click the join line between the two
tables, and select the option to show all records from 'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and zipped
copy of your db. I wouldn't be able to look at it until sometime tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Brook said:
Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the erro is
coming from..

If you have any other ideas, please let me know..

Brook

Brian Bastl said:
Hi Brook,

I assumed that you couldn't just copy and paste what I wrote unless the
fields and tables names you posted were spelled exactly the same as in your
DB, which I suspected they weren't.

What you should do is create the query in the grid. That way there won't be
any mis-spellings. Capitalization and spelling are crucial, so perhaps your
invoicedetails table might actually be tblInvoiceDetails, as opposed to
tblinvoicedetails.

Brian


Brook said:
Brian...

Thank you for the quick post... however when I add the code, I am
getting
a
syntax error... any ideas? I'm taking your code and pasting it into my SQL
code?

BRook

:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


ok... I am working on trying to duplicate your SQL example with
my
data,
but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a little
more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

:

No, there are no payment entries in my payment table if none
are
made.
HTH,
Brian


So, in your case, if no payment has been made, are there any entries
in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I respectfully
beg
to
differ. Have you even tried creating a query as I
suggested?
This
technique
works quite well. Below is the SQL I use in my invoicing system.
It
returns
the sum of payments if any are made, and returns $0.00 if none
were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID =
tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure. You'd
have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a payment
and
the
invoice is past due, the invoice payments table wouldn't work in
this
situation b/c it would contain no data/payments so
therefore
I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What
you'd
do
for
the
report
is join your invoice table to the invoice payments
table,
and
then
change
the join properties to show all invoices and any invoice
payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails
subform,
and
frminvoicepayments subform.

When I initially create an invoice, I add the records
through
the
frminvoicedetails subform, and don't enter a payment into
the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to
create a
report
that will allow for me to show invoices that have a balance
due
with
or
without a payment being made.

I have my main form, with my subforms as tabbed
subforms.

What I would like to do is this, when I click
the
tab
for
the
invoicepayments subform, I would like to be able to
add
a
default
first
record or automatically populate the following
fields,
but
only do
this
once?

paymentdate = Date()
paymenttype = "opening Balance"
paymentamount = "$0.00"
paymentnumber = "0"

Can anyone help me?

Is this possible?

Thanks,

Brook
 
G

Guest

Brian...

Everything is working great... I just implimented your changes and it
works great... I have been trying to figure this out on my own for 2 weeks
now... I always do my best to pick my own brain to figure things out... but
this forum has always been such a great support factor! thank you for your
support and help!

Brook

Brian Bastl said:
Hi Brook,

beyond the errors we've just corrected, I wouldn't know since I don't have
your DB. I would suggest you do this in the query's Design View. After you
have your two tables in the grid, double click the join line between the two
tables, and select the option to show all records from 'tblinvoicedetails'
and any matching records from 'tblinvoicepayments' where the joining fields
are equal. That will create the LEFT JOIN.

If you can't get it to work, you can always send me a compacted and zipped
copy of your db. I wouldn't be able to look at it until sometime tomorrow.

HTH,
Brian
bastelER<at>alltelLER<dot>net
remove all capitalized letters

Brook said:
Actually,

I did a DB Documenter and pasted that info into the forum, so all the
fields are named as is... so I will have to figure out where the erro is
coming from..

If you have any other ideas, please let me know..

Brook

Brian Bastl said:
Hi Brook,

I assumed that you couldn't just copy and paste what I wrote unless the
fields and tables names you posted were spelled exactly the same as in your
DB, which I suspected they weren't.

What you should do is create the query in the grid. That way there won't be
any mis-spellings. Capitalization and spelling are crucial, so perhaps your
invoicedetails table might actually be tblInvoiceDetails, as opposed to
tblinvoicedetails.

Brian


Brian...

Thank you for the quick post... however when I add the code, I am getting
a
syntax error... any ideas? I'm taking your code and pasting it into my SQL
code?

BRook

:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetails.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;


Hi Brook,

using the fieldnames and tablenames just as you have posted them:

SELECT invoiceid, sum(nz([paymentamount],0) As [Total Payments]
FROM tblinvoicedetails LEFT JOIN tblinvoicepayments ON
tblinvoicedetail.invoiceid = tblinvoicepayments.invoiceid
GROUP BY tblinvoicedetails.invoiceid;

you'll need to revise this to accommodate Caps in your names.

Brian


ok... I am working on trying to duplicate your SQL example with my
data,
but
so far i'm unsuccessful...

Here are my tbl names and fields... can you help me? give me a
little
more
guidance?

Table: tblinvoicedetails


invoiceid

orderid

invoicenumber

serialnumber

DesignNumber

DesignName

Quality

Size

SqFt

PricePerSqFoot

TotalPrice

shippingcost

DiscountAmt

invoicetype

clientname



Table: tblinvoicepayments



paymentid

invoiceid

invoicedate

invoicenumber

paymentnumber

paymentdate

paymenttype

paymentamount

invoicetotal

checknumber

amountdue



Thanks,
Brook

:

No, there are no payment entries in my payment table if none are
made.

HTH,
Brian


So, in your case, if no payment has been made, are there any
entries
in
your
invoicepayment table? That is my case...

Brook

:

Hi Brook,

Unless I've completely misunderstood your intent, I
respectfully
beg
to
differ. Have you even tried creating a query as I suggested?
This
technique
works quite well. Below is the SQL I use in my invoicing
system.
It
returns
the sum of payments if any are made, and returns $0.00 if none
were
made. I
don't add dummy records to my tables.

SELECT tblJobs.JobID, Sum(nz([Pmt],0)) AS [Total Payments]
FROM tblJobs LEFT JOIN tblPayments ON tblJobs.JobID =
tblPayments.JobID
GROUP BY tblJobs.JobID;

The above was posted only to show you the basic structure.
You'd
have to
change your table and field names appropriately.

HTH,

Brian



Well, the issue is, is that if the client has not made a
payment
and
the
invoice is past due, the invoice payments table wouldn't
work in
this
situation b/c it would contain no data/payments so therefore
I
couldn't
create the report.

Do you understand?

Brook

:

Hi Brook,

You shouldn't need to add a non-payment at all. What you'd
do
for
the
report
is join your invoice table to the invoice payments table,
and
then
change
the join properties to show all invoices and any invoice
payments
(aka
LEFT
JOIN).

Post back if you need help getting this right.

Brian


godo day all,

I have an frminvoices, frminvoicedetails subform,
and
frminvoicepayments subform.

When I initially create an invoice, I add the
records
through
the
frminvoicedetails subform, and don't enter a payment
into
the
frminvoicepayments until an the first payment is made.

What I am running into is this, I want to be able to
create a
report
that will allow for me to show invoices that have a
balance
due
with
or
without a payment being made.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top