select query not null

[Guest]

I have a emplyee database. I need a select query to show records with values
in certain fields. lets say fields A, B, and C. each field may have any
combination of the values "null"or "not Null":
A B C
12 dan
hat
shoe john

For the most part, these 3 fields will be null in 98% of the records, as
after these fields are entered, they will append another table and then be
cleared. I need the query to select only records with values in these 3
fields, then append the other table. I tried a criteria of ïs not null" in
each field in the query, but this will not show the records unless all 2
fields have values. how do I show the records in which on of these fields is
null?

 

[Allen Browne]

In query design view, there are several rows at the bottom of the screen
under Criteria, e.g.:
Criteria:
Or:
Or:

Place the first Is Not Null on the Criteria row.
Under the next field, place it on the first Or row.
Under the next field, place it on the second Or row.

The query will now select those records where at least one of the fields is
not null.

Note that if you have other criteria as well that must apply to all cases,
you will need to repeat that criteria on the OR rows as well.

 

[Guest]

Thanks Allen, The query selected only the required records and appended them
to another table. I have 2 queries, the 1st appends the records to table 2,
the 2nd updates table 1 by changing the fields to nulls, each has a seperate
cmd button on the form, is it possible to run both queries from 1 cmd button?

thanks again

 

[Allen Browne]

These are both action queries, your command button can use RunSQL in a
macro, or Execute in VBA code to run them both.

Execute is better, because you can know programmatically whether they
succeeded. Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Guest]

the select query works, now I want to update fields a,b,c with values from
fields d,e,f. The update works fine as long as d,e,f do not have a null, if
one does have a null I get an error message:

database didnt update 1 field due to a type conversion failure, o records
due to a key violations, 0 record due to lock violations, and 0 records due
to rule violations.

my sql:
UPDATE tbl_employees SET tbl_employees.Wage = NZ([tbl_employees].[newwage]),
tbl_employees.DepartmentID = NZ([tbl_employees].[Newdept]),
tbl_employees.JobID = NZ([tbl_employees].[Newjob])
WHERE (((tbl_employees.newwage) Is Not Null)) OR (((tbl_employees.Newdept)
Is Not Null)) OR (((tbl_employees.Newjob) Is Not Null));

 

[Allen Browne]

"Type conversion failure" would suggest that either a is a different type of
field than d, b is different to e, or c is different to f.

You used Nz() without specifying an alternative as the 2nd arguemnt. I
suggest you use 0 or "" (depending on whether it is a numeric field or a
text field.)

