criteria query returns null

[Knowlton]

I have a query for the rowsource of a combo that returns null even though there are matching records. I assign this query in the AfterUpdate event of another control(SeedBra nd). During debugging the references contain the correct criteria but the combo is empty. I've recreated the query in a new query window and with no criteria or if I supply the actual values it returns the records but when I put in the control references and then provide the values it returns null.
Here is the table info:
VarietyID, CropID, SeedCompany, Variety
5, 3, 2, 2020
VarietyID is autonumber, CropID and SeedCompany are LongInteger, and Variety is text.
Here is the SQL:

SELECT tblVarieties.VarietyID, tblVarieties.Variety, tblVarieties.SeedCompany, tblVarieties.CropID
FROM tblVarieties
WHERE (((tblVarieties.SeedCompany)=[Me].[SeedBrand]) AND ((tblVarieties.CropID)=[Forms]![frmPlantings]![Crop]));

Thanks for your help!

[Rabbit]

The SQL engine has no idea what Me.SeedBrand is.

And it may not know what [Forms]![frmPlantings]![Crop] is either depending on where you're putting the SQL. If you're putting it directly into the record source, it might not work, I don't know for sure. But if you put it in the query editor and use the query as the record source, then it will work.

[NeoPa]

Your [Forms]![frmPlantings]![Crop] reference should be fine. It wouldn't like any dots (.) in the reference but what you have is fine.

[Me].[SeedBrand], on the other hand, will not work anywhere. This is what you should have got out of your test. You tested the SQL and it failed, just as it did when in the control. That indicates it's wrong.

Me is a VBA (only) item. Trying to use it within any SQL will never work.

Using VBA to build up a SQL string, however, is another matter entirely. Very common practice, and works well when done correctly. For instance, if you were to say :

Private Sub MyButton_Click()
    strSQL As String

    With Me
        strSQL = "SELECT [VarietyID], [Variety], [SeedCompany], [CropID] " & _
                 "FROM   [tblVarieties] " & _
                 "WHERE  ([SeedCompany]=" & .SeedBrand & ")" & _
                 "  AND  ([CropID]=" & Forms!frmPlantings.Crop & ")"
        .MyCombo.ControlSource = strSQL
    End With
End Sub

then I would expect it to work for you.