Data Report Question

[Catrina]

I am trying to create an employee hours report that can be sorted in 4 different ways (By Name, by number, by Div, Name and by Div, number).I am getting the message "DataField comHourlyRpt.RegHrs Not Found". But this field is in the query, in the report and in the SQL statement. Here is what I did:

I went into Access (not through code in VB) and created a Query selecting all the records from the Employee Hours table, I named it HoursQuery.

I then went into VB and created a Command in the Data Environment called comHourlyRpt. In the properties, I selected SQL and entered SELECT * FROM HoursQuery.

I dragged the fields into the report. Then here is the code I entered when running the report:

SQL$ = "SELECT Val(PAYEMP) as PAYEMP, Name,"
SQL$ = SQL$ & "VAL(RegHrs) * .01 AS Reg1,"
SQL$ = SQL$ & "VAL(OvtHrs) * .01 AS Ovt1,"
SQL$ = SQL$ & "VAL(dblHrs) * .01 AS Dbl,"
SQL$ = SQL$ & "VAL(reg2Hrs) * .01 AS Reg2,"
SQL$ = SQL$ & "VAL(Reg3Hrs) * .01 AS Reg3,"
SQL$ = SQL$ & "VAL(Ovt2Hrs) * .01 AS Ovt2,"
SQL$ = SQL$ & "VAL(Ovt3Hrs) * .01 AS Ovt3,"
SQL$ = SQL$ & "VAL(Oth1Ern) * .01 as Oth1,"
SQL$ = SQL$ & "VAL(SickHrs) * .01 AS Sick,"
SQL$ = SQL$ & "VAL(VacHrs) * .01 AS Vac,"
SQL$ = SQL$ & "VAL(HolHrs) * .01 AS Hol,"
SQL$ = SQL$ & "VAL(CommErn) * .01 AS Comm,"
SQL$ = SQL$ & "VAL(Meals) * .01 AS Meals,"
SQL$ = SQL$ & "VAL(Tips) * .01 AS Tips,"
SQL$ = SQL$ & "VAL(MiscHrs) * .01 AS Misc,"
SQL$ = SQL$ & "VAL(Oth2Ern) * .01 AS Oth2,"
SQL$ = SQL$ & "VAL(AdjToNet) * .01 AS Adj,"
SQL$ = SQL$ & "RecType,DeleteRec"
SQL$ = SQL$ & &quot; FROM HoursQuery WHERE RecType='05' AND DeleteRec <>'D' ORDER BY Name ASC&quot;

DtaReports.rscomHourlyRpt.Open SQL$
RptHourly.Sections(2).Controls(21).Caption = Mid(Header$, 29, 2) & &quot;/&quot; & Mid(Header$, 31, 2) & &quot;/&quot; & Mid(Header$, 33, 2)
RptHourly.PrintReport


When run I receive the error I mentioned. Any ideas what I am doing wrong?

One more thing. When I used the SQL$ directly in the Data Environment pulling the data from the Employee Records Table, the report ran fine, but I would have to create 4 different reports for each sorting.

Thanks
Catrina

 

[Perogy]

have you tried right-clicking and refreshing your Command, and everything else involved?

This sometimes helps...

Darryl