problem adding up... (1 Viewer)

[mariaw]

Hi

I have a query which has a formula in it like this:

Mon2: IIf([Mon]=0," ",[Mon])

(and then I have the same for Tues2, Wed2 etc - all the days of the week)

Note that the formula for Mon is: IIf(IsNull([Test]![Mon Hours]),0,1)

and that Mon Hours is a long integer number.

What I want to do is add up Mon2+Tues2+Wed2 etc...
so to do this I put:

Daysworked: [mon2]+[tues2]+[wed2]+[thurs2]+[fri2]+[sat2]+[sun2]

And I get "11111" not 5.....???

How do I get 5?

 

[Dennisk]

use one of the type conversion functions

Cint([mon2]) + Cint(................

 

[RoyVidar]

This seems a bit "spreadsheetish", but if the fields returned from the query will either contain the string " " or a number, try enclosing it in the Val function

Daysworked: Val([mon2])+Val([tues2])+...

Since the IsNull function returns a boolean, where -1 represents True an 0 False, you could probably do the whole calculation like this

Daysworked: ABS((Not IsNull([Mon Hours])+(Not IsNull([Tue Hours])+(Not IsNull([Wed Hours])+<rest of the days>)

in stead of the "three step calculation", where you create the problem you're experiencing - you're mixing strings with numerics, which makes your adding up becoming concatenation in stead.

What's it doing:

IsNull([Mon Hours]) - returns -1 if [Mon Hours] is null, 0 if not

Not - negates the above, so that it returns -1 if it's not null, 0 if Null

ABS - absolute value, switches to positive

 

[mariaw]

Whoops I spoke to soon....

What I need the system to do is, if [Mon Hours] is either 0 (a number) or null, then to return a "0"?

Maria

 

[RoyVidar]

That would probably be the NZ function

NZ([Mon Hours], 0)

But I question why you would need to have a field, which it seems you're going to do calculations on, to return the string "0" over the number 0.

Edit: so the above returns the number 0, if you really, really, really want a string, enclose in quotes "0"

 

[mariaw]

nz function

That would probably be the NZ function

NZ([Mon Hours], 0)

But I question why you would need to have a field, which it seems you're going to do calculations on, to return the string "0" over the number 0.

Edit: so the above returns the number 0, if you really, really, really want a string, enclose in quotes "0"

Hi again

I do want it to return the number 0, so where do I put this NZ function (sorry, not a great whiz on access...)

thanks

Maria

 

[RoyVidar]

<grin> while you say you're no great wiz on Access, our crystal ball might be a bit clouded at the moment with regards to how and what you're doing, so the best I can say is to place it where you need it </grin>

I think we communicate past each other, perhaps someone else can step in, or perhaps you could explain again, perhaps showing the current queries, what is the current data, current result and what you wish at result. Sometimes attaching a sample db, where you've removed whatever sensitive stuff is there, can help us get back on track again?

 

[mariaw]

Hi Roy

I can't get the database to "zip" so it is small enough to attach; any chance I could email it to you directly??

thanks

Maria

 

[RoyVidar]

I don't know if/when I have the opportunity, and I wouldn't be able to make any promises. I'd suggest either if you can

- find some free hosting, there should be several around, to upload the db, and paste a link

or

- create a new db, import only those objects which are necessary to reproduce this (only the few tables/queries relating to this), if the table contains a million records, delete so there's only enough to reproduce the behaviour..

Do a compact/repair before zip-ing - and the importing thingies is found on the File | Get external data menu.

Using any of these methods, all who are interested, and has the time and/or ability to respond, might do so.

 

[mariaw]

the file!

Hi all

Here is the simplified version - if you look at the Sickness query - undated, you will see the problem I am having...


http://www.shrewsbury.gov.uk/Example-system.zip

If you download a copy of the zip file, then make it unmark it so it isn't read-only you can play around with it!

thanks

m

 

[Michael J Ross]

Hi Maria,

See attached (should be in Access 97 ) , in the daysworked field I used the functions Cint and Nz to give the expression:

Daysworked: CInt((Nz([mon2],0)))+CInt((Nz([tues2],0)))

and that comes up with answer 2

Edit, Just reread the thread again and saw I didnt quite do all you asked,

If you change the

Mon2: IIf([Mon]=0," ",[Mon]) to
Mon2:NZ([Mon],0)

that will put a zero in for the monday etc

then amend the Daysworked: CInt((Nz([mon2],0)))+CInt((Nz([tues2],0))) to

Daysworked: CInt([mon2])+CInt([tues2])

that should give you want you want.



Hope this helps

 

[mariaw]

Hi Michael (and everyone who has tried to help me

I have finally sorted it out; unfortunately the only way I could do it was to make sure that if any of the hours (e.g. Mon Hours, Tues Hours etc) was blank, was to put a 0 in it (the number) - and then my formulas did work - sorry for troubling you with this one...but thanks again!!

 

[neileg]

As one local government officer to another, I thought I'd give you a few pointers.

1. Your main table is not normalised. This means it is designed like a spreadsheet with a column for each day of the week. Instead you should have the days in a linked table with one record for each day/person. This would do two things immediately to help your problems. It would mean that you could use a simple totals query to do your adding up, plus, you needn't have a record if the person doesn't work that day.
2. You are using table level lookups. These only work at a very simple level. Once you do anything more complex, they will prove a problem. It is better to use lookups in a form and leave the tables uncluttered.
3. When you are using a lookup, you should store the primary key of the lookup, not the text value. Your lookup tables need PKs adding. This way, should you need to amend the text description, you only change the text in the lookup table, so one record, not all the records populated with that text.
4. You have a check box in your Reason table. That won't work because if it is checked, that means all records with that reason will show the same check box value.

Good luck, and keep coming to these forums. I learned everything I know here.