It's a pretty long SQL because there's lot more than "Pipe Material." When I
added a new record all of my combox boxes returned a number. The data that
was entered days ago show up correctly.
Here's the SQL.
SELECT tblData.DataID, tblData.YearInstalled, (Year(Date())-[YearInstalled])
AS Age,
IIf([Age]>=50,"5",IIf([Age]>=40,"4",IIf([Age]>=30,"3",IIf([Age]>=20,"2",IIf([Age]>=10,"1","0")))))
AS AgeScore, tblData.BackUpDate, tblData.Area, ([BetwnMH1] & " and " &
[BetwnMH2]) AS Location, tblData.BetwnMH1, tblData.BetwnMH2,
tblData.PointRepairNecessary,
IIf([PointRepairNecessary]=Yes,10,IIf([PointRepairNecessary]=No,0)) AS
PointRepairScore, tblData.VideoDate, tblData.ReverseGrade,
IIf([ReverseGrade]=Yes,10,IIf([ReverseGrade]=No,0)) AS ReverseGradeScore,
tblData.PipeFailure, IIf([PipeFailure]=Yes,10,IIf([PipeFailure]=No,0)) AS
PipeFailureScore, tblData.ServiceConnection, tblData.DamagedPipe,
tblData.PipeMissing, tblData.BackUpTypeLU, tblData.DepthLU, IIf([DepthLU]
Like "0*","1",IIf([DepthLU] Like "9*","2",IIf([DepthLU] Like ">15*","3"))) AS
DepthScore, tblData.PipeMatOther, tblData.PipeMaterialLU,
IIf([PipeMaterialLU] Like
"Cast*","3",IIf([PipeMaterialLU]="Concrete","2",IIf([PipeMaterialLU] Like
"Vitrified*","4","1"))) AS MaterialScore, tblData.MainSizeLU,
IIf([MainSizeLU] Like "6*","5",IIf([MainSizeLU] Like
"8*","3",IIf([MainSizeLU] Like "10*","1"))) AS MainSizeScore,
tblData.IandILU,
IIf([IandILU]="None","0",IIf([IandILU]="Medium","3",IIf([IandILU]="Heavy","5",IIf([IandILU]="Light","1"))))
AS IandIScore, tblData.GreaseLU,
IIf([GreaseLU]="None","0",IIf([GreaseLU]="Medium","3",IIf([GreaseLU]="Heavy","5",IIf([GreaseLU]="Light","1"))))
AS GreaseScore, tblData.OffSetLU,
IIf([OffSetLU]="None","0",IIf([OffSetLU]="Minor","1",IIf([OffSetLU]="Major","2",IIf([OffSetLU]="Severe","3"))))
AS OffSetScore, tblData.SagsLU,
IIf([SagsLU]="None","0",IIf([SagsLU]="Minor","1",IIf([SagsLU]="Major","2",IIf([SagsLU]="Severe","3"))))
AS SagsLUScore, tblData.MissingPancakeLU,
IIf([MissingPancakeLU]="None","0",IIf([MissingPancakeLU]="Major","3",IIf([MissingPancakeLU]="Severe","5",IIf([MissingPancakeLU]="Minor","1"))))
AS MissingPancakeScore, tblData.ManHoleCondLU, tblData.ReplaceManhole,
tblData.ManholeDepth, tblData.RootIntrusionLT, tblData.RootIntrusionMED,
tblData.RootIntrusionHVY, tblData.LongCrack, tblData.CircumCrack,
tblData.RootKill, tblData.[Main/PipeBackup], tblData.Inspection,
IIf([RootIntrusionLT]=Yes,1,IIf([RootIntrusionLT]=No,0)) AS RootIntruLTScore,
IIf([RootIntrusionMED]=Yes,3,IIf([RootIntrusionMED]=No,0)) AS
RootIntruMEDScore, IIf([RootIntrusionHVY]=Yes,5,IIf([RootIntrusionHVY]=No,0))
AS RootIntruHVYScore, IIf([LongCrack]=Yes,5,IIf([LongCrack]=No,0)) AS
LongCrackScore, IIf([CircumCrack]=Yes,5,IIf([CircumCrack]=No,0)) AS
CircumCrackScore,
CDbl(NZ([AgeScore],0))+CDbl(NZ([PointRepairScore],0))+CDbl(NZ([MainSizeScore],0))+CDbl(NZ([MaterialScore],0))+CDbl(NZ([DepthScore],0))+CDbl(NZ([PipeFailureScore],0))+CDbl(NZ([IandIScore],0))+CDbl(NZ([ReverseGradeScore],0))+CDbl(NZ([RootIntruLTScore],0))+CDbl(NZ([RootIntruMEDScore],0))+CDbl(NZ([RootIntruHVYScore],0))+CDbl(NZ([OffSetScore],0))+CDbl(NZ([SagsLUScore],0))+CDbl(NZ([LongCrackScore],0))+CDbl(NZ([CircumCrackScore],0))+CDbl(NZ([GreaseScore],0))+CDbl(NZ([MissingPancakeScore],0))
AS TotalScores, tblData.GenlComments, tblData.MainPipeBackupComments,
tblData.RootKillComments, tblData.RevGradeComments,
tblData.InspectionComments, tblData.PipeMissingComments,
tblData.PipeFailureComments, tblData.PtRepairComments,
tblData.ServConnComments, tblData.DamagedPipeComments,
tblData.LongCrackComments, tblData.CircumCrackComments,
tblData.OffSetJointsComments, tblData.RootIntruLTComments,
tblData.RootIntruMEDComments, tblData.RootIntruHVYComments,
tblData.OffSetJoints
FROM tblData;
