Question with Issue Database template.

J

John W. Vinson

How to do queries with all issues with Company name?
Click to expand...

By putting a criterion on the appropriate field.

Please post some more information; we cannot see your database. Do you have a
Text field for Company Name? What do you mean by "all issues" - that is not
an Access term.

John W. Vinson [MVP]
 
L

Larry Daugherty

Don't assume that everyone here has downloaded the same templates from
MS that you have, nor that they are willing to do so in order to
understand your issue.

In order to get relevant technical help you need to explain your issue
in technical terms from the details that are there in front of you.

HTH
--
-Larry-
--

yen said:
I download Access 2003 "Issues Database" template from
http://office.microsoft.com/en-us/templates/CT101426031033.aspx
It has "Search Issues" Form for All open issues But I want to create "Search
Issues" for "all issues" with search by Company name instead of "Assigned By"
or "Opened By". I'm very appreciate your help.

Click to expand...
 
J

John W. Vinson

I download Access 2003 "Issues Database" template from
http://office.microsoft.com/en-us/templates/CT101426031033.aspx
Click to expand...

I haven't.
It has "Search Issues" Form for All open issues But I want to create "Search
Issues" for "all issues" with search by Company name instead of "Assigned By"
or "Opened By". I'm very appreciate your help.
Click to expand...

Please tell us the Recordsource of the form, and post the VBA code for the
"Search Issues" button - or describe how the Search Issues operation works.

Perhaps you aren't aware that the people who answer questions here do NOT work
for Microsoft. We're all volunteers, donating our time to help folks. As such,
we don't automatically have all of Microsoft's templates... nor do we
necessarily have time to download files and do research for you. We'll be glad
to help you - but you will need to help us to do so.

John W. Vinson [MVP]
 
Y

yen

Larry & John:
Thanks for reply and very appreciate that you're trying to help me but I'm
new to Access and just self study basic of Access database last quarter. I
don't have enough technical knowledge of this downloaded database. I don't
even know how they added Contacts_Opened By and Contacts_Assigned By in Open
Issues Queries. I thought this is MS forum and MS T/S could help. Do you
know how can I get help with MS T/S?

Larry Daugherty said:
John, You said that so much better than I did!
Click to expand...
 
J

John W. Vinson

Larry & John:
Thanks for reply and very appreciate that you're trying to help me but I'm
new to Access and just self study basic of Access database last quarter. I
don't have enough technical knowledge of this downloaded database. I don't
even know how they added Contacts_Opened By and Contacts_Assigned By in Open
Issues Queries. I thought this is MS forum and MS T/S could help. Do you
know how can I get help with MS T/S?
Click to expand...

Well... MS T/S is NOT free; you'll need to pay an incident charge. It's really
designed for reporting program bugs, not for tutorials in how to use the
program. You can get to them from Microsoft's website, if that's what you
want.

We'll be glad to help, free of charge. But it's a cooperative process! If
you'll help us, we'll help you, and we'll both benefit (and even lurkers
reading the newsgroups may pick up something).

If there is a Query named [Open Issues Queries] in the Issues database, open
the Query in design view. Select View... SQL. Copy and paste that SQL text to
a reply in this thread, and describe what it's doing that you don't want, or
not doing that you do want.

John W. Vinson [MVP]
 
Y

yen

Thank you John for being so kind. Here's SQL for Open Issue Queries.
----------------------------------------------------------------------------------------------
SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " &
[Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened
By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By
Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date],
[Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail],
[Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail],
Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To],
Issues.[Opened By]
FROM Contacts AS [Contacts_Opened By] INNER JOIN (Contacts AS
[Contacts_Assigned To] INNER JOIN Issues ON [Contacts_Assigned To].ID =
Issues.[Assigned To]) ON [Contacts_Opened By].ID = Issues.[Opened By]
WHERE (((Issues.Status)<>"Closed"))
-------------------------------------------------------------------------------------
And below's VB code for searching with "Opened by" or "Assigned To" from
Open issues Queries from Search issue Form.

question#1. I wonder how can I do search with Company name instead
question#2. If possible I like to do search with Company name with "All
issues" instead of just "open issues".

I'm very appreciate your help. Thanks
-------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Private Sub Clear_Click()
DoCmd.Close
DoCmd.OpenForm "Search Issues"
End Sub

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Assigned To] = " &
Me.AssignedTo & ""
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[Opened By] = " &
Me.OpenedBy & ""
End If

' If Status
If Nz(Me.Status) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Status = '" & Me.Status & "'"
End If

' If Category
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category
& "'"
End If

