ColdFusion event calendar-recurring events

Hi.  I have been asked to create a ColdFusion application to track events for May-July.  The events need to be searchable by users.  The resutls page should display a calendar of the selected month with days with events highlighted.  I have been working with ColdFusion for a few years but have never done anything with dates or a calendar before.  I admit that I am not very advanced.

I have already created a form for admin to enter the event information into the database.  I created a fairly simple table to display the month of May already.  Is this an OK way to do a calendar?  Also, I am stumped on how to display recurring events.  For example, an event that is every Monday in May and June.

I know this question has been asked before and answered on here.  However, I admit the solutions were over my head.  That is why I'm asking again.  My biggest problem is how to handle the recurring events.

Right now I'm so lost I'm not sure where to go next.  Any help is greatly appreciated!
ccnorrisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RCorfmanCommented:
I don't see anything wrong with displaying a calendar as an HTML table.
How are you wanting the recurring events to display?  The easiest way is going to be to just display them on each day as if it were a one-time event each time it is displayed...
0
ccnorrisAuthor Commented:
OK, great!  I went ahead and did some work on the calendar.  I would like the recurring events to display on each day they occur.  For example, if the event is on every Monday I would like the event to show on all the Mondays in the date range.

On my form I have fields to enter the start date, end date and to select if the event is going to repeat.  For the repeat field I have a second selection for the day of the week the event will repeat on.  What I need to do now is figure out how to calculate the repeating event dates.  I assume I should be able to use the start and end dates to calculate the event range.  Can I calculate the number of Mondays (for example) in the date range?

To display an event as if it were a one-time event do I need to have fields on my form for each day the event occurs?  If so I am going to have to have about 14 fields for event dates because some events occur weekly for 2-3 months.  It doesn't seem the people are going to want to determine each of the dates and enter them all in on the form.
0
RCorfmanCommented:
I didn't understand that you were displaying on a form... I figured for calendar display, it would just be output in an html grid... you don't need any fields then, just output the text maybe with a <br> between each event inside the <td> </td> tags for the day.

As far as getting the repeating events, it depends somewhat on how the data is stored in the database.... what does the data record that is storing the events look like? and maybe knowing the database would help... I can't help with all databases if the queries get complicated... only Oracle for sure, but basic queries with the other databases.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

ccnorrisAuthor Commented:
I'm not displaying on a form.  I'm just using the form to get the data for the events.  The data is displayed in an html table.

I have an Access database.  The start and end dates are stored as Date/time fields.  I have 2 fields for the repeating events.  Both are text fields.  The first either is no, daily, or weekly.  If weekly is selected the second field will list a day of the week.
0
RCorfmanCommented:
How do you have the second field, the day of the week formatted? Give a couple of EXACT examples (including case).
0
ccnorrisAuthor Commented:
The second field is a drop down menu that allows multiple options.  The values for the responses are formatted like "Sunday", "Monday", "Tuesday"...

Would it be easier to have the number that corresponds to the day of the week?  Like 1 for Sunday, 2 for Monday...
0
RCorfmanCommented:
No, either works, just curious which it was to fomulate the queries. See if my post on your other question makes sense... If so, it will make sense to refine the query that retries the actual events for a given day to include the more complicated query relative to the recurring day. The question is if a QofQ solution works... if So, I'll build up my own query-of-queries example on my system and confirm I get the data correct in the format that you have... with The Day "Monday", etc in the recurring day column.

What I'm suggesting in the related post is that days with no events wouldn't have any links, just a number, but days with events would list the events... what was left out on that post was how to query the data for the recurring days entries, I don't think it is particularly difficult if the other portion works.
0
RCorfmanCommented:
Assumptions, you have a coldfusion DATE variable named cur_date
Before you run your query, set a couple of additional variables
<cfset cur_day_text = DateFormat(cur_date,"dddd")>
<cfset cur_access_date = DateFormat(cur_date,"mm/dd/yyyy")>
I BELIEVE something like this would work....

