MSA SQL Concatenation Problem

  • Thread starter Thread starter Townsend
  • Start date Start date
T

Townsend

Thanks to Duane for his elegant Concatenate function.

One of the Concatenates I'm using involves differing
datatypes. The source table is 3 fields: ID, Date (MDY),
and Occasion (string). Using the suggested SQL construct
(SELECT date & "-" & occasion FROM ....) produces an
error# which I suspect is from the date datatype. I've
tried to convert date to char using ToChar(date) and CDate
(date)without success. I haven't been able to find a
working SQL function. (Of course I don't know datatype is
the problem yet, I'm just trying this as a debug step). I
could do an intermediate Query and do the conversion first
but thought there had to be a direct SQL method.

Also I'd like the "-" to exist only if there was a non-
null occasion field. Again I could do this with an
intermediate query, but in the interest of my education
wanted to learn more about SQL.

Thanks
Pete

Previous message
There is a generic concatenate function and
samples at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook om
Duane.

--
Duane Hookom
MS Access MVP


message
 
I expect your error is because you are imbedding double-quotes. You can
combine fields and expressions like:
"SELECT LName & ', ' & FName FROM ...."
Make sure you use single-quotes inside the double-quotes. You can use the
Format() function like:
"SELECT Format(VisitDate,'mmmm') FROM ...."
Again, use single-quotes.
To get rid of the "-" if occasion is null, use
("SELECT date & '-' + occasion FROM ....")
 
Tried your suggestions:

DatePerfConcat: Concatenate("SELECT Format
(PerformanceDate,'mm/dd/yy') & '-' + Occasion FROM
tDatePerformed WHERE MusicID =" & [ID])

Worked like a charm.

If you have a suggested website for a synopsis of the SQL
language and its functions and syntax, please let me know.
I'd like to learn a little more.

Thanks for your help.
Pete
 
Do what I would do... search Google on "Access SQL Query"

--
Duane Hookom
MS Access MVP


Townsend said:
Tried your suggestions:

DatePerfConcat: Concatenate("SELECT Format
(PerformanceDate,'mm/dd/yy') & '-' + Occasion FROM
tDatePerformed WHERE MusicID =" & [ID])

Worked like a charm.

If you have a suggested website for a synopsis of the SQL
language and its functions and syntax, please let me know.
I'd like to learn a little more.

Thanks for your help.
Pete
-----Original Message-----
I expect your error is because you are imbedding double- quotes. You can
combine fields and expressions like:
"SELECT LName & ', ' & FName FROM ...."
Make sure you use single-quotes inside the double-quotes. You can use the
Format() function like:
"SELECT Format(VisitDate,'mmmm') FROM ...."
Again, use single-quotes.
To get rid of the "-" if occasion is null, use
("SELECT date & '-' + occasion FROM ....")

--
Duane Hookom
MS Access MVP
--




.
 
Back
Top