p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

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


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

I send hereby non working SQL which was prepared according to 
instuctions
found in the book of  Beginning Access 2000 VBA ( but I'm using MS 
Access
97)
I hope that it is clear because I apply a  function BuildSQLString in 
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 =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
  
End If

If chkIngredient2ID Then  ' checkbox in  frmCriteria when is enabled  I 
can
choose ingredient from combobox
   

   
    strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
    "ON tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID "
    strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
cboIngredient2ID
  
End If



If chkCompanyID Then
    strWHERE =3D strWHERE & " AND tblRecipe.CustomerID =3D " & 
cboCompanyID
End If
If chkFormulationTypeID Then
    strWHERE =3D strWHERE & " AND tblRecipe.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

> -----Messaggio originale-----
> Da:	braxis@b... [SMTP:braxis@b...]
> Inviato:	marted=EC 18 giugno 2002 12.48
> A:	Access
> Oggetto:	[access] Re: recipes treatment - SQL PROBLEM
>
> Hi Peter
>
> Could you send the non-working SQL?
>
> Brian
>
> >  from:    Bohus Peter <peter.bohus@l...>
> >  date:    Tue, 18 Jun 2002 11:05:13
> >  to:      access@p...
> >  subject: Re: [access] recipes treatment - SQL PROBLEM
> >
> > Have a Nice Day for Everybody
> >
> > I hope I  will have similarly nice day if somebody can give  me 
solution
> . I
> > have  several tables and among them there are 3 tables (tblRecipe,
> > tblIngredient and  tblRecipeIngredient)   which ought to be used  
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 
be
> used
> > in many  recipes. Therefore tblRecipeIngredient can be considered 
as
> > intermediate table for crating  many-to-many relationship.
> >
> >
> > 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
> >
> > Select * FROM tblRecipe  INNER JOIN  tblRecipeIngredient  ON
> > tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID
> > WHERE IngredientID=3Dx
> >
> > But I would like to find all recipes which, for example, contain
> > simultaneously 2 or 3 pre-determined ingredient. Using AND  
operator in
> > WHERE clause I don't get required results.
> >
> > What can I do it?
> >
> >
> > tblRecipe:
> > RecipeID
> > Recipename
> > Ingredient
> > ....
> >
> > tblIngredients
> > IngredientID
> > Name
> > Description
> > ..
> > tblRecipesIngredients
> > IngredientRecipeID
> > RecipeID
> > IngredientID
> > Quantity
> >
> > Best regards
> >
> > ciao
> >
> >
> > Peter
> >
>
>
Message #2 by braxis@b... on Tue, 18 Jun 2002 14:37:10 +0100 (BST)
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 = "s.* "
> strFROM = "tblRecipes s "
> If chkIngredient1ID Then  ' checkbox in  frmCriteria when is enabled  I can
> choose ingredient from combobox
>     
>     strFROM = strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
>     "ON s.RecipeID=i.RecipeID "
>     strWHERE = " AND [tblRecipeIngredient].IngredientID= " &
> cboIngredient1ID
CHANGE TO
      strWHERE = " AND i.IngredientID= " & cboIngredient1ID

>    
> End If
> 
> If chkIngredient2ID Then  ' checkbox in  frmCriteria when is enabled  I can
> choose ingredient from combobox
>     
> 
>     
>     strFROM = strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
CHANGE TO
      strFROM = strFROM & "INNER JOIN [tblRecipeIngredient] i2 " & _

>     "ON tblRecipe.RecipeID=tblRecipeIngredient.RecipeID "
CHANGE TO
      "ON s.RecipeID=i2.RecipeID "

>     strWHERE = " AND [tblRecipeIngredient].IngredientID= " &
> cboIngredient2ID
CHANGE TO
      strWHERE = " AND i2.IngredientID= " & cboIngredient2ID

>    
> End If
> 
> 
> 
> If chkCompanyID Then
>     strWHERE = strWHERE & " AND tblRecipe.CustomerID = " & cboCompanyID
CHANGE TO
      strWHERE = strWHERE & " AND s.CustomerID = " & cboCompanyID

