EasyCFM.COM ColdFusion Forums / MS Access Related Issues / Record Count assistance

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Record Count assistance

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

01-07-2008 @ 5:06 AM
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 12
Joined: Nov 2007

I have been trying to do a record count of the following data:

It is a 9 event running event, with each event having a column in the table.  Event 7 is enter on the day so is not in this query.

Basically I want to easily display the number of people for each day competing, and a total value... without having the need for about 20 queries like I currently use.

I use cf5 & Access.

Get the following error:
ODBC Error Code = 37000 (Syntax error or access violation)<P> [Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.


<cfquery name="qSadisticEvent" datasource="#application.ds#">
          blah.count1+blah.count2+blah.count3+blah.count4+blah.count5+blah.count6+blah.count8+blah.count9 as totalCount
     from (
               COUNT(NULLIF(c_e1,0)) as count1,
               COUNT(NULLIF(c_e2,0)) as count2,
               COUNT(NULLIF(c_e3,0)) as count3,
               COUNT(NULLIF(c_e4,0)) as count4,
               COUNT(NULLIF(c_e5,0)) as count5,
               COUNT(NULLIF(c_e6,0)) as count6,
               COUNT(NULLIF(c_e8,0)) as count8,
               COUNT(NULLIF(c_e9,0)) as count9,
          from waientry_competitors) blah</cfquery>

<cfoutput query="qSadisticEvent">
     <table width="200px" border="0" cellspacing="0" cellpadding="0">
              <td width="100px"><strong>Event</strong></td>
              <td width="100px"><strong>Numbers</strong></td></tr>


Any assistance would be most appreciated.  I take it that basically I cannot do the nullif with Access?


01-07-2008 @ 6:22 AM
Send P.M.
My Gravatar!
Powered by Gravatar
Posts: 1149
Joined: Jan 2007

It says you are using a reserved word. Access has A LOT of them. Are you using names that are the same as functions like "count" for example?

My Biz List.com.au :: Feb 08

01-07-2008 @ 4:57 PM
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Like I may have alluded to in earlier posts, Simon, blardy Aussies couldn't find their way out of a wet paper bag. So please forgive louissto56 for not spotting the trailing comma in your select list that inadvertently tells the dogshite DB we all know and h8 that is M$ Access to go looking for a field named 'from' which, as I'm sure we are all aware, is highly likely to be a reserved word.

Gotta love a bit of Aussie bashing. I note the Indians tried it on 'the monkey' in the recent cricket test and look where that got them. Thank gawd me old cobba louissto56 has a SOH.

Good luck.
BTW, I like the rest of your code. Very intuitive and efficient Wink

When the only tool you have is a hammer, everything looks like a nail Smile

This message was edited by Lossed on 1-7-08 @ 5:47 PM

Website Designed and Developed by Pablo Varando.