1. For the IF statement, your comment seems to indicate
something different from your code???

2. Try:

sSQL = "TRANSFORM Sum(qryBillingSummary.Hrs) AS SumOfHrs "
& _
" SELECT qryBillingSummary.Name,
qryBillingSummary.MedRecNum, " & _
" qryBillingSummary.AdmitDate,
qryBillingSummary.Diagnosis, " & _
" qryBillingSummary.PlanName " & _
" FROM qryBillingSummary " & _
" WHERE " & dwhere & _
" GROUP BY qryBillingSummary.Name,
qryBillingSummary.MedRecNum, " & _
" qryBillingSummary.AdmitDate,
qryBillingSummary.Diagnosis, " & _
" qryBillingSummary.PlanName " & _
" PIVOT Format([Date],'Short Date')"

3. I could be wrong but I don't think you can use RunSQL
to execute a Cross-tab Query since RunSQL can only be used
with Action Queries, e.g. INSERT, UPDATE, DELETE ... while
transform is more like a SELECT Query.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Well, it worked in a query, but not in the coding. I still
need syntax assistance with this.

Here's what I have. It is the sql statement that turns red
and won't run..

Dim rs As Recordset, db As Database
Dim sSQL As String, dwhere As String

'determine if the weekday is monday or friday
If Weekday(Now()) = vbMonday Then

dwhere = "((qryBillingSummary.Date) Between
DateAdd('d',-3,Now()) And Now()))"

Else

dwhere = "((qryBillingSummary.Date) Between
DateAdd('d',-5,Now()) And Now()))"

End If

sSQL = "TRANSFORM Sum(qryBillingSummary.Hrs) AS
SumOfHrs " _
& "SELECT qryBillingSummary.Name,
qryBillingSummary.MedRecNum, " _
& "qryBillingSummary.AdmitDate,
qryBillingSummary.Diagnosis, " _
& "qryBillingSummary.PlanName " _
& "FROM qryBillingSummary " _
& "WHERE " & "'"dwhere "'"" _
& "GROUP BY qryBillingSummary.Name,
qryBillingSummary.MedRecNum, " _
& "qryBillingSummary.AdmitDate,
qryBillingSummary.Diagnosis, " _
& "qryBillingSummary.PlanName " _
& "PIVOT Format([Date],"Short Date")"

DoCmd.RunSQL sSQL