Janine, Thanks that got it! My only experience has been with Oracle and this was driving me crazy!!
Jim ----- Original Message ----- From: "Janine Jakim" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, November 28, 2001 11:45 AM Subject: RE: Access problem - Help please > ok I think I really got it for you-sorry i thought you were working in a > access grid-too distracted today... > Even though you're using Access as a backend you are still using SQL queries > so you'll use the + > If I wanted to do the name from a cfquery I'd do the following: > > SELECT (LASTNAME+', '+FIRSTNAME) As NAME > That would print out Smith, John > > <cfquery name="select" DataSource="survey"> > SELECT DISTINCT Coursename, (term+'-'+term1+' '+term2)As Terms > FROM courseeval > </CFQUERY> > This will print out the column info for term-term1 term2 > this assumes term/term1/term2 are all columns in your database > > > > -----Original Message----- > From: Jim Watkins [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, November 28, 2001 11:26 AM > To: CF-Talk > Subject: Re: Access problem - Help please > > > I keep getting this error: > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in > query expression 'Terms:term &[, ]& term1'. > > My code is: > > SELECT distinct coursename,term,term1, TERMS:term &", "& term1 > from courseeval > order by coursename > </cfquery> > > > ----- Original Message ----- > From: "Janine Jakim" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Wednesday, November 28, 2001 10:25 AM > Subject: RE: Access problem - Help please > > > > Did you use the "" around the -? The "" allows you to add spaces, etc to > > make it look better in presentations. > > The ampersand is for concatenating but if you add other symbols etc you > need > > to also use "". > > ie: in access if I want a name to read like Smith, John > > I would put in the query > > Name:LastName &", "& FirstName > > > > so I've taken both fields and given it a new name > > Are you using a query grid to make this? > > If so maybe try > > TERMS:term &"-"&term1&" "& term2&"." > > > > -----Original Message----- > > From: Jim Watkins [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, November 28, 2001 10:17 AM > > To: CF-Talk > > Subject: Re: Access problem - Help please > > > > > > OK I used below suggestion and get following error: > > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) > in > > query expression 'term&[-]&term1 term2' > > > > All the rest of my select statement does work Just get the error when I > try > > to Concatenate > > > > Jim > > > > > > ----- Original Message ----- > > From: "Janine Jakim" <[EMAIL PROTECTED]> > > To: "CF-Talk" <[EMAIL PROTECTED]> > > Sent: Wednesday, November 28, 2001 9:39 AM > > Subject: RE: Access problem - Help please > > > > > > > term &"-"& term1 term2 > > > > > > -----Original Message----- > > > From: Sam Farmer [mailto:[EMAIL PROTECTED]] > > > Sent: Wednesday, November 28, 2001 9:17 AM > > > To: CF-Talk > > > Subject: Re: Access problem - Help please > > > > > > > > > Because pipes are not used for concatenation in Access (only Oracle uses > > > them I think). Try using a + sign or something else (not being an > Access > > > user at all). > > > > > > Sam > > > > > > ----- Original Message ----- > > > From: "Jim Watkins" <[EMAIL PROTECTED]> > > > To: "CF-Talk" <[EMAIL PROTECTED]> > > > Sent: Wednesday, November 28, 2001 8:38 AM > > > Subject: Access problem - Help please > > > > > > > > > > Why do I get this error: > > > > "Microsoft][ODBC Microsoft Access Driver] Invalid use of vertical bars > > in > > > > query expression 'term||'-'||term1 term2'." > > > > > > > > When using this code: > > > > <cfquery name="select" DataSource="survey"> > > > > SELECT distinct coursename,term,term1, term||'-'||term1 term2 > > > > FROM courseeval > > > > </cfquery > > > > > > > > Thanks for your input > > > > > > > > Jim Watkins > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
