need more help... new to access how to loop through a table

[terry]

With the help of Rick Brandt, Dan Artuso, and John Vinson through a previous
post I wrote this function to loop through a table... But I am getting an
error message that my table name is spelled incorrectly and cannot be found.
I may have trouble because all the table names in this database have many
spaces in them... so if you can help me one more time (probably more)....
heree is the function I wrote

The table name is correctly spelled because I copied and pasted it into the
code... Also I tried it with quotes around the table name and also without
the table! in front of the name, but the name still in brackets.


Function CalcAuth(clientid) As Double
Dim calchours As Double
Dim rsa As Recordset
Dim rsp As Recordset
Dim strSqlA As String
Dim strSqlP As String

strSqlA = "Select * From table![CC PCS Authorized Units] Where [au-fk
client id] = clientid"
strSqlP = "Select * from table![CC Procedure Codes] where [proc-pk proc
id] = ra![AU-FK Proc ID]"
Set rsa = CurrentDb.OpenRecordset(strSql)
Set rsp = CurrentDb.OpenRecordset(strSqlP)

Do While Not rs.EOF
If rsa![AU To Date] >= Now() Then
rsp.Requery
calchours = calchours + (rsa![AU Authorized Units] / rsp![Proc
Unit Multiplier])
End If
rsa.MoveNext
Loop

Set rsa = Nothing
Set rsp = Nothing

CalcAuth = calchours
End Function

 

[Rick Brandt]

With the help of Rick Brandt, Dan Artuso, and John Vinson through a
previous post I wrote this function to loop through a table... But I
am getting an error message that my table name is spelled incorrectly
and cannot be found. I may have trouble because all the table names
in this database have many spaces in them... so if you can help me
one more time (probably more).... heree is the function I wrote

The table name is correctly spelled because I copied and pasted it
into the code... Also I tried it with quotes around the table name
and also without the table! in front of the name, but the name still
in brackets.

strSqlA = "Select * From [CC PCS Authorized Units] Where [au-fk client id] =
clientid"

....is proper syntax as far as the table name is concerned, but I don't
understand your WHERE clause. Is clientid a variable? You can't reference
a variable within a SQL statement. Is it a field or control on the form
where the code is running? If any of these, it needs to be outside the
quotes so you SQL string evaluates to the *value* of the clientid rather
than the *name* clientid.

strSqlA = "Select * From [CC PCS Authorized Units] Where [au-fk client id] =
" & clientid

 

[terry]

clientid is being passed to the funtion from a query in a report. I changed
it to the suggestion you gave and now I'm getting the error "syntax error in
from clause"

With the help of Rick Brandt, Dan Artuso, and John Vinson through a
previous post I wrote this function to loop through a table... But I
am getting an error message that my table name is spelled incorrectly
and cannot be found. I may have trouble because all the table names
in this database have many spaces in them... so if you can help me
one more time (probably more).... heree is the function I wrote

The table name is correctly spelled because I copied and pasted it
into the code... Also I tried it with quotes around the table name
and also without the table! in front of the name, but the name still
in brackets.

strSqlA = "Select * From [CC PCS Authorized Units] Where [au-fk client id] =
clientid"

....is proper syntax as far as the table name is concerned, but I don't
understand your WHERE clause. Is clientid a variable? You can't reference
a variable within a SQL statement. Is it a field or control on the form
where the code is running? If any of these, it needs to be outside the
quotes so you SQL string evaluates to the *value* of the clientid rather
than the *name* clientid.

strSqlA = "Select * From [CC PCS Authorized Units] Where [au-fk client id] =
" & clientid

 

[John Vinson]

clientid is being passed to the funtion from a query in a report. I changed
it to the suggestion you gave and now I'm getting the error "syntax error in
from clause"

Please post your code.

John W. Vinson[MVP]

 

[John Vinson]

Dim strSqlP As String

strSqlA = "Select * From table![CC PCS Authorized Units] Where [au-fk
client id] = clientid"
strSqlP = "Select * from table![CC Procedure Codes] where [proc-pk proc
id] = ra![AU-FK Proc ID]"
Set rsa = CurrentDb.OpenRecordset(strSql)
Set rsp = CurrentDb.OpenRecordset(strSqlP)

Do While Not rs.EOF
If rsa![AU To Date] >= Now() Then
rsp.Requery
calchours = calchours + (rsa![AU Authorized Units] / rsp![Proc
Unit Multiplier])
End If
rsa.MoveNext
Loop

Set rsa = Nothing
Set rsp = Nothing

CalcAuth = calchours
End Function

You'll also need to drop the table! term; it's invalid. Just use the
table name in brackets. Also, in strSqlP you have a reference to ra!
which a) isn't valid in a SQL string and b) isn't defined anywhere,
unless it's a typo for rsa!.

How about NO CODE AT ALL:

SELECT Sum([AU Authorized Units] / [Proc Unit Multiplier]) AS CalcAuth
FROM [CC PCS Authorized Units]
INNER JOIN [CC Procedure Codes]
ON [CC PCS Authorized Units].[AU-FK Proc ID] = [CC Procedure
Codes].[proc-pk proc id]
WHERE [AU To Date] > Now();

