Access Error - Too Few Parameters

you might have a mispelled field name or

check this link
http://www.mvps.org/access/queries/qry0013.htm

Well I found one mistake, I wasn't pulling info from the right tables but I fixed my SQL statement and now I am getting the following error:

-----------------------------------------
Runtime error 3075
Syntax error (missing operator) in query expression 'tblPhrase.Phrasenum = cast(tblCustProd.Phrase as smallint)'
----------------------------------------

Here is my new code with the new SQL Statement:

Private Sub CustomerPhrase()

    Dim strItemNum As String
    Dim strCustNum As String
    Dim strSQL As String
    Dim dbs As Database
    Dim rs As DAO.Recordset
   
    strItemNum = Trim(Me.Text30)
    strCustNum = Trim(Me.cust_num)
   
    strSQL = "Select tblPhrase.Phrase, tblCustProd.CustNum, tblCustProd.Product " _
             & "from tblPhrase inner join tblCustProd on (tblPhrase.Phrasenum = cast(tblCustProd.Phrase as smallint)) " _
             & "where tblCustProd.CustNum = '" & strCustNum & "' And tblCustProd.Product = '" & strItemNum & "';"
    Debug.Print strSQL
    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset(strSQL, DB_OPEN_DYNASET, dbSeeChanges)
   
    If rs!Phrase <> Null Then
        Me.lblPhrase = rs!Phrase
    Else
        Me.lblPhrase = "Customer/Product record does not exist."
    End If
    Set dbs = Nothing
    Set rs = Nothing
End Sub

I think the problem is in the sql statement.  It should be the following:

    strsql = "Select tblPhrase.Phrase, tblCustProd.CustNum, tblCustProd.Product " _
             & "from tblPhrase inner join tblCustProd on (tblPhrase.Phrasenum = cast(tblCustProd.Phrase as smallint)) " _
             & "where tblCustProd.CustNum = '" & strCustNum & "'" & " and" & "tblCustProd.Product = '" & strItemNum & "';"


It wasn't a complete string.  You can tell by the word and showing as And.

Thanks but that didn't work.  I even tried added a space behind the word 'and' in your revised query.  Any more ideas?

Did you get the same syntax error?

yes

Itran2015.

Get you supply a working page example?
And the Database Structor as well.
(Just for the Tables that are used)

I have ran across this error many times in the past.
But cannot remember what I did to fix it.
So without having an actual running code, I cannot help.
Though I have searched the net, but all information differs from the next.
So that is of no help.

Carrzkiss

capricorn1, Carrzkiss

One thing that I failed to mention is that both of these tables are SQL Server Linked Tables.

When I run it without "cast()" I get:
-------------------------------------------
Runtime error 3615
Type mismatch in expression.  
-------------------------------------------
It has to be this section b/c   "tblPhrase.Phrasenum = tblCustProd.Phrase"

tblPhrase.Phrasenum is smallint
while tblCustProd.Phrase is char

This is why I was trying to do the type conversion.

CustNum is of type char.

Thanks for all of your help.  I have to get this figured out..