Selasa, 14 Mei 2013

[MS_AccessPros] Re: Combo Box row source search criteria (one or all items)

 

Thank you Bill,

I am able to select individual departments using the UNION now since I put both selects in the same order (I didn't even realize this issue).

There are parameters on the report. This is the report parameters:

SELECT dbo_CORE.ID, dbo_CORE.NCM_NUMBER, dbo_CORE.DEFINITION, dbo_CORE.DEPARTMENT, dbo_CORE.QA_CREATE_DATE, dbo_CORE.LOCATION, dbo_CORE.SERIAL_UNIT, dbo_CORE.PART_ID, dbo_CORE.SUPPLIER, dbo_CORE.SUPPLIER_NAME, dbo_CORE.NCM_DESC, dbo_CORE.QTY, dbo_CORE.ORGINATOR, dbo_CORE.CREATE_DATE, dbo_CORE.QA_COMMENTS, dbo_CORE.QA_REP, dbo_CORE.STATUS, dbo_CORE.STATUS_DATE, dbo_CORE.DESC_STATUS, dbo_CORE.SOURCE_STATUS, dbo_CORE.DISPOSITION_TYPE, dbo_CORE.ACTION_TYPE, dbo_CORE.REFFERENCE_TYPE, dbo_CORE.SYSUSER, dbo_CORE.D_TRACKING_NO, dbo_CORE.D_QA_MGR, dbo_CORE.DISPOSITION_DATE, dbo_CORE.DISPOSITION_COMMENTS, dbo_CORE.EMAILED, MonthName([MONTH_NUM]) AS [MONTH], Month([QA_CREATE_DATE]) AS MONTH_NUM

FROM dbo_CORE

WHERE (((dbo_CORE.DEPARTMENT)=[Forms]![(F)NCM_SearchDates]![Dept_COMBO]) AND ((dbo_CORE.QA_CREATE_DATE) Between [Forms]![(F)NCM_SearchDates]![Date_From] And [Forms]![(F)NCM_SearchDates]![Date_To]));

Am overlooking something here!

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> Rohn
>
> You have to have the fields in the same order in both SELECT statements. I don't know which is your bound column in the combo but it should be the dept_id.
>
> In that case it should be:
> SELECT dbo_DEPARTMENT.DEPRT_ID, dbo_DEPARTMENT.DEPRT_NAME
> FROM dbo_DEPARTMENT
> UNION
> SELECT "*", "<All>" FROM dbo_DEPARTMENT ORDER BY
> dbo_DEPARTMENT.DEPRT_NAME;
>
> And you should only need on combo. The <All> will appear at the top of the dropdown list.
>
> How are you filtering the report? Do you have parameters in the report's query?
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, "Rohn" <reverson@> wrote:
> >
> > Thanks for the reply Bill,
> >
> > Will I need two different controls if I want to be able to select a department separately via the combo box and select "*" for all departments.
> >
> > My select now reads:
> > SELECT dbo_DEPARTMENT.DEPRT_NAME, dbo_DEPARTMENT.DEPRT_ID FROM dbo_DEPARTMENT UNION SELECT "*", "<All>" FROM dbo_DEPARTMENT ORDER BY dbo_DEPARTMENT.DEPRT_NAME;
> >
> > But with the UNION, I always get all departments.
> > Thanks, Rohn
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@> wrote:
> > >
> > > Rohn
> > >
> > > You can do this with a UNION query as the combo's RowSource
> > >
> > > SELECT Dept, Dept
> > > FROM myTable
> > > UNION
> > > SELECT "*", "<All>"
> > > FROM myTable
> > >
> > > Regards,
> > > Bill Mosca, Founder - MS_Access_Professionals
> > > http://www.thatlldoit.com
> > > Microsoft Office Access MVP
> > > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> > > My nothing-to-do-with-Access blog
> > > http://wrmosca.wordpress.com
> > >
> > >
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Rohn" <reverson@> wrote:
> > > >
> > > > Hi all,
> > > > We cannot seem to figure out how to select the departments from a combo
> > > > box and allow selection of all departments in the combo box when
> > > > desired.
> > > > We have a search Form that provides a start and ending date for a date
> > > > report via a command button or you can select the dates and a
> > > > department combo box and run a report via a different command button.
> > > > They both work fine except for selecting all departments.
> > > > We need to add the ability to select ALL DEPARTMENTS in the combo box
> > > > for printing/viewing but cannot seem to figure out how.
> > > > Contents of the Combo Box:* (we need to add this feature, or something
> > > > like it)AssemblyFabricationPaintWeldEct.
> > > >
> > > > the Search form uses OnOpen to get the last work week dates:Private Sub
> > > > Form_Open(Cancel As Integer) Me![Date_From] = Date - Weekday(Date,
> > > > 2) - 6 Me![Date_To] = Me![Date_From] + 5End Sub
> > > > Any guidance would be appreciated.
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> >
>

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar