SQL Statement Problem

[Catrina]

I am using an Access 2000 database and trying to create reports for a payroll application. I have created queries in Access to draw the report information from. I am using VB DataEnvironment and Datareport. I have on report working, but have a problem with the second that I can not figure out.

Working Report. I created a Access query that pulls needed info from one table(All string types), I then get the VAL, * .01 to set the decimal correctly, then order it the way the user selects. The Query is HoursQuery, and in the DataEnvironment command I use SELECT * FROM HoursQuery. Here is the code I use when calling the report

SQL$ = ""
SQL$ = "SELECT Val(PAYEMP) as PAYEMP, Name,"
SQL$ = SQL$ & "VAL(RegHrs) * .01 AS RegHrs,"
SQL$ = SQL$ & "VAL(OvtHrs) * .01 AS OvtHrs,"
SQL$ = SQL$ & "VAL(dblHrs) * .01 AS DblHrs,"
SQL$ = SQL$ & "VAL(reg2Hrs) * .01 AS Reg2Hrs,"
SQL$ = SQL$ & "VAL(Reg3Hrs) * .01 AS Reg3Hrs,"
SQL$ = SQL$ & "VAL(Ovt2Hrs) * .01 AS Ovt2Hrs,"
SQL$ = SQL$ & "VAL(Ovt3Hrs) * .01 AS Ovt3Hrs,"
SQL$ = SQL$ & "VAL(Oth1Ern) * .01 as Oth1Ern,"
SQL$ = SQL$ & "VAL(SickHrs) * .01 AS SickHrs,"
SQL$ = SQL$ & "VAL(VacHrs) * .01 AS VacHrs,"
SQL$ = SQL$ & "VAL(HolHrs) * .01 AS HolHrs,"
SQL$ = SQL$ & "VAL(CommErn) * .01 AS CommErn,"
SQL$ = SQL$ & "VAL(Meals) * .01 AS Meals,"
SQL$ = SQL$ & "VAL(Tips) * .01 AS Tips,"
SQL$ = SQL$ & "VAL(MiscHrs) * .01 AS MiscHrs,"
SQL$ = SQL$ & "VAL(Oth2Ern) * .01 AS Oth2Ern,"
SQL$ = SQL$ & "VAL(AdjToNet) * .01 AS AdjToNet,"
SQL$ = SQL$ & "RecType,DeleteRec"
If PR.PAGEORDER = 5 And ManPage% = 0 Then
frmBalanceMenu.KeyPreview = False
Disable

fraPaging.Visible = True
txtPaging.Text = ""
txtPaging.SetFocus
Stop
Exit Sub
End If
If PR.PAGEORDER = 1 Or ManPage% = 1 Then
SQL$ = SQL$ & " FROM HoursQuery WHERE _
RecType='05' AND DeleteRec <>'D' ORDER BY Name ASC&quot;
GoTo Choose
End If
If PR.PAGEORDER = 2 Or ManPage% = 2 Then
SQL$ = SQL$ &amp; &quot; FROM HoursQuery WHERE _
RecType='05' AND DeleteRec <>'D' ORDER BY PAYEMP ASC&quot;
GoTo Choose
End If
If PR.PAGEORDER = 3 Or ManPage% = 3 Then
SQL$ = SQL$ &amp; &quot; FROM HoursQuery WHERE _
RecType='05' AND DeleteRec <>'D' ORDER BY Div,Dept,PAYEMP ASC&quot;
GoTo Choose
End If
If PR.PAGEORDER = 4 Or ManPage% = 4 Then
SQL$ = SQL$ &amp; &quot; FROM HoursQuery WHERE _
RecType='05' AND DeleteRec <>'D' ORDER BY Div,Dept,Name ASC&quot;
GoTo Choose
End If
Exit Sub
Choose:
DtaReports.rscomHourlyRpt.Open SQL$

RptHourly.PrintReport
MsgBox &quot;printing&quot;


This works exactly like I need. Now for the problem report. It is a little more complicated.
I have created a query in Access with 3 joined tables.
In the DataEnvironment I use SELECT * FROM Salary2Query.
The report runs fine with all the information needed included,
but I need to get the VAL of the string data and divide by .01 just as before.
But in this case I keep getting the error &quot;No Value given for one or more require
parameters&quot;. I'm not sure what I am doing wrong.
COuld it be the joining of the tables giving
me problems? I'm rather new to VB, so detailed help would be valued. Here is the code for it

'Checking% = 1
Checking% = 2
SQL$ = &quot;&quot;
SQL$ = &quot;SELECT Rates.PAYEMP,EmployeeHours.PAYEMP,LName,FstName,&quot;
SQL$ = SQL$ &amp; &quot;VAL(RegHrs) * .01 AS RegHrs,&quot;
SQL$ = SQL$ &amp; &quot;VAL(OvtHrs) * .01 AS OvtHrs,&quot;
SQL$ = SQL$ &amp; &quot;VAL(dblHrs) * .01 AS DblHrs,&quot;
SQL$ = SQL$ &amp; &quot;VAL(Oth1Ern) * .01 as Oth1Ern,&quot;
SQL$ = SQL$ &amp; &quot;VAL(SickHrs) * .01 AS SickHrs,&quot;
SQL$ = SQL$ &amp; &quot;VAL(VacHrs) * .01 AS VacHrs,&quot;
SQL$ = SQL$ &amp; &quot;VAL(HolHrs) * .01 AS HolHrs,&quot;
SQL$ = SQL$ &amp; &quot;VAL(CommErn) * .01 AS CommErn,&quot;
SQL$ = SQL$ &amp; &quot;VAL(Meals) * .01 AS Meals,&quot;
SQL$ = SQL$ &amp; &quot;VAL(Tips) * .01 AS Tips,&quot;
SQL$ = SQL$ &amp; &quot;VAL(MiscHrs) * .01 AS MiscHrs,&quot;
SQL$ = SQL$ &amp; &quot;VAL(Oth2Ern) * .01 AS Oth2Ern,&quot;
SQL$ = SQL$ &amp; &quot;VAL(AdjToNet) * .01 AS AdjToNet,DeletRec &quot;
SQL$ = SQL$ &amp; &quot;FROM Salary2Query WHERE DeleteRec <>'D'&quot;
If Checking% = 2 Then SQL$ = SQL$ &amp; &quot; ORDER BY Rates.PAYEMP asc&quot;
If Checking% = 1 Then SQL$ = SQL$ &amp; &quot; ORDER BY LName,FstName asc&quot;

DtaReports.rscomSalary.Open SQL$

RptSalary.PrintReport
MsgBox &quot;printing&quot;


Any ideas or help would be greatly appreciated! Thanks

Catrina

 

[caf]

Check your syntax, you could've misspelled a field name. The best way to test this is to paste the entire sql statement into access &amp; run it. Access will prompt you for parameters &amp; you'll have to spot which one it is that you never meant to be a parameter but instead meant to be a field name.

Seasons greetings
caf

BTW: Place a breakpoint on the line DtaReports.rscomSalary.Open SQL$

Bring up the debug/immediate window (normally Ctrl+G) &amp; type the foll. text: ?SQL$ &amp; press Enter
Now copy/paste the output into MS Access (New Query - SQL View)

Run the Query.