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
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:
I know that this works correctly as if I MsgBox mergeQC() I get the same result as mergeQueryCriteria
I have the following query:
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:
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:
Alan
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"Orange"
."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 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();
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";
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