In fact, I suggest you use IIf() instead, because Nz() can introduce this
problem. Example:
UPDATE tbl_employees SET
tbl_employees.Wage = IIf([newwage] Is Null, 0, [newwage), ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

the select query works, now I want to update fields a,b,c with values from
fields d,e,f. The update works fine as long as d,e,f do not have a null,
if
one does have a null I get an error message:

database didnt update 1 field due to a type conversion failure, o records
due to a key violations, 0 record due to lock violations, and 0 records
due
to rule violations.

my sql:
UPDATE tbl_employees SET tbl_employees.Wage =
NZ([tbl_employees].[newwage]),
tbl_employees.DepartmentID = NZ([tbl_employees].[Newdept]),
tbl_employees.JobID = NZ([tbl_employees].[Newjob])
WHERE (((tbl_employees.newwage) Is Not Null)) OR (((tbl_employees.Newdept)
Is Not Null)) OR (((tbl_employees.Newjob) Is Not Null));


--
Dan

I have a emplyee database. I need a select query to show records with
values
in certain fields. lets say fields A, B, and C. each field may have any
combination of the values "null"or "not Null":
A B C
12 dan
hat
shoe john

For the most part, these 3 fields will be null in 98% of the records, as
after these fields are entered, they will append another table and then
be
cleared. I need the query to select only records with values in these 3
fields, then append the other table. I tried a criteria of ïs not null"
in
each field in the query, but this will not show the records unless all 2
fields have values. how do I show the records in which on of these
fields is
null?

 

[Guest]

This almost works, I used the IIf statement, however if field d is null I
want to be able to keep field a with its current value:

wage dept newwage new dept
$5 food sales

so for ex. this employee will change from the food dept to the sales dept
but keep the same wage.
--
Dan

"Type conversion failure" would suggest that either a is a different type of
field than d, b is different to e, or c is different to f.

You used Nz() without specifying an alternative as the 2nd arguemnt. I
suggest you use 0 or "" (depending on whether it is a numeric field or a
text field.)

In fact, I suggest you use IIf() instead, because Nz() can introduce this
problem. Example:
UPDATE tbl_employees SET
tbl_employees.Wage = IIf([newwage] Is Null, 0, [newwage), ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

the select query works, now I want to update fields a,b,c with values from
fields d,e,f. The update works fine as long as d,e,f do not have a null,
if
one does have a null I get an error message:

database didnt update 1 field due to a type conversion failure, o records
due to a key violations, 0 record due to lock violations, and 0 records
due
to rule violations.

my sql:
UPDATE tbl_employees SET tbl_employees.Wage =
NZ([tbl_employees].[newwage]),
tbl_employees.DepartmentID = NZ([tbl_employees].[Newdept]),
tbl_employees.JobID = NZ([tbl_employees].[Newjob])
WHERE (((tbl_employees.newwage) Is Not Null)) OR (((tbl_employees.Newdept)
Is Not Null)) OR (((tbl_employees.Newjob) Is Not Null));


--
Dan

I have a emplyee database. I need a select query to show records with
values
in certain fields. lets say fields A, B, and C. each field may have any
combination of the values "null"or "not Null":
A B C
12 dan
hat
shoe john

For the most part, these 3 fields will be null in 98% of the records, as
after these fields are entered, they will append another table and then
be
cleared. I need the query to select only records with values in these 3
fields, then append the other table. I tried a criteria of ïs not null"
in
each field in the query, but this will not show the records unless all 2
fields have values. how do I show the records in which on of these
fields is
null?

 

[Guest]

nevermind, The lightbulb came on over my head. changed the qry:
UPDATE tbl_employees SET tbl_employees.DepartmentID = iif([Newdept] is
null,[DepartmentID],[Newdept]), tbl_employees.JobID = iif([Newjob] is
null,[JobID],[Newjob]), tbl_employees.Wage = iif([Newwage] is
null,[wage],[newwage])
WHERE (((tbl_employees.newwage) Is Not Null)) OR (((tbl_employees.Newjob) Is
Not Null)) OR (((tbl_employees.Newdept) Is Not Null));

So now it just updates the fields which have been changed. I have tried a
few combinations of null fields and it seems to work fine,

thanks
--
Dan

This almost works, I used the IIf statement, however if field d is null I
want to be able to keep field a with its current value:

wage dept newwage new dept
$5 food sales

so for ex. this employee will change from the food dept to the sales dept
but keep the same wage.
--
Dan

"Type conversion failure" would suggest that either a is a different type of
field than d, b is different to e, or c is different to f.

You used Nz() without specifying an alternative as the 2nd arguemnt. I
suggest you use 0 or "" (depending on whether it is a numeric field or a
text field.)

In fact, I suggest you use IIf() instead, because Nz() can introduce this
problem. Example:
UPDATE tbl_employees SET
tbl_employees.Wage = IIf([newwage] Is Null, 0, [newwage), ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

the select query works, now I want to update fields a,b,c with values from
fields d,e,f. The update works fine as long as d,e,f do not have a null,
if
one does have a null I get an error message:

database didnt update 1 field due to a type conversion failure, o records
due to a key violations, 0 record due to lock violations, and 0 records
due
to rule violations.

my sql:
UPDATE tbl_employees SET tbl_employees.Wage =
NZ([tbl_employees].[newwage]),
tbl_employees.DepartmentID = NZ([tbl_employees].[Newdept]),
tbl_employees.JobID = NZ([tbl_employees].[Newjob])
WHERE (((tbl_employees.newwage) Is Not Null)) OR (((tbl_employees.Newdept)
Is Not Null)) OR (((tbl_employees.Newjob) Is Not Null));


--
Dan


:

I have a emplyee database. I need a select query to show records with
values
in certain fields. lets say fields A, B, and C. each field may have any
combination of the values "null"or "not Null":
A B C
12 dan
hat
shoe john

For the most part, these 3 fields will be null in 98% of the records, as
after these fields are entered, they will append another table and then
be
cleared. I need the query to select only records with values in these 3
fields, then append the other table. I tried a criteria of ïs not null"
in
each field in the query, but this will not show the records unless all 2
fields have values. how do I show the records in which on of these
fields is
null?