?
Solved

ColdFusion event calendar-recurring events

Posted on 2006-04-09
86
Medium Priority
?
655 Views
Last Modified: 2013-12-20
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!
0
Comment
Question by:ccnorris
  • 46
  • 40
86 Comments
 
LVL 16

Expert Comment

by:RCorfman
ID: 16462951
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
 

Author Comment

by:ccnorris
ID: 16462985
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16463010
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:ccnorris
ID: 16463017
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16463049
How do you have the second field, the day of the week formatted? Give a couple of EXACT examples (including case).
0
 

Author Comment

by:ccnorris
ID: 16464452
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16464825
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16466818
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16466820
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16466824
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16466825
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
 

Author Comment

by:ccnorris
ID: 16469837
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
 

Author Comment

by:ccnorris
ID: 16471723
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
 

Author Comment

by:ccnorris
ID: 16472285
I'm also having another small problem, but it is for another page in this application so I will submit a new question.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16473624
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
 

Author Comment

by:ccnorris
ID: 16473837
Is how I did the query with the WHERE statement right?  How I added the second part in the WHERE section with AND...
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16474564
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
 

Author Comment

by:ccnorris
ID: 16474575
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16474672
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
 

Author Comment

by:ccnorris
ID: 16474693
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
 

Author Comment

by:ccnorris
ID: 16474712
I hope you are vacationing somewhere nice!
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16474726
>>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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16474740
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
 

Author Comment

by:ccnorris
ID: 16474757
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16474772
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
 

Author Comment

by:ccnorris
ID: 16474781
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16474996
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
 

Author Comment

by:ccnorris
ID: 16475003
OK.  I am not familiar with <cfflush>.  How does this tag work?
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475030
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475032
This is all just temporary debugging code.  To be removed after.
0
 

Author Comment

by:ccnorris
ID: 16475082
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475087
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
 

Author Comment

by:ccnorris
ID: 16475094
<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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475119
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475133
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
 

Author Comment

by:ccnorris
ID: 16475153
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
 

Author Comment

by:ccnorris
ID: 16475160
The query works without any errors when the OR statements are removed.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475164
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475167
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
 

Author Comment

by:ccnorris
ID: 16475172
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
 

Author Comment

by:ccnorris
ID: 16475177
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475182
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475190
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
 

Author Comment

by:ccnorris
ID: 16475193
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475196
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
 

Author Comment

by:ccnorris
ID: 16475199
Thank you so much for your dedication even when you're tired.  And besides it's not your project.  :)
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475203
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
 

Author Comment

by:ccnorris
ID: 16475210
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
 

Author Comment

by:ccnorris
ID: 16475221
I changed the spelling and now it says "too few parameters. expected 1."
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475222
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
 

Author Comment

by:ccnorris
ID: 16475226
ok
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475229
is enddate right? We'll need to know for the QofQ.
0
 

Author Comment

by:ccnorris
ID: 16475233
yes, enddate is right.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475243
ok, what does the current QofQ query look like? We'll tweek it in stages.
0
 

Author Comment

by:ccnorris
ID: 16475245
currently it is

<cfquery name="qryDaysEvents" dbtype="query">
select * from qry_getDate
where qry_getDate.txtstartdate=#dateFormat(current_date,'YYYYMMDD')#
</cfquery>
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475254
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475265
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
 

Author Comment

by:ccnorris
ID: 16475266
It doesn't like one of the names in the QofQ section
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475283
Does it still not like it with frequency spelled different?
0
 

Author Comment

by:ccnorris
ID: 16475288
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475299
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
 

Author Comment

by:ccnorris
ID: 16475309
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475313
just for kicks, change the
format(enddate,'YYYYMMDD') as txtEndDate
to
format(startdate,'YYYYMMDD') as txtEndDate
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475318
I guess, to confirm, which query was failing? the access one when you tried to add the txtEndDate column? or the QofQ?
0
 

Author Comment

by:ccnorris
ID: 16475323
the first query when i added the txtEndDate column.  before that it was throwing an error on the QoQ part
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475329
you need to confirm the name of the enddate column. It has to be that.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475332
As I mentioned, try changing it to startdate just to ensure that is really, truly, the column causing the problem.
0
 

Author Comment

by:ccnorris
ID: 16475338
it is definitely "enddate".  i copied this right from the database
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475351
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
 

Author Comment

by:ccnorris
ID: 16475356
no it still doesn't work with that
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475359
if that worked, try this:
format([enddate],'YYYYMMDD') as txtEndDate

it shouldn't matter, but it won't hurt either.
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475362
Can you past in the current query?
0
 

Author Comment

by:ccnorris
ID: 16475369
<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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475377
<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
 

Author Comment

by:ccnorris
ID: 16475387
it's moving farther down the code now.  the error is "Query Manipulation Error Code = 0.  Expected right parenthesis at:"Daily")"
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475395
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
 

Author Comment

by:ccnorris
ID: 16475401
amazing.  no errors.  finally!
0
 
LVL 16

Accepted Solution

by:
RCorfman earned 2000 total points
ID: 16475409
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
 

Author Comment

by:ccnorris
ID: 16475417
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
 

Author Comment

by:ccnorris
ID: 16475422
as an example the event on May 3rd "Beginner Urban Line Dance" is a weekly event on Wednesdays
0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16475454
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
 

Author Comment

by:ccnorris
ID: 16475471
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
 

Author Comment

by:ccnorris
ID: 16481339
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16484198
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
 
LVL 16

Expert Comment

by:RCorfman
ID: 16484271
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
 

Author Comment

by:ccnorris
ID: 16531280
I have everything working on this now.  I forgot to close out this question.  Sorry!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question