Hi Amy

Many thanks for help. I will try it this evening

ciao

Peter

> -----Messaggio originale-----
> Da:	Amy Wyatt [SMTP:amyw@c...]
> Inviato:	marted=EC 18 giugno 2002 18.01
> A:	Access
> Oggetto:	[access] Re: R: Re: R: Re: recipes treatment - SQL PROBLEM
>
> Check my response in the Second thread you started R: RE: recipe ... 
and
> you will see that if you are using the same table twice (or more) in 
a
> query, you have to use parenthasis () around parts of the from 
statement.
>
> Thanks,
>
> Amy
>
> > Dear Brian
>
> Really I have made some confusion  in alias names (since I'm 
beginner). =3D
> I
> applied changes you suggested. But I get run-time error 3075: syntax 
=3D
> error
> (missing operator) in query expression. But I cannot find this.
> In any case I enclose code again.  I'm afraid of that it is not 
correct =3D
> to
> apply INNER JOIN  two or three times in SQL..  (because when I choose 
=3D
> only 1
> ingredient on frmCriteria or 1 ingredient + company, or 1 ingredient 
+
> formulationtype etc. code is running well. The error is coming when 
=3D
> more
> than 1 ingredients  are being put in criteria.
> =3D20
>  (herein already 3 ingredients on frmCriteria), [ Recipe Detailed]
> corresponds to tblRecipeIngredient
>
>
> Function BuildSQLString(strSQL As String) As Boolean
> Dim strSELECT As String
> Dim strFROM As String
> Dim strWHERE As String
> strSELECT =3D3D "s.* "
> strFROM =3D3D "Recipes s "
> If chkIngredient1ID Then
>    =3D20
>     strFROM =3D3D strFROM & "INNER JOIN [Recipe Detailed] i " & _
>     "ON s.RecipeID=3D3Di.RecipeID "
>     strWHERE =3D3D " AND [Recipe Detailed].IngredientID=3D3D " & =3D
> cboIngredient1ID
>   =3D20
> End If
> If chkCompanyID Then
>     strWHERE =3D3D strWHERE & " AND r.CustomerID =3D3D " & 
cboCompanyID
> End If
> If chkFormulationTypeID Then
>     strWHERE =3D3D strWHERE & " AND r.FormulationTypeID =3D3D " &
> cboFormulationTypeID
> End If
>
> strSQL =3D3D "SELECT " & strSELECT
> strSQL =3D3D strSQL & "FROM " & strFROM
> If strWHERE <> "" Then
>     strSQL =3D3D strSQL & "WHERE " & Mid$(strWHERE, 6)
> End If
> BuildSQLString =3D3D True
>
> End Function
>
> Many thanks for your kind collboration and help, best regards
>
> Peter
>
>
> > -----Messaggio originale-----
> > Da:	braxis@b... [SMTP:braxis@b...]
> > Inviato:	marted=3DEC 18 giugno 2002 15.37
> > A:	Access
> > Oggetto:	[access] Re: R: Re: recipes treatment - SQL PROBLEM
> >=3D20
> > Peter
> >=3D20
> > There seems to be some confusion with the table aliases the code is 
=3D
> using.
> > This may be causing the problem.
> >=3D20
> > Try the changes below:
> >=3D20
> > > Function BuildSQLString(strSQL As String) As Boolean
> > > Dim strSELECT As String
> > > Dim strFROM As String
> > > Dim strWHERE As String
> > > strSELECT =3D3D "s.* "
> > > strFROM =3D3D "tblRecipes s "
> > > If chkIngredient1ID Then  ' checkbox in  frmCriteria when is =3D
> enabled  I
> > can
> > > choose ingredient from combobox
> > >    =3D20
> > >     strFROM =3D3D strFROM & "INNER JOIN [tblRecipeIngredient] i " 
& _
> > >     "ON s.RecipeID=3D3Di.RecipeID "
> > >     strWHERE =3D3D " AND [tblRecipeIngredient].IngredientID=3D3D 
" &
> > > cboIngredient1ID
> > CHANGE TO
> >       strWHERE =3D3D " AND i.IngredientID=3D3D " & cboIngredient1ID
> >=3D20
> > >   =3D20
> > > End If
> > >=3D20
> > > If chkIngredient2ID Then  ' checkbox in  frmCriteria when is =3D
> enabled  I
> > can
> > > choose ingredient from combobox
> > >    =3D20
> > >=3D20
> > >    =3D20
> > >     strFROM =3D3D strFROM & "INNER JOIN [tblRecipeIngredient] i " 
& _
> > CHANGE TO
> >       strFROM =3D3D strFROM & "INNER JOIN [tblRecipeIngredient] i2 
" & =3D
> _
> >=3D20
> > >     "ON tblRecipe.RecipeID=3D3DtblRecipeIngredient.RecipeID "
> > CHANGE TO
> >       "ON s.RecipeID=3D3Di2.RecipeID "
> >=3D20
> > >     strWHERE =3D3D " AND [tblRecipeIngredient].IngredientID=3D3D 
" &
> > > cboIngredient2ID
> > CHANGE TO
> >       strWHERE =3D3D " AND i2.IngredientID=3D3D " & 
cboIngredient2ID
> >=3D20
> > >   =3D20
> > > End If
> > >=3D20
> > >=3D20
> > >=3D20
> > > If chkCompanyID Then
> > >     strWHERE =3D3D strWHERE & " AND tblRecipe.CustomerID =3D3D " 
& =3D
> cboCompanyID
> > CHANGE TO
> >       strWHERE =3D3D strWHERE & " AND s.CustomerID =3D3D " & 
cboCompanyID
> >=3D20
> > > End If
> > > If chkFormulationTypeID Then
> > >     strWHERE =3D3D strWHERE & " AND tblRecipe.FormulationTypeID 
=3D3D " =3D
> &
> > > cboFormulationTypeID
> > CHANGE TO
> >       strWHERE =3D3D strWHERE & " AND s.FormulationTypeID =3D3D " &
> > cboFormulationTypeID
> >=3D20
> > > End If
> > >=3D20
> > > strSQL =3D3D "SELECT " & strSELECT
> > > strSQL =3D3D strSQL & "FROM " & strFROM
> > > If strWHERE <> "" Then
> > >     strSQL =3D3D strSQL & "WHERE " & Mid$(strWHERE, 6)
> > > End If
> > > BuildSQLString =3D3D True
> > >=3D20
> > > End Function
> > >=3D20
> > > The form frmCriteria contain command button cmdFind and  its =3D
> clicking
> > event
> > > call BuildSQLString
> > >=3D20
> > > The frmCriteria contains different checkboxes and combobox.
> > >=3D20
> > >=3D20
> > > Waiting news
> > >=3D20
> > >=3D20
> > > many thanks in advance, best regards, ciao
> > >=3D20
> > > Peter
> > >=3D20
> >=3D20
> >=3D20
> >=3D20
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or=3D20
> > to unsubscribe send a blank email to =3D