PeterBaileyUk
asked on
syntax in dynamic sql access query in vba
I am building up the having clause with:
which produces this:
HAVING (((TblReverseFilter.StrOri ginal) Like ("*bus*") And (TblReverseFilter.StrOrigi nal) Like ("*med*") And (TblReverseFilter.StrOrigi nal) Like ("*prof*")));
it wont run the query, how do I create the syntax like this:
HAVING (((TblReverseFilter.StrOri ginal) like (*bus*) AND like (*med*) AND like (*prof*)));
which was taken from a handmade query in the grid that does work
Do Until .EOF
If start = True Then
FilterWord = " like (*" & .Fields("FilterWord").Value & "*)"
start = False
Else
FilterWord = FilterWord & " AND like (*" & .Fields("FilterWord").Value & "*)"
End If
.MoveNext
Loop
which produces this:
HAVING (((TblReverseFilter.StrOri
it wont run the query, how do I create the syntax like this:
HAVING (((TblReverseFilter.StrOri
which was taken from a handmade query in the grid that does work
Can you post the whole routine please.
ASKER
Dim SelectFinal As String
Dim FromFinal As String
Dim GroupFinal As String
Dim HavingFinal As String
Dim QryFinal As String
SelectFinal = "INSERT INTO TblReverseFiltTemp ( StrOriginal ) SELECT TblReverseFilter.StrOriginal"
FromFinal = " FROM TblReverseFilter"
GroupFinal = " GROUP BY TblReverseFilter.StrOriginal, TblReverseFilter.LngScore"
HavingFinal = " HAVING (((TblReverseFilter.StrOriginal) " & FilterWord & " ));"
QryFinal = SelectFinal & FromFinal & GroupFinal & HavingFinal
Debug.Print "QryFinal: " & QryFinal
DoCmd.RunSQL QryFinal
as requested
I didnt post as its the having causing the problem
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The syntax was created by access I merely took the sql from the query and wanted to make it dynamic i selected group by as the query would otherwise have created duplicates.
SELECT TblReverseFilter.StrOriginal
FROM TblReverseFilter
GROUP BY TblReverseFilter.StrOriginal, TblReverseFilter.LngScore
HAVING (((TblReverseFilter.StrOriginal) Like ("*bus*") And (TblReverseFilter.StrOriginal) Like ("*med*") And (TblReverseFilter.StrOriginal) Like ("*prof*")));
ASKER
I see finally what you meant code is now corrected and working.
thank you
thank you