SQL Syntax in macro with & "

[Paul Dennis]

I have the following SQL in a module which I'm having problems with. I am
trying to concatinate [Last Name] and [First Name] and since the full
statement must have " I have tried to use '' (2 single ' together), however
the sql doesn't work. If I remove the & '' & and replace with [Last Name] &
[Firstr Name] it still doesn't work If however I leave it as [Last Name] then
it does work, so it's just a syntax problem. Just I say, more like a royal
pain.

I have the syntax in 2 places, the select and the group by. Can you help.....

SQLCalcsub = "INSERT INTO [Temp - Position - Riderssub] " & _
"( Rider_ID, Name, YrLessThan, [Year], Total, Points, Bonus
) " & _
"SELECT [Meeting Riders].Rider_ID, [Last Name] & '' '' &
[First Name] AS Name, " & _
"[lngCurrYear] AS YrLessThan, Left([Meeting Number],4)+1 AS
[Year], " & _
"Sum([Meeting Riders].Points+[Meeting Riders].Bonus) AS
Total, " & _
"Sum([Meeting Riders].Points) AS Points, Sum([Meeting
Riders].Bonus) AS Bonus" & _
"GROUP BY [Meeting Riders].Rider_ID, [Last Name] & [First
Name], [lngCurrYear], Left([Meeting Number],4)+1 "

 

[Stefan Hoffmann]

hi Paul,

I have the following SQL in a module which I'm having problems with.

You need to improve your code formatting, then you will see it by yourself.

I have the syntax in 2 places, the select and the group by. Can you help.....
Riders].Bonus) AS Bonus" & _
"GROUP BY [Meeting Riders].Rider_ID, [Last Name] & [First

Here is a space missing after Bonus or before GROUP.


mfG
--> stefan <--

 

[Paul Dennis]

I assume you are refering to the fact that there is no ; at the end. However
I have split this into SQLHavingsub (see below). Other that that I can't see
anything.

SQLHavingsub = "HAVING (([Meeting Riders].Rider_ID) Is Not Null)
And ((Left([Meeting Number], 4) + 1) <= [lngCurrYear])) " & _
"ORDER BY [Meeting Riders].Rider_ID, Sum([Meeting
Riders].Points+[Meeting Riders].Bonus) DESC , Sum([Meeting Riders].Points)
DESC , Sum([Meeting Riders].Bonus) DESC;"

hi Paul,

I have the following SQL in a module which I'm having problems with.

You need to improve your code formatting, then you will see it by yourself.

I have the syntax in 2 places, the select and the group by. Can you help.....
Riders].Bonus) AS Bonus" & _
"GROUP BY [Meeting Riders].Rider_ID, [Last Name] & [First

Here is a space missing after Bonus or before GROUP.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Paul,

I assume you are refering to the fact that there is no ; at the end.

No, take a close look at my answer.


mfG
--> stefan <--

 

[John Spencer]

You can either use a single quote (apostrophe) or two double quotes.

This
"SELECT [Meeting Riders].Rider_ID, [Last Name] & "" ""' & [First Name] AS
Name, " & _
Or this
"SELECT [Meeting Riders].Rider_ID, [Last Name] & ' ' & [First Name] AS Name,
" & _

By the way, I would be careful with using Name as a field name. You may
need to surround it with [].

In addition I see no FROM clause in the record source query

INSERT INTO ...
SELECT ...
FROM <<<<<<
GROUP BY ...

and as noted elsewhere in this thread, you are missing spaces

plus if you modify the calculated field in the SELECT Clause then you must
also do it in the GROUP BY clause.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..