IF Statements With NULL Values

Jul 23, 2007

I've written a query that is assigned either a pass/fail depending on certain criteria. Here is the query:

Pass/Fail: IIf([jc_actlevel]="Yes" And [jc_diet]="Yes" And [jc_medications]="Yes" And [jc_followup]="Yes" And [jc_sympworse]="Yes" And [jc_weightmonitor]="Yes","Passed","Failed")

I want this query to only show me records that have a "yes" or "no" value but some on the records have neither a "yes" OR "no" (just a NULL value)and are showing up in the query as "failed" which is throwing off my count of how many records met the pass/fail criteria (see attached).

How can I NOT show records that have a NULL value in my query? Thanks for any help.

View Replies


ADVERTISEMENT

Null Values And Like Statements

Aug 8, 2005

I am using a query with Like statements that looks at a field in a form. The form defaults to "*" so that all of the records will show. You can search with the normal wildcards. The problem I have is that the records containing Null values don't appear. I was trying to see if I could use an IIF statement in the criteria of the query, but have had difficulty. What is the proper way to have all records show if * is the default, but only the specific criteria if something is entered using wildcards?

View 5 Replies View Related

IIf Statements And NULL Values

Apr 10, 2007

Within my query I have a field that I need to do a nested IIf Statement that has a NULL value. The name of the field is "smokingcessation". The possible responses are a "1" = Yes, "2"= No, "3"=Unknown and there is also the possibility of a "no answer" at all (blank). I've got the following statement to work so far but need to include the possibility of a blank/no answer response. My statement so far is as follows and is working:

IIf([smokingcessation]=1,"Yes",IIf([smokingcessation]=2,"No","Unknown"))

How would I do a statement that would take into account the existing possibilities plus that of a blank (NULL) answer and leave the field blank in that case? In otherwords, I need to see a "Yes", "No", "Unknown" or a blank field.

Thanks for any help in advance.

View 6 Replies View Related

Use Of Null Statements

Nov 15, 2004

I am trying to get a recordset to skip over empty cells in the data...however, I really don't want to use If, Then, Else statements as I already have quite a few in the code, and by using more, it's causing the code not to run

I've been trying to use:

If IsNull(field) = True Then GoTo CertainSpot

here is where I'd like it to continue if it is false, without using If, Then, Else...
so basically, if the above is true, go to that spot...otherwise, just continue running the code

Is there any way of doing this and avoiding the If, Then, Else statements?

thanks

View 14 Replies View Related

SQL Wildcard And Is Null Statements

Jul 10, 2006

I have a SQL that enables the user to search the form from different combo boxes to narrow down the data. I can get this to work but it only finds the fields that exactly match their input data. I want them to be able to type something in the combo box and then it pull up anything with that value in that field, whether it is 1839B or B1839 it should find both if I type in 1839

Here is my sql statements

