Microst Access Help

Tony - it's been a while since i've used Access, but i'm pretty good with DB's in general. It sounds like what you need to do is something like an Oracle DECODE function for the cell that does the totals. The basic idea is that if you're doing the sum and it tries to sum a null value it explodes. So instead you do a decode of the value. In SQL, you go from this:

SELECT SUM(column1) FROM table1

to this:

SELECT SUM(DECODE(column1, null, 0, column1)) FROM table1


The decode function (in Oracle) basically works like this: the first variable is the column you want to decode. After that you can put as many 2-value pairs as you want, where the first is the value you want to change (in this case null, or nothing) and the second is the value you want to change it to (0). the final one is the default value if it doesn't match any of the previous values.

So in oracle, DECODE(column1, null, 0, column1) would change all null values to a 0 for the resulting set, leaving everything else the same.

from http://eis.bris.ac.uk/~ccmjs/ora_sql.htm, we see the equivalent for Access databases: iif(expression, value if true, value if false)

So i would guess the query for an Access DB would look something like:

SELECT sum(iif(isnull(column1), 0, column1)) FROM table1

 

Tony - it's been a while since I've used Access too, so I may be talking out of my arse - but maybe you can use a field input mask? That way blank cells would only have a "__" as an input field -- perhaps. Input mask can be specified in the table set-up for a specific field.

 

I'd use Excel if I could get away with it!

 

Could you post the actual file ? It's easier to debug real code

 

This is really a UI issue more than a data access issue. I personally wouldn't look to a solution inside of a SQL statement, though I'd also use SQL 2005 Express instead of Access for something like this where the UI has grown outside of the "hello world" app that Access is built for.

A quick look at Access 2007 reveals a "Default Value" property on text boxes in Access. How opposed are you to just set that to 0 and make all of the text boxes required fields.

Otherwise, you might be able to make a pretty simple VBA script that assigns handlers and listens for change events.

 

I guess that depends on how your implementation goes... If the DB table(s) backing that form was something along the lines of two columns, a date and a value (which is probably how i'd do it on first glance, but other requirements might change that), then you wouldn't have to do it for all 24 fields, just the totals column (so 4 times):

SELECT sum(iif(isnull(value), 0, value)) FROM table1 WHERE date < 9/30/09 and date > 10/1/07

The idea isn't to suppress the 0 in each individual field, it's to account for a possible null value in those rows of the database. The SQL query lets you tackle the entire total list in one go, without having to worry about listing out each field. I do not know, maybe i'm missing something in your implementation...

As someone else said, if you can post the associated file(s) as an example, it would be much easier to debug... if you don't want to do it publicly, feel free to send it to my hotmail account.

 

There is an option in Excel to hide cells with zeros. Might be simpler.

 

Tony,

I'm a few years away from when I did a lot of Access work, but you might consider this solely a display problem.

For example, as previously mentioned, you could set your default field values to 0 to avoid the math problems.

Then, for your online data entry form or printed report, you could write a simple Macro that performs a set value function (perhaps turning the 0 font color to white or the field background to color to match that of the form) that runs "on load" for the form and "after update" for the field.

The workability of this sort of depends on whether you're displaying more than one field record in the form and the general design of your data (i.e., is it nearly Excel with one table or is it a buch of cross-linked tables) and whether this is a small or huge database.

The version of Access you're trying to do this in would also be good to post.