accessing columns in a listbox

[Dsperry101 via AccessMonster.com]

Hello,
I am working with Access 2000 . I have a listbox with 2 columns . The
first column is populated from a query. I want to take the values in the
first column and do a query with each item and enter the result in the second
column . The listbox is lstNames with 2 columns . How do I insert values in
the second column ??????

 

[Douglas J. Steele]

That sounds like a very unusual requirement.

Can you not just create a join (or joins) so that one query returns both
columns?

 

[Dsperry101 via AccessMonster.com]

The reason I was doing this was that Access doesn't have a count distinct. If
you could suggest a way to do this that would be great.

I have a work order database with table tblNewMwo. When an individual work
order is done a date complete is inserted into [Date_Complete] and an email
is sent to the [Requestor]. When the requestor responds to the email the
system puts the text "Signed off" into the field [Sign_Off_By_Request]. I
want to send an email to the complete but not signed off [Requestor].
I get a list of requestors with
SELECT DISTINCT tblNewMwo.Requestor AS cntrequests
FROM tblNewMwo
WHERE (((tblNewMwo.Date_Complete)<>"") AND ((tblNewMwo.Sign_Off_by_Request)
=""));

access 2000 doesn't have a select distinct but in the form I get each
distinct requestor and count the "unsigned off " with

DCount("[Requestor]", "tblNewMwo", "[Requestor] = '" & teststr & _
"' AND [Sign_Off_By_Request] LIKE '' AND [Date_Complete]
<> '' ")
teststr is the Requestors from the first query
Thanks ahead of reply

That sounds like a very unusual requirement.

Can you not just create a join (or joins) so that one query returns both
columns?

Hello,
I am working with Access 2000 . I have a listbox with 2 columns . The

[quoted text clipped - 4 lines]

in
the second column ??????

 

[jan]

Hello,
I am working with Access 2000 . I have a listbox with 2 columns . The
first column is populated from a query. I want to take the values in the
first column and do a query with each item and enter the result in the
second
column . The listbox is lstNames with 2 columns . How do I insert values
in
the second column ??????

 

[Douglas J. Steele]

SELECT Requestor, Count(*) AS cntrequests
FROM tblNewMwo
WHERE (Date_Complete<>"") AND (Sign_Off_by_Request ="")
GROUP BY Requestor


Just a couple of comments. It would appear that both Date_Complete and
SIgn_Off_by_Request fields are text fields with their Required property set
to True (and Allow Zero Length set to True). Personally, I think you'd be
far better off with Date_Complete being a Date/Time field, and both fields
having their Required property set to False so that they can contain Nulls
when there isn't a value.

You'd then use

SELECT Requestor, Count(*) AS cntrequests
FROM tblNewMwo
WHERE (Date_Complete IS NOT NULL) AND (Sign_Off_by_Request IS NULL)
GROUP BY Requestor

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The reason I was doing this was that Access doesn't have a count distinct.
If
you could suggest a way to do this that would be great.

I have a work order database with table tblNewMwo. When an individual work
order is done a date complete is inserted into [Date_Complete] and an
email
is sent to the [Requestor]. When the requestor responds to the email the
system puts the text "Signed off" into the field [Sign_Off_By_Request]. I
want to send an email to the complete but not signed off [Requestor].
I get a list of requestors with
SELECT DISTINCT tblNewMwo.Requestor AS cntrequests
FROM tblNewMwo
WHERE (((tblNewMwo.Date_Complete)<>"") AND
((tblNewMwo.Sign_Off_by_Request)
=""));

access 2000 doesn't have a select distinct but in the form I get each
distinct requestor and count the "unsigned off " with

DCount("[Requestor]", "tblNewMwo", "[Requestor] = '" & teststr & _
"' AND [Sign_Off_By_Request] LIKE '' AND
[Date_Complete]
<> '' ")
teststr is the Requestors from the first query
Thanks ahead of reply

That sounds like a very unusual requirement.

Can you not just create a join (or joins) so that one query returns both
columns?

Hello,
I am working with Access 2000 . I have a listbox with 2 columns .
The

[quoted text clipped - 4 lines]

in
the second column ??????

 

[Dsperry101 via AccessMonster.com]

Doug,

Thanks ! The query worked great .

SELECT Requestor, Count(*) AS cntrequests
FROM tblNewMwo
WHERE (Date_Complete<>"") AND (Sign_Off_by_Request ="")
GROUP BY Requestor

Just a couple of comments. It would appear that both Date_Complete and
SIgn_Off_by_Request fields are text fields with their Required property set
to True (and Allow Zero Length set to True). Personally, I think you'd be
far better off with Date_Complete being a Date/Time field, and both fields
having their Required property set to False so that they can contain Nulls
when there isn't a value.

You'd then use

SELECT Requestor, Count(*) AS cntrequests
FROM tblNewMwo
WHERE (Date_Complete IS NOT NULL) AND (Sign_Off_by_Request IS NULL)
GROUP BY Requestor

The reason I was doing this was that Access doesn't have a count distinct.
If

[quoted text clipped - 34 lines]