VBA Variable from Function as Query Criteria - Please put me out of my misery!

sistemalan

Registered User.
Local time
Today, 20:55
Joined
Jun 19, 2009
Messages
77
Hi and thanks for taking the time to read,

I have read lots of posts and advice about this subject but I still haven't managed to crack it. This is where I have got to:

I have managed to create code that turns selections from a listBox into a string. The listBox contains a list of colours. The result is a string called mergeQueryCriteria. When this code has run I put in the line MsgBox mergeQueryCritera and get a result such as
, or in the case of multi-selections
"Blue" OR "Purple" OR "Orange"
.

I have learned that I need to call a function from the query, not the variable directly and so I have created the following function at the top of a standard module:

Code:
Function mergeQC() As String

mergeQC = mergeQueryCriteria

End Function
I know that this works correctly as if I MsgBox mergeQC() I get the same result as mergeQueryCriteria

I have the following query:

Code:
SELECT T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.Address3, T_Adults.Address4, T_Adults.PostCode, Q_SiblingNamesTest.ShortNames, T_Children.Orchestra
FROM (T_Adults LEFT JOIN Q_SiblingNamesTest ON T_Adults.AdultID=Q_SiblingNamesTest.AdultID) RIGHT JOIN (Q_RemoveTwins INNER JOIN T_Children ON (Q_RemoveTwins.GuardianID=T_Children.GuardianID) AND (Q_RemoveTwins.MinOfFirstName=T_Children.FirstName)) ON T_Adults.AdultID=T_Children.GuardianID
WHERE Orchestra=mergeQC();
This returns no results. However if I replace mergeQC() which I know to be a working function with the exact string that I know it contains, it works perfectly. ie:

Code:
SELECT T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.Address3, T_Adults.Address4, T_Adults.PostCode, Q_SiblingNamesTest.ShortNames, T_Children.Orchestra
FROM (T_Adults LEFT JOIN Q_SiblingNamesTest ON T_Adults.AdultID=Q_SiblingNamesTest.AdultID) RIGHT JOIN (Q_RemoveTwins INNER JOIN T_Children ON (Q_RemoveTwins.GuardianID=T_Children.GuardianID) AND (Q_RemoveTwins.MinOfFirstName=T_Children.FirstName)) ON T_Adults.AdultID=T_Children.GuardianID
WHERE Orchestra="Blue" OR "Purple" OR "Orange";
A great deal of thanks to anyone who can point out what I'm missing here. I am developing this for the charity I work for and could really use a hand! (It's so close to being done!!!)

In case it's of any use, the VBA which creates the mergeQueryCriteria string is as follows:

Code:
If Me!OrchestraList.ItemsSelected.Count > 0 Then
        For Each varItem In Me!OrchestraList.ItemsSelected
          mergeQueryCriteria = mergeQueryCriteria & Chr(34) & Me!OrchestraList.ItemData(varItem) & Chr(34) & " OR "
        Next varItem
            mergeQueryCriteria = Left(mergeQueryCriteria, Len(mergeQueryCriteria) - 4)
       End If
Alan
 
*fires cannon at now headless alan*

There put out of your misery...


WHERE Orchestra="Blue" OR "Purple" OR "Orange";
is not the same as:
WHERE Orchestra=mergeQC();

MergeQC returns a text string that contains "Blue" OR "Purple" OR "Orange"
the result will thus be:
WHERE Orchestra="""Blue"" OR ""Purple"" OR ""Orange""";

With rather obvious results
 
Hi NamLiam,

Was cannon the most humane way you could think of? Whatever happened to lethal injection?

Thanks for pointing that out. Following some advice on another forum I have modified my code as follows:

Code:
mergeQueryCriteria = "(("
    If Me!OrchestraList.ItemsSelected.Count > 0 Then
        For Each varItem In Me!OrchestraList.ItemsSelected
             mergeQueryCriteria = mergeQueryCriteria & "(T_Children.Orchestra)=" & Chr(34) & Me!OrchestraList.ItemData(varItem) & Chr(34) & " OR "
        Next varItem
        mergeQueryCriteria = Left(mergeQueryCriteria, Len(mergeQueryCriteria) - 4)
        mergeQueryCriteria = mergeQueryCriteria & "))"
     End If

And changed the SQL to read:

Code:
SELECT T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.Address3, T_Adults.Address4, T_Adults.PostCode, Q_SiblingNamesTest.ShortNames, T_Children.Orchestra
FROM (T_Adults LEFT JOIN Q_SiblingNamesTest ON T_Adults.AdultID=Q_SiblingNamesTest.AdultID) RIGHT JOIN (Q_RemoveTwins INNER JOIN T_Children ON (Q_RemoveTwins.MinOfFirstName=T_Children.FirstName) AND (Q_RemoveTwins.GuardianID=T_Children.GuardianID)) ON T_Adults.AdultID=T_Children.GuardianID
WHERE mergeQC();

This is getting closer, but it does not filter out any colours not selected. Instead these are returned as NULL values.

Any more thoughts?
 
Hi NamLiam,

