accessing columns in a listbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dsperry101 - 05 Apr 2008 12:35 GMT

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 ??????

Signature

Danny C. Sperry

Reply to this Message

Douglas J. Steele - 05 Apr 2008 12:52 GMT

That sounds like a very unusual requirement.

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hello,
> I am working with Access 2000 . I have a listbox with 2 columns . The
[quoted text clipped - 4 lines]
> in
> the second column ??????

Reply to this Message

Dsperry101 - 05 Apr 2008 13:56 GMT

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.
>
[quoted text clipped - 6 lines]
>> in
>> the second column ??????

Signature

Danny C. Sperry

Reply to this Message

Douglas J. Steele - 05 Apr 2008 15:19 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> The reason I was doing this was that Access doesn't have a count distinct.
> If
[quoted text clipped - 34 lines]
>>> in
>>> the second column ??????

Reply to this Message

Dsperry101 - 07 Apr 2008 11:08 GMT

Doug,

Thanks ! The query worked great .

>SELECT Requestor, Count(*) AS cntrequests
>FROM tblNewMwo
[quoted text clipped - 20 lines]
>>>> in
>>>> the second column ??????

Signature

Danny C. Sperry

Reply to this Message

jan - 05 Apr 2008 15:04 GMT

> Hello,
> I am working with Access 2000 . I have a listbox with 2 columns . The
[quoted text clipped - 4 lines]
> in
> the second column ??????

Reply to this Message