SELECT DISTINCT [WeldingSpecification].[Spec], [WeldingSpecification].[Steel Type], [WeldingSpecification].[Group11], [WeldingSpecification].[Group143], [WeldingSpecification].[Substitute1]
FROM WeldingSpecification
WHERE ((([spec] & "") Like IIf([Forms]![frmSearchCriteriaMain]![spec] Is Null,"*", [Forms]![frmSearchCriteriaMain]![Spec] & "*"))
And (([SteelType] & "") Like IIf([Forms]![frmSearchCriteriaMain]![SteelType] Is Null,"*",[Forms]![frmSearchCriteriaMain]![SteelType] & "*"))
And (([Group11] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Group11] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Group11] & "*"))
And (([Group143] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Group143] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Group143] & "*"))
And (([Substitute1] & "")([spec] Like "*" & Forms![frmSearchCriteriaMain]!Spec & "*" & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*"))

Any help is appreciated, please ask if you do not understand fully

Riley

View 12 Replies View Related

Modules & VBA :: Using Parameterized Values In SQL Statements

Oct 15, 2013

I've seen a common solution is to double up the apostrophe, replacing ' with " to avoid the special character however i also hear of another solution using "parameters".I tried have a look around on the internet and got confused so I thought maybe it would be better to ask here.

I just want to change my INSERT INTO and UPDATE commands to be able to use this (to prevent a potential sql injection) also i think its important to know im using DOA to code? (not sure if its important) either way.

View 3 Replies View Related

Modules & VBA :: Invalid Use Of Null With No (obvious) Null Values

Jul 5, 2013

It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:

Code:
Private Sub cmdUpdateDates_Click()
'###################################
'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time].
'###################################
Dim intCounter As Integer
intCounter = 0
Dim rs As ADODB.Recordset

[Code] ....

View 1 Replies View Related

Queries :: IIF Conditional Statements To Assign New Values In Table

Apr 11, 2013

I am writing some iif conditional statements in one of my tables to assign new values in that table.

Code:
FP: IIf([cohort with status].[Intake Date] Between #04/01/2012# And #26/04/2012#,201201) Or IIf([cohort with status].[Intake Date] Between #27/04/2012# And #24/05/2012#,201202)

When I run it, I am getting some -1 values in the new column FP.

but if I get rid of the second IIF, then it works.

how do you combine multiple IIF statements in a query?

View 1 Replies View Related

Field Criteria: Is Null; There Are Null Values In That Field; No Records Are Returned

Nov 16, 2007

I think the title pretty much sums it up....

I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.

I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.

The query is refusing to return any results...

Any ideas?

View 10 Replies View Related

I Want To See Null Values......

Aug 23, 2005

Easy one for the experts here I hope:

I want query to show all results including any null values because at the moment it just misses the null values out.

So for example instead of the query saying that Fruiterer A has 5 apples, 0 oranges and 4 bananas it just says that Fruiterer A has 5 apples and 4 bananas. Therefore because oranges was a null value then it simply makes no reference to oranges.

Please help, many thanks,

Paul

View 2 Replies View Related

Null Values

Sep 26, 2005

Hello,

I have two tables called table1 and table2 with following fields:

Table1:
Name
NameId


Table2:
Name
NameID
Visits


where name and nameid is same but visit can me 0 to 230. name in table2 is
same as table2 but only showing visit 1 or higher. How can I make new table that can list all name, nameId and visits.

I can create new table but it shows only table2's record. so all I need is
table1 and table2 record if names are missing in table2 then put 0.

Viral

View 2 Replies View Related

Null Values

May 24, 2006

Hi all
I have 3 linked tables in a vehicle database. 1 keeps a record of Service Due date, 1 has the Fleet numbers and the last contains Vehicle Condition reports.
They are linked by the Fleet numbers. I have a query that returns the Services due between 2 dates. What I would like is to have the same query return any outstanding Vehicle Condition items as well for any Vehicles due for servicing. Currently, I can include the field in the query but if there are no current items for a vehicle in the Vehicle Condition table then the query does not return that vehicle as a result. Any suggestions would be appreciated.

Regards
Craig

View 2 Replies View Related

Null Values

Oct 30, 2006

Hope someone can help with this. I'm sure it's dead simple but it's driving me insane!

The scenario is a number of departments who are processing something (sales for example). So, for October

Dept 1 - 20 (Sales)
Dept 2 - 10
Dept 3 - 2
Dept 4 - 7

Now I want to count the number of sales completed from these totals. However, as Dept 3 has no sales completed for October it does not show in the query. So I get

Dept 1 - 7
Dept 2 - 2
Dept 4 - 6

Any idea how to get Dept 3 - 0 to show up in the query?

Any help greatly appreciated!

View 3 Replies View Related

Not Getting Null Values

Dec 14, 2006

I've found several threads on this subject, but I still can't get it to work, so please forgive me if I seem to be duplicating a question.

I'm making a very simple db. It has only 2 tables:
MATERIAL (one)
TEST RESULTS (many) with left join.

I need to report average test results (by MATERIAL) weekly, even if there were no tests for a particular product that week, ie;

MATERIAL A = 100
MATERIAL B = 90
MATERIAL C =
MATERIAL D = 105

When I try to query, I only get results for A, B and D. It seems to be ignoring the MATERIAL table and looking only at the TEST RESULTS table.

I'm sure it's something simple that I'm overlooking but how do I get C to show, even if it has no results.

Here's my query SQL.:

SELECT DISTINCTROW [MATERIAL Query].MATERIAL, Avg([TEST RESULTS].V50) AS [Avg Of V50]
FROM [MATERIAL Query] LEFT JOIN [TEST RESULTS] ON [MATERIAL Query].MATERIAL = [TEST RESULTS].MATERIAL
WHERE ((([TEST RESULTS].[TEST DATE]) Between [Forms]![Date Range Form]![Start Date] And [Forms]![Date Range Form]![END DATE]))
GROUP BY [MATERIAL Query].MATERIAL;


As always, thanks in advance.
BeckieO

View 3 Replies View Related

Null Values

Sep 28, 2007

Hi all.

I am trying to build a query that combines several fields. The situation is as follows....

1. If a is > b then i need a
2. If b is > a then i need b
3. If a is null then b is not null
4. If b is null then a if a is not null
5. If a and b are null then '999999'

I have everything taken care of up to If 4. The problem i am having is that when create the sql language for 5 it is adding 999999 to the filed when step 3 or 4 are the case. See languange below.

Any help would be greatly appreciated.

IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]>MSC.[TDS<2500_S_GW_NR_Model_mg/kg],MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_Model_mg/kg]>MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg],MSC.[TDS<2500_S_GW_NR_Model_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg] Is Null,MSC.[TDS<2500_S_GW_NR_Model_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_Model_mg/kg] Is Null,MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg]) & IIf(MSC.[TDS<2500_S_GW_NR_100xGWMSC_mg/kg],MSC.[TDS<2500_S_GW_NR_Model_mg/kg] Is Null,'999999') AS MSC_SGW

View 2 Replies View Related

Null Values...

Oct 3, 2007

I have a series of values within a table that includes a number of null values. I would like to include this field within a query, however if the value is null I would like to show a 0, if the field is not null, I would like it to show the original value.

I have tried to use the IsNull in an iif formula without luck.

PS: If I was conducting this in Excel I would use the following =IF(J3="",0,J3)

View 5 Replies View Related

Sum Of Null Values

Jan 14, 2008

I have a query set up to sum 2 fields to a 3rd field, for example, I have
field1, field2, and field3.
Fields 1 and 2 are data from a table.
In Field3 I have an expression:
Field3: [Field1] + [Field2]
This works fine except when either Field1 and/or Field2 contain no data, then the sum does not work. Any ideas how to correct this?
I realize this may not be the best way to accomplish what I am trying to do but this is a small part of a very large application and i would like to change as little as possible since everything else is working ok.
Thanks very much.

View 2 Replies View Related

Null Values

Jan 16, 2005

I'm trying to get my head around null values.

data required is no. Allow zero length strings is yes.

txt2 is a memo field

in a report on page event, I'm trying to get the following code to work.

If IsNull(txtg2) = True Then
Me.lblg2.Visible = False
Else

I've tried this too:

If Me.txtg2 = Null Then
Me.lblg2.Visible = False
Else

It doesn't seem to work. Any ideas?

View 8 Replies View Related

Deleting The Null Values

Jul 20, 2005

hello friends,
I have written an insert query but sometime it does not work properly and leave some column blank. I want to write a query which should check for the blank columns and delete these records.thanks in advance

View 1 Replies View Related

Crosstab With Null Values

Aug 9, 2005

I currently have a crosstab query that compares data for two years. The results look like this.

month 2004 2005
January 98% 95%
.....
Decemeber 98

What I am getting at is that the query returns null values for the fields that do not have data recorded for the months in 2005 because we have not reached them yet. Is there a way for me to make sure that at least the value 0 is entered in the null value areas?