> End If
> If chkFormulationTypeID Then
>     strWHERE = strWHERE & " AND tblRecipe.FormulationTypeID = " &
> cboFormulationTypeID
CHANGE TO
      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 = 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 "Amy Wyatt" <amyw@c...> on Tue, 18 Jun 2002 15:48:35
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 = "s.* "

    strFROM = "tblRecipes s "
    If chkIngredient1ID Then  ' true choose ingredient from combobox 1
        'Add parentheses to from statement in case second ingredient used
         strFROM = "(" & strFROM & "INNER JOIN tblRecipeIngredient i ON " _
                  & "s.RecipeID =i.RecipeID) "
        strWHERE = " AND i.IngredientID=" & cboIngredient1ID
    End If
    
    If chkIngredient2ID Then  ' true choose ingredient from combobox
        strFROM = strFROM & "INNER JOIN tblRecipeIngredient z ON " _
                & "s.RecipeID = z.RecipeID"
        strWHERE = strWHERE & " AND z.IngredientID= " & cboIngredient2ID
    End If
    
    
    
    If chkCompanyID Then
        strWHERE = strWHERE & "  AND s.CustomerID=" & cboCompanyID
    End If
    
    If chkFormulationTypeID 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

Hope this helps.

Amy

> Hi Brian,

I send hereby non working SQL which was prepared according to 
instuctions
found in the book of  Beginning Access 2000 VBA ( but I'm using MS 
Access
97)
I hope that it is clear because I apply a  function BuildSQLString in 
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 =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
  
End If

If chkIngredient2ID Then  ' checkbox in  frmCriteria when is enabled  I 
can
choose ingredient from combobox
   

   
    strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
    "ON tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID "
    strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
cboIngredient2ID
  
End If



If chkCompanyID Then
    strWHERE =3D strWHERE & " AND tblRecipe.CustomerID =3D " & 
cboCompanyID
End If
If chkFormulationTypeID Then
    strWHERE =3D strWHERE & " AND tblRecipe.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

