IIF And IsNull
Feb 18, 2008
I am trying to figure out a formula using the iif and isnull function.
I am doing an on time report that looks at the Date Received, Date Required, Ship Date, Todays date and a few other things.
The formula I am trying to work with is
DAYS WORK DONE IN: IIf(IsNull([SHIP_DATE]),[TODAY]-[REQUIRED]+1,[SHIP_DATE]-[RECEIVED]+1)
What I am trying to get it to do is if the ship is empty subtract the todays date from the required date and add 1. If the ship date has a date subtract the ship date from the received date and add 1.
When I run the query I just get #Error.
Is there an easier way to do this or do I just have an error in my code?
Thanks for your help
Mike
View Replies
ADVERTISEMENT
Mar 27, 2008
Hi,
simple question. i have an unbound textbox on a form that has its source as
=[field1]/100*17.5
when field1 is empty on loading, the textbox shows nothing.
when field1 has a value entered, it shows the value
when field1 is reset, the textbox shows #Error
( field1 is cleared by code - me![field1].value = "" )
how do i suppress this to show nothing? i have tried
=isnull([field1]="","",([Field1]/100*17.5) &
=isnull([field1]/100*17.5)
im not great with sql statements ( if thats what you call them )
any advice would be great.
nigel
View 2 Replies
View Related
Jul 25, 2006
I am trying to insert a value in a field from an existing fields. I attached a sample of my database to show the query. In the NewProduct field I need "change of product" data to override the "product" data but if "change of product" is null/empty the "product" value should be in the NewProduct field. In other words the NewProduct field must take the value of either/or of the change of product or the product field. If
This is what I have so far:
NewProduct: IIf([Change to Product]<>[Product],[Change to Product],"")
Thanks in advance.
View 4 Replies
View Related
Nov 27, 2006
I am trying to create a multiple IIF statement with IIF(ISNULL([xxx]) and an or and it does not work any help would be appreciated.
See below
IIf(IsNull([AI Rate]) or IIf([AI Rate]>0),0,1)
View 2 Replies
View Related
Oct 27, 2005
I've been searching the forum for a while, so hopefully I haven't missed the answer to this one. It may sound like a small problem, but it's driving me mad.
I have a series of TextBoxes on a Form.
Two of these are named 'Department' and 'Shift'.
Originally, the ControlSource for the former was 'Department' and for the latter was 'Shift', both from the same source table.
All data was being displayed without any problems (any null values resulting in an empty field).
I altered the ControlSource on each to read:
=IIf(IsNull([Department]),"Unknown",[Department])
and
=IIf(IsNull([Shift]),"Unknown",[Shift])
The Shift field works as I'd hoped it would, but the Department field displays '#Error', whether there is a value or not.
I've tried deleting the problematic TextBox, then recreating it by copying and pasting from one that works (in case there was some setting I was missing). In this case, the only difference between the two is the source field, but given that using the source on it's own doesn't present any problems, I'm at a loss.
This same problem occurs on a small number of other Forms, as well as on certain Reports.
Any thoughts welcome.
View 6 Replies
View Related
Jun 19, 2006
Hi guys,
I have a mod called EntryAge. It requires two arguments. A start date and an End date.
I'm trying to use it in a Query, but I am running into problems because there are times when the Start date and the end date are both Blank.
I've tried this nested statement and it keeps telling me. Contains invalid syntax or I need to enclose it in quotes.
I've copy and pasted my code below. Can someone please help me figure out what is wrong with this silly thing.
intSecondaryTime: EntryAge(IIF(ISNull([dtmSecondaryReceived]),0,[dtmSecondaryReceived])),(IIF(ISNull([dtmSecondaryComplete]),0,[dtmSecondaryComplete])))
What I am trying to say is if the received date is null make it 0 or use the date given, and if the complete date is null use 0 or use the complete date.
It then will perform the calculation that the Entry Date mod does.
If you want I can copy and past the Entry Date Mod. If that will help.
View 4 Replies
View Related
Sep 27, 2005
Hi all,
I had a little problem with delete query. I would like to delete the records by checking two fields if both fields are empty. I mean if a records had both these fields are empty only. If one of the field has data , the record should stay there.. Is there any way I can do this?
Thanks in advance.
Thanks
View 4 Replies
View Related
Aug 15, 2006
I have a query and it can be a null value if it is null I want it to return 1 if not null I want the calculation. This is what I have but it will not work.
Please help!!!
repo on sitePercent of copy jobs not rejected:IIF([Rejected Job Percentage]=0,1,(1-[Rejected Job Percentage])
View 3 Replies
View Related
Mar 19, 2008
Basically i have a drop down that i want controlling the Criteria for each field in my query to have an advanced search. If i leave a field blank the query comes up with nothing so i tried this:IIf(IsNull([Forms]![Inventory Report Search]![Model]),Is Null,[Forms]![Inventory Report Search]![Model])this is not working. it is still returning the query blank. if i put valid critera in the dropdown it comes back Fine.how do i make is so if a field is blank it will return it as null or not even there.Note: also tried this;Forms]![Inventory Report Search]![Model] Or Forms]![Inventory Report Search]![Model] Is Nullworks, But comes back as too complex after a few searches and when i open the query there is a million or's in there. so that wont work.
View 3 Replies
View Related
Oct 19, 2005
The below SQL gets it's criteria from a form but if the form has no Date1 and Date2 values I would like it to return all records.
I could not tinker it into submission. :confused:
Any pointers would be great.
SELECT [Central Western Region Database].[Date of Term], [Central Western Region Database].[Agent Number], [Central Western Region Database].[First Name], [Central Western Region Database].[Last Name], [Central Western Region Database].[District Name]
FROM [Central Western Region Database]
WHERE ((([Central Western Region Database].[Date of Term]) Between [Forms]![Compliance Lair Reporting]![Date1] And [Forms]![Compliance Lair Reporting]![Date2]) AND (([Central Western Region Database].Status)="inactive") AND (([Central Western Region Database].[Employee Type])="agent"))
ORDER BY [Central Western Region Database].[Date of Term];
View 2 Replies
View Related
Jul 28, 2015
I am using a frame on form to get report. Below is the code used to filter report.
Select Case Me.fraReportType.Value
Case 1
strReportType = "Like '*'"
Case 2
strReportType = " Is Null"
Case 3
strReportType = " Is Not Null"
End Select
The second and Third case is working fine. While the first condition is not working. This filter is on date field. There are three possibilities:
1. If we need all data
2. If we need is null data
3. If we need is not null data
How I can get the first condition using my code mentioned above.
View 3 Replies
View Related
May 3, 2013
All using access 2010. I have a query1, query2 and query3. Query1 is my master. Query2 and Query3 was created based on different criteria but derived from the Query1. I now want to exclude the records from Query1 that are in Query2 and Query3. When I try to put isnull in criteria of both queries Im trying to exclude; instead of returning the remainder records in the master I get none.
View 3 Replies
View Related
Jun 15, 2005
I am using the QBE grid and am writing a select query to select only records with an empty Date Closed Field. The Date Closed field is a Date/Time Field. I am using Access 2003. When I use in the criteria IsNull([DateClosed]) I do not get any records selected which have an empty Date Closed field, have I a bug? and if so please could anyone point me in the right direction.
View 4 Replies
View Related
Nov 27, 2013
I have a form with cascading combo boxes pulling from a table. They work perfectly, no worries. My problem now is if I do not enter information in every combo box (i.e. only two out of four combo boxes), how can I still run the query and get the appropriate information?
For example (these are my combo box titles in order):
Product
Type
Customer
Contract #
I don't want to necessarily look by Contract # all the time, but sometimes just by the general Product and Type to get a larger view. How do I set up expressions/criteria in my query to accurately produce that information? Right now it just produces a blank query table if I don't fill out all the boxes.
I've tried a couple of expressions with "isnull" criteria, but I must be doing it incorrectly.
View 2 Replies
View Related
