Whay can't I get this queary to work?????

  • Thread starter Thread starter Brady Snow
  • Start date Start date
B

Brady Snow

strSQL = "SELECT MixDesignID.PlantID, MixDesignID.MixID,
MixDesignID.StoneName, MixDesignID.StoneWt " &_

"FROM (MixDesignID INNER JOIN PlantID ON MixDesignID.ID =
PlantID.ID) INNER JOIN StoneID ON (MixDesignID.ID =
StoneID.ID) AND (PlantID.ID = StoneID.ID)" &

"WHERE (((MixDesignID.PlantID)= " & ME.PlantID)" & " AND
((MixDesignID.MixID)=" & ME.MixID))"




It works in access but when I put in the forms coding it
does not like it.

MAny Thanks,

Brady Snow
 
Where in the form's coding are you trying to use it? How are you calling it?
Please....more info.
 
Because the whay has gone astray and the queary is queasy! <smile>

The String construction for the WHERE clause is incorrect. You need to take
the references to the Form Controls / Fields out of the literal Strings.

Try:

"WHERE (((MixDesignID.PlantID)= " & Me.PlantID & _
") AND ((MixDesignID.MixID)= " & Me.MixID & "))"

Also, the 2 equalities in the last ON clause looks like a duplication since
you had previously (in the first ON clause)

MixDesignID.ID = PlantID.ID

If you look at the first part of the second ON clause

MixDesignID.ID = StoneID.ID

Combining the about 2, you have:

MixDesignID.ID = PlantID.ID = StoneID.ID

which means that the second part of the second ON clause

PlantID.ID = StoneID.ID

has already been specified. The duplicated condition may be harmless but it
may also confuse the JET database engine! It is better to take it out!

HTH
Van T. Dinh
MVP (Access)
 
Back
Top