#error in query results

[John]

When I run a select query of my production table, I get a "#error" message
in many of the result cells. The Table that I query includes some groups of
cells where there is no production for certain periods of time, and empty
cells for some individuals who don't perform certain tasks. I want to be
able to generate collective reports for all employees and tasks, but would
like to clean up the #error message in those result cells (Perhaps
indicating a "0" or "N/A" instead. Any guidance will be appreciated. Thanks

 

[Tom Wickerath]

Hi John,

It would be helpful if you included the SQL statement for this query. In query design
view, click on View > SQL View. Copy the SQL statement and paste it in a reply, so that
we can see the actual query. Also helpful would be to let us know the datatype (text,
long integer, etc.) of all fields referenced in your SQL statement. You can find this
information in table design view for each table referenced by your query.

Tom
______________________________________


When I run a select query of my production table, I get a "#error" message
in many of the result cells. The Table that I query includes some groups of
cells where there is no production for certain periods of time, and empty
cells for some individuals who don't perform certain tasks. I want to be
able to generate collective reports for all employees and tasks, but would
like to clean up the #error message in those result cells (Perhaps
indicating a "0" or "N/A" instead. Any guidance will be appreciated. Thanks

 

[John]

Following is the SQL statement:
SELECT DISTINCTROW RegLicProd.Name,
Sum([RegLicProd]![RCtyBlk])/Sum([RegLicProd]![RCtyBlkHrs]) AS RCBlk,
Sum([RegLicProd]![RAddChg])/Sum([RegLicProd]![RAddChgHrs]) AS RAddChg,
Sum([RegLicProd]![R EdSrt])/Sum([RegLicProd]![R EdSrtHrs]) AS REdSrt,
Sum([RegLicProd]![Corr])/Sum([RegLicProd]![CorrHrs]) AS CorroRate,
Sum([RegLicProd]![R RP])/Sum([RegLicProd]![R RP Hrs]) AS [R RP],
Sum([RegLicProd]![R Rej])/Sum([RegLicProd]![R RejHrs]) AS [R Rej],
Sum([RegLicProd]![LinMx])/Sum([RegLicProd]![LinMXHrs]) AS LinMx,
Sum([RegLicProd]![L RP])/Sum([RegLicProd]![L RP Hrs]) AS [L RP],
Sum([RegLicProd]![LrnMX])/Sum([RegLicProd]![LrnMxHrs]) AS LrnMx,
Sum([RegLicProd]![LicPrnt])/Sum([RegLicProd]![LicPrntHrs]) AS LicPrnt,
Sum([RegLicProd]![LEdSrt])/Sum([RegLicProd]![LEdSrtHrs]) AS LEdSrt,
Sum([RegLicProd]![LRej])/Sum([RegLicProd]![LRejHrs]) AS LRej
FROM RegLicProd
WHERE (((RegLicProd.Date) Between [Enter Begin Date (M/D/YYYY)] And [Enter
End Date(M/D/YYYY)]))
GROUP BY RegLicProd.Name;

The datatype for the production counts (e.g. RCtyBlk) is Number/Long
Integer, and the hours worked (e.g. RCtyBlkHrs) is Single/General Number.
Perhaps this has been the source of my problem.

 

[Tom Wickerath]

Hi John,

I created the table you described, and placed some dummy data into two records. At
present, I have not been able to reproduce a #error message using this SQL statement. Is
it possible for you to send just the table RegLicProd and this saved query to me? If you
will, then I can try to look at it more for you.

I don't think this is causing your errors, but I recommend renaming two fields in this
table: "Name" and "Date". These two field names are using reserved words in Access. See
the following KB article for more information:

ACC2000: Reserved Words in Microsoft Access
http://support.microsoft.com/?id=209187

Tom


________________________________________________


Following is the SQL statement:

SELECT DISTINCTROW RegLicProd.Name,
Sum([RegLicProd]![RCtyBlk])/Sum([RegLicProd]![RCtyBlkHrs]) AS RCBlk,
Sum([RegLicProd]![RAddChg])/Sum([RegLicProd]![RAddChgHrs]) AS RAddChg,
Sum([RegLicProd]![R EdSrt])/Sum([RegLicProd]![R EdSrtHrs]) AS REdSrt,
Sum([RegLicProd]![Corr])/Sum([RegLicProd]![CorrHrs]) AS CorroRate,
Sum([RegLicProd]![R RP])/Sum([RegLicProd]![R RP Hrs]) AS [R RP],
Sum([RegLicProd]![R Rej])/Sum([RegLicProd]![R RejHrs]) AS [R Rej],
Sum([RegLicProd]![LinMx])/Sum([RegLicProd]![LinMXHrs]) AS LinMx,
Sum([RegLicProd]![L RP])/Sum([RegLicProd]![L RP Hrs]) AS [L RP],
Sum([RegLicProd]![LrnMX])/Sum([RegLicProd]![LrnMxHrs]) AS LrnMx,
Sum([RegLicProd]![LicPrnt])/Sum([RegLicProd]![LicPrntHrs]) AS LicPrnt,
Sum([RegLicProd]![LEdSrt])/Sum([RegLicProd]![LEdSrtHrs]) AS LEdSrt,
Sum([RegLicProd]![LRej])/Sum([RegLicProd]![LRejHrs]) AS LRej
FROM RegLicProd
WHERE (((RegLicProd.Date) Between [Enter Begin Date (M/D/YYYY)] And [Enter
End Date(M/D/YYYY)]))
GROUP BY RegLicProd.Name;

The datatype for the production counts (e.g. RCtyBlk) is Number/Long
Integer, and the hours worked (e.g. RCtyBlkHrs) is Single/General Number.
Perhaps this has been the source of my problem.