View 2 Replies View Related

Null Values In Query

Dec 4, 2005

I need to create a query with the MTD field
I have created the MTD and it works!!! but now I need it to show all of the PartNum fields even if null. do I need to convert all nulls to zeros? if so how do I go about this?

Here are the fields in the query

First Field:
Field: PartNum
Table: Part
Total: Group By

Second Field:
Field: UnitPrice
Table: OrderDetail
Total: Group By

Third Field:
Field: NumOrdered
Table: OrderDetail
Total: Sum

Fourth Field:
Field: MTD: Sum([NumOrdered]*[UnitPrice])
Table: (blank)
Total: Expression

Fifth Field:
Field: CurrentYear: Year([OrderDate])
Table: (blank)
Total: Group By
Criteria: Year(Date())

Sixth Field:
Field: CurrentMonth: Month([OrderDate])
Table: (blank)
Total: Group By
Criteria: Month(Date())

When I run the query I only get 2 parts and their total. but I want all parts to show up even if they have not been ordered in the month. the parts that have not been ordered should say $0.00 in the MTD field.

Any Suggestions???

View 2 Replies View Related

Converting Null Values To Zero

Mar 30, 2006

Hi all, need some help. In my query I have 4 fields, a weekly labour cost, weekly material cost, weekly plant cost and a weekly summary. The problem I have is that my weekly summary shows a null value on some weeks as not all the weekly costs have a value assigned to them.

I have been told there is a way in the query to convert Null values to zero so that the weekly summary field doesn't show a blank cell.

Could someone please explain to me how this is done? Thanks

View 6 Replies View Related

Addition With Null Values

Feb 28, 2007

Hi all,i am running a query that (as part of it) returns a total time.. using a formula i found on this site. this formula uses a start and end time and works wonders. works!IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60my problem however is an option that i need my users to have to enter a time not based on a start end time, but a "OtherTime" if u will. (ie they have 4 hours instead of writeing 1:00 - 5:00) already changed this from default value null to 0... (which i didnt wanna have to do cuz it messes up the query)Doesnt Work(IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60)+[OtherTime]i want the total time in the query to show the 4 hours, but it is leaving it blank. i am guessing that it is because my start end times are blank(null) is there a way to add a if statement into a query expression that will bypass this? Tried, but thats invalid in a query expressionTotalTime: If IsNull [StartTime] Then =[OtherTime]Else IIf([StartTime]<[EndTime],DateDiff("n",[StartTime],[EndTime]),1440-DateDiff("n",[EndTime],[StartTime]))/60+[OtherTime] end if

View 2 Replies View Related

Nz - Null Values In Queries

Sep 12, 2007

i have checked the forums and i know they kind of answer this question, but im not really sure where i am ment to write it!

I have two queries, which a third query subtracts the 1st query and 2nd query values and gives the end value. If the 2nd value is null, i get a null value at the end, as i need to change this to zero instead. i know i need to use nz (as seen in microsoft access help, and other places on this forum) but im not sure exactly where to put this on my query, and in which query.

Basically:

(qryCountpartNo.CountPartCode)-(qryTotalquantityallocated.sumofquantity) = qryTotalFree

both countpartcode and sumofquantity may be null at any time

Any help would be greatly appreciated!

Thanks in advance,

Emily

View 14 Replies View Related

Hiding Null Values?

Jul 18, 2005

Hi, when doing an Access query is it possible to hide null values?

Thanks

View 1 Replies View Related

Null Values In Crosstabs

Apr 24, 2006

Hi there, instead of blank fields in my crosstab query, and hence my report, i would like a standard comment, such as 'No Booking'.

My crosstab is something like this:

.........A...........B..............C............. D
1.......X

2.......X..........................X

3...................X............................. .X

(Ignore the dots, obviously)

Currently, when i try to open the report based on this query, it fails unless each column has data in it for at least one record.

I've tried using the Nz function but haven't been able to make it work. Thankyou in advance!

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved