|
 |
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
|
|
|
View Cart
