Subtracting when the value is null

[Phoebe]

Hello again,

I am trying to finish a database that keeps track of points employees accrue when they miss work.

I have the forms set up to enter the info daily, as well as the queries that automatically sum the points.

My problem comes when I try to SUBTRACT points. When an employee has a month of perfect attendance, they drop a point. I cannot get my head around this for some reason. I created a query to determine which employees had zero points for a month, but I can't figure out the next step to subtract a point from my main table.

Thanks so much in advance--you people are wonderful to share your expertise with novices in over our heads!

 

[Isskint]

If i am reading your inquiry correctly, an update query would do this for you. The 'trick' is to use the NZ() function in case the points accumulated this month is null.

in the following SQL Table1 would be a table of employees showing their attendance points and table2 woudl be a table showing the accrued points for the current month.

UPDATE [Table1] INNER JOIN Table2 ON [Table1].ID = Table2.ID SET [Table1].points = nz([Table1]![points])-1
WHERE (((Table2.points)=0));

 

[Phoebe]

First, Thanks! This makes perfect sense if I can just get it to work. I keep getting an error message. My two tables are TotalPointsT and MonthlyPointsT, with the field name "SumOfPoints" in both--can you help me fix the error in the following?

UPDATE [TotalPointsT] INNER JOIN MonthlyPointsT ON [TotalPointsT].ID = MonthlyPointsT.ID SET [MontlhlyPointsT].SumOfPoints = nz([TotalPointsT]![SumOfPoints])-1WHERE (((MonthlyPointsT.SumOfPoints)=0));

 

[Isskint]

What error are you getting?

 

[Phoebe]

The error reads:

"The expression you entered contains invalid syntax.
You may have entered an operand without an operator"

 

[Isskint]

if the SQL you have posted is copied direct from the query editor, then it may be the lack of a space before the WHERE.

 

[Phoebe]

I put a space before the WHERE and I still get the same error. Any other ideas? I have no doubt that it's me, I just can't figure out what I'm doing. And yes, it is a direct copy and paste from the Query.

 

[John Big Booty]

You seem to be missing part of your Nz() function, you have not provided value if null.

Nz ( variant, [COLOR="Red"][ value_if_null ][/COLOR] )

 

[Isskint]

You seem to be missing part of your Nz() function, you have not provided value if null.

Nz ( variant, [COLOR=red][ value_if_null ][/COLOR] )

Yes, JBB is quite right. Because we are using it in a query the optional part of the NZ() function is required or it will still return Null.
So Change;
Nz([TotalPointsT]![SumOfPoints])-1
to
Nz([TotalPointsT]![SumOfPoints],0)-1

 

[Phoebe]

I'm afraid I'm still getting the same error message. I really appreciate the help, I wish I knew more and could help! It doesn't look like I'm missing a paren or anything like that.

 

[Isskint]

I can not see anything wrong with the synatx of the expression and as such i am puzzled. The error message you are getting is often asscoiated, in a query, with a field name containing a space character, a question mark (?), or an at sign (@) which do not appear in your expression.

I wonder, would you be able to attach your DB or copy the tables/queries into a new DB and attach that?

 

[Phoebe]

I can certainly do that and would be grateful for the help. Can I just attach them to a reply or is there something else I need to do first?

 

[Isskint]

You can just do it on the reply. Below the message box you will see a panel Additional Options that includes a button Manage Attachments.

 

[Phoebe]

Again, many thanks for your help. I am enjoying learning Access but realize that I may have gotten in a little over my head on this. Please realize this is a work in progress, and I had to delete some of the things I had done that reflected any company info and had to replace all the employee names.

It wouldn't allow me to save the query with the expression since there was an error, but the Query is called "PointsSubtractionQ."

If I am going about this the wrong way, I welcome any suggestions. When I first mapped it out it seemed relatively simple, but I am now realizing that it's not (at least, not for me).

 

[John Big Booty]

I think the first thing you need to do is to Normalises your table structure. The tutorial here may be of assistance. You might also find a useful data model here to use as a starting point.

 

[Phoebe]

I will take a look at both links right now, thanks so much!