By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,210 Members | 1,499 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,210 IT Pros & Developers. It's quick & easy.

Query with user-defined function doesn't appear to work.

P: n/a
I have to write an application to do some data cleansing. It's a
Contact database, but over a number of years there are multiple
companies which are all essentially the same entity. For each company
there are also multiple contacts, some of which may be congruent.

It's a very simple app. with only two forms. The first form shows two
lists. The left-hand list is a Single-Select showing all the companies.
The user chooses one company - the "Chosen" company. The right-hand
list is Multi-Select, again showing all companies. The user selects
all the candidates from this that are reckoned to be the same company
as the company chosen from the left-hand list. These are the
"Candidates".

The "Next >>" button takes you to another form with two lists. This is
the code that fires at this point:

Private Sub cmdNext_Click()

Dim i As Integer
Dim var As Variant

' Set a global value for the "Chosen" company
FirstCompanyID = Me!lstAvailableCompanies.Column(0)

' Get all the "Candidate" companies
SelectedCompanyIDs = ""
For Each var In Me!lstSelectedCompanies.ItemsSelected
SelectedCompanyIDs = SelectedCompanyIDs &
Me!lstSelectedCompanies.Column(0, var) & ","
Next

' Strip off trailing comma
SelectedCompanyIDs = Left(SelectedCompanyIDs, Len(SelectedCompanyIDs) -
1)

Forms("frmCompanies").Visible = False
DoCmd.OpenForm ("frmContacts")
End Sub

The left-hand list on the new form (frmContacts) shows all the Contacts
at the "Chosen" company. The right-hand list shows all the Contacts at
the various "Candidates". The user can select a single Contact from
the "Chosen" list, one or more from the "Candidates" list, then press a
button and the system will update all related records to reference the
"Chosen" Contact, and then delete all the "Candidate" contacts also
chosen. Etcetera.

My problem is the query that populates the two lists on the frmContacts
form. Here's the Form_Load event

Private Sub Form_Load()

Me!lstChosenContacts.RowSource = "qryGetContactsForSingleCompany"

Me!lstCandidateContacts.RowSource = "qryGetSelectedContacts"

End Sub

The first query works fine:

SELECT [dbo_tblContacts].[fldContactID], Nz([fldTitle])+'
'+Nz([fldInitials])+' '+Nz([fldFirstName])+' '+Nz([fldSurname]) AS
FullName
FROM dbo_tblContacts
WHERE fldCompanyID=GetSelectedCompany();

As you can see, it calls the function "GetSelectedCompany" (see below)

The second query doesn't work at all:

SELECT [dbo_tblContacts].[fldContactID], Nz([fldTitle])+'
'+Nz([fldInitials])+' '+Nz([fldFirstName])+' '+Nz([fldSurname]) AS
FullName
FROM dbo_tblContacts
WHERE fldCompanyID IN (GetSelectedCompanies());

It also calls a function GetSelectedCompanies (see also below).

However, it doesn't return any rows. If I put a breakpoint in the
function GetSelectedCompanies and retrieve the string
SelectedCompanyIDs I can paste this into the Query Builder thus:

SELECT [dbo_tblContacts].[fldContactID], Nz([fldTitle])+'
'+Nz([fldInitials])+' '+Nz([fldFirstName])+' '+Nz([fldSurname]) AS
FullName
FROM dbo_tblContacts
WHERE fldCompanyID IN
({F7046A04-4BC0-4172-BDEA-595A82468479},{C63AA40B-E60E-4DB3-82DE-3CE9358C4921},{4BE3568C-84CE-4FDD-8D74-B32F0C1A3766});

This works fine - returns the rows that I would expect.

Anyone any ideas?

TIA

Edward

Option Compare Database

Global FirstCompanyID As Variant
Global FirstCompanyName As Variant
Global SelectedCompanyIDs As String
Public Function GetSelectedCompany() As Variant

GetSelectedCompany = FirstCompanyID

End Function
Public Function GetSelectedCompanies() As Variant

GetSelectedCompanies = SelectedCompanyIDs