Was cannon the most humane way you could think of? Whatever happened to lethal injection?
(a big enough) Cannon I can fire from my desk, Injection I have to come all the way TO you :(
Just me being lazy

Thanks for pointing that out. Following some advice on another forum I have modified my code as follows:

WHERE mergeQC();

MergeQC still returns a text string thus:

WHERE "Orchestra=""Blue"" OR ""Purple"" OR ""Orange""";

to fix this try creating the sql in code all together:
Code:
mySQL = ""
mySQL = mySQL & " Select .... "
mySQL = mySQL & " FROM .... "
mySQL = mySQL & " where " & mergeQC
Debug.print mySQL
Currentdb.querydefs("YourQuery").sql = mySQL

This will work.... or should :P, its air code, so dont *shoot* me if it dont
 
This will work.... or should :P, its air code, so dont *shoot* me if it dont

I'm unlikely to shoot you as I've already been decapitated and cannoned!

To fix this try creating the sql in code all together:

OK, I think I can do that. The next thing that happens in the code is that this query gets used to perform a mail merge using Albert Kallan's Super Easy Word Merge code (http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html)

Having played about with this a bit I got it to work with my query by first creating a temporary table and then running the mail merge code using that. This is my code:

Code:
Dim strSourceQuery As String
    Dim strDestTable As String
    
    strSourceQuery = "Q_MailMergeTest"
    strDestTable = "T_MailMergeTemp"
    
    DoCmd.TransferSpreadsheet acExport, 8, strSourceQuery, "c:\temp_table", True, ""
    DoCmd.TransferSpreadsheet acImport, 8, strDestTable, "c:\temp_table", True
    Kill "c:\temp_table.xls"
   
   MergeAllWord ("select * from T_MailMergeTemp"), "C:\WordTest\", True

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM T_MailMergeTemp"
DoCmd.SetWarnings True

Any chance you could help me understand how best to modify this code if I create the SQL in code as you suggest?
 
Woohoo it works.

Here's what I ended up with...

Code:
' Declare variables
    Dim db As DAO.Database
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    Dim mergeSQL As String
    
' Get the database and stored query
    Set db = CurrentDb()
    
'Reset mergeQueryCriteria to blank
   mergeQueryCriteria = ""
   
    ' Loop through the selected items in the list box and build two text strings
    mergeQueryCriteria = "(("
    If Me!OrchestraList.ItemsSelected.Count > 0 Then
        For Each varItem In Me!OrchestraList.ItemsSelected
            strCriteria = strCriteria & "'" & Me!OrchestraList.ItemData(varItem) & "',"
            mergeQueryCriteria = mergeQueryCriteria & "(T_Children.Orchestra)=" & Chr(34) & Me!OrchestraList.ItemData(varItem) & Chr(34) & " OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 1)
        mergeQueryCriteria = Left(mergeQueryCriteria, Len(mergeQueryCriteria) - 4)
        mergeQueryCriteria = mergeQueryCriteria & "))"
    Else
        strCriteria = "tblData.Region Like '*'"
    End If
    
  'Build the SQL string
  mergeSQL = ""
  mergeSQL = mergeSQL & "SELECT T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.Address3, T_Adults.Address4, T_Adults.PostCode, Q_SiblingNamesTest.ShortNames, T_Children.Orchestra FROM (T_Adults LEFT JOIN Q_SiblingNamesTest ON T_Adults.AdultID=Q_SiblingNamesTest.AdultID) RIGHT JOIN (Q_RemoveTwins INNER JOIN T_Children ON (Q_RemoveTwins.MinOfFirstName=T_Children.FirstName) AND (Q_RemoveTwins.GuardianID=T_Children.GuardianID)) ON T_Adults.AdultID=T_Children.GuardianID WHERE"
  mergeSQL = mergeSQL & mergeQueryCriteria & " Order By ShortNames Asc;"
  
' can't remember whether I use variable sqlStuff any more. Leave it in for now just to be safe
   sqlStuff = strCriteria
    
'Refresh some stuff
    Set db = Nothing
    Set qdf = Nothing
    Me.Refresh

'Perform Mailmerge to selected recipients using query defined above
   MergeAllWord (mergeSQL), "C:\WordTest\", True

Thanks for the help. Careful with that Canon, you could have someone's eye out!
 
It really pays to keep your code readable, ie:
Code:
  mergeSQL = ""
  mergeSQL = mergeSQL & "SELECT T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.Address3, T_Adults.Address4, T_Adults.PostCode, Q_SiblingNamesTest.ShortNames, T_Children.Orchestra FROM (T_Adults LEFT JOIN Q_SiblingNamesTest ON T_Adults.AdultID=Q_SiblingNamesTest.AdultID) RIGHT JOIN (Q_RemoveTwins INNER JOIN T_Children ON (Q_RemoveTwins.MinOfFirstName=T_Children.FirstName) AND (Q_RemoveTwins.GuardianID=T_Children.GuardianID)) ON T_Adults.AdultID=T_Children.GuardianID WHERE"
  mergeSQL = mergeSQL & mergeQueryCriteria & " Order By ShortNames Asc;"
is not readable...

Instead something like:
Code:
  mergeSQL = ""
  mergeSQL = mergeSQL & " SELECT T_Adults.AdultName, T_Adults.Address1, T_Adults.Address2, T_Adults.Address3, T_Adults.Address4, T_Adults.PostCode, Q_SiblingNamesTest.ShortNames, T_Children.Orchestra "
  mergeSQL = mergeSQL & " FROM (T_Adults "
  mergeSQL = mergeSQL & " LEFT JOIN Q_SiblingNamesTest ON T_Adults.AdultID=Q_SiblingNamesTest.AdultID) "
  mergeSQL = mergeSQL & " RIGHT JOIN (Q_RemoveTwins "
  mergeSQL = mergeSQL & " INNER JOIN T_Children ON (Q_RemoveTwins.MinOfFirstName=T_Children.FirstName) AND (Q_RemoveTwins.GuardianID=T_Children.GuardianID)) ON T_Adults.AdultID=T_Children.GuardianID "
  mergeSQL = mergeSQL & " WHERE " & mergeQueryCriteria 
  mergeSQL = mergeSQL & " Order By ShortNames Asc;"
adds a lot of readability though done quickly and can be done EVEN better

Well done on getting it to work though :)
 

Users who are viewing this thread

Back
Top Bottom