If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date criteria syntax
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 |
#2
|
|||
|
|||
Date criteria syntax
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 . |
#3
|
|||
|
|||
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 . . |
#4
|
|||
|
|||
Date criteria syntax - Still need help
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 |
#5
|
|||
|
|||
Date criteria syntax - Still need help
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 . |
#6
|
|||
|
|||
Date criteria syntax - Still need help
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 . |
#7
|
|||
|
|||
Date criteria syntax - Still need help
On Sun, 23 May 2004 20:04:04 -0700, "Ginger"
wrote: Still got an error. Extra ) in query expression '((qryBillingSummary.Date) Between DateAdd('d',-5,Now()) and Now()))'. 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(). John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#8
|
|||
|
|||
Date criteria syntax - Still need help
Thank you! Your help is greatly appreciated.
Ginger -----Original Message----- On Sun, 23 May 2004 20:04:04 -0700, "Ginger" wrote: Still got an error. Extra ) in query expression '((qryBillingSummary.Date) Between DateAdd('d',-5,Now()) and Now()))'. 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(). John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public . |
#9
|
|||
|
|||
Date criteria syntax - Still need help
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 . . |
Thread Tools | |
Display Modes | |
|
|