RE: Grouping help
Jake, Alright, didn't know that you were on Access. it's been a while since I've used Access but I'm pretty sure that you want to parse out each part of your date, concatenate it together and return it as a string using CStr(). The whole SQL query would look like this. SELECT theTitle CStr(Month(theDate) & '/' & Day(theDate) & '/' & Year(theDate)) AS theDate FROM theTable ORDER BY theDate This will return the date as a string with no timestamp on it. Performance might suck just a little bit, but this is access after all. . Hope this helps, -- Jeff Garza _ From: Jake . [mailto:[EMAIL PROTECTED] Sent: Sunday, April 04, 2004 5:09 AM To: CF-Talk Subject: Re: Grouping help Not sure if that would help. I'll have to check it out, since I'm using Access. Thanks! >If you are using dates to group by, make sure to get rid of any extraneous >information from the datefield. If you are using SQL Server, a datetime >field will return the date, hours, minutes, seconds, and milliseconds. You >can use a CAST or CONVERT function in your SQL statement to limit what is >returned to just year, month and day. > > > >SELECT theTitle, CONVERT(varchar(10), theDate, 101) AS dateasstring >FROM theTable >ORDER BY theDate > > > >Would this help any? > > > >Jeff Garza > > > > _ > >From: Jake . [mailto:[EMAIL PROTECTED] >Sent: Saturday, April 03, 2004 10:00 AM >To: CF-Talk >Subject: Re: Grouping help > > > >Thanks, I appreciate the help. Looks like part of my problem is (as always) >the fact that I'm using dates. Does this method work with dates? > > > _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Grouping help
Jake: What's the query look like now? > -Original Message- > From: Jake . [mailto:[EMAIL PROTECTED] > Sent: Sunday, April 04, 2004 5:09 AM > To: CF-Talk > Subject: Re: Grouping help > > > Not sure if that would help. I'll have to check it out, since > I'm using Access. Thanks! > > >If you are using dates to group by, make sure to get rid of any > >extraneous information from the datefield. If you are using SQL > >Server, a datetime field will return the date, hours, > minutes, seconds, > >and milliseconds. You can use a CAST or CONVERT function in > your SQL > >statement to limit what is returned to just year, month and day. > > > > > > > >SELECT theTitle, CONVERT(varchar(10), theDate, 101) AS dateasstring > >FROM theTable ORDER BY theDate > > > > > > > >Would this help any? > > > > > > > >Jeff Garza > > > > > > > > _ > > > >From: Jake . [mailto:[EMAIL PROTECTED] > >Sent: Saturday, April 03, 2004 10:00 AM > >To: CF-Talk > >Subject: Re: Grouping help > > > > > > > >Thanks, I appreciate the help. Looks like part of my problem is (as > >always) the fact that I'm using dates. Does this method work with > >dates? > > > > > > _ > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Grouping help
Not sure if that would help. I'll have to check it out, since I'm using Access. Thanks! >If you are using dates to group by, make sure to get rid of any extraneous >information from the datefield. If you are using SQL Server, a datetime >field will return the date, hours, minutes, seconds, and milliseconds. You >can use a CAST or CONVERT function in your SQL statement to limit what is >returned to just year, month and day. > > > >SELECT theTitle, CONVERT(varchar(10), theDate, 101) AS dateasstring >FROM theTable >ORDER BY theDate > > > >Would this help any? > > > >Jeff Garza > > > > _ > >From: Jake . [mailto:[EMAIL PROTECTED] >Sent: Saturday, April 03, 2004 10:00 AM >To: CF-Talk >Subject: Re: Grouping help > > > >Thanks, I appreciate the help. Looks like part of my problem is (as always) >the fact that I'm using dates. Does this method work with dates? > > > _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Grouping help
If you are using SQL Server, here is one solution I came up with: SELECT EntryInfo, DATENAME(mm,EntryDate) + ' ' + CAST(DATEPART(d,EntryDate) AS varchar(2)) AS DisplayDate, EntryDate FROM DateGroup ORDER BY EntryDate #DisplayDate# #EntryInfo# I created a test table called DateGroup with two fields: EntryInfo varchar(20) EntryDate smalldatetime (4) The other solution wasn't taking into account that your dates had times associated with them, so when the output was being grouped, a new group was created for each event because each date/time value was unique! I like this solution because it allows the database to do the work, which is where I feel it should be done in many situations. You shouldn't have too much of a problem adapting the code to work with RDBMSs other than SQL Server, I don't think. Andy -Original Message- From: Jake . [mailto:[EMAIL PROTECTED] Sent: Saturday, April 03, 2004 12:00 PM To: CF-Talk Subject: Re: Grouping help Thanks, I appreciate the help. Looks like part of my problem is (as always) the fact that I'm using dates. Does this method work with dates? >I have a tutorial on grouping in CF here: >http://tutorial150.easycfm.com/ > >Hope that helps :) > >Charlie > > >> [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Grouping help
If you are using dates to group by, make sure to get rid of any extraneous information from the datefield. If you are using SQL Server, a datetime field will return the date, hours, minutes, seconds, and milliseconds. You can use a CAST or CONVERT function in your SQL statement to limit what is returned to just year, month and day. SELECT theTitle, CONVERT(varchar(10), theDate, 101) AS dateasstring FROM theTable ORDER BY theDate Would this help any? Jeff Garza _ From: Jake . [mailto:[EMAIL PROTECTED] Sent: Saturday, April 03, 2004 10:00 AM To: CF-Talk Subject: Re: Grouping help Thanks, I appreciate the help. Looks like part of my problem is (as always) the fact that I'm using dates. Does this method work with dates? >I have a tutorial on grouping in CF here: >http://tutorial150.easycfm.com/ > >Hope that helps :) > >Charlie > > >> _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Grouping help
Thanks, I appreciate the help. Looks like part of my problem is (as always) the fact that I'm using dates. Does this method work with dates? >I have a tutorial on grouping in CF here: >http://tutorial150.easycfm.com/ > >Hope that helps :) > >Charlie > > >> [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Grouping help
Unfortunately, that method yielded: == Date == Entry == Date == Entry == Date == Entry Ideas? > >SELECT theTitle, theDate >FROM theTable >ORDER BY theDate > > >== #DateFormat(qTest.theDate," d")# == > >- #qTest.theTitle# > > > >Pascal > >> [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Grouping help
Jake: I have a tutorial on grouping in CF here: http://tutorial150.easycfm.com/ Hope that helps :) Charlie > -Original Message- > From: Jake McKee [mailto:[EMAIL PROTECTED] > Sent: Saturday, April 03, 2004 12:04 AM > To: CF-Talk > Subject: Grouping help > > > I'm trying to run a query then tag each day of the query with > a title. Something like: > > > > == March 1 == > > - Entry 1 > > - Entry 2 > > - Entry 3 > > > > > > == March 2 == > > - Entry 4 > > - Entry 5 > > > > == March 7 == > > - Entry 6 > > - Entry 7 > > > > Any ideas how to do this? > > Thanks! > Jake > > > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Grouping help
SELECT theTitle, theDate FROM theTable ORDER BY theDate == #DateFormat(qTest.theDate," d")# == - #qTest.theTitle# Pascal > -Original Message- > From: Jake McKee [mailto:[EMAIL PROTECTED] > Sent: zaterdag 3 april 2004 9:04 > To: CF-Talk > Subject: Grouping help > > I'm trying to run a query then tag each day of the query with a title. > Something like: > > > > == March 1 == > > - Entry 1 > > - Entry 2 > > - Entry 3 > > > > > > == March 2 == > > - Entry 4 > > - Entry 5 > > > > == March 7 == > > - Entry 6 > > - Entry 7 > > > > Any ideas how to do this? > > Thanks! > Jake > > > > > > > [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
