0

So, I've looked all over the web for this simple answer...and I can't find it.

I am trying to search an access DB via coldfusion query.

<cfquery name = "x" datasource = "cassupport_computers">
SELECT last, first, dept, location, purchasedate, (last + ' ' + first + ' ' + dept + ' ' + location + ' ' + purchasedate AS searchs)
FROM cas_computers
WHERE searchs like '%#form.searchfield#%'
</cfquery>

What am I doing wrong? x:

winwaed
  • 7,645
  • 6
  • 36
  • 81
Bri
  • 729
  • 2
  • 17
  • 38
  • 1 suggestion, try running the query in access directly, and then it'll be far easier to tell what syntax works and not works. – crosenblum Mar 16 '11 at 13:32

1 Answers1

3
<cfquery name = "x" datasource = "cassupport_computers">
SELECT last, first, dept, location, purchasedate, last & ' ' & first & ' ' & dept & ' ' & location & ' ' & purchasedate AS searchs
FROM cas_computers
WHERE searchs like '%#form.searchfield#%'
</cfquery>
amit_g
  • 30,880
  • 8
  • 61
  • 118
  • To be clear : MS Access uses the "&" symbol to concatenate columns not the "+" symbol. – Stephen Moretti Mar 15 '11 at 21:43
  • 1
    I would have said that it was not so much the +, which can be used for special cases to concatenate, but the parentheses, which Access is very fussy about. – Fionnuala Mar 15 '11 at 23:29
  • Either + or & works from within cfquery. But the column alias needs to be *outside* the parenthesis. – Leigh Mar 16 '11 at 20:04
  • 1
    In Access/Jet/ACE, the + concatenation operator propagates Nulls ("string" + Null = Null), while the & operator does not ("string" & Null = "string"). This is quite useful in situations like names, e.g., Mid(("12" + LastName) & (", " + FirstName), 3), but you have to be careful if there's numeric data involved, or if both sides of the concatenation can be implicitly coerced to numeric types (in which case they will be added, e.g., "12" + "6" might give you 18 instead of "126"). – David-W-Fenton Mar 17 '11 at 04:17
  • @David-W-Fenton - +1 for the tip about nulls. But I am surprised about the second part. I do not use Access much, but I would expect any numeric values to be treated as strings as long as they are quoted. – Leigh Mar 17 '11 at 18:00
  • One would expact that, but Access was built with the philosophy of trying to do what you MEAN instead of what you say, and so is really big on implicit type coercion. This is why I never rely on it, and explicitly coerce everything. The thing is that the + operator and coercing numeric strings into actual numeric values is not something that happens predictably. I've seen it happen in real life, but every time I try to come up with a test case, can't reproduce it. So, I just say that it's something you have to watch about with the + operator. – David-W-Fenton Mar 19 '11 at 18:35
  • @David-W-Fenton - Yes, relying on implicit conversion is risky in any database. So it sounds like it is better to use "&" and avoid the issue. – Leigh Mar 19 '11 at 22:08
  • I use & unless I specifically want to propagate Nulls. – David-W-Fenton Mar 23 '11 at 21:37