If then Statement

[Jennie]

I have a query that lists 84 accounts and the most current date this account
was billed. I need to add a "Yes/No" field, "Yes" if the most current bill
date was in the previous month and "No" if the most current bill date is
prior to that. For example, this is the month of December, for every account
that was billed in November, I want a "yes" in the field, and for every
account that was billed in October or before, I want a "no". I have tried
several ways and can't get it to work. Any help would be greatly appreciated.
Thanks!

 

[Duane Hookom]

How about providing some table and field names?

 

[Clifford Bass]

Hi Jennie,

Try something like this:

SELECT qrySome_Query.ID, qrySome_Query.Last_Name, qrySome_Query.Last_Billed,
Val(Format(Date(),"yyyymm"))-Val(Format([Last_Billed],"yyyymm"))<=1 AS Yes_No
FROM qrySome_Query;

The Yes_No column will be a boolean what you can either use with a
check box or in a text box with its format set to Yes/No.

Clifford Bass

 

[Jeff Boyce]

Jennie

If you (and Access) already know that "the most current bill" was dated
"last" month (the month preceding the month of the current date), then you
DON'T need or want to add another field to keep track of that. For one
thing, if the checkbox is checked but the most recent bill is dated 5 months
ago, which one do you believe?!

A query to find all those accounts where DateBilled is in the "previous"
month is a lot simpler and not prone to the data integrity issue in the
previous paragraph. Of course, you'd need to keep DateBilled data...

Please post a more specfic description of your data structure (relevant
tables/fields) for more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jennie]

My table name is CadanceComparison. The Field ReceiveDate shows the date an
invoice has been received for a particular account. We have 74 accounts and
not each account is billed each month. For example, we just received the
billing info for December, but only 28 of the accounts were billed during
that period. I need a way to show which ones received a bill, maybe an
asterick next to that account number for example.

 

[hor vannara]

My table name is CadanceComparison. The Field ReceiveDate shows the date
an
invoice has been received for a particular account. We have 74 accounts
and
not each account is billed each month. For example, we just received the
billing info for December, but only 28 of the accounts were billed during
that period. I need a way to show which ones received a bill, maybe an
asterick next to that account number for example.

 

[Clifford Bass]

Hi Jennie,

Did you try my solution, as adapted to your table and/or query and
column names?

Clifford Bass