Trouble w/ Query criteria filter

P

Pat Dools

Hello,

I have a baseball statistics database. I want to be able to run a query w/ a
prompt that will allow the user to look at the last 'X' games, with 'X'
being supplied by the user in form of a parameter prompt. Here is the code I
am trying to use, but I continue to get syntax errors:

PARAMETERS XGames Value;
SELECT tGameStats.gamenum, tGameStats.gamedate, tOpponentTeam.teamname,
tGameStats.atbats, tGameStats.hits, tGameStats!hits/tGameStats!atbats AS BA,
tGameStats.runs, tGameStats.doubles, tGameStats.triples, tGameStats.homeruns,
tGameStats.rbi, tGameStats.walks, tGameStats.strikeouts, tGameStats.LOB,
tGameStats.sb, tGameStats.errors
FROM tGameStats LEFT JOIN tOpponentTeam ON tGameStats.teamopponent =
tOpponentTeam.teamnumber
WHERE (((tGameStats.gamenum)=SELECT(Max([gamenum])-[XGames]));

It keeps telling me I have syntax errors. How can I clean this up?
 
G

Gary Walter

Pat Dools said:
I have a baseball statistics database. I want to be able to run a query w/
a
prompt that will allow the user to look at the last 'X' games, with 'X'
being supplied by the user in form of a parameter prompt. Here is the
code I
am trying to use, but I continue to get syntax errors:

PARAMETERS XGames Value;
SELECT tGameStats.gamenum, tGameStats.gamedate, tOpponentTeam.teamname,
tGameStats.atbats, tGameStats.hits, tGameStats!hits/tGameStats!atbats AS
BA,
tGameStats.runs, tGameStats.doubles, tGameStats.triples,
tGameStats.homeruns,
tGameStats.rbi, tGameStats.walks, tGameStats.strikeouts, tGameStats.LOB,
tGameStats.sb, tGameStats.errors
FROM tGameStats LEFT JOIN tOpponentTeam ON tGameStats.teamopponent =
tOpponentTeam.teamnumber
WHERE (((tGameStats.gamenum)=SELECT(Max([gamenum])-[XGames]));

It keeps telling me I have syntax errors. How can I clean this up?
--
Click to expand...
Hi Pat,

Since it will only be run once, I see no reason
that you could not use domain function DMax().
Plus, it would appear to me that you would want
gamenum >= max-xgames

PARAMETERS XGames Long;
SELECT tGameStats.gamenum, tGameStats.gamedate, tOpponentTeam.teamname,
tGameStats.atbats, tGameStats.hits, tGameStats!hits/tGameStats!atbats AS BA,
tGameStats.runs, tGameStats.doubles, tGameStats.triples,
tGameStats.homeruns,
tGameStats.rbi, tGameStats.walks, tGameStats.strikeouts, tGameStats.LOB,
tGameStats.sb, tGameStats.errors
FROM tGameStats LEFT JOIN tOpponentTeam ON tGameStats.teamopponent =
tOpponentTeam.teamnumber
WHERE
tGameStats.gamenum >= DMax("gamenum","tGameStats") -[XGames];

good luck,

gary
 
G

Gary Walter

Gary Walter said:
Pat Dools said:
I have a baseball statistics database. I want to be able to run a query
w/ a
prompt that will allow the user to look at the last 'X' games, with 'X'
being supplied by the user in form of a parameter prompt. Here is the
code I
am trying to use, but I continue to get syntax errors:

PARAMETERS XGames Value;
SELECT tGameStats.gamenum, tGameStats.gamedate, tOpponentTeam.teamname,
tGameStats.atbats, tGameStats.hits, tGameStats!hits/tGameStats!atbats AS
BA,
tGameStats.runs, tGameStats.doubles, tGameStats.triples,
tGameStats.homeruns,
tGameStats.rbi, tGameStats.walks, tGameStats.strikeouts, tGameStats.LOB,
tGameStats.sb, tGameStats.errors
FROM tGameStats LEFT JOIN tOpponentTeam ON tGameStats.teamopponent =
tOpponentTeam.teamnumber
WHERE (((tGameStats.gamenum)=SELECT(Max([gamenum])-[XGames]));

It keeps telling me I have syntax errors. How can I clean this up?
--
Click to expand...
Hi Pat,

Since it will only be run once, I see no reason
that you could not use domain function DMax().
Plus, it would appear to me that you would want
gamenum >= max-xgames

PARAMETERS XGames Long;
SELECT tGameStats.gamenum, tGameStats.gamedate, tOpponentTeam.teamname,
tGameStats.atbats, tGameStats.hits, tGameStats!hits/tGameStats!atbats AS
BA,
tGameStats.runs, tGameStats.doubles, tGameStats.triples,
tGameStats.homeruns,
tGameStats.rbi, tGameStats.walks, tGameStats.strikeouts, tGameStats.LOB,
tGameStats.sb, tGameStats.errors
FROM tGameStats LEFT JOIN tOpponentTeam ON tGameStats.teamopponent =
tOpponentTeam.teamnumber
WHERE
tGameStats.gamenum >= DMax("gamenum","tGameStats") -[XGames];
Click to expand...
Also, it is always a good idea to test denominator
before performing division in a query.

IIF(atbats<>0, hits/atbats,0) AS BA
will give 0 if atbats is 0 or Null

PARAMETERS XGames Long;
SELECT
t.gamenum,
t.gamedate,
O.teamname,
t.atbats,
t.hits,
IIF(t.atbats<>0, t.hits/t.atbats,0) AS BA,
t.runs,
t.doubles,
t.triples,
t.homeruns,
t.rbi,
t.walks,
t.strikeouts,
t.LOB,
t.sb,
t.errors
FROM
tGameStats As t
LEFT JOIN
tOpponentTeam As O
ON
t.teamopponent = O.teamnumber
WHERE
t.gamenum >= DMax("gamenum","tGameStats") -[XGames];
 
Top