One works, the other doesn't. Why?

[oleBucky]

In another thread the forum helped me come up with the following SQL to allow the selection for one fantasy team or all fantasy teams.

Select "*" AS HLBID, "(All HLB Teams)" AS HLBName
FROM tblHLBTeams AS T1;

UNION

SELECT T1.HLBTeamID AS HLBID, T1.TeamName AS HLBName
FROM tblHLBTeams AS T1
ORDER BY HLBName;

This worked as desired by adding this criteria to the row source for the object list box (listPlayers)
Field: [HLBTeamID]
Table: [tblPlayers]
Criteria: Like [Forms]![frmCardByPosition]![listHLBTeam]

I then went to test the second list box (for MLB Teams) independently by removing the above row source criteria. I am using the SQL below for the list box listMLBTeam then the following criteria to transfer the selections made in listMLBTeam to listPlayers

SELECT "*" AS MLBID, "(All MLB Teams)" as MLBTeam
FROM tblMLBTeams AS T2;

UNION

SELECT T2.MLBTeamID AS MLBID, [T2.League] & " " & [T2.City] & " " & [T2.Club] AS MLBTeam
FROM tblMLBTeams AS T2
ORDER BY MLBTeam;

Field: [MLBTeamID]
Table: [tblPlayers]


Criteria: Like [Forms]![frmCardByPosition]![listMLBTeam]

The source list box, listMLBTeams, appears just as I would expect but the object list box (listPlayers) comes up blank. After much tweaking (brackets out and in, "T2" out and in, removing the concatenating calculated expression, etc., I am unable to make it work. This is the SQL for listPlayers.

SELECT tblPlayers.PlayerID, tblPlayers.NameLast, tblPlayers.NameFirst, tblPlayers.Year, tblMLBTeams.League, tblMLBTeams.City, tblMLBTeams.Club, tblPlayers.MLBTeamID
FROM tblMLBTeams INNER JOIN tblPlayers ON tblMLBTeams.MLBTeamID = tblPlayers.MLBTeamID
WHERE (((tblPlayers.MLBTeamID) Like [Forms]![frmCardByPosition]![listMLBTeam]))
ORDER BY tblPlayers.NameLast, tblPlayers.NameFirst;

Might the forum give this a look and help me find the mistake, big or small?

[orange]

Hi Bucky,

Not sure if I'm following completely but I see 2 things that might be issues.

1. Year is a reserved word in Access. You may have to use [Year].

2. In your SQL you have used Like

...
WHERE (((tblPlayers.MLBTeamID) Like [Forms]![frmCardByPosition]!
[listMLBTeam])) ...

Try using = instead of Like and see if you get data

Post back with results.

[oleBucky]

Thanks, but no help with =. listPlayers comes back blank. BTW when I tested it with listHLBTeams, Like worked (returned the expected data for the selection in listHLBTeams).

[orange]

Ok,

I'd try this

SELECT tblPlayers.PlayerID, tblPlayers.NameLast, tblPlayers.NameFirst, tblPlayers.Year, tblMLBTeams.League, tblMLBTeams.City, tblMLBTeams.Club, tblPlayers.MLBTeamID
FROM tblMLBTeams INNER JOIN tblPlayers ON tblMLBTeams.MLBTeamID = tblPlayers.MLBTeamID
WHERE (((tblPlayers.MLBTeamID) Like [Forms]![frmCardByPosition]!
[listMLBTeam]))
ORDER BY tblPlayers.NameLast, tblPlayers.NameFirst;

I would add this to your code

.....
 Like [Forms]![frmCardByPosition]!
[listMLBTeam] & "*"))
....

Can you try running this sql directly in the Query window

SELECT tblPlayers.PlayerID, tblPlayers.NameLast, tblPlayers.NameFirst, tblPlayers.Year, tblMLBTeams.League, tblMLBTeams.City, tblMLBTeams.Club, tblPlayers.MLBTeamID
FROM tblMLBTeams INNER JOIN tblPlayers ON tblMLBTeams.MLBTeamID = tblPlayers.MLBTeamID
ORDER BY tblPlayers.NameLast, tblPlayers.NameFirst;

I just removed the WHERE clause.
I expect you should get back all the player data.


Can you show the exact SQL you used in this?

BTW when I tested it with listHLBTeams, Like worked (returned the expected data for the selection in listHLBTeams).

[oleBucky]

Can you show the exact SQL you used in this?

Here's the SQL that works from listHLBTeam to listPlayers.

SELECT tblPlayers.PlayerID, tblPlayers.NameLast, tblPlayers.NameFirst, tblPlayers.Year, tblMLBTeams.League, tblMLBTeams.City, tblMLBTeams.Club, tblPlayers.HLBTeamID
FROM tblMLBTeams INNER JOIN tblPlayers ON tblMLBTeams.MLBTeamID = tblPlayers.MLBTeamID
WHERE (((tblPlayers.HLBTeamID) Like [Forms]![frmCardByPosition]![listHLBTeam]))
ORDER BY tblPlayers.NameLast, tblPlayers.NameFirst;

I just tested it again and all is working.

I will try your suggestions with listMLBTeam and post back.

[oleBucky]

I added the asterisk and changed the field name from "Year" to "Yr". With this code, listPlayers is no longer blank. However, the code returned all players regardless of the selection in listMLBTeams.

SELECT tblPlayers.PlayerID, tblPlayers.NameLast, tblPlayers.NameFirst, tblPlayers.Yr, tblMLBTeams.League, tblMLBTeams.City, tblMLBTeams.Club, tblPlayers.MLBTeamID
FROM tblMLBTeams INNER JOIN tblPlayers ON tblMLBTeams.MLBTeamID = tblPlayers.MLBTeamID
WHERE (((tblPlayers.MLBTeamID) Like [Forms]![frmCardByPosition]![listMLBTeam] & "*"))
ORDER BY tblPlayers.NameLast, tblPlayers.NameFirst;

I also ran the code with out the WHERE clause as you suggested in the Query window. It too came up with all the players.

[oleBucky]

completely.

I decided to run some text boxes to look at what was coming out of the list boxes in question. It was immediately apparent the listMLBTeams was not sending a value to my test text box. Upon review of ALL the properties on listMLBTeams, I had, at some point in this process, set the Multi Select property to Extended. I reset the property to None and all is well.

Now all I have to do is try to integrate all my criteria list boxes into one result for my player list box. Wish me luck.

Again, thanks for all the input along the way and regrets on the ridiculous oversight.