or some minor variation on this?

John W. Vinson[MVP]
SET

 

[Rick Brandt]

clientid is being passed to the funtion from a query in a report. I
changed it to the suggestion you gave and now I'm getting the error
"syntax error in from clause"

My standard practice to set up and troubleshoot this kind of stuff is to put a
break point in the code immediately after the SQL string variable has been set
and then go to the immediate window and type...

?strSqlA <Enter>

This will return the exact string contained in the variable which I can examine
for errors or even paste into a temporary query object to make sure it works. I
suggest you try that.

 

[terry]

I'm calling the function from a summary line in a report which already is
using this query for the detail
This query is the schedule of visits of home health employees to clients
homes.
It calculates the number of hours the employee spend in the client's home
per week.
The information I was trying to get from the other query was the number of
hours the client is authorized to receive per week. The function was to
return the authorized hours in the summary band for the client. Do you have
any suggestions on how I can accomplish that?

Thanks for all your help...

SELECT Employee![EMP Last Name] & ", " & Employee![EMP First Name] & " " &
Employee![EMP Middle Name] AS Employee, IIf(IsNull(Clients![CL Last
Name]),"",Clients![CL Last Name] & ", " & Clients![CL First Name] & " " &
Clients![CL Middle Name]) AS Client, Schedule.[SCH Sunday], Schedule.[SCH
Monday], Schedule.[SCH Tuesday], Schedule.[SCH Wednesday], Schedule.[SCH
Thursday], Schedule.[SCH Friday], Schedule.[SCH Saturday], Schedule.[SCH
Start Time], Schedule.[SCH End Time], Schedule.[SCH EveryOtherWeekend],
Schedule.[SCH Comments], Schedule.[SCH Calculate Emp Hours], calchours([SCH
End Time]-[SCH Start Time],[SCH Monday],[SCH Tuesday],[SCH Wednesday],[SCH
Thursday],[SCH Friday],[SCH Saturday],[SCH Sunday],[SCH
EveryOtherWeekend],[SCH Calculate Emp Hours]) AS hours, Employee.[EMP-PK Emp
ID], Clients.[CL-PK Client ID]
FROM Clients RIGHT JOIN (Employee INNER JOIN Schedule ON Employee.[EMP-PK
Emp ID] = Schedule.[SCH-FK Emp ID]) ON Clients.[CL-PK Client ID] =
Schedule.[SCH-FK Client ID]
ORDER BY Employee![EMP Last Name] & ", " & Employee![EMP First Name] & " " &
Employee![EMP Middle Name], Schedule.[SCH Sunday], Schedule.[SCH Monday],
Schedule.[SCH Tuesday], Schedule.[SCH Wednesday], Schedule.[SCH Thursday],
Schedule.[SCH Friday], Schedule.[SCH Saturday], Schedule.[SCH Start Time],
Schedule.[SCH End Time];

Dim strSqlP As String

strSqlA = "Select * From table![CC PCS Authorized Units] Where [au-fk
client id] = clientid"
strSqlP = "Select * from table![CC Procedure Codes] where [proc-pk proc
id] = ra![AU-FK Proc ID]"
Set rsa = CurrentDb.OpenRecordset(strSql)
Set rsp = CurrentDb.OpenRecordset(strSqlP)

Do While Not rs.EOF
If rsa![AU To Date] >= Now() Then
rsp.Requery
calchours = calchours + (rsa![AU Authorized Units] / rsp![Proc
Unit Multiplier])
End If
rsa.MoveNext
Loop

Set rsa = Nothing
Set rsp = Nothing

CalcAuth = calchours
End Function

You'll also need to drop the table! term; it's invalid. Just use the
table name in brackets. Also, in strSqlP you have a reference to ra!
which a) isn't valid in a SQL string and b) isn't defined anywhere,
unless it's a typo for rsa!.

How about NO CODE AT ALL:

SELECT Sum([AU Authorized Units] / [Proc Unit Multiplier]) AS CalcAuth
FROM [CC PCS Authorized Units]
INNER JOIN [CC Procedure Codes]
ON [CC PCS Authorized Units].[AU-FK Proc ID] = [CC Procedure
Codes].[proc-pk proc id]
WHERE [AU To Date] > Now();

or some minor variation on this?

John W. Vinson[MVP]
SET

 

[John Vinson]

I'm calling the function from a summary line in a report which already is
using this query for the detail
This query is the schedule of visits of home health employees to clients
homes.
It calculates the number of hours the employee spend in the client's home
per week.
The information I was trying to get from the other query was the number of
hours the client is authorized to receive per week. The function was to
return the authorized hours in the summary band for the client. Do you have
any suggestions on how I can accomplish that?

Terry, did you ever get a resolution on this issue?

John W. Vinson[MVP]

 

[tw]

No, I just put it on the back burner and went on to other reports and
features of the system. I do still need to figure this out.