Eliminate Data in QUERY

[Warren Phillips]

I have a database with payroll records. A sample of the data looks like this

Emp# Adjustment Code Netpay
123 50.00 NT 500.00
456 75.00 UB 600.00
789 45.00 NT 350.00

I need my QUERY to display all of the data above except for the following:

If the "Code" field has anything other than "NT", then I need to delete the
value in the "Adjustment" Column....but I need to pull the other data in this
row (ie Emp# and Netpay)...

Any ideas???

 

[Jeff L]

Make a field that has:
iif(

 = "NT", [Adjustment], Null)

Hope that helps!

 

[John Vinson]

I have a database with payroll records. A sample of the data looks like this

Emp# Adjustment Code Netpay
123 50.00 NT 500.00
456 75.00 UB 600.00
789 45.00 NT 350.00

I need my QUERY to display all of the data above except for the following:

If the "Code" field has anything other than "NT", then I need to delete the
value in the "Adjustment" Column....but I need to pull the other data in this
row (ie Emp# and Netpay)...

Any ideas???

Do you want to permanently and irrevokably delete the value in
[Adjustment] from the underlying table? Or do you just want it to not
be displayed in this query? Those are two separate operations!

Guessing that you want the latter, try using a calculated field. In a
vacant Field cell in the query type

IIF(

 = "NT", [Adjustment], NULL)

to display the Adjustment value only if code is equal to NT. This
won't affect what's stored in your table (and the adjustment value
will not be editable).

John W. Vinson[MVP]

 

[Warren Phillips]

Thank you

Make a field that has:
iif(

 = "NT", [Adjustment], Null)

Hope that helps!


[QUOTE="Warren"]
I have a database with payroll records.  A sample of the data looks like this

Emp#     Adjustment      Code    Netpay
123         50.00              NT                 500.00
456         75.00              UB                 600.00
789         45.00              NT                 350.00

I need my QUERY to display all of the data above except for the following:

If the "Code" field has anything other than "NT", then I need to delete the
value in the "Adjustment" Column....but I need to pull the other data in this
row (ie Emp# and Netpay)...

Any ideas???[/QUOTE]
[/QUOTE]