Date criteria syntax

  • Thread starter Thread starter Ginger
  • Start date Start date
G

Ginger

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
 
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
 
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
.
Click to expand...
.
Click to expand...
 
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)
 
Thank you Van, I'll give that a try. The If statement is
that this will be run on Fridays, showing Mon-Fri data. It
will also be run on Mondays, showing only Sat-Sun data.

I'll let you know if this works. Thank you for your quick
response.

Ginger

-----Original Message-----
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
Click to expand...

.
Click to expand...
 
Still got an error.

Extra ) in query expression '((qryBillingSummary.Date)
Between DateAdd('d',-5,Now()) and Now()))'.

Ginger

-----Original Message-----
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
Click to expand...

.
Click to expand...
 
Still got an error.

Extra ) in query expression '((qryBillingSummary.Date)
Between DateAdd('d',-5,Now()) and Now()))'.
Click to expand...

The trick I use to count parentheses is to mentally go through the
string, adding one for each ( and subtracting 1 for each ). Access's
habit of adding lots of uneeded extra parens makes this a more tedious
exercise!

The expression you have quoted appears to have an extra ) since it
comes out to -1. Just remove the last ). Putting each parenthetical
block on a separate line you can see why:

(
(qryBillingSummary.Date
)
BETWEEN DateAdd(
'd', -5, Now()
)
AND Now()
)

Note that: Date is a reserved word and is a bad choice of fieldnames;
I'd suggest either changing the fieldname to BillingDate or
consistantly using square brackets. Also, Now() is NOT today's date;
it'e the current date and time accurate to a few microseconds. If you
want billing records between 11:31:14 am five days ago and 11:31:14
today, the syntax above will work; if you want the past five days, I'd
suggest using Date() in place of Now().
 
Sorry. I didn't check your assignment statement. You had
unbalanced parentheses as John explained.

Regarding your explanation for the If statement, what
happens if Weekday is Tues / Wednes / Thurs / Sat or Sun?

You may be thinking of only Monday and Friday but sooner
rather than later, someone will run your Query on
the "wrong" day ...

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Still got an error.

Extra ) in query expression '((qryBillingSummary.Date)
Between DateAdd('d',-5,Now()) and Now()))'.

Ginger

-----Original Message-----
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
Click to expand...

.
Click to expand...
.
Click to expand...
 
Back
Top