Solved VBA adds spaces in SQL

[ClaraBarton]

sql = "SELECT qryRecipe.recipeID, t_recipeingredient.ingredienttext AS Ingredient, " _
        & "qryRecipe.recipename, qryRecipe.t_cookbook.name AS cookbook, " _
        & "qryRecipe.t_cookbookchapter.name AS chapter, qryRecipe.createdate, " _
        & "qryRecipe.source, qryRecipe.author " _
        & "FROM qryRecipe LEFT JOIN t_recipeingredient ON qryRecipe.recipeID = t_recipeingredient.recipeid " _
        & "WHERE (((t_recipeingredient.ingredienttext) Like " * " & [Forms]![frmSearch]![ingredient] & " * ") " _
        & "AND ((qryRecipe.recipename) Like " * " & [Forms]![frmSearch]![recipename] & " * ") " _
        & "AND ((qryRecipe.t_cookbook.name) Like " * " & [Forms]![frmSearch]![cookbook] & " * ") " _
        & "AND ((qryRecipe.t_cookbookchapter.name) Like " * " & [Forms]![frmSearch]![chapter] & " * ") " _
        & "AND ((qryRecipe.createdate) Like " * " & [Forms]![frmSearch]![createdate] & " * ") " _
        & "AND ((qryRecipe.source) Like " * " & [Forms]![frmSearch]![source] & " * ") " _
        & "AND ((qryRecipe.author) Like " * " & [Forms]![frmSearch]![author] & " * "))"

This above sql statement will not work unless I remove the spaces between the " and the *
i.e. "*" and not " * ".
But every time I change all of them, Access puts them back. Auto correct or something. How do I fix this?

 

[Galaxiom]

They are being treated as string delimiters for the sql. Use single quotes around the asterisk or escape the double quotes by doubling them.

Usually best with the latter as this will avoid problems with apostrophes in names.

"AND ((qryRecipe.author) Like ""*"" & [Forms]![frmSearch]![author] & ""*""))"

 

[arnelgp]

access don't need those extra space:
it will work mostly with Text fields (don't know about [createDate] (date field?))

& "WHERE (((t_recipeingredient.ingredienttext) Like '*" & [Forms]![frmSearch]![ingredient] & "*') " _
        & "AND ((qryRecipe.recipename) Like '*" & [Forms]![frmSearch]![recipename] & "*') " _
        & "AND ((qryRecipe.t_cookbook.name) Like '*" & [Forms]![frmSearch]![cookbook] & "*') " _
        & "AND ((qryRecipe.t_cookbookchapter.name) Like '*" & [Forms]![frmSearch]![chapter] & "*') " _
        & "AND ((qryRecipe.createdate) Like '*" & [Forms]![frmSearch]![createdate] & "*') " _
        & "AND ((qryRecipe.source) Like '*" & [Forms]![frmSearch]![source] & "*') " _
        & "AND ((qryRecipe.author) Like '*" & [Forms]![frmSearch]![author] & "*'))"

 

[plog]

I think your WHERE is turning your LEFT JOIN into an INNER JOIN. When you add criteria to a table that has been used in a LEFT JOIN (t_recipeingredient) it requires data to be present to make it through the WHERE:

        & "FROM qryRecipe LEFT JOIN t_recipeingredient ON qryRecipe.recipeID = t_recipeingredient.recipeid " _
        & "WHERE (((t_recipeingredient.ingredienttext) Like " * " & [Forms]![frmSearch]![ingredient] & " * ") " _

So if a there is a recipeID in qryRecipe that has no matches in t_recipeingrient, it will not be in your results because a NULL value in ingredienttext will not make it through the WHERE.

Further, I think NULL values in any qryRecipe field in the WHERE will not allow that record into the query results. I suggest you fix your SQL by testing each form input for a value then adding it to the WHERE only if there is a value:

sql = "SELECT qryRecipe.recipeID, t_recipeingredient.ingredienttext AS Ingredient, " _
        & "qryRecipe.recipename, qryRecipe.t_cookbook.name AS cookbook, " _
        & "qryRecipe.t_cookbookchapter.name AS chapter, qryRecipe.createdate, " _
        & "qryRecipe.source, qryRecipe.author " _
        & "FROM qryRecipe LEFT JOIN t_recipeingredient ON qryRecipe.recipeID = t_recipeingredient.recipeid " _
        & "WHERE (1=1)"

if IsNull([Forms]![frmSearch]![recipename])=False THEN sql = sql & " AND (qryRecipe.recipename Like '*" & [Forms]![frmSearch]![recipename] & "*')"
if IsNull([Forms]![frmSearch]![cookbook])=False THEN sql = sql & " AND (qryRecipe.t_cookbook.name Like '*" & [Forms]![frmSearch]![cookbook] & "*')"
...

 

[ClaraBarton]

Thank you for your help. This is actually a a well tested query. No way I would write that mess from scratch. It's a search form that searches only on filled blanks and works very well. I forgot about the double quotes.