UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Remove #error Value In Query Results, Access 2007    
 
   
halefamily104
post Jun 10 2013, 02:20 PM
Post#1



Posts: 400
Joined: 7-November 10



Hello Again,
This is the result of a recent thread but a new question, I am trying to hide the #Error values in my query results. Here is what I have:
YBZ1: IIf(Nz([YB Certified],0)=True,"Certified",IIf(Nz([YBActualProj],0)<0,"Not Due",IIf(Nz([YBActualProj],0)<=7,"Due in 1 Week",IIf(Nz([YBActualProj],0)<=14,"Due in 2 Weeks",IIf(Nz([YBActualProj],0)<90,"Due Soon",IIf(Nz([YBActualProj],0)=90,"Due Today","OVERDUE"), 0, “ “)))))
Values are not hiding. Any Ideas?
Go to the top of the page
 
halefamily104
post Jun 10 2013, 02:20 PM
Post#2



Posts: 400
Joined: 7-November 10



Hello Again,
This is the result of a recent thread but a new question, I am trying to hide the #Error values in my query results. Here is what I have:
YBZ1: IIf(Nz([YB Certified],0)=True,"Certified",IIf(Nz([YBActualProj],0)<0,"Not Due",IIf(Nz([YBActualProj],0)<=7,"Due in 1 Week",IIf(Nz([YBActualProj],0)<=14,"Due in 2 Weeks",IIf(Nz([YBActualProj],0)<90,"Due Soon",IIf(Nz([YBActualProj],0)=90,"Due Today","OVERDUE"), 0, “ “)))))
Values are not hiding. Any Ideas?
Go to the top of the page
 
RAZMaddaz
post Jun 11 2013, 07:20 AM
Post#3


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


Why did you add the Nz around each of the Fields?
Can you upload a small zipped copy of your database and I'd be glad to take a look?
Go to the top of the page
 
halefamily104
post Jun 11 2013, 08:39 AM
Post#4



Posts: 400
Joined: 7-November 10



Trying everything......
Here is a scaled down version...
Go to the top of the page
 
RAZMaddaz
post Jun 11 2013, 09:16 AM
Post#5


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


I don't what you did, but the zipped files are Text files not databases.
Try the following and see if it works for you:
YBZ1: IIf([YB Certified]=True,"Certified",IIf([YBActualProj]=0,"Not Due",IIf([YBActualProj]<=7,"Due in 1 Week",IIf([YBActualProj]<=14,"Due in 2 Weeks",IIf([YBActualProj]<=90,"Due Soon",IIf([YBActualProj]=90,"Due Today",IIf([YBActualProj]>90,"OVERDUE","")))))))
Go to the top of the page
 
halefamily104
post Jun 11 2013, 09:30 AM
Post#6



Posts: 400
Joined: 7-November 10



To reduce size, I saved them as txt files. If you change the extension to .accdb (or the extension of your Access program), you can see the databases.
Go to the top of the page
 
RAZMaddaz
post Jun 11 2013, 09:39 AM
Post#7


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


I deleted the Text File after it didn't work. Sorry! Have you tried what I suggested above?
o decrease the size of the database, do the following with a COPIED database, NOT the original:
1. Open the copied file.
2. Delete all private data.
3. Under the Office button in the far top left corner go to Manage - Compact and Repair.
4. Quit out of Access
5. Zip the file.
6. Upload the zipped file.
Go to the top of the page
 
halefamily104
post Jun 11 2013, 09:49 AM
Post#8



Posts: 400
Joined: 7-November 10



No worries! Here it is! The query in question is Training form qry, linked to CombinedTraining form and source is Training.
Go to the top of the page
 
RAZMaddaz
post Jun 11 2013, 10:02 AM
Post#9


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


I have downloaded everything successfully and will try to figure out the problem. Meanwhile, have you tried what I suggested?
Go to the top of the page
 
halefamily104
post Jun 11 2013, 10:08 AM
Post#10



Posts: 400
Joined: 7-November 10



Yes, I did and that formual works great! What I am trying to do with that exact formula is hide the #Errors that are generated.
Go to the top of the page
 
RAZMaddaz
post Jun 11 2013, 10:14 AM
Post#11


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


In your Training Table, I don't see any Field named "YB Certified" and get an error as a result when trying to run your original Query. Did you delete or rename that Field?
Go to the top of the page
 
halefamily104
post Jun 11 2013, 10:33 AM
Post#12



Posts: 400
Joined: 7-November 10



Try this one....Replace the one you have with this one. Not sure what happened!
Go to the top of the page
 
RAZMaddaz
post Jun 11 2013, 10:45 AM
Post#13


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


The reason for the error message is because the YBActualProj Field, is actually a calculation and not a Field. Once I entered something in the CourseEndDate Field, then YBActualProj Field is able to do a calcualtion and not return an "error" and then the YBZ1 will return "Not Due" or "Certified", etc.
nderstand?
Go to the top of the page
 
halefamily104
post Jun 11 2013, 10:57 AM
Post#14



Posts: 400
Joined: 7-November 10



Totally! Can the #Error be hidden in the event there are no dates entered?
Go to the top of the page
 
RAZMaddaz
post Jun 11 2013, 11:04 AM
Post#15


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


Well, in your Query for the CourseEndDate Field, you could enter Not "IsNull" and then it will not return any of the records that do not have something entered in the CourseEndDate Field.
Edit: Forget what I said I'm wrong, the above will not work.
Go to the top of the page
 
halefamily104
post Jun 11 2013, 11:12 AM
Post#16



Posts: 400
Joined: 7-November 10



Data Type mismatch error.
Go to the top of the page
 
R. Hicks
post Jun 11 2013, 11:12 AM
Post#17


UA Forum Administrator
Posts: 40,467
Joined: 8-February 00
From: Birmingham, Alabama USA


You may try using the IsError() function to do what you need ...
.
Go to the top of the page
 
RAZMaddaz
post Jun 11 2013, 11:15 AM
Post#18


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


It needs to be entered Is Not Null. Sorry!!!
Go to the top of the page
 
halefamily104
post Jun 11 2013, 11:22 AM
Post#19



Posts: 400
Joined: 7-November 10



Tried it and ran the query. No values returned at all. I know we can get this, RAZ!
Thanks for the info R. Hicks. Would I enter that in the criteria field of my query? Just IsError()?
Go to the top of the page
 
RAZMaddaz
post Jun 11 2013, 11:24 AM
Post#20


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


Well, are you only running this with the data you sent me, because that is what will happen?
Edit: Enter a date in the CourseEndDate Field and use the Criteria and that data you entered should be the only record that returns when the Query is run.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    24th May 2018 - 01:15 AM