Basic Subtraction in Access

[akrasodomski]

Hi there,

I'd like to have an ticker at the top of my Form that subtracts the number of PAID invoices (records with a YES in the paid column) from the number of TOTAL invoices (records), thus leaving me with a number of UNPAID invoices.

At the moment, I am using a Yes/No under the heading 'Paid' to determine which invoices are paid and which are not. Is there a way this subtraction can done?

Thanks,

[Robeen]

If the paid invoices have a 'Yes' in the 'Paid' column and the unpaid invoices don't have 'Yes' - you can just run a query something like this:

Select Count(Paid)
From [YourTableName] 
Where Paid <> 'Yes';

To get your count.
You can do that using VBA code that is triggered in an Event of your choice on the Form and then get that number out of the SQL and onto your Form.

I don't totally get how you are wanting the number of unpaid invoices to be displayed on your Form but if you can give me a few more details, I might have a couple of suggestions.

[akrasodomski]

If the paid invoices have a 'Yes' in the 'Paid' column and the unpaid invoices don't have 'Yes' - you can just run a query something like this:

Select Count(Paid)
From [YourTableName] 
Where Paid <> 'Yes';

To get your count.
You can do that using VBA code that is triggered in an Event of your choice on the Form and then get that number out of the SQL and onto your Form.

I don't totally get how you are wanting the number of unpaid invoices to be displayed on your Form but if you can give me a few more details, I might have a couple of suggestions.

I've fallen at the first hurdle: http://i44.tinypic.com/f24j8k.jpg

Why can't it find my table?

[RayMilhon]

Why couldn't you use =DCount([Paid],sourcetable,[paid]=0) as the controlsource of a textbox?

[akrasodomski]

Why couldn't you use =DCount([Paid],sourcetable,[paid]=0) as the controlsource of a textbox?

Ray,

I just got a #Name? Error when I tried that as the controlsource. Why is it not finding the value?

[RayMilhon]

Did you include the actual table name where I put sourcetable? Since I don't know your table structure. the syntax of the function is Dcount(fieldname,tablename,criteria)

[R_Badger]

You need to be specific and name it correctly

dcount works as such
for numbers
=dcount("[Field_name]","[Table_name]","[where_condition]="&[field])
for strings
=dcount("[Field_name]","[Table_name]","[where_condition]='"&[field]&"'")
for dates
=dcount("[Field_name]","[Table_name]","[where_condition]=#"&[field]&"#")

Worth reading:
http://allenbrowne.com/casu-07.html

We can't write it completely for you because you never provided the table name.

And you could do a double calculation

=DCount([Paid],sourcetable,[paid]=0)-DCount([Paid],sourcetable,[paid]<>0)

[RayMilhon]

Just looked at the actual syntax from Microsoft and my definition above is different because of the way I've always used it. The true Syntax from microsoft is DCount(Expression,domain,criteria).

[RayMilhon]

R Badger much better explanation than mine. Thanks

[akrasodomski]

You need to be specific and name it correctly

dcount works as such
for numbers
=dcount("[Field_name]","[Table_name]","[where_condition]="&[field])
for strings
=dcount("[Field_name]","[Table_name]","[where_condition]='"&[field]&"'")
for dates
=dcount("[Field_name]","[Table_name]","[where_condition]=#"&[field]&"#")

Worth reading:
http://allenbrowne.com/casu-07.html

We can't write it completely for you because you never provided the table name.

And you could do a double calculation

=DCount([Paid],sourcetable,[paid]=0)-DCount([Paid],sourcetable,[paid]<>0)

I don't understand how - with all this help - I still can't do it. My table appears to be called RecievedInvoices, but that still throws up an error (note: the typo is in the original).


=DCount([Paid],sourcetable,[paid]=0)-DCount([Paid],sourcetable,[paid]<>0)[/QUOTE]

The 'Paid?' column has a ? and is a yes/no.

Perhaps if you write it I can work back from the answer to work out what I'm doing wrong. Appreciate the effort, thanks.

[RayMilhon]

Why are you using a wildcard character in your fieldname. MS Access has 2 wild card characters the * and ? for example "*test*" would return any value with test anywhere in the string. example "this is a test" would be true "?test?" would return any value that is length of 6 with any single character before or after "test" example "This is a test" would be false "atest1" would be true The * is a wildcard for a string the ? is a wildcard for a character.

[RagJose]

Please note Dcount arguments are all String type as R_Badger illustrated. Therefore, the control in your form should have a source like

=DCount("[Paid]", "[RecievedInvoices]", "[Paid]<>'YES' ")

The above assumes the "Paid" field holds the string "YES" when the invoice has been paid, and something else otherwise. If not, adapt the condition accordingly.

HOWEVER...

if "something else" can be a Null, the above won't work because DCount ignores records whose count field is Null, and comparisons with Null yield Null instead of True/False. Use instead:

=DCount ("*", "RecievedInvoices", "IsNull [Paid] or [Paid]<>'YES' ")