A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date criteria syntax



 
 
Thread Tools Display Modes
  #1  
Old May 24th, 2004, 12:15 AM
Ginger
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 01:06 AM
Ginger
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 01:16 AM
Ginger
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 03:17 AM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 03:56 AM
Ginger
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 04:04 AM
Ginger
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 05:03 AM
John Vinson
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 06:07 AM
Ginger
external usenet poster
 
Posts: n/a
Default 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  
Old May 24th, 2004, 06:33 AM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.