Still no luck, no obvious syntax issues. Copied and pasted into SQL
view(removed " & _) Same result as before, with INNER JOIN works fine,
RIGHT
JOIN " JOIN expression not supported. Running Access 2007 saved in
Access
2007 format.
--
David McKnight
:
Add a step to your code to print out the SQL statement that you build:
Debug.Print qdf.SQL
take a look at the string and make sure that it's the correct syntax.
Post
it here if you don't see anything obvious.
Also, paste the printed-out SQL string into a new query (SQL View).
Will
it
run? Switch back to design view with it, then back to SQL view --
ACCESS
will "change" the statement to what it likes. Try using that syntax
for
building your SQL statement in code.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
message
I added space before WHERE but still get same error "JOIN function
not
supported". Works with INNER but not LEFT or RIGHT join.
Yes I did meam to say that txtSeason could have values such as 2007,
or
a
value such as 1974, etc.
qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin)
AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division -
Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
" FROM Coefficients, [School Season Division] RIGHT JOIN ([School
Season
Division] AS [School Season Division_1] RIGHT JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School
Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" &
_
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight
:
You appear to be missing a required space before the WHERE word,
which
may
be the cause of your error:
Scores.Season) AND ([School Season Division].Home = Scores.Home)" &
_
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
The above should be
Scores.Season) AND ([School Season Division].Home = Scores.Home)" &
_
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
You said ' The txtSeason text box has values such as "1974 or 2007"
'.
Do
you mean the text box would contain the value 1874, or the value
2007?
I
trust that you don't mean "1974 or 2007" as a literal string in the
textbox,
as that will not work in your query for the criterion.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
message
Yes, I'm bulding SQL statement with VBA code. Essentially the
statement
below
is the code, but I've truncated out non relavant, but here it is
just
in
case. The txtSeason text box has values such as "1974 or 2007".
qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS
[Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv
Margin],Scores!Margin)
AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division -
Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School
Season
Division] AS [School Season Division_1] INNER JOIN Scores ON " &
_
"([School Season Division_1].Home = Scores.Visitor)AND ([School
Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)"
&
_
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight
:
"Form-code"? Are you trying to build the SQL statement in VBA
code?
Or
something else?
Post the code that you're using if that is what you're doing.
Also,
what
types of values (show examples) will be in the txtSeason
textbox.
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
in
message
Yes this worked for query, but it doesn't work when it is in a
form-code
(sorry I didn't mention that) such as :
SELECT Scores.Season, Scores.Visitor, Scores.Home,
Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND
([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"
Erroor says "join function is not supported" - is there a work
around?
--
David McKnight
:
Try this:
SELECT Scores.Season, Scores.Visitor, Scores.Home,
Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND
([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
"David McKnight" <
[email protected]>
wrote
in
message
I have a query :
SELECT Scores.Season, Scores.Visitor, Scores.Home,
Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season
Division]
AS
[School Season Division_1] INNER JOIN Scores ON ([School
Season
Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season
Division].Home)
AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));
I would like to make it to make it a left or right join
(not
sure
which)
on
season & home so that when there is missing data from the
[School
Season
Division] table I get a blank in the query for that