' If Priority
If Nz(Me.Priority) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority
& "'"
End If

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " &
GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Opened Date To
If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " &
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Due Date From
If IsDate(Me.DueDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] >= " &
GetDateFilter(Me.DueDateFrom)
ElseIf Nz(Me.DueDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Due Date To
If IsDate(Me.DueDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] <= " &
GetDateFilter(Me.DueDateTo)
ElseIf Nz(Me.DueDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title &
"*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True
End If
End Sub

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function
---------------------------------------------------------------------------------------

John W. Vinson said:
Larry & John:
Thanks for reply and very appreciate that you're trying to help me but I'm
new to Access and just self study basic of Access database last quarter. I
don't have enough technical knowledge of this downloaded database. I don't
even know how they added Contacts_Opened By and Contacts_Assigned By in Open
Issues Queries. I thought this is MS forum and MS T/S could help. Do you
know how can I get help with MS T/S?
Click to expand...

Well... MS T/S is NOT free; you'll need to pay an incident charge. It's really
designed for reporting program bugs, not for tutorials in how to use the
program. You can get to them from Microsoft's website, if that's what you
want.

We'll be glad to help, free of charge. But it's a cooperative process! If
you'll help us, we'll help you, and we'll both benefit (and even lurkers
reading the newsgroups may pick up something).

If there is a Query named [Open Issues Queries] in the Issues database, open
the Query in design view. Select View... SQL. Copy and paste that SQL text to
a reply in this thread, and describe what it's doing that you don't want, or
not doing that you do want.

John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

Thank you John for being so kind. Here's SQL for Open Issue Queries.
----------------------------------------------------------------------------------------------
SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " &
[Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened
By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By
Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date],
[Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail],
[Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail],
Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To],
Issues.[Opened By]
FROM Contacts AS [Contacts_Opened By] INNER JOIN (Contacts AS
[Contacts_Assigned To] INNER JOIN Issues ON [Contacts_Assigned To].ID =
Issues.[Assigned To]) ON [Contacts_Opened By].ID = Issues.[Opened By]
WHERE (((Issues.Status)<>"Closed"));
-------------------------------------------------------------------------------------
And below's VB code for searching with "Opened by" or "Assigned To" from
Open issues Queries from Search issue Form.

question#1. I wonder how can I do search with Company name instead
Click to expand...

Probably, but I don't know where in the database the Company name is stored.
It's not in this query. Is there a Companies table? If so how is it related to
the Issues table or the Contacts table?
question#2. If possible I like to do search with Company name with "All
issues" instead of just "open issues".
Click to expand...

To include closed issues in the search, simply remove the portion

WHERE (((Issues.Status)<>"Closed"))

from the query above. I presume - well, guess - that the [Open Issue Query]
above is the Recordsource for your search form. If it's not, please post the
SQL of that query too.


John W. Vinson [MVP]
 
Y

yen

John,
Thanks so much for reply. There's only SQL for Open Issues which I already
posted. There are contacts and issues table. Company name is in Contacts
table. Looking at Open issues SQL. I don't know how they added
Contacts_Assigned To and Contacts_Opened By tables ... I guess I need another
Queries with Contacts_Company tables but couldn't figure out yet.

John W. Vinson said:
Thank you John for being so kind. Here's SQL for Open Issue Queries.
----------------------------------------------------------------------------------------------
SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " &
[Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened
By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By
Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date],
[Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail],
[Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail],
Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To],
Issues.[Opened By]
FROM Contacts AS [Contacts_Opened By] INNER JOIN (Contacts AS
[Contacts_Assigned To] INNER JOIN Issues ON [Contacts_Assigned To].ID =
Issues.[Assigned To]) ON [Contacts_Opened By].ID = Issues.[Opened By]
WHERE (((Issues.Status)<>"Closed"));
-------------------------------------------------------------------------------------
And below's VB code for searching with "Opened by" or "Assigned To" from
Open issues Queries from Search issue Form.

question#1. I wonder how can I do search with Company name instead
Click to expand...

Probably, but I don't know where in the database the Company name is stored.
It's not in this query. Is there a Companies table? If so how is it related to
the Issues table or the Contacts table?
question#2. If possible I like to do search with Company name with "All
issues" instead of just "open issues".
Click to expand...

To include closed issues in the search, simply remove the portion

WHERE (((Issues.Status)<>"Closed"))

from the query above. I presume - well, guess - that the [Open Issue Query]
above is the Recordsource for your search form. If it's not, please post the
SQL of that query too.


John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

John,
Thanks so much for reply. There's only SQL for Open Issues which I already
posted. There are contacts and issues table. Company name is in Contacts
table. Looking at Open issues SQL. I don't know how they added
Contacts_Assigned To and Contacts_Opened By tables ... I guess I need another
Queries with Contacts_Company tables but couldn't figure out yet.

John W. Vinson said:
Thank you John for being so kind. Here's SQL for Open Issue Queries.
----------------------------------------------------------------------------------------------
SELECT Issues.ID, Issues.Title, [Contacts_Assigned to].[First Name] & " " &
[Contacts_Assigned To].[Last Name] AS [Assigned To Name], [Contacts_Opened
By].[First Name] & " " & [Contacts_Opened By].[Last Name] AS [Opened By
Name], Issues.[Opened Date], Issues.Comment, Issues.[Due Date],
[Contacts_Opened By].[E-mail Address] AS [Opened By E-Mail],
[Contacts_Assigned To].[E-mail Address] AS [Assigned To E-Mail],
Issues.Status, Issues.Category, Issues.Priority, Issues.[Assigned To],
Issues.[Opened By]
FROM Contacts AS [Contacts_Opened By] INNER JOIN (Contacts AS
Click to expand...
Click to expand...
Click to expand...

Here's how they created the Contacts_Opened By alias for the Contacts table.
[Contacts_Assigned To] INNER JOIN Issues ON [Contacts_Assigned To].ID =
Click to expand...
Click to expand...
Click to expand...

And here's how they created the Contacts_Assigned To alias for the Contacts
table.
Issues.[Assigned To]) ON [Contacts_Opened By].ID = Issues.[Opened By]
WHERE (((Issues.Status)<>"Closed"));
-------------------------------------------------------------------------------------
Click to expand...
Click to expand...
Click to expand...

You can include

[Contacts_Assigned To].[Company]

in your query by simply selecting the Company field from that copy of the
Contacts table in the query grid. You can use it like any other field; search
by it, include it in the code, whatever you want to do with it, just like the
Category or Opened Date field.

John W. Vinson [MVP]
 
Top