#Num! error help please (1 Viewer)

cstacy

Registered User.
Local time
Today, 00:04
Joined
Jan 19, 2011
Messages
22
Sorry for what may end up being a fairly simple solution, but I can't seem to get rid of the #Num! error on my formula. Looking at previous posts It seems I may not have the best formula so I hope you can help.

I have a query I run that contains all of my formulas. One of them is:

IIf([FAMILY TOOLING]=Yes,(2*([QTY PER ANNUM]/[PRODUCTION PER HOUR])),([QTY PER ANNUM]/[PRODUCTION PER HOUR]))

From previous post it would appear I have another divisor if it's false when I should have a 0, but I need that other set of figures as well. This works great as long as there are data in all of the feilds, but if one field is blank I get the #Num! error.

Is there anyway to stop this from occuring?

Thank you for your help.
 

vbaInet

AWF VIP
Local time
Today, 07:04
Joined
Jan 22, 2010
Messages
26,374
What are the data types of the two fields used?
 

cstacy

Registered User.
Local time
Today, 00:04
Joined
Jan 19, 2011
Messages
22
What are the data types of the two fields used?

The [Family Tool] field is a check box, and the other two fields are both numbers. I put all of the formula's into the Query I run for this form using the layout:

Run Time (HRS):IIf([FAMILY TOOLING]=Yes,(2*([QTY PER ANNUM]/[PRODUCTION PER HOUR])),([QTY PER ANNUM]/[PRODUCTION PER HOUR]))

I'm guessing the ,([QTY PER ANNUM]/[PRODUCTION PER HOUR])) should have had a 0 to allow a 0 answer, but I need this worked out if it's false as well.

Thanks again.
 

vbaInet

AWF VIP
Local time
Today, 07:04
Joined
Jan 22, 2010
Messages
26,374
Are you sure this is in Access? Or is it a spreadsheet linked to Access?

I don't remember seeing #Num! in Access, it's mostly #Error.

You say that both fields are Number data types but the #Num! error has to do with non-numeric divisions.

Can we see you db?
 

cstacy

Registered User.
Local time
Today, 00:04
Joined
Jan 19, 2011
Messages
22
Nice to see I'm breaking new barriers with this error. The Error shows on the "Quotation Form" under the general tab. The formula is located in the Run Time (HRS) field on the query.

After you mentioned about the formating of the fields I looked at it again and all fields apart from the Family tools is a number.

I've attached it for you to look at. Still very much a work in progress.

Thanks,
 

Attachments

  • testing copy.zip
    472.8 KB · Views: 293

vbaInet

AWF VIP
Local time
Today, 07:04
Joined
Jan 22, 2010
Messages
26,374
I don't normally get errors or at least I don't get #Num! errors so I don't remember whether it's an Excel or Access thing :)

Will have a look when I can.
 

vbaInet

AWF VIP
Local time
Today, 07:04
Joined
Jan 22, 2010
Messages
26,374
Access 2010? Save it in an older format (e.g. 2003) if you can. I don't have 2010.

Do it on a copy of course :)
 

cstacy

Registered User.
Local time
Today, 00:04
Joined
Jan 19, 2011
Messages
22
Access 2010? Save it in an older format (e.g. 2003) if you can. I don't have 2010.

Do it on a copy of course :)

Well it seems I'm using some new features so it won't allow me to save as a previous version. Lucky for me I have my original database in 2000 which shows the same error. If you open up the Quotation Form for the error, and the formula is still on the query. Apprently in 2010 you can put calculations in the tables so I can start having fun with that as well. Good times!

Thanks,
 

Attachments

  • QUOTE.zip
    265.5 KB · Views: 236

vbaInet

AWF VIP
Local time
Today, 07:04
Joined
Jan 22, 2010
Messages
26,374
Here you go:
Code:
RUN TIME (HRS): IIf([FAMILY TOOLING]=Yes, IIF([PRODUCTION PER HOUR] = 0, Null, 2*([QTY PER ANNUM]/[PRODUCTION PER HOUR])), IIF([PRODUCTION PER HOUR] = 0, Null, [QTY PER ANNUM]/[PRODUCTION PER HOUR]))
Your db needs an overhaul. It lacks normalisation. I would advise you read up on that subject. The following link will get you started:

http://support.microsoft.com/kb/283878

Good luck!
 

cstacy

Registered User.
Local time
Today, 00:04
Joined
Jan 19, 2011
Messages
22
Here you go:
Code:
RUN TIME (HRS): IIf([FAMILY TOOLING]=Yes, IIF([PRODUCTION PER HOUR] = 0, Null, 2*([QTY PER ANNUM]/[PRODUCTION PER HOUR])), IIF([PRODUCTION PER HOUR] = 0, Null, [QTY PER ANNUM]/[PRODUCTION PER HOUR]))
Your db needs an overhaul. It lacks normalisation. I would advise you read up on that subject. The following link will get you started:

http://support.microsoft.com/kb/283878

Good luck!

Thank you for the formula, it work's great. I've tried to normalise in the past, but really can't seem to relate it to my database. I'll keep trying though. I may actually get this running yet.

Thanks again for your help.
 

Users who are viewing this thread

Top Bottom