calculation within a query?

[1jet]

Hi all,

Please have a quick look at my awesome VBA code...

        strSQL = "INSERT INTO tblHours ( [Project ID], [Employee ID], TS_Date, TS_Hours ) VALUES (" & selectproject & ", Forms!frmEmployeeTimesheet!cboSelectName, " & CStr(datebox) & ", " & CStr(Box) & ");"
        DoCmd.RunSQL strSQL

I'd like to be able insert another value into tblHours. The field name has already been setup as "Payment." The value is calculated as follows...
CStr(Box) * ( tblEmployee.[Hourly Rate] WHERE [Employee ID] = tblEmployee.[Hourly Rate] )

Now I'm not sure of the correct syntax but the below is the best I was able to come up with.

strSQL = "INSERT INTO tblHours ( [Project ID], [Employee ID], TS_Date, TS_Hours, [COLOR=Red]Payment [/COLOR]) VALUES (" & selectproject & ", Forms!frmEmployeeTimesheet!cboSelectName, " & CStr(datebox) & ", " & CStr(Box) & "[COLOR=Red], tblEmployee.[Hourly Rate] * " & Box & ") WHERE ( tblHours.[Employee ID] = Forms!frmEmployeeTimesheet!cboSelectName) ;"[/COLOR]

Now when I run this, the error is "Query input must contain at least one table or query"

Help?

 

[WayneRyan]

1jet,

The Values clause implies that it is a list of values,
The Where clause implies that the data is from a table.
It can't be both.

Change:

strSQL = "INSERT INTO tblHours ( [Project ID], 
                                 [Employee ID], 
                                 TS_Date, 
                                 TS_Hours, 
                                 Payment ) 
          VALUES (" & selectproject & ", 
                      Forms!frmEmployeeTimesheet!cboSelectName, " & 
                      CStr(datebox) & ", " & 
                      CStr(Box) & ", 
                      tblEmployee.[Hourly Rate] * " & 
                      Box & ") 
          WHERE ( tblHours.[Employee ID] = Forms!frmEmployeeTimesheet!cboSelectName) ;"

To:

strSQL = "INSERT INTO tblHours ( [Project ID], 
                                 [Employee ID], 
                                 TS_Date, 
                                 TS_Hours, 
                                 Payment ) 
          VALUES (" & selectproject & ", 
                      Forms!frmEmployeeTimesheet!cboSelectName, " & 
                      CStr(datebox) & ", " & 
                      CStr(Box) & ", 
                      Nz([B]DLookUp[/B]("[Hourly Rate]", "tblEmployee", "[Employee ID] = " & Forms!frmEmployeeTimesheet!cboSelectName), 0)

But I don't see any "punctuation" in your values clause.
Dates should be surrounded by # -->

strSQL = "INSERT INTO tblHours ( [Project ID], " & _
         "                       [Employee ID], " & _
         "                       TS_Date, " & _
         "                       TS_Hours, " & _
         "                       Payment ) " & _
         "VALUES (" & selectproject & ", " & _
                      Forms!frmEmployeeTimesheet!cboSelectName, [B][SIZE="3"]#[/SIZE][/B]" & 
                      CStr(datebox) & "[B][SIZE="3"]#[/SIZE][/B], " & _
                      CStr(Box) & ", " & _
                      Nz(DLookUp("[Hourly Rate]", "tblEmployee", "[Employee ID] = " & Forms!frmEmployeeTimesheet!cboSelectName), 0) & ")"

hth
Wayne

 

[1jet]

sorry wayne...u've misread my question

in my query, the 5th value going into tblHours.Payment is not tblEmployee.[Hourly Rate]
instead, its actually tblEmployee.[Hourly Rate] * Box (where Box is a textbox with number of hours worked)

Sorry about my messy code...but I've tidied it up as follow making some changes since my first post. I think the problem could be that I need to JOIN tblHours and tblEmployee to get [Hourly Rate].

        strSQL = "INSERT INTO tblHours ( [Project ID], [Employee ID], TS_Date, TS_Hours, Payment ) " & _
                    " SELECT (" & selectproject & ", Forms!frmEmployeeTimesheet!cboSelectName, " & CStr(datebox) & ", " & CStr(Box) & ", (tblEmployee.[Hourly Rate] * " & Box & " )) " & _
                    " FROM ( tblHours INNER JOIN tblEmployee ) " & _
                    " ON ( tblHours.[Employee ID] = tblEmployee.[Employee ID] ) " & _
                    " WHERE ( tblEmployee.[Employee ID] = Forms!frmEmployeeTimesheet!cboSelectName ) ; "
        DoCmd.RunSQL strSQL