MS Access database support - Query with user-defined function doesn't appear to work.

Author

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

teddysnips@hotmail.com

2006-03-06, 7:28 pm

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!lstAvailableCompa
nies.Column(0)

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

' Strip off trailing comma
SelectedCompanyIDs = Left(SelectedCompany
IDs, Len(SelectedCompanyI
Ds) -
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 = " qryGetContactsForSin
gleCompany"

Me!lstCandidateConta
cts.RowSource = " qryGetSelectedContac
ts"

End Sub

The first query works fine:

SELECT & #91;dbo_tblContacts]
.[fldContactID], Nz([fldTitle])+'
'+Nz([fldInitials])+' '+Nz(& #91;fldFirstName])+'
'+Nz([fldSurname]) AS
FullName
FROM dbo_tblContacts
WHERE fldCompanyID=GetSele
ctedCompany();

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

The second query doesn't work at all:

SELECT & #91;dbo_tblContacts]
.[fldContactID], Nz([fldTitle])+'
'+Nz([fldInitials])+' '+Nz(& #91;fldFirstName])+'
'+Nz([fldSurname]) AS
FullName
FROM dbo_tblContacts
WHERE fldCompanyID IN (GetSelectedCompanie
s());

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 & #91;dbo_tblContacts]
.[fldContactID], Nz([fldTitle])+'
'+Nz([fldInitials])+' '+Nz(& #91;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

MGFoster

2006-03-06, 7:28 pm

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

The use of

WHERE fldCompanyID IN (GetSelectedCompanie
s())

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/ AwUBRANyCYechKqOuFEg
EQL+7QCbBmAR2k4kHxvG
3GXAZvpk2ktH0WMAoL1a

5GkXRThrifFNHtdKlYSf
YNkF
=M8Wi
-----END PGP SIGNATURE-----

teddysnips@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!lstAvailableCompa
nies.Column(0)
>
> ' Get all the "Candidate" companies
> SelectedCompanyIDs = ""
> For Each var In Me!lstSelectedCompan
ies.ItemsSelected
> SelectedCompanyIDs = SelectedCompanyIDs &
> Me!lstSelectedCompan
ies.Column(0, var) & ","
> Next
>
> ' Strip off trailing comma
> SelectedCompanyIDs = Left(SelectedCompany
IDs, Len(SelectedCompanyI
Ds) -
> 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 = " qryGetContactsForSin
gleCompany"
>
> Me!lstCandidateConta
cts.RowSource = " qryGetSelectedContac
ts"
>
> End Sub
>
> The first query works fine:
>
> SELECT & #91;dbo_tblContacts]
.[fldContactID], Nz([fldTitle])+'
> '+Nz([fldInitials])+' '+Nz(& #91;fldFirstName])+'
'+Nz([fldSurname]) AS
> FullName
> FROM dbo_tblContacts
> WHERE fldCompanyID=GetSele
ctedCompany();
>
> As you can see, it calls the function "GetSelectedCompany" (see below)
>
> The second query doesn't work at all:
>
> SELECT & #91;dbo_tblContacts]
.[fldContactID], Nz([fldTitle])+'
> '+Nz([fldInitials])+' '+Nz(& #91;fldFirstName])+'
'+Nz([fldSurname]) AS
> FullName
> FROM dbo_tblContacts
> WHERE fldCompanyID IN (GetSelectedCompanie
s());
>
> 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 & #91;dbo_tblContacts]
.[fldContactID], Nz([fldTitle])+'
> '+Nz([fldInitials])+' '+Nz(& #91;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
>

teddysnips@hotmail.com

2006-03-06, 7:29 pm

MGFoster wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> The use of
>
> WHERE fldCompanyID IN (GetSelectedCompanie
s())
>
> 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

ChasW

2006-04-08, 7:27 am

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 (GetSelectedCompanie
s())
>
>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

ChasW

2006-04-08, 7:27 am

On 28 Feb 2006 00:10:43 -0800, teddysnips@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

Bob Quintal

2006-04-08, 11:28 am

ChasW <> wrote in
news:pp9f3210f7rk5k6
am95f996c1c48rbtluv@
4ax.com:

> On Mon, 27 Feb 2006 21:41:24 GMT, MGFoster <me@privacy.com>
> wrote:
>

Yes, but the string itself is 'a', 'b', 'c', 'd'[color=darkred]

which is what we got from the function.
[color=darkred]

with the four separate values embedded in it.
[color=darkred]
> 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.

Sponsored Links