SELECT *
FROM your_event_Table
WHERE
(
(startdate=#cur_access_date#)
OR (startdate<=#cur_access_date# AND recurring="daily" AND enddate>=#cur_access_date#)
OR (startdate<=#cur_access_date# AND recurring="daily" AND enddate>=#cur_access_date# AND recurringdate="#cur_day_text#")
)

The above query can be applied everywhere you need to resolve the recurring dates.
The columns names need to be set to match your table.
It will retrieve events based ANY one of three criteria
1) The startdate matches the cur_date
2) The event is "Daily" and the cur_date is between the start and end date
3) The event is "Weekly" and the current day matches the day of week and the start and end date match

0
RCorfmanCommented:
dang... sorry, this one needs to have the # in the value to work in the query
<cfset cur_access_date = DateFormat(cur_date,"mm/dd/yyyy")>
Should be....
<cfset cur_access_date = "#" & DateFormat(cur_date,"mm/dd/yyyy") & "#">
0
RCorfmanCommented:
This way, for today, the query would be something like:

SELECT *
FROM your_event_Table
WHERE
(
(startdate=#04/16/2006#)
OR (startdate<=#04/16/2006# AND recurring="daily" AND enddate>=#04/16/2006#)
OR (startdate<=#04/16/2006# AND recurring="daily" AND enddate>=#04/16/2006# AND recurringdate="Sunday")
)
0
RCorfmanCommented:
Teach me not to test... another stupid typo.  The second Or should be "Weekly" not "Daily"

SELECT *
FROM your_event_Table
WHERE
(
(startdate=#cur_access_date#)
OR (startdate<=#cur_access_date# AND recurring="Daily" AND enddate>=#cur_access_date#)
OR (startdate<=#cur_access_date# AND recurring="Weekly" AND enddate>=#cur_access_date# AND recurringdate="#cur_day_text#")
)
0
ccnorrisAuthor Commented:
I will actually need this query on the same page where I was checking if events matched the calendar day.  Can I modify the query I have on that page from

<cfset current_date = #DateFormat(cal_date, 'mm/dd/yy')#>                          
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate
FROM Table1
WHERE event_county = '#form.searchcounty#'
</cfquery>    

to

<cfset current_date = #DateFormat(cal_date, 'mm/dd/yy')#>                        
<cfset cur_day_text = #DateFormat(current_date,"dddd")#>
<cfset cur_access_date = #DateFormat(current_date,"mm/dd/yyyy")#>
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate
FROM Table1
WHERE event_county = '#form.searchcounty#' AND (
(startdate=#cur_access_date#)
OR (startdate<=#cur_access_date# AND recurring="Daily" AND enddate>=#cur_access_date#)
OR (startdate<=#cur_access_date# AND recurring="Weekly" AND enddate>=#cur_access_date# AND recurringdate="#cur_day_text#")
)
</cfquery>  

Or do I need to create a new query to check for the recurring events?  For the "WHERE" part of the query I wasn't sure if I could just add the AND to make it work.  Also, where will I be checking if an event reoccurs?  The two fields I have in the database releated to this are event_frequency which will either be "NoRepeat", "Daily", or "Weekly".  The seond field is called event_frequency_day and will be a day of the week like Sunday, Monday...
0
ccnorrisAuthor Commented:
Nevermind about the second part of my question.  I think I see where to put my field names for the recurring events.

I have

<cfset current_date = #DateFormat(cal_date, 'mm/dd/yy')#>                        
<cfset cur_day_text = #DateFormat(current_date,"dddd")#>
<cfset cur_access_date = #DateFormat(current_date,"mm/dd/yyyy")#>
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate
FROM Table1
WHERE event_county = '#form.searchcounty#' AND (
(startdate=#cur_access_date#)
OR (startdate<=#cur_access_date# AND event_freqency="Daily" AND enddate>=#cur_access_date#)
OR (startdate<=#cur_access_date# AND event_freqency="Weekly" AND enddate>=#cur_access_date# AND event_freqency_day="#cur_day_text#")
)
</cfquery>  

I'm not sure if all this is right though because I'm getting the error message "ODBC Error Code = 07001 (Wrong number of parameters).  
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5."
0
ccnorrisAuthor Commented:
I'm also having another small problem, but it is for another page in this application so I will submit a new question.
0
RCorfmanCommented:
when you do a CFSET, you don't need to use the # on the right side as you are inside a CF tag, so you don't need to tell it it is a function.
<cfset current_date = DateFormat(cal_date, 'mm/dd/yy')>                    
<cfset cur_day_text = DateFormat(current_date,"dddd")>

But, in the case of this last one, we want Access to treat it as a date literal, so from Access's point of view, it must have #05/03/2006# actually passed in, so that one should be
<cfset cur_access_date = "##" & DateFormat(current_date,"mm/dd/yyyy") & "##">

See if that makes the query run more smoothly.


And, yes, the intent was to replace all your event queries with a version like this so that you would be retrieving the recurring events.
0
ccnorrisAuthor Commented:
Is how I did the query with the WHERE statement right?  How I added the second part in the WHERE section with AND...
0
RCorfmanCommented:
Yes, I believe that is correct. I placed a "big" ( ) set around the OR'ed schedule criteria so that you could do that. It looks correct to me... or at least was how I would have approached it initially.

Does what I did make sense?  As long as you understand it and it makes sense, then what doesn't work you can troubleshoot if it needs tweeking. The intent is to help/collaborate. I do see that you changed the column names, etc so I think the assumption is that you are understanding the intent, that is the important part.
0
ccnorrisAuthor Commented:
I understand most of it.  I've been playing around with it and still cannot figure out how to make it work.  Problem is I had to have this done today and I'm still not done.  :(
0
RCorfmanCommented:
I'll be online for a while yet, then back in the morning... for a bit. I'm on vacation and we are sightseeing
0
ccnorrisAuthor Commented:
in this part:

(
(startdate=#cur_access_date#)
OR (startdate<=#cur_access_date# AND event_freqency="Daily" AND enddate>=#cur_access_date#)
OR (startdate<=#cur_access_date# AND event_freqency="Weekly" AND enddate>=#cur_access_date# AND event_freqency_day="#cur_day_text#")
)

I see the first line is just checking to see if the current date equals the start date.  I understand that for sure.  In the next two links where there is startdate<=.....AND enddate> does that essentially mean between startdate and enddate?  Does the query know what "Daily" and "Weekly" mean?  I'm unsure about that part.
0
ccnorrisAuthor Commented:
I hope you are vacationing somewhere nice!
0
RCorfmanCommented:
>>does that essentially mean between startdate and enddate?  Yes... and in fact, between could have been used, I'm just more used to useing >= and <=.
>>Does the query know what "Daily" and "Weekly" mean? Well, Yes, they are literals... the trick is that we gave it different criterial... With Daily, we are saying any record that has the dates between that... with Weekly, we are saying any date that is between that as long as the Day is the same TOO (AND).  Then with the OR criterias, we are saying as long as any one of the three are the same...
ANY ONE of:
the day is the same...
It is a daily event and the day is between the start and end....
it is a weekly event and the day is between the start and end AND the day matches (Mon, Tues, etc).
0
RCorfmanCommented:
For me the hard part is getting the criteria passed into Access correctly. Dates have tripped me up until recently... answering questions here on EE has finally gotten that straight... in my mind at least.

Access has to see it as
#mm/dd/yyyy#

The # have special meaning in CF so you have to get it just right when you build up the query or you have problems.  Hence strange things like:
<cfset cur_access_date = "##" & DateFormat(current_date,"mm/dd/yyyy") & "##">
This is an effort to get
#mm/dd/yyyy# to pass into Access.
0
ccnorrisAuthor Commented:
OK.  I'm understanding the things you said in both your last posts.  Yes, dates are very tricky for me.

When I try to run the query I keep getting the error message "ODBC Error Code = 07001 (Wrong number of parameters).  [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5.  Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly."  I am not sure what this means or what it means I'm doing wrong.
0
RCorfmanCommented:
Is Cold fusion displaying the query back to you with the error? Usually, if the errored query is looked at, it makes more sense what isn't right.
0
ccnorrisAuthor Commented:
No it isn't doing that.  It just gives the error message and "The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (5:1) to (5:58)."  Line 5 is the start of the query.
0
RCorfmanCommented:
OK, can you <cfoutput> all the variables involved with the query, then do a <cfflush> THEN try and execute the query. With any luck you can see EXACTLY what is in the variables involved with the query. Then, paste the entire sql statement that is inside of the <CFQUERY> tags along with the actual values in all the variables involved with the query and we can see if we can reconstruct the passed in query and try to identify what doesn't look right.
0
ccnorrisAuthor Commented:
OK.  I am not familiar with <cfflush>.  How does this tag work?
0
RCorfmanCommented:
http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Tags30.htm#2130748
It flushes the coldfusion buffer to the client. That way you can be sure to get the data out even if the remainder of the coldfusion session bombs badly. Usually you'll get the data anyway, but this way you would know you would.
Output the variables involved with the query. <cfflush>, then run the query.
something like
<cfoutput>Cur_access_Date=&quot;#cur_access_date#&quot;<br>
cur_day_text=&quot;#cur_day_text#&quot;<br>
</cfoutput>
<cfflush>

Include any other variables that are part of the query too.
0
RCorfmanCommented:
This is all just temporary debugging code.  To be removed after.
0
ccnorrisAuthor Commented:
Thanks for the explanation!  

I did the output of the variables.  Only some of the variables have an output.  The query still will not work with the cfflush in there.

Here is what I got from the output:

05/01/06 - current_date
Monday - cur_day_text
#05/01/2006# - cur_access_date
- startdate
- txtStartDate
- event_county
Baltimore County - form.searchcounty
- enddate
- event_frequency
- event_frequency_day

0
RCorfmanCommented:
and what is the actual query currently inside the <cfquery> tag in CF?  We can substitued the CF variable values into the query to see what it looks like.
0
ccnorrisAuthor Commented:
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate
FROM Table1
WHERE event_county = '#form.searchcounty#' AND (
(startdate=#cur_access_date#)
OR (startdate<=#cur_access_date# AND event_freqency="Daily" AND enddate>=#cur_access_date#)
OR (startdate<=#cur_access_date# AND event_freqency="Weekly" AND enddate>=#cur_access_date# AND event_freqency_day="#cur_day_text#")
)
</cfquery>
0
RCorfmanCommented:
This is what the query should be translating to. Do you have direct access to the Access database? Can you try pasting this into a query window in access?  Create a new query, then, when you have the query-by-example window up, you click on the little arrow next to what would run the query, and pick SQL from the list, then you can paste this into the window and see if it runs...  It looks like it should to me, without problem.

SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate
FROM Table1
WHERE event_county = 'Baltimore County' AND (
(startdate=#05/01/2006#)
OR (startdate<=#05/01/2006# AND event_freqency="Daily" AND enddate>=#05/01/2006#)
OR (startdate<=#05/01/2006# AND event_freqency="Weekly" AND enddate>=#05/01/2006# AND event_freqency_day="Monday")
)

If you don't have access, or are unable to do this, does it work ok when it is stripped down to not having the OR statements?  Just this for the query...
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate
FROM Table1
WHERE event_county = '#form.searchcounty#' AND (
(startdate=#cur_access_date#)
)
</cfquery>
0
RCorfmanCommented:
I must have missed something... This may be that I'm getting myself confused between the other posts regarding this application. Are you still retrieving all the data for the month, then trying to look at each day?  If so, you still need to retrieve all the data for the entire month by just checking the one date, then it would be on the daily hightlight that you need to count the individual records... this wouldn't actually apply to the access query if that is the case....  I honestly am not sure though.
0
ccnorrisAuthor Commented:
This is the query I was using originally to retrieve data for events matching a search word and then matching the startdate with the calendar date.  And then highlighting matching days.
0
ccnorrisAuthor Commented:
The query works without any errors when the OR statements are removed.
0
RCorfmanCommented:
Does this one work?
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate
FROM Table1
WHERE event_county = 'Baltimore County' AND (
(startdate=#05/01/2006#)
OR (startdate<=#05/01/2006# AND event_freqency="Daily" AND enddate>=#05/01/2006#)
)
0
RCorfmanCommented:
I think it is moot. Sorry, I think it is only in the QofQ statement, where you were trying to hightlight the single day that this recurring information is important....
Is that right?
0
ccnorrisAuthor Commented:
No.  The error message is "Just in time compilation error.  Invalid parser construct found on line 24 at position 13. ColdFusion was looking at the following text:05"

Seems it doesn't like the 05 in the startdate.
0
ccnorrisAuthor Commented:
Yes, I guess you're right.  

The query is grabbing all the data that matches the the search word.  The QofQ narrows down the results to only include the current day.  Does the check for recurring events need to be in the QofQ statement then?
0
RCorfmanCommented:
sorry, should have been this one... errr, I'm getting tired (fuzzy headed).
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate
FROM Table1
WHERE event_county = '#form.searchcounty#' AND (
(startdate=#cur_access_date#)
OR (startdate<=#cur_access_date# AND event_freqency="Daily" AND enddate>=#cur_access_date#)
)
</cfquery>

But I think we need to identify if we even need to change the criteria on the access statement.
0
RCorfmanCommented:
yes, sorry, needs to be in the QofQ statement.

Easily fixed. You do need a change to the Access query though, based on the prior problems we had...
You need to add a txtEndDate to the access statement.

SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate
format(enddate,'YYYYMMDD') as txtEndDate
FROM Table1
where (what you had before)

Then, if you can provide your current, without changes, QofQ query, We should be able to tweek that... hopefully it won't groan at the OR statements so much....
0
ccnorrisAuthor Commented:
I think we're gettin closer....

The error message now is " [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.  
Hint: The cause of this error is usually that your query contains a reference to a field which does not exist. You should verify that the fields included in your query exist and that you have specified their names correctly."

Before it was wanting 5 parameters so 2 must be better, right?
0
RCorfmanCommented:
If it doesn't like the Or criteria, we can get around it with a union query.  No problems. We'll get this part done.
0
ccnorrisAuthor Commented:
Thank you so much for your dedication even when you're tired.  And besides it's not your project.  :)
0
RCorfmanCommented:
AND event_freqency="Daily"
It is moot though, as the access query doesn't need to change, but we will need to know the actual column name for the QofQ.
Is it event_freqency or event_freqUency?
0
ccnorrisAuthor Commented:
the second one with the "u".  event_frequency

oh my gosh.......i spelled that wrong way at the top and a long time ago.   i'm so sorry. that's awful
0
ccnorrisAuthor Commented:
I changed the spelling and now it says "too few parameters. expected 1."
0
RCorfmanCommented:
Like I've never done that.... But you need to put it back to what it was originally except for the new column.  We'll need that for the QofQ.
0
ccnorrisAuthor Commented:
ok
0
RCorfmanCommented:
is enddate right? We'll need to know for the QofQ.
0
ccnorrisAuthor Commented:
yes, enddate is right.
0
RCorfmanCommented:
ok, what does the current QofQ query look like? We'll tweek it in stages.
0
ccnorrisAuthor Commented:
currently it is

<cfquery name="qryDaysEvents" dbtype="query">
select * from qry_getDate
where qry_getDate.txtstartdate=#dateFormat(current_date,'YYYYMMDD')#
</cfquery>
0
RCorfmanCommented:
ok, you need to add the txtenddate to the access query and assure that works.
Let's try adding part of the daily piece to the QofQ and see if we don't get any trouble.

<cfquery name="qryDaysEvents" dbtype="query">
select * from qry_getDate
where qry_getDate.txtstartdate=#dateFormat(current_date,'YYYYMMDD')#
or (qry_getDate.txtstartdate<=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.txtenddate>=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.event_freqency="Daily")
</cfquery>
0
RCorfmanCommented:
errr, but spell frequency right... ;)  unlike me too. I fixed it below.
<cfquery name="qryDaysEvents" dbtype="query">
select * from qry_getDate
where qry_getDate.txtstartdate=#dateFormat(current_date,'YYYYMMDD')#
or (qry_getDate.txtstartdate<=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.txtenddate>=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.event_frequency="Daily")
</cfquery>
0
ccnorrisAuthor Commented:
It doesn't like one of the names in the QofQ section
0
RCorfmanCommented:
Does it still not like it with frequency spelled different?
0
ccnorrisAuthor Commented:
no, still doesn't.

for qry_getDate.txtenddate should we have set that somewhere?  in the first query we set up txtstartdate but not the enddate one.
0
RCorfmanCommented:
yes, you need to add txtenddate to the original access query.
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate,
format(enddate,'YYYYMMDD') as txtEndDate
FROM Table1
where (what you had before)
0
ccnorrisAuthor Commented:
hmmmm........it is still acting like there is a wrong fieldname but i don't know what it could be.  i don't see any errors
0
RCorfmanCommented:
just for kicks, change the
format(enddate,'YYYYMMDD') as txtEndDate
to
format(startdate,'YYYYMMDD') as txtEndDate
0
RCorfmanCommented:
I guess, to confirm, which query was failing? the access one when you tried to add the txtEndDate column? or the QofQ?
0
ccnorrisAuthor Commented:
the first query when i added the txtEndDate column.  before that it was throwing an error on the QoQ part
0
RCorfmanCommented:
you need to confirm the name of the enddate column. It has to be that.
0
RCorfmanCommented:
As I mentioned, try changing it to startdate just to ensure that is really, truly, the column causing the problem.
0
ccnorrisAuthor Commented:
it is definitely "enddate".  i copied this right from the database
0
RCorfmanCommented:
when you change the line from
format(enddate,'YYYYMMDD') as txtEndDate
to
format(startdate,'YYYYMMDD') as txtEndDate
Does it work... I know this isn't right, but we need to at least narrow down that that really is the problem.
0
ccnorrisAuthor Commented:
no it still doesn't work with that
0
RCorfmanCommented:
if that worked, try this:
format([enddate],'YYYYMMDD') as txtEndDate

it shouldn't matter, but it won't hurt either.
0
RCorfmanCommented:
Can you past in the current query?
0
ccnorrisAuthor Commented:
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate,
format(startdate,'YYYYMMDD') as txtEndDate
FROM Table1
WHERE event_county = '#form.searchcounty#' AND (
(startdate=#cur_access_date#)
OR (startdate<=#cur_access_date# AND event_frequency="Daily" AND enddate>=#cur_access_date#)
)
</cfquery>
<cfquery name="qryDaysEvents" dbtype="query">
select * from qry_getDate
where qry_getDate.txtStartDate=#dateFormat(current_date,'YYYYMMDD')#
or (qry_getDate.txtStartDdate<=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.txtEndDate>=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.event_frequency="Daily")
</cfquery>
0
RCorfmanCommented:
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT *,
format(startdate,'YYYYMMDD') as txtStartDate,
format(enddate,'YYYYMMDD') as txtEndDate
FROM Table1
WHERE event_county = '#form.searchcounty#'
</cfquery>
Use this, I don't think we needed to have any date criteria if I remember right from the prior questions.

Then, we need to know if everything is working, if so, we'll need to add the last, "Weekly" criteria to the QofQ.
0
ccnorrisAuthor Commented:
it's moving farther down the code now.  the error is "Query Manipulation Error Code = 0.  Expected right parenthesis at:"Daily")"
0
RCorfmanCommented:
Try this...
<cfquery name="qryDaysEvents" dbtype="query">
select * from qry_getDate
where qry_getDate.txtStartDate=#dateFormat(current_date,'YYYYMMDD')#
or (qry_getDate.txtStartDdate<=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.txtEndDate>=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.event_frequency='Daily'
)
</cfquery>
0
ccnorrisAuthor Commented:
amazing.  no errors.  finally!
0
RCorfmanCommented:
so, we venture to the last criteria...

<cfquery name="qryDaysEvents" dbtype="query">
select * from qry_getDate
where qry_getDate.txtStartDate=#dateFormat(current_date,'YYYYMMDD')#
or (qry_getDate.txtStartDdate<=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.txtEndDate>=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.event_frequency='Daily'
)
or (qry_getDate.txtStartDdate<=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.txtEndDate>=#dateFormat(current_date,'YYYYMMDD')#
   and qry_getDate.event_frequency='Weekly'
   and qry_getDate.event_freqency_day='#cur_day_text#'
)
</cfquery>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ccnorrisAuthor Commented:
ok.  no errors with that either.  but in this part should recurring events now be highlighted on the calendar?  if so something is still not right.  you can see it here http://r.assoclink.com/cnorris/carolyn/calendar/.  Do a search for "Baltimore County".
0
ccnorrisAuthor Commented:
as an example the event on May 3rd "Beginner Urban Line Dance" is a weekly event on Wednesdays
0
RCorfmanCommented:
Yes, I would expect it to work. Unfortunately, I have to get up in 5 1/2 hours so I really have to stop for now. I'm sorry.
I would <cfoutput> the entire access query and all the columns, and look to ensure the data is really what you think it is in the retrieved query.  I'm pretty sure that we got the >= and <= sign directions right. ALso, ensure event_frequency_day matches "Wednesday".

Just output the data on the page, all of it.
<table>
<cfoutput query="qry_getdate">
<tr><td>#event_frequency#</td>
<td>#event_freqency_day#</td>
<td>#txtStartDdate#</td>
<td>#txtEndDdate#</td>
</tr>
</cfoutput>
</table>

To confirm that it is really what you are expecting it to look like, especially on those recurring events.
0
ccnorrisAuthor Commented:
No problem.  I appreciate all your help thus far.  I too have to get up in about 5 hours.

A quick check give the event_frequency_day as Monday.  Then the output gets hung up at txtStartDate.

I'll have to work on this more in the morning.  Thanks again!
0
ccnorrisAuthor Commented:
OK.  I've gotten the recurring events highlighting now.  Yeah!

Only problem is that this query messed up something else.  Now, the issue is that the URL on the recurring events is not leading to the event listing page.

What I originally had for this next query is:

<cfif 'url.var' neq ''>
<cfquery name="qry_showresults" datasource="cnorris_calendar" dbtype="ODBC">
SELECT DISTINCT startdate, enddate, event_title, event_location, event_county, event_level, ID
FROM Table1
WHERE event_county = '#url.var#'
<cfif len(trim(url.EventDate))>
and startdate = ###url.EventDate###
</cfif>
ORDER by startdate
</cfquery>
</cfif>

I tried putting the OR statements in the WHERE section.  I'm not sure if that would work.  I'm starting to really confuse myself.
0
RCorfmanCommented:
OK, so the calendar is working, and this is on the page that shows the detailed events for the day?  This is an access query right?
0
RCorfmanCommented:
I'm going to assume it is... since this is the access query, and we had so much problems with access and trying to use the Or, I'm going to give two options at this point. Try them both and see if either works. This is just going to check for the daily recurring ones, not the weekly. If either of these work, then, depending on which, we can add the weekly.

First this version.... Hopefully it will work.

<cfif 'url.var' neq ''>
<cfquery name="qry_showresults" datasource="cnorris_calendar" dbtype="ODBC">
SELECT DISTINCT startdate, enddate, event_title, event_location, event_county, event_level, ID
FROM Table1
WHERE event_county = '#url.var#'
<cfif len(trim(url.EventDate))>
and (
   ( startdate = ###url.EventDate### )
 or ( Startdate <= ###url.EventDate###
   and EndDate >= ###url.EventDate###
   and qry_getDate.event_frequency='Daily'
   )
 )
</cfif>
ORDER by startdate
</cfquery>
</cfif>

If not, an alternative is to use a union query.... I don't like this as much, but it is a valid method.
<cfif 'url.var' neq ''>
<cfquery name="qry_showresults" datasource="cnorris_calendar" dbtype="ODBC">
SELECT startdate, enddate, event_title, event_location, event_county, event_level, ID
FROM Table1
WHERE event_county = '#url.var#'
<cfif len(trim(url.EventDate))>
and startdate = ###url.EventDate###
</cfif>
union
SELECT startdate, enddate, event_title, event_location, event_county, event_level, ID
FROM Table1
WHERE event_county = '#url.var#'
<cfif len(trim(url.EventDate))>
   and Startdate <= ###url.EventDate###
   and EndDate >= ###url.EventDate###
   and qry_getDate.event_frequency='Daily'
</cfif>
ORDER by startdate
</cfquery>
</cfif>
0
ccnorrisAuthor Commented:
I have everything working on this now.  I forgot to close out this question.  Sorry!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.