EasyCFM.COM ColdFusion Forums / MS Access Related Issues / syntax error

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: syntax error -- page: 1 2

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

Lossed
01-03-2008 @ 10:52 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Error message:
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'if dateDiff([day],getDate(),e.eDate) <0 1 else 0'.

Any clues? Tks.

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

louissto56
01-04-2008 @ 2:01 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Can we see the whole query? Why Access? I thought you knew better lossed.

____________________________
My Biz List.com.au :: Feb 08

Lossed
01-04-2008 @ 2:23 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

M8,

I've read most people start with MS Access and then move on to real DB's but I never touched MS Access and don't actually have it set up on my machine. But I'm trying to help someone out who's site uses CF5 and MS Access so I guess I'm gonna have to set up at least the DB. But I was hoping the problem would be obvious from the error message and I wouldn't need to.

But I guess when in Rome...

If I get stuck with it I'll shoot back the full query.

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

gmilby
01-04-2008 @ 8:13 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 281
Joined: Nov 2007

(shooting mouth off - guessing - shot in the dark)...
but the error you're getting almost appears to be generated from a web app trying to run a local stored query...
as if the queries were all setup in msaccess and a web app was trying to execute it.

if so then the queries will need to be redone to where they will work with the web app. (like i said - i'm blowing a hunchie hot air cloud here)


Success is the ablity to go from one failure to another with no loss of enthusiasm.
-Sir Winston Churchill

CJ
01-04-2008 @ 12:37 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4262
Joined: Oct 2002

i don't believe Access uses the getDate() function.  try replacing that with now().

-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

Teachers open the door. You enter by yourself.
—Chinese Proverb

Lossed
01-05-2008 @ 3:19 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Cheers ears.

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

Lossed
01-07-2008 @ 3:18 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

OK,

I'm officially highly plished off with this now.

Having decided to give in and re-install the dogpoo excuse for a DB AKA M$ Access, only to spend over an hour  trying to open the freaking thing with a bunch of needed downloads and service packs blah blah because it's been so long since I've ever needed office that all I have a valid copy of is Office '97.

Long story short, I'm flucked if I'm wasting another freak'n second trying to install Bill's software.

If anyone can see where I'm going wrong with this please can you sing out?
Also, trying to find SQL references for MS access is like looking for a needle in a slow loading, slit my wrists waiting, kinda haystack that is the M$ site, so if anyone has any other reference sites that reference some or all of the things I'm trying to do in the query, please can they advise?

Or better yet, somewhere I can get a free (I've already paid enough in my time plissing around with crazy installs and wild goose chases for those service packs that claim (but fail) to plug the holes in this installation) download of M$ Access?

Preferably NOT from Bill's site.

Right now, I want to wrap that smarmy paper-clip office assistant tightly around the neck of the nearest M$ rep I can find.

The guts of the query, which is now so bent out of shape it is a train wreck of it's former self as a perfectly executing MySQL query:

SELECT
    dateName("month",e.eDate) as theMonthName,
          (if dateDiff("day",now(),e.eDate) < 0
               1
          else
               0) as isPast,
          if month(now())=month(e.edate)
               if dateDiff("day",now(),e.eDate) between -30 and -1
                    dateDiff("day",now(),e.eDate)
               else
                    0
          else
               0 as pastThisMonth,
          if month(now())=month(e.edate)
               if dateDiff("day",now(),e.eDate) between 0 and 30
                    dateDiff("day",now(),e.eDate)
               else
                    0
          else
               0 as nextThisMonth,
          if dateDiff("month",now(),e.eDate) between -1 and -12
               dateDiff("month",now(),e.eDate)
          else
               0 as pastMonthDiff,
          if dateDiff("month",now(),e.eDate) between 1 and 12
               dateDiff("month",now(),e.eDate)
          else
               0 as nextMonthDiff,
          if datePart("weekday",e.edate) in (7,1)
               1
          else
               0 as isWeekend,
          if e.Centre in ("Northland","Auckland","Waikato","HBG","Taranaki","Manawatu","Wellington")
               1
          else
               0 as isNorth,
          if e.Centre in ("Northland","Auckland","Waikato")
               1
          else
               0 as isUpperNorth,
          dateName("day",e.eDate) as fEDOM,
          r.EventType,r.RaceNumber,r.RunWalk,round(r.Distance, 0) as distance,
          if datePart("hour",e.eDate) < 12
                datePart("hour",e.eDate) + ":" + datePart("minute",e.eDate) + "AM"
          else
               datePart("hour",e.eDate) + ":" + datePart("minute",e.eDate) + "PM" as fStartTime,
          c.EventCode
     FROM
          Events e
               INNER JOIN Races r on e.id = r.eventID
               INNER JOIN Codes c on c.EventType = r.EventType
     WHERE dateDiff("year",now(),e.eDate) BETWEEN -1 AND 1


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

louissto56
01-07-2008 @ 3:49 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

*slowly pats lossed on the back*

I still don't know why you have to use BS Access. (BS = Bullshit) Smile  I'm so funny.

____________________________
My Biz List.com.au :: Feb 08

gmilby
01-07-2008 @ 9:30 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 281
Joined: Nov 2007

actually we call is MS  (MULE SH*T).... that's another story.

@Lossed, if it will help you - send me your db and i'll host on my site and give you access - i can use/run the db/setup the dsn and you can just ftp to my site and work on it.  at least you'll be getting factual info/errors.

just let me know if you want to use it.

gcmilby AT syr.edu is where i am at the moment.

Success is the ablity to go from one failure to another with no loss of enthusiasm.
-Sir Winston Churchill

louissto56
01-07-2008 @ 11:02 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

For only 6 easy payments of $99.95

____________________________
My Biz List.com.au :: Feb 08

PAGE: 1 2

Website Designed and Developed by Pablo Varando.