p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: R: Re: R: Re: recipes treatment - SQL PROBLEM


Message #1 by Bohus Peter <peter.bohus@l...> on Tue, 18 Jun 2002 16:47:07 +0200
Dear Brian

Really I have made some confusion  in alias names (since I'm beginner). 
I
applied changes you suggested. But I get run-time error 3075: syntax 
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 
to
apply INNER JOIN  two or three times in SQL..  (because when I choose 
only 1
ingredient on frmCriteria or 1 ingredient + company, or 1 ingredient +
formulationtype etc. code is running well. The error is coming when 
more
than 1 ingredients  are being put in criteria.

 (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 =3D "s.* "
strFROM =3D "Recipes s "
If chkIngredient1ID Then
   
    strFROM =3D strFROM & "INNER JOIN [Recipe Detailed] i " & _
    "ON s.RecipeID=3Di.RecipeID "
    strWHERE =3D " AND [Recipe Detailed].IngredientID=3D " & 
cboIngredient1ID
  
End If
If chkCompanyID Then
    strWHERE =3D strWHERE & " AND r.CustomerID =3D " & cboCompanyID
End If
If chkFormulationTypeID Then
    strWHERE =3D strWHERE & " AND r.FormulationTypeID =3D " &
cboFormulationTypeID
End If

strSQL =3D "SELECT " & strSELECT
strSQL =3D strSQL & "FROM " & strFROM
If strWHERE <> "" Then
    strSQL =3D strSQL & "WHERE " & Mid$(strWHERE, 6)
End If
BuildSQLString =3D True

End Function

Many thanks for your kind collboration and help, best regards

Peter


> -----Messaggio originale-----
> Da:	braxis@b... [SMTP:braxis@b...]
> Inviato:	marted=EC 18 giugno 2002 15.37
> A:	Access
> Oggetto:	[access] Re: R: Re: recipes treatment - SQL PROBLEM
>
> Peter
>
> There seems to be some confusion with the table aliases the code is 
using.
> This may be causing the problem.
>
> Try the changes below:
>
> > Function BuildSQLString(strSQL As String) As Boolean
> > Dim strSELECT As String
> > Dim strFROM As String
> > Dim strWHERE As String
> > strSELECT =3D "s.* "
> > strFROM =3D "tblRecipes s "
> > If chkIngredient1ID Then  ' checkbox in  frmCriteria when is 
enabled  I
> can
> > choose ingredient from combobox
> >    
> >     strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> >     "ON s.RecipeID=3Di.RecipeID "
> >     strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
> > cboIngredient1ID
> CHANGE TO
>       strWHERE =3D " AND i.IngredientID=3D " & cboIngredient1ID
>
> >   
> > End If
> >
> > If chkIngredient2ID Then  ' checkbox in  frmCriteria when is 
enabled  I
> can
> > choose ingredient from combobox
> >    
> >
> >    
> >     strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> CHANGE TO
>       strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i2 " & 
_
>
> >     "ON tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID "
> CHANGE TO
>       "ON s.RecipeID=3Di2.RecipeID "
>
> >     strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
> > cboIngredient2ID
> CHANGE TO
>       strWHERE =3D " AND i2.IngredientID=3D " & cboIngredient2ID
>
> >   
> > End If
> >
> >
> >
> > If chkCompanyID Then
> >     strWHERE =3D strWHERE & " AND tblRecipe.CustomerID =3D " & 
cboCompanyID
> CHANGE TO
>       strWHERE =3D strWHERE & " AND s.CustomerID =3D " & cboCompanyID
>
> > End If
> > If chkFormulationTypeID Then
> >     strWHERE =3D strWHERE & " AND tblRecipe.FormulationTypeID =3D " 
&
> > cboFormulationTypeID
> CHANGE TO
>       strWHERE =3D strWHERE & " AND s.FormulationTypeID =3D " &
> cboFormulationTypeID
>
> > End If
> >
> > strSQL =3D "SELECT " & strSELECT
> > strSQL =3D strSQL & "FROM " & strFROM
> > If strWHERE <> "" Then
> >     strSQL =3D strSQL & "WHERE " & Mid$(strWHERE, 6)
> > End If
> > BuildSQLString =3D True
> >
> > End Function
> >
> > The form frmCriteria contain command button cmdFind and  its 
clicking
> event
> > call BuildSQLString
> >
> > The frmCriteria contains different checkboxes and combobox.
> >
> >
> > Waiting news
> >
> >
> > many thanks in advance, best regards, ciao
> >
> > Peter
> >
>
>
>
Message #2 by "Amy Wyatt" <amyw@c...> on Tue, 18 Jun 2002 16:01:24
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). 
I
applied changes you suggested. But I get run-time error 3075: syntax 
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 
to
apply INNER JOIN  two or three times in SQL..  (because when I choose 
only 1
ingredient on frmCriteria or 1 ingredient + company, or 1 ingredient +
formulationtype etc. code is running well. The error is coming when 
more
than 1 ingredients  are being put in criteria.

 (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 =3D "s.* "
strFROM =3D "Recipes s "
If chkIngredient1ID Then
   
    strFROM =3D strFROM & "INNER JOIN [Recipe Detailed] i " & _
    "ON s.RecipeID=3Di.RecipeID "
    strWHERE =3D " AND [Recipe Detailed].IngredientID=3D " & 
cboIngredient1ID
  
End If
If chkCompanyID Then
    strWHERE =3D strWHERE & " AND r.CustomerID =3D " & cboCompanyID
End If
If chkFormulationTypeID Then
    strWHERE =3D strWHERE & " AND r.FormulationTypeID =3D " &
cboFormulationTypeID
End If

strSQL =3D "SELECT " & strSELECT
strSQL =3D strSQL & "FROM " & strFROM
If strWHERE <> "" Then
    strSQL =3D strSQL & "WHERE " & Mid$(strWHERE, 6)
End If
BuildSQLString =3D True

End Function

Many thanks for your kind collboration and help, best regards

Peter


> -----Messaggio originale-----
> Da:	braxis@b... [SMTP:braxis@b...]
> Inviato:	marted=EC 18 giugno 2002 15.37
> A:	Access
> Oggetto:	[access] Re: R: Re: recipes treatment - SQL PROBLEM
>
> Peter
>
> There seems to be some confusion with the table aliases the code is 
using.
> This may be causing the problem.
>
> Try the changes below:
>
> > Function BuildSQLString(strSQL As String) As Boolean
> > Dim strSELECT As String
> > Dim strFROM As String
> > Dim strWHERE As String
> > strSELECT =3D "s.* "
> > strFROM =3D "tblRecipes s "
> > If chkIngredient1ID Then  ' checkbox in  frmCriteria when is 
enabled  I
> can
> > choose ingredient from combobox
> >    
> >     strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> >     "ON s.RecipeID=3Di.RecipeID "
> >     strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
> > cboIngredient1ID
> CHANGE TO
>       strWHERE =3D " AND i.IngredientID=3D " & cboIngredient1ID
>
> >   
> > End If
> >
> > If chkIngredient2ID Then  ' checkbox in  frmCriteria when is 
enabled  I
> can
> > choose ingredient from combobox
> >    
> >
> >    
> >     strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
> CHANGE TO
>       strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i2 " & 
_
>
> >     "ON tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID "
> CHANGE TO
>       "ON s.RecipeID=3Di2.RecipeID "
>
> >     strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
> > cboIngredient2ID
> CHANGE TO
>       strWHERE =3D " AND i2.IngredientID=3D " & cboIngredient2ID
>
> >   
> > End If
> >
> >
> >
> > If chkCompanyID Then
> >     strWHERE =3D strWHERE & " AND tblRecipe.CustomerID =3D " & 
cboCompanyID
> CHANGE TO
>       strWHERE =3D strWHERE & " AND s.CustomerID =3D " & cboCompanyID
>
> > End If
> > If chkFormulationTypeID Then
> >     strWHERE =3D strWHERE & " AND tblRecipe.FormulationTypeID =3D " 
&
> > cboFormulationTypeID
> CHANGE TO
>       strWHERE =3D strWHERE & " AND s.FormulationTypeID =3D " &
> cboFormulationTypeID
>
> > End If
> >
> > strSQL =3D "SELECT " & strSELECT
> > strSQL =3D strSQL & "FROM " & strFROM
> > If strWHERE <> "" Then
> >     strSQL =3D strSQL & "WHERE " & Mid$(strWHERE, 6)
> > End If
> > BuildSQLString =3D True
> >
> > End Function
> >
> > The form frmCriteria contain command button cmdFind and  its 
clicking
> event
> > call BuildSQLString
> >
> > The frmCriteria contains different checkboxes and combobox.
> >
> >
> > Waiting news
> >
> >
> > many thanks in advance, best regards, ciao
> >
> > Peter
> >
>
>
>
Message #3 by Bohus Peter <peter.bohus@l...> on Tue, 18 Jun 2002 17:14:19 +0200
Dear Dave,

Many thanks for help. I will try it this evening

ciao

Peter


> -----Messaggio originale-----
> Da:	Carnley, Dave [SMTP:dcarnley@a...]
> Inviato:	marted=EC 18 giugno 2002 16.53
> A:	Access
> Oggetto:	[access] Re: R: Re: recipes treatment - SQL PROBLEM
>
> There is no need to make multiple joins to the cross-reference table.
>
> You can retrieve the list of RecipeID's that have the correct set of
> ingredients witht he following query, then use that list and a second
> query
> to add further criteria (like company and method)
>
>
>
> SELECT Recipe.recipeID, COUNT(RecIng.IngredientID) AS CountIng
> FROM Recipe INNER JOIN
>     RecipeIngredient ON Recipe.RecipeID =3D RecipeIngredient.RecipeID
> WHERE (RecipeIngredient.IngredientID IN (1, 2, 3))
> GROUP BY Recipe.recipeID
> HAVING (COUNT(RecipeIngredient.IngredientID) =3D 3)
>
> This will return the list of recipe IDs, you could then use that 
recordset
> to build a query using the other criteria, and make na IN list of 
recipe
> IDs
>
> Select * from Recipe where RecipeID in (...)
> and companyID =3D ...
> and FormulationTypeID =3D ...
>
>
>
>
>
> -----Original Message-----
> From: Amy Wyatt [mailto:amyw@c...]
> Sent: Tuesday, June 18, 2002 10:49 AM
> To: Access
> Subject: [access] Re: R: Re: recipes treatment - SQL PROBLEM
>
>
> In order for this to work you have to add the table 
tblRecipeIngredient to
>
> the query twice which means you have to enclose the firs part of the 
FROM
> statement in (). Also, you are not adding the strWhere to the second
> strWhere you are replacing it when they choose a second ingredient. 
You
> have to concatinate the first where to the second.
>
> Try this:
> Function BuildSQLString() As String
>     Dim strSELECT As String
>     Dim strFROM As String
>     Dim strWHERE As String
>     dim strSQL as String
>
>     Me.Refresh
>     strSELECT =3D "s.* "
>
>     strFROM =3D "tblRecipes s "
>     If chkIngredient1ID Then  ' true choose ingredient from combobox 
1
>         'Add parentheses to from statement in case second ingredient 
used
>          strFROM =3D "(" & strFROM & "INNER JOIN tblRecipeIngredient 
i ON "
> _
>                   & "s.RecipeID =3Di.RecipeID) "
>         strWHERE =3D " AND i.IngredientID=3D" & cboIngredient1ID
>     End If
>    
>     If chkIngredient2ID Then  ' true choose ingredient from combobox
>         strFROM =3D strFROM & "INNER JOIN tblRecipeIngredient z ON " 
_
>                 & "s.RecipeID =3D z.RecipeID"
>         strWHERE =3D strWHERE & " AND z.IngredientID=3D " & 
cboIngredient2ID
>     End If
>    
>    
>    
>     If chkCompanyID Then
>         strWHERE =3D strWHERE & "  AND s.CustomerID=3D" & 
cboCompanyID
>     End If
>    
>     If chkFormulationTypeID Then
>         strWHERE =3D strWHERE & " AND s.FormulationTypeID=3D" _
>                        & cboFormulationTypeID
>     End If
>    
>     strSQL =3D "SELECT " & strSELECT
>     strSQL =3D strSQL & "FROM " & strFROM
>     If strWHERE <> "" Then
>         strSQL =3D strSQL & " WHERE " & Mid$(strWHERE, 6)
>     End If
>     BuildSQLString =3D strSQL
> End Function
>
> Hope this helps.
>
> Amy
>
> > Hi Brian,
>
> I send hereby non working SQL which was prepared according to =3D
> instuctions
> found in the book of  Beginning Access 2000 VBA ( but I'm using MS 
=3D
> Access
> 97)
> I hope that it is clear because I apply a  function BuildSQLString in 
=3D
> order
> to create SQL. This Function can be found in  class modul of one form
> (frmCriteria) and I intend to find recipe
>
> 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 enabled  
I =3D
> 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
>   =3D20
> End If
>
> If chkIngredient2ID Then  ' checkbox in  frmCriteria when is enabled  
I =3D
> can
> choose ingredient from combobox
>    =3D20
>
>    =3D20
>     strFROM =3D3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
>     "ON tblRecipe.RecipeID=3D3DtblRecipeIngredient.RecipeID "
>     strWHERE =3D3D " AND [tblRecipeIngredient].IngredientID=3D3D " &
> cboIngredient2ID
>   =3D20
> End If
>
>
>
> If chkCompanyID Then
>     strWHERE =3D3D strWHERE & " AND tblRecipe.CustomerID =3D3D " & 
=3D
> cboCompanyID
> End If
> If chkFormulationTypeID Then
>     strWHERE =3D3D strWHERE & " AND tblRecipe.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
>
> The form frmCriteria contain command button cmdFind and  its clicking 
=3D
> event
> call BuildSQLString
>
> The frmCriteria contains different checkboxes and combobox.
>
>
> Waiting news
>
>
> many thanks in advance, best regards, ciao
>
> Peter
>
> > -----Messaggio originale-----
> > Da:	braxis@b... [SMTP:braxis@b...]
> > Inviato:	marted=3DEC 18 giugno 2002 12.48
> > A:	Access
> > Oggetto:	[access] Re: recipes treatment - SQL PROBLEM
> >=3D20
> > Hi Peter
> >=3D20
> > Could you send the non-working SQL?
> >=3D20
> > Brian
> >=3D20
> > >  from:    Bohus Peter <peter.bohus@l...>
> > >  date:    Tue, 18 Jun 2002 11:05:13
> > >  to:      access@p...
> > >  subject: Re: [access] recipes treatment - SQL PROBLEM
> > >=3D20
> > > Have a Nice Day for Everybody
> > >=3D20
> > > I hope I  will have similarly nice day if somebody can give  me 
=3D
> solution
> > . I
> > > have  several tables and among them there are 3 tables 
(tblRecipe,
> > > tblIngredient and  tblRecipeIngredient)   which ought to be used  
=3D
> for
> > SQL.
> > > The appropriate relationship is as follows:
> > > tblrecipe (1) -> (n) tblRecipeIngredient  (n) <- (1) 
tblIngredient,
> > since a
> > > recipe is composed of many ingredients and the same ingredient 
can =3D
> be
> > used
> > > in many  recipes. Therefore tblRecipeIngredient can be considered 
=3D
> as
> > > intermediate table for crating  many-to-many relationship.
> > >=3D20
> > >=3D20
> > > I would like to make a query (or SQL...) in order to choose out 
all
> > recipes
> > > in database which contain certain ingredient (IngredientID)
> > > When I must choose only one ingredient the SQL,  like next one , 
is
> > working
> > > well
> > >=3D20
> > > Select * FROM tblRecipe  INNER JOIN  tblRecipeIngredient  ON
> > > tblRecipe.RecipeID=3D3DtblRecipeIngredient.RecipeID
> > > WHERE IngredientID=3D3Dx
> > >=3D20
> > > But I would like to find all recipes which, for example, contain
> > > simultaneously 2 or 3 pre-determined ingredient. Using AND  =3D
> operator in
> > > WHERE clause I don't get required results.
> > >=3D20
> > > What can I do it?
> > >=3D20
> > >=3D20
> > > tblRecipe:
> > > RecipeID
> > > Recipename
> > > Ingredient
> > > ....
> > >=3D20
> > > tblIngredients
> > > IngredientID
> > > Name
> > > Description
> > > ..
> > > tblRecipesIngredients
> > > IngredientRecipeID
> > > RecipeID
> > > IngredientID
> > > Quantity
> > >=3D20
> > > Best regards
> > >=3D20
> > > ciao
> > >=3D20
> > >=3D20
> > > Peter
> > >=3D20
> > > ---
> > > Change your mail options at http://p2p.wrox.com/manager.asp 
or=3D20
> >=3D20
> >=3D20
> > ---
> > Change your mail options at http://p2p.wrox.com/manager.asp or=3D20
> > to unsubscribe send a blank email to =3D
>
Message #4 by Bohus Peter <peter.bohus@l...> on Thu, 20 Jun 2002 15:00:37 +0200
Hi Amy,

Many thanks for comments. I have applied BuildSQLstring function with return
value declared as Boolean since in development stage I wanted to know
whether I was able to create SQL for query or not. It seems that your
solution is more flexible. I put in enclosed code of frmCriteria module.
Now I have  problem only to display results. As said before I 'm beginner
but step by step - following all useful instructions available under this
forum(list) I will arrange each problem.

Option Compare Database
Option Explicit
' frmCriteria contains five checkboxes and five comboboxes
' this structure provides to choose field values to be found
' and finally to create SQL string
' but first of all it is necessary to know how the comboboxes
' are enabled and disabled. Eacf of the check boxes has an event
' procedure handling its CLICK event
Private Sub chkCompanyID_Click()
cboCompanyID.Enabled = chkCompanyID
End Sub

Private Sub chkFormulationTypeID_Click()
cboFormulationTypeID.Enabled = chkFormulationTypeID
End Sub


Private Sub chkIngredient1ID_Click()
cboIngredient1ID.Enabled = chkIngredient1ID
End Sub

Private Sub chkIngredient2ID_Click()
cboIngredient2ID.Enabled = chkIngredient2ID
End Sub

Private Sub chkIngredient3ID_Click()
cboIngredient3ID.Enabled = chkIngredient3ID
End Sub
' the frmCriteria contains two command button: cmClose and cmdFind, they
' have CLICK events too.
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


    DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    
End Sub

Private Sub cmdFind_Click()
' cmdFind button will take Find Matches process , its CLICK
' event calls 2 different functions: EntriesValid and BuildSQLString
' these callings are carried out in indirect way through If Not ..Then
' condition. The said functions return variable Boolean
'Dim strSQL As String
' as Amy suggested I can introduce other declaration like next one
 Dim strReturnSQL As String 'supposing that  function
' BuildStrSQL returns with string but  in original version at
' function gives Boolean value
If Not EntriesValid Then Exit Sub
'If Not BuildSQLString(strSQL) Then
   ' MsgBox "There was a problem building the SQL string"
   ' Exit Sub
'End If
' in order to see strSQL string I can use Msgbox, but it will
' be cancelled from this development stage
strReturnSQL = BuildSQLString
MsgBox strReturnSQL
' using strReturnSQL a query is being built before displaying found
' and matched records
CurrentDb.QueryDefs("qryExample").SQL = BuildSQLString
' as Amy suggested to display results
Me.Refresh
' Amy suggested to open form but I have already prepared report
' rptRecipesSelected
DoCmd.OpenReport "rptRecipesSelected", acViewPreview
Reports!rptRecipesSelected.RecordSource = strReturnSQL
Reports!rptRecipesSelected.Requery
End Sub

Private Sub Form_Open(Cancel As Integer)
' when  I open form frmCriteria  I want that each combox is disabled
' just with checking on checkboxes I will be able to use comboboxes

cboCompanyID.Enabled = False
cboFormulationTypeID.Enabled = False
cboIngredient1ID.Enabled = False
cboIngredient2ID.Enabled = False
cboIngredient3ID.Enabled = False
End Sub

Function EntriesValid() As Boolean
'this function must control whether any checkbox is fired in appropriate
combobox
'any value is coming chosen or not. The return value is Boolean.
'ez a fuggveny azt ellenorzi, hogy amennyiben egy checkbox ki lett jelolve,
'ugy a megfelelo combolistben szinteen hozza lett e rendelve bemeneti
eerteek
Dim sMsg As String
Dim sBullet As String

sBullet = "*"

If chkCompanyID And IsNull(cboCompanyID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by company but " & _
    "have not selected a company. Either select a company or " & _
    "uncheck the ""Company is"" checkbox."
End If

If chkFormulationTypeID And IsNull(cboFormulationTypeID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by Formulation Type but " & _
    "have not selected a Formulation Type. Either select a Formlation Type
or " & _
    "uncheck the ""Formulation Type"" checkbox."
End If

If chkIngredient1ID And IsNull(cboIngredient1ID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
    "have not selected a first ingredient. Either select a first ingredient
or " & _
    "uncheck the ""First component"" checkbox."
End If
If chkIngredient2ID And IsNull(cboIngredient2ID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
    "have not selected a second ingredient. Either select a second
ingredient or " & _
    "uncheck the ""Second component"" checkbox."
End If
If chkIngredient3ID And IsNull(cboIngredient3ID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
    "have not selected a third ingredient. Either select a third ingredient
or " & _
    "uncheck the ""Third component"" checkbox."
End If


If sMsg <> "" Then
    MsgBox "The following errors are preventing you from " & _
           "viewing the records that match the criteria " & _
           "you have selected:" & sMsg & vbCrLf & vbCrLf & _
           "Please correct these errors and try again.", _
           vbExclamation + vbOKOnly
Else
    EntriesValid = True
End If

End Function

Function BuildSQLString() As String
'Function BuildSQLString(strSQL As String) As Boolean
' alternatively strSQL declaration was given as parameter
' and function return value was declared as Boolean
Dim strSQL As String

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Me.Refresh ' as suggested Amy
strSELECT = "s.* "
strFROM = "Recipes s "
If chkIngredient1ID = True Then ' true choose ingredient from combobox
CboIngredient1ID
    ' Amy suggested to add parentheses to FROM statement
    strFROM = "(" & strFROM & "INNER JOIN [Recipe Detailed] i " & _
    "ON s.RecipeID=i.RecipeID)"
    strWHERE = " AND i.IngredientID= " & cboIngredient1ID
   
End If
If chkIngredient2ID = True Then ' true choose ingredient from combobox
CboIngredient2ID
    
    strFROM = strFROM & "INNER JOIN [Recipe Detailed] i2 " & _
    "ON s.RecipeID=i2.RecipeID "
    strWHERE = strWHERE & " AND i2.IngredientID= " & cboIngredient2ID
   
End If
If chkIngredient3ID = True Then 'true choose ingredient from combobox
CboIngredient2ID
    
    
    strFROM = strFROM & "INNER JOIN [Recipe Detailed] i3 " & _
    "ON s.RecipeID=i3.RecipeID "
    strWHERE = strWHERE & " AND i3.IngredientID= " & cboIngredient3ID
   
End If

If chkCompanyID = True Then
    strWHERE = strWHERE & " AND s.CustomerID = " & cboCompanyID
End If
If chkFormulationTypeID = True Then
    strWHERE = strWHERE & " AND s.FormulationTypeID = " &
cboFormulationTypeID
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then
    strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
End If
BuildSQLString = strSQL

End Function


Best regards

Peter
> -----Messaggio originale-----
> Da:	Amy Wyatt [SMTP:amyw@c...]
> Inviato:	mercoledý 19 giugno 2002 15.56
> A:	Access
> Oggetto:	[access] Re: R: RE: recipes treatment - SQL PROBLEM
> 
> I made the change of the declaration and actually to the function itself 
> (I made it a string instead of a boolean) because from the code you sent I
> 
> did not see where you passed any variable that the function used. The 
> change I made would allow you to return the SQL string through the 
> function so that it can be used at the Call location.
> 
> For instance, if after the SQL is built you wanted to display the results 
> in another form you could do that as follows from the OnClick event of you
> 
> command button (the one that fires the BuildSQLString function):
> 
>    Private Sub cmdBSQL_Click()
>      Dim strReturnSql As String
>         
>         Me.Refresh
>         strReturnSql = BuildSQLString
>         DoCmd.OpenForm "frmDisplayResult", acNormal
>         Forms!frmDisplayResult.RecordSource = strReturnSql
>         Forms!frmDisplayResult.Requery
>         DoCmd.Close acForm, "frmCriteria", acSaveNo
>     
>    End Sub
> 
> If you do not need to passing information to the function there is really 
> no point including a parameter in the function. 
> 
> This, of course, is just the method I would use. Not knowing what you are 
> doing with the rest of the code makes it difficult to determine the best 
> method to use.
> 
> Hope this helps,
> 
> Amy
> 
> >  Hello " Boys" , Dear Dave, Leo, Amy and Brian
> 
> First of all I have to say a lot thanks  for your kind help since my 
> problem
> could be quickly  solved with success. Finally  I have applied  Amy's
> recommendations . Dave's solution is good too. As Leo supposed my
> IngredientID  is  keyfield having numeric value. In any case, as Dave 
> says I
> need to find all recipes containing pre-determined ingredients
> simultaneously (AND relation and not OR)
> Brian gave good contribution to understand alias name application and 
> to
> avoid confusion.
> 
> Looking at Amy's answer  I see that in function you have changed the 
> place
> of declaration of variable strSQL as string. What advantages or
> disadvantages can be considered regarding this change.
> 
>  Thanks again and ciao
> 
> Peter
> 
Message #5 by "Amy Wyatt" <amyw@c...> on Thu, 20 Jun 2002 15:23:45
I am not exactly sure which things are working and which you need help on 
but after examining the code there are a couple of things you may need to 
change. 

Before calling the EntriesValid function you should do a form refresh 
because, combo boxes and check boxes sometimes do not update as expected. 
Doing a refresh forces the data to be saved in the control after a change. 
(I don't know why this is but it happens.)

Second, you can't apply a record source to a report in preview mode. You 
will have to first open it in design mode, apply the recordsource and then 
open it in preview mode.
     DoCmd.OpenReport "rptRecipesSelected", acViewDesign
     Reports!rptRecipesSelected.RecordSource = strReturnSQL
     DoCmd.OpenReport "rptRecipesSelected", acViewPreview

I'm not sure why you are defining the query again as in 
(CurrentDb.QueryDefs("qryExample").SQL = BuildSQLString). I don't think 
you need to do this unless you are using it somewhere else in the code.

You should also make sure the sql was built even if it is not returning 
the boolean by 
     If strReturnSQL<>"" Then
        DoCmd.OpenReport .....
     
     Else
        MsgBox "There were no matches for the criteria you entered."
     End If

This way, you can prevent the report from opening with the dreaded #Error 
message when no data is found. This also means you would need to check the 
SQL statement in the BuildSQLString function to make sure it is returning 
at least one record. To do this you would define a recordset using strSQL 
and check to see if .EOF = True. Following is the code to add to the 
BuildSQLStrig function to accomplish this:

    Dim rstCheck as ADODB.Recordset

'put this before the line BuildSQLString = strSQL
    Set rstCheck=New ADODB.Recordset
    rstCheck.Open strSQL, CurrentProject.Connection, adOpenStatic, _
            adLockReadOnly, adCmdText
    If rstCheck.EOF Then strSQL=""
    rstCheck.Close
    set rstCheck=Nothing

This will determine if the SQL statement will return any rows and if it 
does not then the function returns the "" string.

Hope these are the things you are looking for.

Amy




> Hi Amy,

Many thanks for comments. I have applied BuildSQLstring function with 
return
value declared as Boolean since in development stage I wanted to know
whether I was able to create SQL for query or not. It seems that your
solution is more flexible. I put in enclosed code of frmCriteria module.
Now I have  problem only to display results. As said before I 'm beginner
but step by step - following all useful instructions available under this
forum(list) I will arrange each problem.

Option Compare Database
Option Explicit
' frmCriteria contains five checkboxes and five comboboxes
' this structure provides to choose field values to be found
' and finally to create SQL string
' but first of all it is necessary to know how the comboboxes
' are enabled and disabled. Eacf of the check boxes has an event
' procedure handling its CLICK event
Private Sub chkCompanyID_Click()
cboCompanyID.Enabled = chkCompanyID
End Sub

Private Sub chkFormulationTypeID_Click()
cboFormulationTypeID.Enabled = chkFormulationTypeID
End Sub


Private Sub chkIngredient1ID_Click()
cboIngredient1ID.Enabled = chkIngredient1ID
End Sub

Private Sub chkIngredient2ID_Click()
cboIngredient2ID.Enabled = chkIngredient2ID
End Sub

Private Sub chkIngredient3ID_Click()
cboIngredient3ID.Enabled = chkIngredient3ID
End Sub
' the frmCriteria contains two command button: cmClose and cmdFind, they
' have CLICK events too.
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


    DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    
End Sub

Private Sub cmdFind_Click()
' cmdFind button will take Find Matches process , its CLICK
' event calls 2 different functions: EntriesValid and BuildSQLString
' these callings are carried out in indirect way through If Not ..Then
' condition. The said functions return variable Boolean
'Dim strSQL As String
' as Amy suggested I can introduce other declaration like next one
 Dim strReturnSQL As String 'supposing that  function
' BuildStrSQL returns with string but  in original version at
' function gives Boolean value
If Not EntriesValid Then Exit Sub
'If Not BuildSQLString(strSQL) Then
   ' MsgBox "There was a problem building the SQL string"
   ' Exit Sub
'End If
' in order to see strSQL string I can use Msgbox, but it will
' be cancelled from this development stage
strReturnSQL = BuildSQLString
MsgBox strReturnSQL
' using strReturnSQL a query is being built before displaying found
' and matched records
CurrentDb.QueryDefs("qryExample").SQL = BuildSQLString
' as Amy suggested to display results
Me.Refresh
' Amy suggested to open form but I have already prepared report
' rptRecipesSelected
DoCmd.OpenReport "rptRecipesSelected", acViewPreview
Reports!rptRecipesSelected.RecordSource = strReturnSQL
Reports!rptRecipesSelected.Requery
End Sub

Private Sub Form_Open(Cancel As Integer)
' when  I open form frmCriteria  I want that each combox is disabled
' just with checking on checkboxes I will be able to use comboboxes

cboCompanyID.Enabled = False
cboFormulationTypeID.Enabled = False
cboIngredient1ID.Enabled = False
cboIngredient2ID.Enabled = False
cboIngredient3ID.Enabled = False
End Sub

Function EntriesValid() As Boolean
'this function must control whether any checkbox is fired in appropriate
combobox
'any value is coming chosen or not. The return value is Boolean.
'ez a fuggveny azt ellenorzi, hogy amennyiben egy checkbox ki lett jelolve,
'ugy a megfelelo combolistben szinteen hozza lett e rendelve bemeneti
eerteek
Dim sMsg As String
Dim sBullet As String

sBullet = "*"

If chkCompanyID And IsNull(cboCompanyID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by company but " & _
    "have not selected a company. Either select a company or " & _
    "uncheck the ""Company is"" checkbox."
End If

If chkFormulationTypeID And IsNull(cboFormulationTypeID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by Formulation Type but " & _
    "have not selected a Formulation Type. Either select a Formlation Type
or " & _
    "uncheck the ""Formulation Type"" checkbox."
End If

If chkIngredient1ID And IsNull(cboIngredient1ID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
    "have not selected a first ingredient. Either select a first ingredient
or " & _
    "uncheck the ""First component"" checkbox."
End If
If chkIngredient2ID And IsNull(cboIngredient2ID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
    "have not selected a second ingredient. Either select a second
ingredient or " & _
    "uncheck the ""Second component"" checkbox."
End If
If chkIngredient3ID And IsNull(cboIngredient3ID) Then
    sMsg = sMsg & vbCrLf & vbCrLf & sBullet & " You have chosen to restrict
by ingredient but " & _
    "have not selected a third ingredient. Either select a third ingredient
or " & _
    "uncheck the ""Third component"" checkbox."
End If


If sMsg <> "" Then
    MsgBox "The following errors are preventing you from " & _
           "viewing the records that match the criteria " & _
           "you have selected:" & sMsg & vbCrLf & vbCrLf & _
           "Please correct these errors and try again.", _
           vbExclamation + vbOKOnly
Else
    EntriesValid = True
End If

End Function

Function BuildSQLString() As String
'Function BuildSQLString(strSQL As String) As Boolean
' alternatively strSQL declaration was given as parameter
' and function return value was declared as Boolean
Dim strSQL As String

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Me.Refresh ' as suggested Amy
strSELECT = "s.* "
strFROM = "Recipes s "
If chkIngredient1ID = True Then ' true choose ingredient from combobox
CboIngredient1ID
    ' Amy suggested to add parentheses to FROM statement
    strFROM = "(" & strFROM & "INNER JOIN [Recipe Detailed] i " & _
    "ON s.RecipeID=i.RecipeID)"
    strWHERE = " AND i.IngredientID= " & cboIngredient1ID
   
End If
If chkIngredient2ID = True Then ' true choose ingredient from combobox
CboIngredient2ID
    
    strFROM = strFROM & "INNER JOIN [Recipe Detailed] i2 " & _
    "ON s.RecipeID=i2.RecipeID "
    strWHERE = strWHERE & " AND i2.IngredientID= " & cboIngredient2ID
   
End If
If chkIngredient3ID = True Then 'true choose ingredient from combobox
CboIngredient2ID
    
    
    strFROM = strFROM & "INNER JOIN [Recipe Detailed] i3 " & _
    "ON s.RecipeID=i3.RecipeID "
    strWHERE = strWHERE & " AND i3.IngredientID= " & cboIngredient3ID
   
End If

If chkCompanyID = True Then
    strWHERE = strWHERE & " AND s.CustomerID = " & cboCompanyID
End If
If chkFormulationTypeID = True Then
    strWHERE = strWHERE & " AND s.FormulationTypeID = " &
cboFormulationTypeID
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then
    strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
End If
BuildSQLString = strSQL

End Function


Best regards

Peter

  Return to Index