Different criteria 2

[Eddyoftheyear]

I am working on a form that has 3 options to choose from before someone can run the form. it is a task form where you can specify the time frame you are looking for. e.g. all tasks due in a week.
all tasks due this month.
all tasks
tasks due today

what will be the best efficient way to do this?

The query for the month form will be:

SELECT Tbl_Organization.OrganizationName, tblActivities.actDescription, tblSchedule.schDate, Tbl_Staff.Name, tblSchedule.schSchID, tblSchedule.schNotes, Tbl_Projects.ProjectTypeID, Tbl_Projects.Description, Tbl_Projects.ProjectDate
FROM ((tblSchedule RIGHT JOIN (Tbl_Organization LEFT JOIN Tbl_Projects ON Tbl_Organization.OrganizationID = Tbl_Projects.OrganizationID) ON tblSchedule.ProjectID = Tbl_Projects.ProjectID) LEFT JOIN Tbl_Staff ON tblSchedule.schStaffID = Tbl_Staff.GCStaffID) LEFT JOIN tblActivities ON tblSchedule.schActID = tblActivities.actActID
WHERE (((Month([schDate]))=Month(Now())) AND ((tblSchedule.schStaffID)=[Forms]![Stafflookup_frm]![Combo0]));

 

[SkipVought]

Hi,

dim sSQL as string

ssql = "SELECT Tbl_Organization.OrganizationName, tblActivities.actDescription, tblSchedule.schDate, Tbl_Staff.Name, tblSchedule.schSchID, tblSchedule.schNotes, Tbl_Projects.ProjectTypeID, Tbl_Projects.Description, Tbl_Projects.ProjectDateFROM ((tblSchedule RIGHT JOIN (Tbl_Organization LEFT JOIN Tbl_Projects ON Tbl_Organization.OrganizationID = Tbl_Projects.OrganizationID) ON tblSchedule.ProjectID = Tbl_Projects.ProjectID) LEFT JOIN Tbl_Staff ON tblSchedule.schStaffID = Tbl_Staff.GCStaffID) LEFT JOIN tblActivities ON tblSchedule.schActID = tblActivities.actActID "

select case [forms]![your form]![your option control].value
   case "This Month"
     ssql = ssql & "WHERE (((Month([schDate]))=Month(Now())) AND ((tblSchedule.schStaffID)=[Forms]![Stafflookup_frm]![Combo0]));
   case "Today"
     ssql = ssql & "WHERE [schDate]))=Date() AND ((tblSchedule.schStaffID)=[Forms]![Stafflookup_frm]![Combo0]));
   Case Else
      'noop
end select

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[MajP]

One thing on the combo0. Either ensure it always has a default value or wrap the query in a NZ function. If not it there is no choice selected this will throw an error
=[Forms]![Stafflookup_frm]![Combo0]
when the combo is null

There are all kinds of ways to do this.
1) Use the forms filter property
2) build the sql str in code and change the recordsource
3) Have 3 stored queries.

Unless these are huge queries, I am not sure if efficiency would matter. Ease of building and maintaining would probably be more important. I like to build the form filter in code, a lot of people like to build sql strings.

Probably the most efficient would be to have three stored queries.
qryDueToday
qryDueThisMonth
qryAllTasks

Then you could just have three radio buttons or a combo and something like
...
select Case Me.frameChoices
case 1
me.recordsource = "qryDueToday"
case 2
me.recordsource = "qryDueThisMonth"
case 3
me.recordsource = "qryAllTasks"
end Select

 

[MajP]

Because of Rushmore optimization the stored query is "theoretically" more efficient than building the query string as Skip demonstrates. In most real life application you would never notice the difference. However, with a very complex query the differences can be noticeable.

 

[Eddyoftheyear]

I have the 3 queries created. When you select the name from the combo0, the form opens frm_TaskStaffUpdate that has subform name frmsub_Assign where the 3 queries will run from.

I like the idea of the radio buttons and the user will specify the timeframe and choose the name from the combo0. I don't know how to program the radio buttons to tggole between queries.

 

[Eddyoftheyear]

SkipVought,

would I insert

dim sSQL as string

ssql = "SELECT Tbl_Organization.OrganizationName, tblActivities.actDescription, tblSchedule.schDate, Tbl_Staff.Name, tblSchedule.schSchID, tblSchedule.schNotes, Tbl_Projects.ProjectTypeID, Tbl_Projects.Description, Tbl_Projects.ProjectDateFROM ((tblSchedule RIGHT JOIN (Tbl_Organization LEFT JOIN Tbl_Projects ON Tbl_Organization.OrganizationID = Tbl_Projects.OrganizationID) ON tblSchedule.ProjectID = Tbl_Projects.ProjectID) LEFT JOIN Tbl_Staff ON tblSchedule.schStaffID = Tbl_Staff.GCStaffID) LEFT JOIN tblActivities ON tblSchedule.schActID = tblActivities.actActID "

as a record source to the subform?

where would you instert:

select case [forms]![your form]![your option control].value
case "This Month"
ssql = ssql & "WHERE (((Month([schDate]))=Month(Now())) AND ((tblSchedule.schStaffID)=[Forms]![Stafflookup_frm]![Combo0]));
case "Today"
ssql = ssql & "WHERE [schDate]))=Date() AND ((tblSchedule.schStaffID)=[Forms]![Stafflookup_frm]![Combo0]));
Case Else
'noop
end select

 

[SkipVought]

MajP gave you the basis of a solution...

Then you could just have three radio buttons or a combo  and something like
  ...

  select Case Me.frameChoices
    case 1
      me.recordsource = "qryDueToday"
    case 2
      me.recordsource = "qryDueThisMonth"
    case 3
      me.recordsource = "qryAllTasks"
  end Select

      


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}