Solved VBA adds spaces in SQL (1 Viewer)

ClaraBarton

Registered User.
Local time
Today, 09:45
Joined
Oct 14, 2019
Messages
427
Code:
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

Super Moderator
Staff member
Local time
Tomorrow, 03:45
Joined
Jan 20, 2009
Messages
12,849
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.

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:45
Joined
May 7, 2009
Messages
19,169
access don't need those extra space:
it will work mostly with Text fields (don't know about [createDate] (date field?))
Code:
& "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

Banishment Pending
Local time
Today, 11:45
Joined
May 11, 2011
Messages
11,611
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:

Code:
        & "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:


Code:
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

Registered User.
Local time
Today, 09:45
Joined
Oct 14, 2019
Messages
427
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.
 

Users who are viewing this thread

Top Bottom