Ian,
Try this....
SELECT diary.id AS diaryid,
diary.directoryid,
diary.artform AS diaryartform,
directory.fullname,
directory.town,
towns.id AS townid,
towns.town AS town
FROM diary
LEFT JOIN directory ON diary.directoryid = directory.id
LEFT JOIN towns ONdirectory.town = towns.id
WHERE diary.publish = 1
AND ((diary.startdate >= #CreateODBCDateTime(session.searchdatestart)#))
OR ( diary.enddate >=
#CreateODBCDateTime(session.searchdatestart)#))
<cfif form.town IS NOT 'all'> AND directory.town = #form.town# </cfif>
<cfif form.artform IS NOT 'all'> AND diary.artform = #form.artform# </cfif>
ORDER BY diary.startdate,diary.enddate, diary.title
Stephen
PS. The answer is in the CreateODBCDateTime() function and possibly in the
diary.publish = 1 (if you're using a Yes/No in access then its 1/0 in ODBC
SQL)
----- Original Message -----
From: "Ian Westbrook" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2003 3:37 PM
Subject: Re: [ cf-dev ] syntax error...
> doesn't seem to make any difference to it working, but at least I'm
getting
> a _different_ error message now, whihc at least is progress ;-)
>
> ODBC Error Code = 22005 (Error in assignment)
>
>
> [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria
> expression.
>
>
> SQL = "SELECT diary.id AS diaryid, diary.directoryid, diary.artform AS
> diaryartform, diary.*, directory.id AS directoryid, directory.fullname,
> directory.town, towns.id AS townid, towns.town AS town FROM diary,
> directory, towns WHERE diary.directoryid = directory.id AND directory.town
=
> towns.id AND directory.town = 116 AND diary.artform = 10 AND ((
> diary.startdate >= 'Tuesday July 29, 2003') OR ( diary.enddate >= 'Tuesday
> July 29, 2003')) AND diary.publish = 'yes' ORDER BY diary.startdate,
> diary.enddate, diary.title"
>
> ????
>
> Ian W
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, July 29, 2003 3:26 PM
> Subject: Re: [ cf-dev ] syntax error...
>
>
> > I reackon it might be the quoteless dates!
> >
> > In a message dated 29/07/2003 15:25:59 GMT Daylight Time,
> > [EMAIL PROTECTED] writes:
> >
> >
> > > -----Original Message-----
> > > From: Ian Westbrook [mailto:[EMAIL PROTECTED]
> > > Sent: 29 July 2003 15:16
> > > To: cfug dev list
> > > Subject: [ cf-dev ] syntax error...
> > >
> > >
> > > Hi
> > > been staring at this for half an hour now, and can't see what's wrong
> with
> > > it. anyone else have an idea?
> > >
> > > this is the query:
> > >
> > > <cfquery datasource="#dsn#" name="getdiary">
> > > SELECT diary.id AS diaryid, diary.directoryid, diary.artform AS
> > > diaryartform, diary.*, directory.id AS directoryid,
directory.fullname,
> > > directory.town, towns.id AS townid, towns.town AS town
> > > FROM diary, directory, towns
> > > WHERE diary.directoryid = directory.id
> > > AND directory.town = towns.id
> > > <cfif form.town IS NOT 'all'>
> > > AND directory.town = #form.town#
> > > </cfif>
> > > <cfif form.artform IS NOT 'all'>
> > > AND diary.artform = #form.artform#
> > > </cfif>
> > > AND
> > > (( diary.startdate >= #session.searchdatestart#)
> > > OR
> > > ( diary.enddate >= #session.searchdatestart#))
> > > AND diary.publish = 'yes'
> > > ORDER BY diary.startdate, diary.enddate, diary.title
> > > </cfquery>
> > >
> > > this is the error message:
> > >
> > > ODBC Error Code = 37000 (Syntax error or access violation)
> > >
> > >
> > > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> operator) in
> > > query expression 'diary.directoryid = directory.id AND directory.town
=
> > > towns.id AND directory.town = 116 AND diary.artform = 10 AND ((
> > > diary.startdate >= Tuesday July 29, 2003) OR ( diary.enddate >=
Tuesday
> July
> > > 29, 2003))'.
> > >
> > >
> > > SQL = "SELECT diary.id AS diaryid, diary.directoryid, diary.artform AS
> > > diaryartform, diary.*, directory.id AS directoryid,
directory.fullname,
> > > directory.town, towns.id AS townid, towns.town AS town FROM diary,
> > > directory, towns WHERE diary.directoryid = directory.id AND
> directory.town =
> > > towns.id AND directory.town = 116 AND diary.artform = 10 AND ((
> > > diary.startdate >= Tuesday July 29, 2003) OR ( diary.enddate >=
Tuesday
> July
> > > 29, 2003)) ORDER BY diary.startdate, diary.enddate, diary.title"
> > >
> > > it doesn't seem to be seeing the WHERE in front of 'diary.directoryid
=
> > > directory.id', but I'll be buggered if I can see why. maybe a fresh
pair
> of
> > > eyes can spot it?
> > >
> > > TIA
> > >
> > > Ian W
> > >
> >
> >
>
>
> --
> ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> For human help, e-mail: [EMAIL PROTECTED]
>
>
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
