Date criteria syntax - Still need help
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
-----Original Message-----
sorry for the double post.
I think I got it. I used the following for the where and
can change the where in my sql according to my if statement
Between DateAdd("d",-90,Now()) And Now()
Thanks anyway!
Ginger
-----Original Message-----
My last post did not appear, so here it is again.
I get an error on the "short date" format. Any suggestions?
sSQL = "TRANSFORM Sum(qryBillingSummary.Hrs) AS SumOfHrs " _
& "SELECT qryBillingSummary.Name,
qryBillingSummary.MedRecNum, " _
& "qryBillingSummary.AdmitDate,
qryBillingSummary.Diagnosis, " _
& "qryBillingSummary.PlanName " _
& "FROM qryBillingSummary " _
& "WHERE qryBillingSummary.AdmitDate between bdate and
edate " _
& "GROUP BY qryBillingSummary.Name,
qryBillingSummary.MedRecNum, " _
& "qryBillingSummary.AdmitDate,
qryBillingSummary.Diagnosis, " _
& "qryBillingSummary.PlanName " _
& "PIVOT Format([AdmitDate],"short date")"
Thank you in advance!
Ginger
.
.
|