Re: accessing columns in a listbox

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
http://I.Am/DougSteele
(no private e-mails, please)


"Dsperry101 via AccessMonster.com" <u24149@uwe> wrote in message
news:82396b67f3dbd@xxxxxx
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

Douglas J. Steele wrote:
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 ??????

--
Danny C. Sperry

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200804/1



.

Relevant Pages

  • Re: accessing columns in a listbox
    ... Douglas J. Steele wrote: ... FROM tblNewMwo ... SIgn_Off_by_Request fields are text fields with their Required property set ... SELECT Requestor, CountAS cntrequests ...
    (microsoft.public.access.forms)
  • Re: accessing columns in a listbox
    ... I have a work order database with table tblNewMwo. ... When the requestor responds to the email the ... teststr is the Requestors from the first query ... the second column ?????? ...
    (microsoft.public.access.forms)