Calculated field is blank for records with an even primary key! (1 Viewer)

k1sh83

New member
Local time
Today, 04:35
Joined
Jun 17, 2013
Messages
3
Hi. I have a claculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:

Code:
TotalPt: [Att1Pt]+[Att2Pt]+[Att3Pt]+[Att4Pt]+[Att5Pt]+[Att6Pt]+[Att7Pt]+[Att8Pt]+[Att9Pt]+[Att10Pt]+[Att11Pt]+[Att12Pt]+[Att13Pt]+[Att14Pt]+[Att15Pt]+[Att16Pt]+[Att17Pt]+[Att18Pt]+[Att19Pt]+[Att20Pt]+[Att21Pt]+[Quiz1Pt]+[Quiz2Pt]+[Quiz3Pt]+[Quiz4Pt]+[Quiz5Pt]+[Quiz6Pt]+[Quiz7Pt]+[Quiz8Pt]+[Quiz9Pt]+[Quiz10Pt]+[MidtermWritPt]+[FinalWritPt]+[Proc1Pt]+[Proc2Pt]+[Proc3Pt]+[Proc4Pt]+[Proc5Pt]+[Proc6Pt]+[Proc7Pt]+[Proc8Pt]+[Proc9Pt]+[Proc10Pt]+[Proc11Pt]+[Proc12Pt]+[Proc13Pt]+[Proc14Pt]+[ProcPracPt]

I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!

I've attached a screenshot.
 

Attachments

  • Screenshot.jpg
    Screenshot.jpg
    58.6 KB · Views: 121

SOS

Registered Lunatic
Local time
Today, 02:35
Joined
Aug 27, 2008
Messages
3,517
You have to deal with NULLS. Surround each of those with the NZ function like this:

Nz([Att1Pt],0) + Nz([Att2Pt],0)...etc.

And that calculation makes me think you don't have a properly normalized database.
 

k1sh83

New member
Local time
Today, 04:35
Joined
Jun 17, 2013
Messages
3
You have to deal with NULLS. Surround each of those with the NZ function like this:

Nz([Att1Pt],0) + Nz([Att2Pt],0)...etc.

And that calculation makes me think you don't have a properly normalized database.

Thank you very much, SOS. That fixed it. I'm a newbie in Access so it's quite likely that I make fundamental mistakes in my database. Can you tell me why it's probably not a normalized database?

I've attached my database (with student names removed).
 

Attachments

  • HCP 206 2012-13 Spring 2.accdb
    1.1 MB · Views: 99
Last edited:

trkl

Registered User.
Local time
Today, 13:35
Joined
Jun 30, 2013
Messages
31
calculated field works in ms Access 2010 but not in mS Access 2007.if you have ms access 2007 then its not gonna work but if you have ms access 2010 then its work.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:35
Joined
Jan 20, 2009
Messages
12,849
calculated field works in ms Access 2010 but not in mS Access 2007.if you have ms access 2007 then its not gonna work but if you have ms access 2010 then its work.

The OP opened their poset with "I have a calculated field in a query".

Moreover they acknowledged the advice given to them solved the problem. You really should read what is posted.
 

Users who are viewing this thread

Top Bottom