> -----Messaggio originale-----
> Da:	braxis@b... [SMTP:braxis@b...]
> Inviato:	marted=EC 18 giugno 2002 12.48
> A:	Access
> Oggetto:	[access] Re: recipes treatment - SQL PROBLEM
>
> Hi Peter
>
> Could you send the non-working SQL?
>
> Brian
>
> >  from:    Bohus Peter <peter.bohus@l...>
> >  date:    Tue, 18 Jun 2002 11:05:13
> >  to:      access@p...
> >  subject: Re: [access] recipes treatment - SQL PROBLEM
> >
> > Have a Nice Day for Everybody
> >
> > I hope I  will have similarly nice day if somebody can give  me 
solution
> . I
> > have  several tables and among them there are 3 tables (tblRecipe,
> > tblIngredient and  tblRecipeIngredient)   which ought to be used  
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 
be
> used
> > in many  recipes. Therefore tblRecipeIngredient can be considered 
as
> > intermediate table for crating  many-to-many relationship.
> >
> >
> > 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
> >
> > Select * FROM tblRecipe  INNER JOIN  tblRecipeIngredient  ON
> > tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID
> > WHERE IngredientID=3Dx
> >
> > But I would like to find all recipes which, for example, contain
> > simultaneously 2 or 3 pre-determined ingredient. Using AND  
operator in
> > WHERE clause I don't get required results.
> >
> > What can I do it?
> >
> >
> > tblRecipe:
> > RecipeID
> > Recipename
> > Ingredient
> > ....
> >
> > tblIngredients
> > IngredientID
> > Name
> > Description
> > ..
> > tblRecipesIngredients
> > IngredientRecipeID
> > RecipeID
> > IngredientID
> > Quantity
> >
> > Best regards
> >
> > ciao
> >
> >
> > Peter
> >
>
>
Message #4 by "Carnley, Dave" <dcarnley@a...> on Tue, 18 Jun 2002 09:52:54 -0500
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 = RecipeIngredient.RecipeID
WHERE (RecipeIngredient.IngredientID IN (1, 2, 3))
GROUP BY Recipe.recipeID
HAVING (COUNT(RecipeIngredient.IngredientID) = 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 = ...
and FormulationTypeID = ...





-----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 = "s.* "

    strFROM = "tblRecipes s "
    If chkIngredient1ID Then  ' true choose ingredient from combobox 1
        'Add parentheses to from statement in case second ingredient used
         strFROM = "(" & strFROM & "INNER JOIN tblRecipeIngredient i ON " _
                  & "s.RecipeID =i.RecipeID) "
        strWHERE = " AND i.IngredientID=" & cboIngredient1ID
    End If
    
    If chkIngredient2ID Then  ' true choose ingredient from combobox
        strFROM = strFROM & "INNER JOIN tblRecipeIngredient z ON " _
                & "s.RecipeID = z.RecipeID"
        strWHERE = strWHERE & " AND z.IngredientID= " & cboIngredient2ID
    End If
    
    
    
    If chkCompanyID Then
        strWHERE = strWHERE & "  AND s.CustomerID=" & cboCompanyID
    End If
    
    If chkFormulationTypeID 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

Hope this helps.

Amy

> Hi Brian,

I send hereby non working SQL which was prepared according to 
instuctions
found in the book of  Beginning Access 2000 VBA ( but I'm using MS 
Access
97)
I hope that it is clear because I apply a  function BuildSQLString in 
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 =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
  
End If

If chkIngredient2ID Then  ' checkbox in  frmCriteria when is enabled  I 
can
choose ingredient from combobox
   

   
    strFROM =3D strFROM & "INNER JOIN [tblRecipeIngredient] i " & _
    "ON tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID "
    strWHERE =3D " AND [tblRecipeIngredient].IngredientID=3D " &
cboIngredient2ID
  
End If



If chkCompanyID Then
    strWHERE =3D strWHERE & " AND tblRecipe.CustomerID =3D " & 
cboCompanyID
End If
If chkFormulationTypeID Then
    strWHERE =3D strWHERE & " AND tblRecipe.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

> -----Messaggio originale-----
> Da:	braxis@b... [SMTP:braxis@b...]
> Inviato:	marted=EC 18 giugno 2002 12.48
> A:	Access
> Oggetto:	[access] Re: recipes treatment - SQL PROBLEM
>
> Hi Peter
>
> Could you send the non-working SQL?
>
> Brian
>
> >  from:    Bohus Peter <peter.bohus@l...>
> >  date:    Tue, 18 Jun 2002 11:05:13
> >  to:      access@p...
> >  subject: Re: [access] recipes treatment - SQL PROBLEM
> >
> > Have a Nice Day for Everybody
> >
> > I hope I  will have similarly nice day if somebody can give  me 
solution
> . I
> > have  several tables and among them there are 3 tables (tblRecipe,
> > tblIngredient and  tblRecipeIngredient)   which ought to be used  
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 
be
> used
> > in many  recipes. Therefore tblRecipeIngredient can be considered 
as
> > intermediate table for crating  many-to-many relationship.
> >
> >
> > 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
> >
> > Select * FROM tblRecipe  INNER JOIN  tblRecipeIngredient  ON
> > tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID
> > WHERE IngredientID=3Dx
> >
> > But I would like to find all recipes which, for example, contain
> > simultaneously 2 or 3 pre-determined ingredient. Using AND  
operator in
> > WHERE clause I don't get required results.
> >
> > What can I do it?
> >
> >
> > tblRecipe:
> > RecipeID
> > Recipename
> > Ingredient
> > ....
> >
> > tblIngredients
> > IngredientID
> > Name
> > Description
> > ..
> > tblRecipesIngredients
> > IngredientRecipeID
> > RecipeID
> > IngredientID
> > Quantity
> >
> > Best regards
> >
> > ciao
> >
> >
> > Peter
> >
>
>
Message #5 by Bohus Peter <peter.bohus@l...> on Wed, 19 Jun 2002 09:11:40 +0200
 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

> -----Messaggio originale-----
> Da:	Carnley, Dave [SMTP:dcarnley@a...]
> Inviato:	marted=EC 18 giugno 2002 18.54
> A:	Access
> Oggetto:	[access] RE: recipes treatment - SQL PROBLEM
>
> Leo,
> that was my first thought too, but just using IN by itself gives you
> recipes
> that have any one of the ingredients, but I think he needs a way to 
find
> recipes that have all of the listed ingredients.
>
> -----Original Message-----
> From: Leo Scott [mailto:leoscott@c...]
> Sent: Tuesday, June 18, 2002 11:07 AM
> To: Access
> Subject: [access] RE: recipes treatment - SQL PROBLEM
>
>
> Change your SQL to something like this:
>
> WHERE IngredientID In(a,b,c,d,e)
>
> That assumes IngredientID is a numeric value if it is a string then
> WHERE IngredientID In('a','b','c','d','e')
>
> |-----Original Message-----
> |From: Bohus Peter [mailto:peter.bohus@l...]
> |Sent: Tuesday, June 18, 2002 3:05 AM
> |To: Access
> |Subject: [access] recipes treatment - SQL PROBLEM
> |Importance: High
> |
> |
> |Have a Nice Day for Everybody
> |
> |I hope I  will have similarly nice day if somebody can give  me
> |solution . I
> |have  several tables and among them there are 3 tables (tblRecipe,
> |tblIngredient and  tblRecipeIngredient)   which ought to be used  
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 
be
> used
> |in many  recipes. Therefore tblRecipeIngredient can be considered as
> |intermediate table for crating  many-to-many relationship.
> |
> |
> |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
> |
> |Select * FROM tblRecipe  INNER JOIN  tblRecipeIngredient  ON
> |tblRecipe.RecipeID=3DtblRecipeIngredient.RecipeID
> |WHERE IngredientID=3Dx
> |
> |But I would like to find all recipes which, for example, contain
> |simultaneously 2 or 3 pre-determined ingredient. Using AND  operator 
in
> |WHERE clause I don't get required results.
> |
> |What can I do it?
> |
> |
> |tblRecipe:
> |RecipeID
> |Recipename
> |Ingredient
> |....
> |
> |tblIngredients
> |IngredientID
> |Name
> |Description
> |..
> |tblRecipesIngredients
> |IngredientRecipeID
> |RecipeID
> |IngredientID
> |Quantity
> |
> |Best regards
> |
> |ciao
> |
> |
> |Peter
> |
>
>
>
Message #6 by "Amy Wyatt" <amyw@c...> on Wed, 19 Jun 2002 13:46:20
I was unclear what you were passing since it was not being used in the 
code you sent. You were returning a boolean, I'm assuming, that determined 
if any SQL was built or not. The change I made would actually return the 
SQL statement through the function. That way you have the statement to use 
wherever the function is called. Otherwise, in order to use the strSQL 
statement that is built, you would need to globalize the strSQL variable. 
Returning strSQL through the function just streamlines the process and it 
can be used anywhere to build the SQL. You can always test for strSQL="" 
after the function returns the SQL string.

This is an example:

Your form Criteria has your check boxes and combo boxes. After the user 
makes their choices they click a find button. This runs the BuildSQLString 
function and returns the statement to the Criteria Form. Now you can open 
the display form with the results and enter the returned strSQL as the 
RecordSource of that form and 'viola' the list of recipes are displayed. 
You do not have to pass any information to the function at all since you 
are reading directly from the form. The cmdFind buttons code would look 
something like this...

     Sub cmdFind_Click()
        Dim strReturnSql as String
        
        Me.Refresh
        strReturnSql=BuildSQLString
        Docmd.OpenForm "frmDisplayResult",acNormal
        Forms!frmDisplayResult.RecordSource=strReturnSql
        Forms!frmDisplayResult.Requery
     End Sub

It is certainly not something you have to do but it is the way I would 
have done it so it is just another way to look at it.

Thanks,

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 #7 by "Amy Wyatt" <amyw@c...> on Wed, 19 Jun 2002 13:56:21
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


  Return to Index