End Function

Feb 27 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The use of

WHERE fldCompanyID IN (GetSelectedCompanies())

doesn't work 'cuz the function GetSelectedCompanies() returns one value
like this (I'm using strings for clarification):

"'a','b','c','d'"

The IN () predicate requires separate values like this:

'a', 'b', 'c', 'd'

The above example shows FOUR separate values. Your function is
returning ONE value. Using your function, the query is looking for
records that have the value "'a', 'b', 'c', 'd'" instead of looking for
records that have an 'a' value or a 'b' value or a 'c' value or a 'd'
value.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRANyCYechKqOuFEgEQL+7QCbBmAR2k4kHxvG3GXAZvpk2k tH0WMAoL1a
5GkXRThrifFNHtdKlYSfYNkF
=M8Wi
-----END PGP SIGNATURE-----

te********@hotmail.com wrote:
I have to write an application to do some data cleansing. It's a
Contact database, but over a number of years there are multiple
companies which are all essentially the same entity. For each company
there are also multiple contacts, some of which may be congruent.

It's a very simple app. with only two forms. The first form shows two
lists. The left-hand list is a Single-Select showing all the companies.
The user chooses one company - the "Chosen" company. The right-hand
list is Multi-Select, again showing all companies. The user selects
all the candidates from this that are reckoned to be the same company
as the company chosen from the left-hand list. These are the
"Candidates".

The "Next >>" button takes you to another form with two lists. This is
the code that fires at this point:

Private Sub cmdNext_Click()

Dim i As Integer
Dim var As Variant

' Set a global value for the "Chosen" company
FirstCompanyID = Me!lstAvailableCompanies.Column(0)

' Get all the "Candidate" companies
SelectedCompanyIDs = ""
For Each var In Me!lstSelectedCompanies.ItemsSelected
SelectedCompanyIDs = SelectedCompanyIDs &
Me!lstSelectedCompanies.Column(0, var) & ","
Next

' Strip off trailing comma
SelectedCompanyIDs = Left(SelectedCompanyIDs, Len(SelectedCompanyIDs) -
1)

Forms("frmCompanies").Visible = False
DoCmd.OpenForm ("frmContacts")
End Sub

The left-hand list on the new form (frmContacts) shows all the Contacts
at the "Chosen" company. The right-hand list shows all the Contacts at
the various "Candidates". The user can select a single Contact from
the "Chosen" list, one or more from the "Candidates" list, then press a
button and the system will update all related records to reference the
"Chosen" Contact, and then delete all the "Candidate" contacts also
chosen. Etcetera.

My problem is the query that populates the two lists on the frmContacts
form. Here's the Form_Load event

Private Sub Form_Load()

Me!lstChosenContacts.RowSource = "qryGetContactsForSingleCompany"

Me!lstCandidateContacts.RowSource = "qryGetSelectedContacts"

End Sub

The first query works fine:

SELECT [dbo_tblContacts].[fldContactID], Nz([fldTitle])+'
'+Nz([fldInitials])+' '+Nz([fldFirstName])+' '+Nz([fldSurname]) AS
FullName
FROM dbo_tblContacts
WHERE fldCompanyID=GetSelectedCompany();

As you can see, it calls the function "GetSelectedCompany" (see below)

The second query doesn't work at all:

SELECT [dbo_tblContacts].[fldContactID], Nz([fldTitle])+'
'+Nz([fldInitials])+' '+Nz([fldFirstName])+' '+Nz([fldSurname]) AS
FullName
FROM dbo_tblContacts
WHERE fldCompanyID IN (GetSelectedCompanies());

It also calls a function GetSelectedCompanies (see also below).

However, it doesn't return any rows. If I put a breakpoint in the
function GetSelectedCompanies and retrieve the string
SelectedCompanyIDs I can paste this into the Query Builder thus:

SELECT [dbo_tblContacts].[fldContactID], Nz([fldTitle])+'
'+Nz([fldInitials])+' '+Nz([fldFirstName])+' '+Nz([fldSurname]) AS
FullName
FROM dbo_tblContacts
WHERE fldCompanyID IN
({F7046A04-4BC0-4172-BDEA-595A82468479},{C63AA40B-E60E-4DB3-82DE-3CE9358C4921},{4BE3568C-84CE-4FDD-8D74-B32F0C1A3766});

This works fine - returns the rows that I would expect.

Anyone any ideas?

TIA

Edward

Option Compare Database

Global FirstCompanyID As Variant
Global FirstCompanyName As Variant
Global SelectedCompanyIDs As String
Public Function GetSelectedCompany() As Variant

GetSelectedCompany = FirstCompanyID

End Function
Public Function GetSelectedCompanies() As Variant

GetSelectedCompanies = SelectedCompanyIDs

End Function

Feb 27 '06 #2

P: n/a

MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The use of

WHERE fldCompanyID IN (GetSelectedCompanies())

doesn't work 'cuz the function GetSelectedCompanies() returns one value
like this (I'm using strings for clarification):

"'a','b','c','d'"

The IN () predicate requires separate values like this:

'a', 'b', 'c', 'd'

The above example shows FOUR separate values. Your function is
returning ONE value. Using your function, the query is looking for
records that have the value "'a', 'b', 'c', 'd'" instead of looking for
records that have an 'a' value or a 'b' value or a 'c' value or a 'd'
value.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)


Ah, of course (strikes head with flat of hand). I'll have to try to
think of a way to pass these discrete values via a function.....

Thanks for that.

Edward

Feb 28 '06 #3

P: n/a
On Mon, 27 Feb 2006 21:41:24 GMT, MGFoster <me@privacy.com> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The use of

WHERE fldCompanyID IN (GetSelectedCompanies())

doesn't work 'cuz the function GetSelectedCompanies() returns one value
like this (I'm using strings for clarification):

"'a','b','c','d'"

The IN () predicate requires separate values like this:

'a', 'b', 'c', 'd'

The above example shows FOUR separate values. Your function is
returning ONE value. Using your function, the query is looking for
records that have the value "'a', 'b', 'c', 'd'" instead of looking for
records that have an 'a' value or a 'b' value or a 'c' value or a 'd'
value.


I am experiencing this same thing. I would like to use IN with my
query but give it a function instead of values and have the function
return the separate values.

Is this even possible? If not, what is the next best / closest thing?
Chas
Apr 8 '06 #4

P: n/a
On 28 Feb 2006 00:10:43 -0800, te********@hotmail.com wrote:

Ah, of course (strikes head with flat of hand). I'll have to try to
think of a way to pass these discrete values via a function.....

Thanks for that.

Edward


Hi,
Did you ever come across a solution for this? I am having the same
problem you originally posted about.

Thanks in advance,
Chas
Apr 8 '06 #5

P: n/a
ChasW <> wrote in
news:pp********************************@4ax.com:
On Mon, 27 Feb 2006 21:41:24 GMT, MGFoster <me@privacy.com>
wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The use of

WHERE fldCompanyID IN (GetSelectedCompanies())

doesn't work 'cuz the function GetSelectedCompanies() returns
one value like this (I'm using strings for clarification):

"'a','b','c','d'"
Yes, but the string itself is 'a', 'b', 'c', 'd'
The IN () predicate requires separate values like this:

'a', 'b', 'c', 'd'
which is what we got from the function.

The above example shows FOUR separate values. Your function
is returning ONE value.

with the four separate values embedded in it.
I am experiencing this same thing. I would like to use IN
with my query but give it a function instead of values and
have the function return the separate values.

Is this even possible? If not, what is the next best /
closest thing? Chas


yes it is.

I could not find the original post either on my newsserver or on
Google. (a pox on X-noarchive)

so try

Public Function makelist(ctlname As Control) As String
Dim varItem As Variant
For Each varItem In ctlname.ItemsSelected
makelist = makelist & ",'" _
& ctlname.ItemData(varItem) _
& "'"
Next varItem
makelist = Mid(makelist, 2)
End Function

--
Bob Quintal

PA is y I've altered my email address.
Apr 8 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.