Filtering Via Form, and then Export

Hi:

I have a database 'query' that has about 200K rows of data.  My user 
population, and any given time, is interested in working with (charting, 
excel'ing, powerpointing, etc) just a small subset of the data (for example, 
all rows of data for a given month, to work on month end reports (the file 
contains ~2 years of data).

Each of these users sends me requests for excel files (sometimes access 
table data creation, as well) for their specific needs, based upon a fairly 
limited set of what I call requirements and/or filters.  I envision a "form" 
that they could use that wherein they would select specific filters (like, 
MonthYY of a set of transactions) based upon data that actually exists (like 
a dropdown).  There would be, maybe, 3-4 of these, with the option of 
exporting to an excel file, or two another access table (like a make-table 
query that they can change the data within, however often they want).

Something like this in a form (and I cannot show the dropdowns, but you get 
the picture, maybe):

Select MonthYear of Data to Export:  <dropdown choices>
Select Company: <dropdown choices>
Select Order Status: <dropdown choices>

Output Format (check one):    Excel   X       Access 

(GO BUTTON/Cancel)

Is there an "easy" way to do this (note:  I do not want them using filters 
on the forms, etc...it needs to be check boxes and dropdown choices or I will 
never be able to train them all).


0
Utf
11/7/2007 12:02:12 AM
access.queries 6343 articles. 1 followers. Follow

13 Replies
1095 Views

Similar Articles

[PageSpeed] 52

Okay, there's 2 parts to this.

Firstly, you are asking how to create a simple interface where users can 
enter various criteria. Here's an example of how to do that:
    Search form - Handle many optional criteria
at:
    http://allenbrowne.com/ser-62.html

Once you have worked out how to build the Filter string, you can apply it as 
the WHERE clause of a query, and then export the query. Typically a query 
contains the same field list and ORDER BY clause, so it's just the WHERE 
clause that changes. You can do that like this:
    Dim strWhere As String
    Dim strFile As String
    Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
    Const strcTail = ") ORDER BY Field1;"
    strFile = "C:\MyFolder\MyFile.xls"
    'Build up strWhere as shown in the example above:
    CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail
    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "Query1", 
strFile, True

Since you want to choose different formats, you code will need to use a 
Select Case to choose TransferText, TransferDatabase, or whatever.

If you want to give a File SaveAs dialog to choose the file name, use the 
code in this link to do that:
    http://www.mvps.org/access/api/api0001.htm

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PatK" <PatK@discussions.microsoft.com> wrote in message
news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
> Hi:
>
> I have a database 'query' that has about 200K rows of data.  My user
> population, and any given time, is interested in working with (charting,
> excel'ing, powerpointing, etc) just a small subset of the data (for 
> example,
> all rows of data for a given month, to work on month end reports (the file
> contains ~2 years of data).
>
> Each of these users sends me requests for excel files (sometimes access
> table data creation, as well) for their specific needs, based upon a 
> fairly
> limited set of what I call requirements and/or filters.  I envision a 
> "form"
> that they could use that wherein they would select specific filters (like,
> MonthYY of a set of transactions) based upon data that actually exists 
> (like
> a dropdown).  There would be, maybe, 3-4 of these, with the option of
> exporting to an excel file, or two another access table (like a make-table
> query that they can change the data within, however often they want).
>
> Something like this in a form (and I cannot show the dropdowns, but you 
> get
> the picture, maybe):
>
> Select MonthYear of Data to Export:  <dropdown choices>
> Select Company: <dropdown choices>
> Select Order Status: <dropdown choices>
>
> Output Format (check one):    Excel   X       Access
>
> (GO BUTTON/Cancel)
>
> Is there an "easy" way to do this (note:  I do not want them using filters
> on the forms, etc...it needs to be check boxes and dropdown choices or I 
> will
> never be able to train them all). 

0
Allen
11/7/2007 12:10:37 PM
You have captured what I am trying to do, very well.  Thank you for the 
pointers and the sample code.  I shall give 'er a go and let you know how it 
works out!

Thanks, Allen!

Patk

"Allen Browne" wrote:

> Okay, there's 2 parts to this.
> 
> Firstly, you are asking how to create a simple interface where users can 
> enter various criteria. Here's an example of how to do that:
>     Search form - Handle many optional criteria
> at:
>     http://allenbrowne.com/ser-62.html
> 
> Once you have worked out how to build the Filter string, you can apply it as 
> the WHERE clause of a query, and then export the query. Typically a query 
> contains the same field list and ORDER BY clause, so it's just the WHERE 
> clause that changes. You can do that like this:
>     Dim strWhere As String
>     Dim strFile As String
>     Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
>     Const strcTail = ") ORDER BY Field1;"
>     strFile = "C:\MyFolder\MyFile.xls"
>     'Build up strWhere as shown in the example above:
>     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail
>     DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "Query1", 
> strFile, True
> 
> Since you want to choose different formats, you code will need to use a 
> Select Case to choose TransferText, TransferDatabase, or whatever.
> 
> If you want to give a File SaveAs dialog to choose the file name, use the 
> code in this link to do that:
>     http://www.mvps.org/access/api/api0001.htm
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "PatK" <PatK@discussions.microsoft.com> wrote in message
> news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
> > Hi:
> >
> > I have a database 'query' that has about 200K rows of data.  My user
> > population, and any given time, is interested in working with (charting,
> > excel'ing, powerpointing, etc) just a small subset of the data (for 
> > example,
> > all rows of data for a given month, to work on month end reports (the file
> > contains ~2 years of data).
> >
> > Each of these users sends me requests for excel files (sometimes access
> > table data creation, as well) for their specific needs, based upon a 
> > fairly
> > limited set of what I call requirements and/or filters.  I envision a 
> > "form"
> > that they could use that wherein they would select specific filters (like,
> > MonthYY of a set of transactions) based upon data that actually exists 
> > (like
> > a dropdown).  There would be, maybe, 3-4 of these, with the option of
> > exporting to an excel file, or two another access table (like a make-table
> > query that they can change the data within, however often they want).
> >
> > Something like this in a form (and I cannot show the dropdowns, but you 
> > get
> > the picture, maybe):
> >
> > Select MonthYear of Data to Export:  <dropdown choices>
> > Select Company: <dropdown choices>
> > Select Order Status: <dropdown choices>
> >
> > Output Format (check one):    Excel   X       Access
> >
> > (GO BUTTON/Cancel)
> >
> > Is there an "easy" way to do this (note:  I do not want them using filters
> > on the forms, etc...it needs to be check boxes and dropdown choices or I 
> > will
> > never be able to train them all). 
> 
> 
0
Utf
11/7/2007 2:08:00 PM
quick noob quiestion:  In your sample search code, you have code like:

    If Not IsNull(Me.txtFilterCity) Then
        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND "
    End If

Can you tell me what the "Me. prefix on search items like txtFilterCity 
means?  As I look at the code, I am trying to to determine what values might 
be in Me.txtFilterCity, and I am unable to determine the data source.  I am 
guessing the Me. is the key, but not sure.  I know...really DUMB question, 
but I am new to coding in Access.

"PatK" wrote:

> You have captured what I am trying to do, very well.  Thank you for the 
> pointers and the sample code.  I shall give 'er a go and let you know how it 
> works out!
> 
> Thanks, Allen!
> 
> Patk
> 
> "Allen Browne" wrote:
> 
> > Okay, there's 2 parts to this.
> > 
> > Firstly, you are asking how to create a simple interface where users can 
> > enter various criteria. Here's an example of how to do that:
> >     Search form - Handle many optional criteria
> > at:
> >     http://allenbrowne.com/ser-62.html
> > 
> > Once you have worked out how to build the Filter string, you can apply it as 
> > the WHERE clause of a query, and then export the query. Typically a query 
> > contains the same field list and ORDER BY clause, so it's just the WHERE 
> > clause that changes. You can do that like this:
> >     Dim strWhere As String
> >     Dim strFile As String
> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
> >     Const strcTail = ") ORDER BY Field1;"
> >     strFile = "C:\MyFolder\MyFile.xls"
> >     'Build up strWhere as shown in the example above:
> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail
> >     DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "Query1", 
> > strFile, True
> > 
> > Since you want to choose different formats, you code will need to use a 
> > Select Case to choose TransferText, TransferDatabase, or whatever.
> > 
> > If you want to give a File SaveAs dialog to choose the file name, use the 
> > code in this link to do that:
> >     http://www.mvps.org/access/api/api0001.htm
> > 
> > -- 
> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> > 
> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
> > > Hi:
> > >
> > > I have a database 'query' that has about 200K rows of data.  My user
> > > population, and any given time, is interested in working with (charting,
> > > excel'ing, powerpointing, etc) just a small subset of the data (for 
> > > example,
> > > all rows of data for a given month, to work on month end reports (the file
> > > contains ~2 years of data).
> > >
> > > Each of these users sends me requests for excel files (sometimes access
> > > table data creation, as well) for their specific needs, based upon a 
> > > fairly
> > > limited set of what I call requirements and/or filters.  I envision a 
> > > "form"
> > > that they could use that wherein they would select specific filters (like,
> > > MonthYY of a set of transactions) based upon data that actually exists 
> > > (like
> > > a dropdown).  There would be, maybe, 3-4 of these, with the option of
> > > exporting to an excel file, or two another access table (like a make-table
> > > query that they can change the data within, however often they want).
> > >
> > > Something like this in a form (and I cannot show the dropdowns, but you 
> > > get
> > > the picture, maybe):
> > >
> > > Select MonthYear of Data to Export:  <dropdown choices>
> > > Select Company: <dropdown choices>
> > > Select Order Status: <dropdown choices>
> > >
> > > Output Format (check one):    Excel   X       Access
> > >
> > > (GO BUTTON/Cancel)
> > >
> > > Is there an "easy" way to do this (note:  I do not want them using filters
> > > on the forms, etc...it needs to be check boxes and dropdown choices or I 
> > > will
> > > never be able to train them all). 
> > 
> > 
0
Utf
11/7/2007 5:41:02 PM
Me refers to the form or report in which the code is executing.

Me.txtFilterCity is short hand for referring to the control txtFilterCity 
that exists on the current form.
[Forms]![NameOfCurrentForm]![txtFilterCity]

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"PatK" <PatK@discussions.microsoft.com> wrote in message 
news:9905558E-E192-4EFA-BA94-EEB7585488BB@microsoft.com...
> quick noob quiestion:  In your sample search code, you have code like:
>
>    If Not IsNull(Me.txtFilterCity) Then
>        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND 
> "
>    End If
>
> Can you tell me what the "Me. prefix on search items like txtFilterCity
> means?  As I look at the code, I am trying to to determine what values 
> might
> be in Me.txtFilterCity, and I am unable to determine the data source.  I 
> am
> guessing the Me. is the key, but not sure.  I know...really DUMB question,
> but I am new to coding in Access.
>
> "PatK" wrote:
>
>> You have captured what I am trying to do, very well.  Thank you for the
>> pointers and the sample code.  I shall give 'er a go and let you know how 
>> it
>> works out!
>>
>> Thanks, Allen!
>>
>> Patk
>>
>> "Allen Browne" wrote:
>>
>> > Okay, there's 2 parts to this.
>> >
>> > Firstly, you are asking how to create a simple interface where users 
>> > can
>> > enter various criteria. Here's an example of how to do that:
>> >     Search form - Handle many optional criteria
>> > at:
>> >     http://allenbrowne.com/ser-62.html
>> >
>> > Once you have worked out how to build the Filter string, you can apply 
>> > it as
>> > the WHERE clause of a query, and then export the query. Typically a 
>> > query
>> > contains the same field list and ORDER BY clause, so it's just the 
>> > WHERE
>> > clause that changes. You can do that like this:
>> >     Dim strWhere As String
>> >     Dim strFile As String
>> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
>> >     Const strcTail = ") ORDER BY Field1;"
>> >     strFile = "C:\MyFolder\MyFile.xls"
>> >     'Build up strWhere as shown in the example above:
>> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail
>> >     DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, 
>> > "Query1",
>> > strFile, True
>> >
>> > Since you want to choose different formats, you code will need to use a
>> > Select Case to choose TransferText, TransferDatabase, or whatever.
>> >
>> > If you want to give a File SaveAs dialog to choose the file name, use 
>> > the
>> > code in this link to do that:
>> >     http://www.mvps.org/access/api/api0001.htm
>> >
>> > -- 
>> > Allen Browne - Microsoft MVP.  Perth, Western Australia
>> > Tips for Access users - http://allenbrowne.com/tips.html
>> > Reply to group, rather than allenbrowne at mvps dot org.
>> >
>> > "PatK" <PatK@discussions.microsoft.com> wrote in message
>> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
>> > > Hi:
>> > >
>> > > I have a database 'query' that has about 200K rows of data.  My user
>> > > population, and any given time, is interested in working with 
>> > > (charting,
>> > > excel'ing, powerpointing, etc) just a small subset of the data (for
>> > > example,
>> > > all rows of data for a given month, to work on month end reports (the 
>> > > file
>> > > contains ~2 years of data).
>> > >
>> > > Each of these users sends me requests for excel files (sometimes 
>> > > access
>> > > table data creation, as well) for their specific needs, based upon a
>> > > fairly
>> > > limited set of what I call requirements and/or filters.  I envision a
>> > > "form"
>> > > that they could use that wherein they would select specific filters 
>> > > (like,
>> > > MonthYY of a set of transactions) based upon data that actually 
>> > > exists
>> > > (like
>> > > a dropdown).  There would be, maybe, 3-4 of these, with the option of
>> > > exporting to an excel file, or two another access table (like a 
>> > > make-table
>> > > query that they can change the data within, however often they want).
>> > >
>> > > Something like this in a form (and I cannot show the dropdowns, but 
>> > > you
>> > > get
>> > > the picture, maybe):
>> > >
>> > > Select MonthYear of Data to Export:  <dropdown choices>
>> > > Select Company: <dropdown choices>
>> > > Select Order Status: <dropdown choices>
>> > >
>> > > Output Format (check one):    Excel   X       Access
>> > >
>> > > (GO BUTTON/Cancel)
>> > >
>> > > Is there an "easy" way to do this (note:  I do not want them using 
>> > > filters
>> > > on the forms, etc...it needs to be check boxes and dropdown choices 
>> > > or I
>> > > will
>> > > never be able to train them all).
>> >
>> > 


0
John
11/7/2007 7:48:32 PM
Now I "am" embarrassed. LOL..thanks John

"John Spencer" wrote:

> Me refers to the form or report in which the code is executing.
> 
> Me.txtFilterCity is short hand for referring to the control txtFilterCity 
> that exists on the current form.
> [Forms]![NameOfCurrentForm]![txtFilterCity]
> 
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
> 
> "PatK" <PatK@discussions.microsoft.com> wrote in message 
> news:9905558E-E192-4EFA-BA94-EEB7585488BB@microsoft.com...
> > quick noob quiestion:  In your sample search code, you have code like:
> >
> >    If Not IsNull(Me.txtFilterCity) Then
> >        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND 
> > "
> >    End If
> >
> > Can you tell me what the "Me. prefix on search items like txtFilterCity
> > means?  As I look at the code, I am trying to to determine what values 
> > might
> > be in Me.txtFilterCity, and I am unable to determine the data source.  I 
> > am
> > guessing the Me. is the key, but not sure.  I know...really DUMB question,
> > but I am new to coding in Access.
> >
> > "PatK" wrote:
> >
> >> You have captured what I am trying to do, very well.  Thank you for the
> >> pointers and the sample code.  I shall give 'er a go and let you know how 
> >> it
> >> works out!
> >>
> >> Thanks, Allen!
> >>
> >> Patk
> >>
> >> "Allen Browne" wrote:
> >>
> >> > Okay, there's 2 parts to this.
> >> >
> >> > Firstly, you are asking how to create a simple interface where users 
> >> > can
> >> > enter various criteria. Here's an example of how to do that:
> >> >     Search form - Handle many optional criteria
> >> > at:
> >> >     http://allenbrowne.com/ser-62.html
> >> >
> >> > Once you have worked out how to build the Filter string, you can apply 
> >> > it as
> >> > the WHERE clause of a query, and then export the query. Typically a 
> >> > query
> >> > contains the same field list and ORDER BY clause, so it's just the 
> >> > WHERE
> >> > clause that changes. You can do that like this:
> >> >     Dim strWhere As String
> >> >     Dim strFile As String
> >> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
> >> >     Const strcTail = ") ORDER BY Field1;"
> >> >     strFile = "C:\MyFolder\MyFile.xls"
> >> >     'Build up strWhere as shown in the example above:
> >> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail
> >> >     DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, 
> >> > "Query1",
> >> > strFile, True
> >> >
> >> > Since you want to choose different formats, you code will need to use a
> >> > Select Case to choose TransferText, TransferDatabase, or whatever.
> >> >
> >> > If you want to give a File SaveAs dialog to choose the file name, use 
> >> > the
> >> > code in this link to do that:
> >> >     http://www.mvps.org/access/api/api0001.htm
> >> >
> >> > -- 
> >> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> >> > Tips for Access users - http://allenbrowne.com/tips.html
> >> > Reply to group, rather than allenbrowne at mvps dot org.
> >> >
> >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> >> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
> >> > > Hi:
> >> > >
> >> > > I have a database 'query' that has about 200K rows of data.  My user
> >> > > population, and any given time, is interested in working with 
> >> > > (charting,
> >> > > excel'ing, powerpointing, etc) just a small subset of the data (for
> >> > > example,
> >> > > all rows of data for a given month, to work on month end reports (the 
> >> > > file
> >> > > contains ~2 years of data).
> >> > >
> >> > > Each of these users sends me requests for excel files (sometimes 
> >> > > access
> >> > > table data creation, as well) for their specific needs, based upon a
> >> > > fairly
> >> > > limited set of what I call requirements and/or filters.  I envision a
> >> > > "form"
> >> > > that they could use that wherein they would select specific filters 
> >> > > (like,
> >> > > MonthYY of a set of transactions) based upon data that actually 
> >> > > exists
> >> > > (like
> >> > > a dropdown).  There would be, maybe, 3-4 of these, with the option of
> >> > > exporting to an excel file, or two another access table (like a 
> >> > > make-table
> >> > > query that they can change the data within, however often they want).
> >> > >
> >> > > Something like this in a form (and I cannot show the dropdowns, but 
> >> > > you
> >> > > get
> >> > > the picture, maybe):
> >> > >
> >> > > Select MonthYear of Data to Export:  <dropdown choices>
> >> > > Select Company: <dropdown choices>
> >> > > Select Order Status: <dropdown choices>
> >> > >
> >> > > Output Format (check one):    Excel   X       Access
> >> > >
> >> > > (GO BUTTON/Cancel)
> >> > >
> >> > > Is there an "easy" way to do this (note:  I do not want them using 
> >> > > filters
> >> > > on the forms, etc...it needs to be check boxes and dropdown choices 
> >> > > or I
> >> > > will
> >> > > never be able to train them all).
> >> >
> >> > 
> 
> 
> 
0
Utf
11/7/2007 9:13:03 PM
I think I have the filter form working ok (THANKS!).  Question:  Once I bound 
my query to the form, it wants to populate the detail section of the form 
immediately, at first opening of the form, before I have chosen any filters.  
Any way to stop this behavior?  I want it to wait to always require filter 
choices, before populating.

As it is working now, it does, in fact, return the filtered record set as I 
expect, but since I have 200K (and growing rows), the presenation of the 
initial filter form take a bit longer than I am comfortable with.  Note: All 
my filter fields are actually bound to very small tables created that contain 
all possible filtered values, or short lists I have provide, so they won't 
slow down the setup of the combo boxes, initially).

Thanks again, should you have any ideas.....

Patk

"PatK" wrote:

> Now I "am" embarrassed. LOL..thanks John
> 
> "John Spencer" wrote:
> 
> > Me refers to the form or report in which the code is executing.
> > 
> > Me.txtFilterCity is short hand for referring to the control txtFilterCity 
> > that exists on the current form.
> > [Forms]![NameOfCurrentForm]![txtFilterCity]
> > 
> > -- 
> > John Spencer
> > Access MVP 2002-2005, 2007
> > Center for Health Program Development and Management
> > University of Maryland Baltimore County
> > ..
> > 
> > "PatK" <PatK@discussions.microsoft.com> wrote in message 
> > news:9905558E-E192-4EFA-BA94-EEB7585488BB@microsoft.com...
> > > quick noob quiestion:  In your sample search code, you have code like:
> > >
> > >    If Not IsNull(Me.txtFilterCity) Then
> > >        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND 
> > > "
> > >    End If
> > >
> > > Can you tell me what the "Me. prefix on search items like txtFilterCity
> > > means?  As I look at the code, I am trying to to determine what values 
> > > might
> > > be in Me.txtFilterCity, and I am unable to determine the data source.  I 
> > > am
> > > guessing the Me. is the key, but not sure.  I know...really DUMB question,
> > > but I am new to coding in Access.
> > >
> > > "PatK" wrote:
> > >
> > >> You have captured what I am trying to do, very well.  Thank you for the
> > >> pointers and the sample code.  I shall give 'er a go and let you know how 
> > >> it
> > >> works out!
> > >>
> > >> Thanks, Allen!
> > >>
> > >> Patk
> > >>
> > >> "Allen Browne" wrote:
> > >>
> > >> > Okay, there's 2 parts to this.
> > >> >
> > >> > Firstly, you are asking how to create a simple interface where users 
> > >> > can
> > >> > enter various criteria. Here's an example of how to do that:
> > >> >     Search form - Handle many optional criteria
> > >> > at:
> > >> >     http://allenbrowne.com/ser-62.html
> > >> >
> > >> > Once you have worked out how to build the Filter string, you can apply 
> > >> > it as
> > >> > the WHERE clause of a query, and then export the query. Typically a 
> > >> > query
> > >> > contains the same field list and ORDER BY clause, so it's just the 
> > >> > WHERE
> > >> > clause that changes. You can do that like this:
> > >> >     Dim strWhere As String
> > >> >     Dim strFile As String
> > >> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
> > >> >     Const strcTail = ") ORDER BY Field1;"
> > >> >     strFile = "C:\MyFolder\MyFile.xls"
> > >> >     'Build up strWhere as shown in the example above:
> > >> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail
> > >> >     DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, 
> > >> > "Query1",
> > >> > strFile, True
> > >> >
> > >> > Since you want to choose different formats, you code will need to use a
> > >> > Select Case to choose TransferText, TransferDatabase, or whatever.
> > >> >
> > >> > If you want to give a File SaveAs dialog to choose the file name, use 
> > >> > the
> > >> > code in this link to do that:
> > >> >     http://www.mvps.org/access/api/api0001.htm
> > >> >
> > >> > -- 
> > >> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > >> > Tips for Access users - http://allenbrowne.com/tips.html
> > >> > Reply to group, rather than allenbrowne at mvps dot org.
> > >> >
> > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> > >> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
> > >> > > Hi:
> > >> > >
> > >> > > I have a database 'query' that has about 200K rows of data.  My user
> > >> > > population, and any given time, is interested in working with 
> > >> > > (charting,
> > >> > > excel'ing, powerpointing, etc) just a small subset of the data (for
> > >> > > example,
> > >> > > all rows of data for a given month, to work on month end reports (the 
> > >> > > file
> > >> > > contains ~2 years of data).
> > >> > >
> > >> > > Each of these users sends me requests for excel files (sometimes 
> > >> > > access
> > >> > > table data creation, as well) for their specific needs, based upon a
> > >> > > fairly
> > >> > > limited set of what I call requirements and/or filters.  I envision a
> > >> > > "form"
> > >> > > that they could use that wherein they would select specific filters 
> > >> > > (like,
> > >> > > MonthYY of a set of transactions) based upon data that actually 
> > >> > > exists
> > >> > > (like
> > >> > > a dropdown).  There would be, maybe, 3-4 of these, with the option of
> > >> > > exporting to an excel file, or two another access table (like a 
> > >> > > make-table
> > >> > > query that they can change the data within, however often they want).
> > >> > >
> > >> > > Something like this in a form (and I cannot show the dropdowns, but 
> > >> > > you
> > >> > > get
> > >> > > the picture, maybe):
> > >> > >
> > >> > > Select MonthYear of Data to Export:  <dropdown choices>
> > >> > > Select Company: <dropdown choices>
> > >> > > Select Order Status: <dropdown choices>
> > >> > >
> > >> > > Output Format (check one):    Excel   X       Access
> > >> > >
> > >> > > (GO BUTTON/Cancel)
> > >> > >
> > >> > > Is there an "easy" way to do this (note:  I do not want them using 
> > >> > > filters
> > >> > > on the forms, etc...it needs to be check boxes and dropdown choices 
> > >> > > or I
> > >> > > will
> > >> > > never be able to train them all).
> > >> >
> > >> > 
> > 
> > 
> > 
0
Utf
11/7/2007 11:39:01 PM
This will cause the form to load without any records:

Private Sub Form_Open(Cancel As Integer)
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub

A WHERE clause (or filter) is ultimately an expression that evaluates to 
True or False for each record. The expression above evaluates to False for 
all records (since False is always False), so no records load.

If your source query is read-only, or you don't allow new records, you will 
find that the Detail section of your form goes completely blank. If that's a 
problem, see:
    http://allenbrowne.com/casu-20.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PatK" <PatK@discussions.microsoft.com> wrote in message
news:20AAACBF-3138-4EAA-A338-325777BB5C4D@microsoft.com...
>I think I have the filter form working ok (THANKS!).  Question:  Once I 
>bound
> my query to the form, it wants to populate the detail section of the form
> immediately, at first opening of the form, before I have chosen any 
> filters.
> Any way to stop this behavior?  I want it to wait to always require filter
> choices, before populating.
>
> As it is working now, it does, in fact, return the filtered record set as 
> I
> expect, but since I have 200K (and growing rows), the presenation of the
> initial filter form take a bit longer than I am comfortable with.  Note: 
> All
> my filter fields are actually bound to very small tables created that 
> contain
> all possible filtered values, or short lists I have provide, so they won't
> slow down the setup of the combo boxes, initially).
>
> Thanks again, should you have any ideas.....
>
> Patk
>
> "PatK" wrote:
>
>> Now I "am" embarrassed. LOL..thanks John
>>
>> "John Spencer" wrote:
>>
>> > Me refers to the form or report in which the code is executing.
>> >
>> > Me.txtFilterCity is short hand for referring to the control 
>> > txtFilterCity
>> > that exists on the current form.
>> > [Forms]![NameOfCurrentForm]![txtFilterCity]
>> >
>> > -- 
>> > John Spencer
>> > Access MVP 2002-2005, 2007
>> > Center for Health Program Development and Management
>> > University of Maryland Baltimore County
>> > ..
>> >
>> > "PatK" <PatK@discussions.microsoft.com> wrote in message
>> > news:9905558E-E192-4EFA-BA94-EEB7585488BB@microsoft.com...
>> > > quick noob quiestion:  In your sample search code, you have code 
>> > > like:
>> > >
>> > >    If Not IsNull(Me.txtFilterCity) Then
>> > >        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) 
>> > > AND
>> > > "
>> > >    End If
>> > >
>> > > Can you tell me what the "Me. prefix on search items like 
>> > > txtFilterCity
>> > > means?  As I look at the code, I am trying to to determine what 
>> > > values
>> > > might
>> > > be in Me.txtFilterCity, and I am unable to determine the data source. 
>> > > I
>> > > am
>> > > guessing the Me. is the key, but not sure.  I know...really DUMB 
>> > > question,
>> > > but I am new to coding in Access.
>> > >
>> > > "PatK" wrote:
>> > >
>> > >> You have captured what I am trying to do, very well.  Thank you for 
>> > >> the
>> > >> pointers and the sample code.  I shall give 'er a go and let you 
>> > >> know how
>> > >> it
>> > >> works out!
>> > >>
>> > >> Thanks, Allen!
>> > >>
>> > >> Patk
>> > >>
>> > >> "Allen Browne" wrote:
>> > >>
>> > >> > Okay, there's 2 parts to this.
>> > >> >
>> > >> > Firstly, you are asking how to create a simple interface where 
>> > >> > users
>> > >> > can
>> > >> > enter various criteria. Here's an example of how to do that:
>> > >> >     Search form - Handle many optional criteria
>> > >> > at:
>> > >> >     http://allenbrowne.com/ser-62.html
>> > >> >
>> > >> > Once you have worked out how to build the Filter string, you can 
>> > >> > apply
>> > >> > it as
>> > >> > the WHERE clause of a query, and then export the query. Typically 
>> > >> > a
>> > >> > query
>> > >> > contains the same field list and ORDER BY clause, so it's just the
>> > >> > WHERE
>> > >> > clause that changes. You can do that like this:
>> > >> >     Dim strWhere As String
>> > >> >     Dim strFile As String
>> > >> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
>> > >> >     Const strcTail = ") ORDER BY Field1;"
>> > >> >     strFile = "C:\MyFolder\MyFile.xls"
>> > >> >     'Build up strWhere as shown in the example above:
>> > >> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & 
>> > >> > strcTail
>> > >> >     DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,
>> > >> > "Query1",
>> > >> > strFile, True
>> > >> >
>> > >> > Since you want to choose different formats, you code will need to 
>> > >> > use a
>> > >> > Select Case to choose TransferText, TransferDatabase, or whatever.
>> > >> >
>> > >> > If you want to give a File SaveAs dialog to choose the file name, 
>> > >> > use
>> > >> > the
>> > >> > code in this link to do that:
>> > >> >     http://www.mvps.org/access/api/api0001.htm
>> > >> >
>> > >> > -- 
>> > >> > Allen Browne - Microsoft MVP.  Perth, Western Australia
>> > >> > Tips for Access users - http://allenbrowne.com/tips.html
>> > >> > Reply to group, rather than allenbrowne at mvps dot org.
>> > >> >
>> > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
>> > >> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
>> > >> > > Hi:
>> > >> > >
>> > >> > > I have a database 'query' that has about 200K rows of data.  My 
>> > >> > > user
>> > >> > > population, and any given time, is interested in working with
>> > >> > > (charting,
>> > >> > > excel'ing, powerpointing, etc) just a small subset of the data 
>> > >> > > (for
>> > >> > > example,
>> > >> > > all rows of data for a given month, to work on month end reports 
>> > >> > > (the
>> > >> > > file
>> > >> > > contains ~2 years of data).
>> > >> > >
>> > >> > > Each of these users sends me requests for excel files (sometimes
>> > >> > > access
>> > >> > > table data creation, as well) for their specific needs, based 
>> > >> > > upon a
>> > >> > > fairly
>> > >> > > limited set of what I call requirements and/or filters.  I 
>> > >> > > envision a
>> > >> > > "form"
>> > >> > > that they could use that wherein they would select specific 
>> > >> > > filters
>> > >> > > (like,
>> > >> > > MonthYY of a set of transactions) based upon data that actually
>> > >> > > exists
>> > >> > > (like
>> > >> > > a dropdown).  There would be, maybe, 3-4 of these, with the 
>> > >> > > option of
>> > >> > > exporting to an excel file, or two another access table (like a
>> > >> > > make-table
>> > >> > > query that they can change the data within, however often they 
>> > >> > > want).
>> > >> > >
>> > >> > > Something like this in a form (and I cannot show the dropdowns, 
>> > >> > > but
>> > >> > > you
>> > >> > > get
>> > >> > > the picture, maybe):
>> > >> > >
>> > >> > > Select MonthYear of Data to Export:  <dropdown choices>
>> > >> > > Select Company: <dropdown choices>
>> > >> > > Select Order Status: <dropdown choices>
>> > >> > >
>> > >> > > Output Format (check one):    Excel   X       Access
>> > >> > >
>> > >> > > (GO BUTTON/Cancel)
>> > >> > >
>> > >> > > Is there an "easy" way to do this (note:  I do not want them 
>> > >> > > using
>> > >> > > filters
>> > >> > > on the forms, etc...it needs to be check boxes and dropdown 
>> > >> > > choices
>> > >> > > or I
>> > >> > > will
>> > >> > > never be able to train them all).
>> > >> >
>> > >> >
>> >
>> >
>> > 

0
Allen
11/8/2007 12:46:05 AM
Allen: Could you tell me where I would add this code?  I added it to the form 
itself (where I have the cmdClick routines, etc), as well as in the routine 
where the filter where string is created.  But now, it seems to be doing two 
queries at open, instead of one, taking twice as long.

Maybe I am not understanding the filter process at all. When I open my form, 
I see a "running query" status bar pop up in the low left corner of Access.  
I presume this is not running a query and I have none bound to the form.  
Rather, that the "filter" itself is the query it is executing, and if so, and 
I am not asking for any data returned, why is it now executing twice, instead 
of once, since I added the code you mentioned below (thus, my thinking I 
stuck it in the wrong place).  It is true that since I added the code, I do 
not, in fact, get any detail rows (great), but then, what the heck are the 
queries doing.  My intent was to pop open the form, allow the user to set 
their filter parameters, and then execute the filter and present the data.  
Also interestingly, somehow, my filter only seems to work once in a row, now.

Sorry...and again, thanks for any help you may provide.

for the time being, I will revert my code back, until I figure out the issue.

Cheers!

patk

"Allen Browne" wrote:

> This will cause the form to load without any records:
> 
> Private Sub Form_Open(Cancel As Integer)
>     Me.Filter = "(False)"
>     Me.FilterOn = True
> End Sub
> 
> A WHERE clause (or filter) is ultimately an expression that evaluates to 
> True or False for each record. The expression above evaluates to False for 
> all records (since False is always False), so no records load.
> 
> If your source query is read-only, or you don't allow new records, you will 
> find that the Detail section of your form goes completely blank. If that's a 
> problem, see:
>     http://allenbrowne.com/casu-20.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "PatK" <PatK@discussions.microsoft.com> wrote in message
> news:20AAACBF-3138-4EAA-A338-325777BB5C4D@microsoft.com...
> >I think I have the filter form working ok (THANKS!).  Question:  Once I 
> >bound
> > my query to the form, it wants to populate the detail section of the form
> > immediately, at first opening of the form, before I have chosen any 
> > filters.
> > Any way to stop this behavior?  I want it to wait to always require filter
> > choices, before populating.
> >
> > As it is working now, it does, in fact, return the filtered record set as 
> > I
> > expect, but since I have 200K (and growing rows), the presenation of the
> > initial filter form take a bit longer than I am comfortable with.  Note: 
> > All
> > my filter fields are actually bound to very small tables created that 
> > contain
> > all possible filtered values, or short lists I have provide, so they won't
> > slow down the setup of the combo boxes, initially).
> >
> > Thanks again, should you have any ideas.....
> >
> > Patk
> >
> > "PatK" wrote:
> >
> >> Now I "am" embarrassed. LOL..thanks John
> >>
> >> "John Spencer" wrote:
> >>
> >> > Me refers to the form or report in which the code is executing.
> >> >
> >> > Me.txtFilterCity is short hand for referring to the control 
> >> > txtFilterCity
> >> > that exists on the current form.
> >> > [Forms]![NameOfCurrentForm]![txtFilterCity]
> >> >
> >> > -- 
> >> > John Spencer
> >> > Access MVP 2002-2005, 2007
> >> > Center for Health Program Development and Management
> >> > University of Maryland Baltimore County
> >> > ..
> >> >
> >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> >> > news:9905558E-E192-4EFA-BA94-EEB7585488BB@microsoft.com...
> >> > > quick noob quiestion:  In your sample search code, you have code 
> >> > > like:
> >> > >
> >> > >    If Not IsNull(Me.txtFilterCity) Then
> >> > >        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) 
> >> > > AND
> >> > > "
> >> > >    End If
> >> > >
> >> > > Can you tell me what the "Me. prefix on search items like 
> >> > > txtFilterCity
> >> > > means?  As I look at the code, I am trying to to determine what 
> >> > > values
> >> > > might
> >> > > be in Me.txtFilterCity, and I am unable to determine the data source. 
> >> > > I
> >> > > am
> >> > > guessing the Me. is the key, but not sure.  I know...really DUMB 
> >> > > question,
> >> > > but I am new to coding in Access.
> >> > >
> >> > > "PatK" wrote:
> >> > >
> >> > >> You have captured what I am trying to do, very well.  Thank you for 
> >> > >> the
> >> > >> pointers and the sample code.  I shall give 'er a go and let you 
> >> > >> know how
> >> > >> it
> >> > >> works out!
> >> > >>
> >> > >> Thanks, Allen!
> >> > >>
> >> > >> Patk
> >> > >>
> >> > >> "Allen Browne" wrote:
> >> > >>
> >> > >> > Okay, there's 2 parts to this.
> >> > >> >
> >> > >> > Firstly, you are asking how to create a simple interface where 
> >> > >> > users
> >> > >> > can
> >> > >> > enter various criteria. Here's an example of how to do that:
> >> > >> >     Search form - Handle many optional criteria
> >> > >> > at:
> >> > >> >     http://allenbrowne.com/ser-62.html
> >> > >> >
> >> > >> > Once you have worked out how to build the Filter string, you can 
> >> > >> > apply
> >> > >> > it as
> >> > >> > the WHERE clause of a query, and then export the query. Typically 
> >> > >> > a
> >> > >> > query
> >> > >> > contains the same field list and ORDER BY clause, so it's just the
> >> > >> > WHERE
> >> > >> > clause that changes. You can do that like this:
> >> > >> >     Dim strWhere As String
> >> > >> >     Dim strFile As String
> >> > >> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
> >> > >> >     Const strcTail = ") ORDER BY Field1;"
> >> > >> >     strFile = "C:\MyFolder\MyFile.xls"
> >> > >> >     'Build up strWhere as shown in the example above:
> >> > >> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & 
> >> > >> > strcTail
> >> > >> >     DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,
> >> > >> > "Query1",
> >> > >> > strFile, True
> >> > >> >
> >> > >> > Since you want to choose different formats, you code will need to 
> >> > >> > use a
> >> > >> > Select Case to choose TransferText, TransferDatabase, or whatever.
> >> > >> >
> >> > >> > If you want to give a File SaveAs dialog to choose the file name, 
> >> > >> > use
> >> > >> > the
> >> > >> > code in this link to do that:
> >> > >> >     http://www.mvps.org/access/api/api0001.htm
> >> > >> >
> >> > >> > -- 
> >> > >> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> >> > >> > Tips for Access users - http://allenbrowne.com/tips.html
> >> > >> > Reply to group, rather than allenbrowne at mvps dot org.
> >> > >> >
> >> > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> >> > >> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
> >> > >> > > Hi:
> >> > >> > >
> >> > >> > > I have a database 'query' that has about 200K rows of data.  My 
> >> > >> > > user
> >> > >> > > population, and any given time, is interested in working with
> >> > >> > > (charting,
> >> > >> > > excel'ing, powerpointing, etc) just a small subset of the data 
> >> > >> > > (for
> >> > >> > > example,
> >> > >> > > all rows of data for a given month, to work on month end reports 
> >> > >> > > (the
> >> > >> > > file
> >> > >> > > contains ~2 years of data).
> >> > >> > >
> >> > >> > > Each of these users sends me requests for excel files (sometimes
> >> > >> > > access
> >> > >> > > table data creation, as well) for their specific needs, based 
> >> > >> > > upon a
> >> > >> > > fairly
> >> > >> > > limited set of what I call requirements and/or filters.  I 
> >> > >> > > envision a
> >> > >> > > "form"
> >> > >> > > that they could use that wherein they would select specific 
> >> > >> > > filters
> >> > >> > > (like,
> >> > >> > > MonthYY of a set of transactions) based upon data that actually
> >> > >> > > exists
> >> > >> > > (like
> >> > >> > > a dropdown).  There would be, maybe, 3-4 of these, with the 
> >> > >> > > option of
> >> > >> > > exporting to an excel file, or two another access table (like a
> >> > >> > > make-table
> >> > >> > > query that they can change the data within, however often they 
> >> > >> > > want).
> >> > >> > >
> >> > >> > > Something like this in a form (and I cannot show the dropdowns, 
> >> > >> > > but
> >> > >> > > you
> >> > >> > > get
> >> > >> > > the picture, maybe):
> >> > >> > >
> >> > >> > > Select MonthYear of Data to Export:  <dropdown choices>
> >> > >> > > Select Company: <dropdown choices>
> >> > >> > > Select Order Status: <dropdown choices>
> >> > >> > >
> >> > >> > > Output Format (check one):    Excel   X       Access
> >> > >> > >
> >> > >> > > (GO BUTTON/Cancel)
> >> > >> > >
> >> > >> > > Is there an "easy" way to do this (note:  I do not want them 
> >> > >> > > using
> >> > >> > > filters
> >> > >> > > on the forms, etc...it needs to be check boxes and dropdown 
> >> > >> > > choices
> >> > >> > > or I
> >> > >> > > will
> >> > >> > > never be able to train them all).
> >> > >> >
> >> > >> >
> >> >
> >> >
> >> > 
> 
> 
0
Utf
11/8/2007 4:31:02 PM
Sorry...one possibly important clarification:  My "detail" data (that which I 
am not asking to be presented initially, "IS" bound to a query, but my 
presumption was since we set the filter parameter to false, that it would not 
execute the query.  Maybe that is the problem?

"PatK" wrote:

> Allen: Could you tell me where I would add this code?  I added it to the form 
> itself (where I have the cmdClick routines, etc), as well as in the routine 
> where the filter where string is created.  But now, it seems to be doing two 
> queries at open, instead of one, taking twice as long.
> 
> Maybe I am not understanding the filter process at all. When I open my form, 
> I see a "running query" status bar pop up in the low left corner of Access.  
> I presume this is not running a query and I have none bound to the form.  
> Rather, that the "filter" itself is the query it is executing, and if so, and 
> I am not asking for any data returned, why is it now executing twice, instead 
> of once, since I added the code you mentioned below (thus, my thinking I 
> stuck it in the wrong place).  It is true that since I added the code, I do 
> not, in fact, get any detail rows (great), but then, what the heck are the 
> queries doing.  My intent was to pop open the form, allow the user to set 
> their filter parameters, and then execute the filter and present the data.  
> Also interestingly, somehow, my filter only seems to work once in a row, now.
> 
> Sorry...and again, thanks for any help you may provide.
> 
> for the time being, I will revert my code back, until I figure out the issue.
> 
> Cheers!
> 
> patk
> 
> "Allen Browne" wrote:
> 
> > This will cause the form to load without any records:
> > 
> > Private Sub Form_Open(Cancel As Integer)
> >     Me.Filter = "(False)"
> >     Me.FilterOn = True
> > End Sub
> > 
> > A WHERE clause (or filter) is ultimately an expression that evaluates to 
> > True or False for each record. The expression above evaluates to False for 
> > all records (since False is always False), so no records load.
> > 
> > If your source query is read-only, or you don't allow new records, you will 
> > find that the Detail section of your form goes completely blank. If that's a 
> > problem, see:
> >     http://allenbrowne.com/casu-20.html
> > 
> > -- 
> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> > 
> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> > news:20AAACBF-3138-4EAA-A338-325777BB5C4D@microsoft.com...
> > >I think I have the filter form working ok (THANKS!).  Question:  Once I 
> > >bound
> > > my query to the form, it wants to populate the detail section of the form
> > > immediately, at first opening of the form, before I have chosen any 
> > > filters.
> > > Any way to stop this behavior?  I want it to wait to always require filter
> > > choices, before populating.
> > >
> > > As it is working now, it does, in fact, return the filtered record set as 
> > > I
> > > expect, but since I have 200K (and growing rows), the presenation of the
> > > initial filter form take a bit longer than I am comfortable with.  Note: 
> > > All
> > > my filter fields are actually bound to very small tables created that 
> > > contain
> > > all possible filtered values, or short lists I have provide, so they won't
> > > slow down the setup of the combo boxes, initially).
> > >
> > > Thanks again, should you have any ideas.....
> > >
> > > Patk
> > >
> > > "PatK" wrote:
> > >
> > >> Now I "am" embarrassed. LOL..thanks John
> > >>
> > >> "John Spencer" wrote:
> > >>
> > >> > Me refers to the form or report in which the code is executing.
> > >> >
> > >> > Me.txtFilterCity is short hand for referring to the control 
> > >> > txtFilterCity
> > >> > that exists on the current form.
> > >> > [Forms]![NameOfCurrentForm]![txtFilterCity]
> > >> >
> > >> > -- 
> > >> > John Spencer
> > >> > Access MVP 2002-2005, 2007
> > >> > Center for Health Program Development and Management
> > >> > University of Maryland Baltimore County
> > >> > ..
> > >> >
> > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> > >> > news:9905558E-E192-4EFA-BA94-EEB7585488BB@microsoft.com...
> > >> > > quick noob quiestion:  In your sample search code, you have code 
> > >> > > like:
> > >> > >
> > >> > >    If Not IsNull(Me.txtFilterCity) Then
> > >> > >        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) 
> > >> > > AND
> > >> > > "
> > >> > >    End If
> > >> > >
> > >> > > Can you tell me what the "Me. prefix on search items like 
> > >> > > txtFilterCity
> > >> > > means?  As I look at the code, I am trying to to determine what 
> > >> > > values
> > >> > > might
> > >> > > be in Me.txtFilterCity, and I am unable to determine the data source. 
> > >> > > I
> > >> > > am
> > >> > > guessing the Me. is the key, but not sure.  I know...really DUMB 
> > >> > > question,
> > >> > > but I am new to coding in Access.
> > >> > >
> > >> > > "PatK" wrote:
> > >> > >
> > >> > >> You have captured what I am trying to do, very well.  Thank you for 
> > >> > >> the
> > >> > >> pointers and the sample code.  I shall give 'er a go and let you 
> > >> > >> know how
> > >> > >> it
> > >> > >> works out!
> > >> > >>
> > >> > >> Thanks, Allen!
> > >> > >>
> > >> > >> Patk
> > >> > >>
> > >> > >> "Allen Browne" wrote:
> > >> > >>
> > >> > >> > Okay, there's 2 parts to this.
> > >> > >> >
> > >> > >> > Firstly, you are asking how to create a simple interface where 
> > >> > >> > users
> > >> > >> > can
> > >> > >> > enter various criteria. Here's an example of how to do that:
> > >> > >> >     Search form - Handle many optional criteria
> > >> > >> > at:
> > >> > >> >     http://allenbrowne.com/ser-62.html
> > >> > >> >
> > >> > >> > Once you have worked out how to build the Filter string, you can 
> > >> > >> > apply
> > >> > >> > it as
> > >> > >> > the WHERE clause of a query, and then export the query. Typically 
> > >> > >> > a
> > >> > >> > query
> > >> > >> > contains the same field list and ORDER BY clause, so it's just the
> > >> > >> > WHERE
> > >> > >> > clause that changes. You can do that like this:
> > >> > >> >     Dim strWhere As String
> > >> > >> >     Dim strFile As String
> > >> > >> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
> > >> > >> >     Const strcTail = ") ORDER BY Field1;"
> > >> > >> >     strFile = "C:\MyFolder\MyFile.xls"
> > >> > >> >     'Build up strWhere as shown in the example above:
> > >> > >> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & 
> > >> > >> > strcTail
> > >> > >> >     DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,
> > >> > >> > "Query1",
> > >> > >> > strFile, True
> > >> > >> >
> > >> > >> > Since you want to choose different formats, you code will need to 
> > >> > >> > use a
> > >> > >> > Select Case to choose TransferText, TransferDatabase, or whatever.
> > >> > >> >
> > >> > >> > If you want to give a File SaveAs dialog to choose the file name, 
> > >> > >> > use
> > >> > >> > the
> > >> > >> > code in this link to do that:
> > >> > >> >     http://www.mvps.org/access/api/api0001.htm
> > >> > >> >
> > >> > >> > -- 
> > >> > >> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > >> > >> > Tips for Access users - http://allenbrowne.com/tips.html
> > >> > >> > Reply to group, rather than allenbrowne at mvps dot org.
> > >> > >> >
> > >> > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> > >> > >> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
> > >> > >> > > Hi:
> > >> > >> > >
> > >> > >> > > I have a database 'query' that has about 200K rows of data.  My 
> > >> > >> > > user
> > >> > >> > > population, and any given time, is interested in working with
> > >> > >> > > (charting,
> > >> > >> > > excel'ing, powerpointing, etc) just a small subset of the data 
> > >> > >> > > (for
> > >> > >> > > example,
> > >> > >> > > all rows of data for a given month, to work on month end reports 
> > >> > >> > > (the
> > >> > >> > > file
> > >> > >> > > contains ~2 years of data).
> > >> > >> > >
> > >> > >> > > Each of these users sends me requests for excel files (sometimes
> > >> > >> > > access
> > >> > >> > > table data creation, as well) for their specific needs, based 
> > >> > >> > > upon a
> > >> > >> > > fairly
> > >> > >> > > limited set of what I call requirements and/or filters.  I 
> > >> > >> > > envision a
> > >> > >> > > "form"
> > >> > >> > > that they could use that wherein they would select specific 
> > >> > >> > > filters
> > >> > >> > > (like,
> > >> > >> > > MonthYY of a set of transactions) based upon data that actually
> > >> > >> > > exists
> > >> > >> > > (like
> > >> > >> > > a dropdown).  There would be, maybe, 3-4 of these, with the 
> > >> > >> > > option of
> > >> > >> > > exporting to an excel file, or two another access table (like a
> > >> > >> > > make-table
> > >> > >> > > query that they can change the data within, however often they 
> > >> > >> > > want).
> > >> > >> > >
> > >> > >> > > Something like this in a form (and I cannot show the dropdowns, 
> > >> > >> > > but
> > >> > >> > > you
> > >> > >> > > get
> > >> > >> > > the picture, maybe):
> > >> > >> > >
> > >> > >> > > Select MonthYear of Data to Export:  <dropdown choices>
> > >> > >> > > Select Company: <dropdown choices>
> > >> > >> > > Select Order Status: <dropdown choices>
> > >> > >> > >
> > >> > >> > > Output Format (check one):    Excel   X       Access
> > >> > >> > >
> > >> > >> > > (GO BUTTON/Cancel)
> > >> > >> > >
> > >> > >> > > Is there an "easy" way to do this (note:  I do not want them 
> > >> > >> > > using
> > >> > >> > > filters
> > >> > >> > > on the forms, etc...it needs to be check boxes and dropdown 
> > >> > >> > > choices
> > >> > >> > > or I
> > >> > >> > > will
> > >> > >> > > never be able to train them all).
> > >> > >> >
> > >> > >> >
> > >> >
> > >> >
> > >> > 
> > 
> > 
0
Utf
11/8/2007 4:33:01 PM
well...I may have solved my problem.   Basically, I just converted they query 
the detail rows were bound to, to a make table action query, and have indexes 
that, using the filter set up you have provide, eliminates the whole query 
process.  So, it brings up the 200K rows "instantly" on the initial form 
load, thus eliminating any concern  I had about the slowness of loading the 
form.

I just need to write the code to purge, and then reload the table each month 
(or add only new records...still ponder which.  But the filter form works 
GREAT (instantly) every time I change the filter.  Working on the export 
now...thanks Allen!

Patk

"PatK" wrote:

> Sorry...one possibly important clarification:  My "detail" data (that which I 
> am not asking to be presented initially, "IS" bound to a query, but my 
> presumption was since we set the filter parameter to false, that it would not 
> execute the query.  Maybe that is the problem?
> 
> "PatK" wrote:
> 
> > Allen: Could you tell me where I would add this code?  I added it to the form 
> > itself (where I have the cmdClick routines, etc), as well as in the routine 
> > where the filter where string is created.  But now, it seems to be doing two 
> > queries at open, instead of one, taking twice as long.
> > 
> > Maybe I am not understanding the filter process at all. When I open my form, 
> > I see a "running query" status bar pop up in the low left corner of Access.  
> > I presume this is not running a query and I have none bound to the form.  
> > Rather, that the "filter" itself is the query it is executing, and if so, and 
> > I am not asking for any data returned, why is it now executing twice, instead 
> > of once, since I added the code you mentioned below (thus, my thinking I 
> > stuck it in the wrong place).  It is true that since I added the code, I do 
> > not, in fact, get any detail rows (great), but then, what the heck are the 
> > queries doing.  My intent was to pop open the form, allow the user to set 
> > their filter parameters, and then execute the filter and present the data.  
> > Also interestingly, somehow, my filter only seems to work once in a row, now.
> > 
> > Sorry...and again, thanks for any help you may provide.
> > 
> > for the time being, I will revert my code back, until I figure out the issue.
> > 
> > Cheers!
> > 
> > patk
> > 
> > "Allen Browne" wrote:
> > 
> > > This will cause the form to load without any records:
> > > 
> > > Private Sub Form_Open(Cancel As Integer)
> > >     Me.Filter = "(False)"
> > >     Me.FilterOn = True
> > > End Sub
> > > 
> > > A WHERE clause (or filter) is ultimately an expression that evaluates to 
> > > True or False for each record. The expression above evaluates to False for 
> > > all records (since False is always False), so no records load.
> > > 
> > > If your source query is read-only, or you don't allow new records, you will 
> > > find that the Detail section of your form goes completely blank. If that's a 
> > > problem, see:
> > >     http://allenbrowne.com/casu-20.html
> > > 
> > > -- 
> > > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > > 
> > > "PatK" <PatK@discussions.microsoft.com> wrote in message
> > > news:20AAACBF-3138-4EAA-A338-325777BB5C4D@microsoft.com...
> > > >I think I have the filter form working ok (THANKS!).  Question:  Once I 
> > > >bound
> > > > my query to the form, it wants to populate the detail section of the form
> > > > immediately, at first opening of the form, before I have chosen any 
> > > > filters.
> > > > Any way to stop this behavior?  I want it to wait to always require filter
> > > > choices, before populating.
> > > >
> > > > As it is working now, it does, in fact, return the filtered record set as 
> > > > I
> > > > expect, but since I have 200K (and growing rows), the presenation of the
> > > > initial filter form take a bit longer than I am comfortable with.  Note: 
> > > > All
> > > > my filter fields are actually bound to very small tables created that 
> > > > contain
> > > > all possible filtered values, or short lists I have provide, so they won't
> > > > slow down the setup of the combo boxes, initially).
> > > >
> > > > Thanks again, should you have any ideas.....
> > > >
> > > > Patk
> > > >
> > > > "PatK" wrote:
> > > >
> > > >> Now I "am" embarrassed. LOL..thanks John
> > > >>
> > > >> "John Spencer" wrote:
> > > >>
> > > >> > Me refers to the form or report in which the code is executing.
> > > >> >
> > > >> > Me.txtFilterCity is short hand for referring to the control 
> > > >> > txtFilterCity
> > > >> > that exists on the current form.
> > > >> > [Forms]![NameOfCurrentForm]![txtFilterCity]
> > > >> >
> > > >> > -- 
> > > >> > John Spencer
> > > >> > Access MVP 2002-2005, 2007
> > > >> > Center for Health Program Development and Management
> > > >> > University of Maryland Baltimore County
> > > >> > ..
> > > >> >
> > > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> > > >> > news:9905558E-E192-4EFA-BA94-EEB7585488BB@microsoft.com...
> > > >> > > quick noob quiestion:  In your sample search code, you have code 
> > > >> > > like:
> > > >> > >
> > > >> > >    If Not IsNull(Me.txtFilterCity) Then
> > > >> > >        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) 
> > > >> > > AND
> > > >> > > "
> > > >> > >    End If
> > > >> > >
> > > >> > > Can you tell me what the "Me. prefix on search items like 
> > > >> > > txtFilterCity
> > > >> > > means?  As I look at the code, I am trying to to determine what 
> > > >> > > values
> > > >> > > might
> > > >> > > be in Me.txtFilterCity, and I am unable to determine the data source. 
> > > >> > > I
> > > >> > > am
> > > >> > > guessing the Me. is the key, but not sure.  I know...really DUMB 
> > > >> > > question,
> > > >> > > but I am new to coding in Access.
> > > >> > >
> > > >> > > "PatK" wrote:
> > > >> > >
> > > >> > >> You have captured what I am trying to do, very well.  Thank you for 
> > > >> > >> the
> > > >> > >> pointers and the sample code.  I shall give 'er a go and let you 
> > > >> > >> know how
> > > >> > >> it
> > > >> > >> works out!
> > > >> > >>
> > > >> > >> Thanks, Allen!
> > > >> > >>
> > > >> > >> Patk
> > > >> > >>
> > > >> > >> "Allen Browne" wrote:
> > > >> > >>
> > > >> > >> > Okay, there's 2 parts to this.
> > > >> > >> >
> > > >> > >> > Firstly, you are asking how to create a simple interface where 
> > > >> > >> > users
> > > >> > >> > can
> > > >> > >> > enter various criteria. Here's an example of how to do that:
> > > >> > >> >     Search form - Handle many optional criteria
> > > >> > >> > at:
> > > >> > >> >     http://allenbrowne.com/ser-62.html
> > > >> > >> >
> > > >> > >> > Once you have worked out how to build the Filter string, you can 
> > > >> > >> > apply
> > > >> > >> > it as
> > > >> > >> > the WHERE clause of a query, and then export the query. Typically 
> > > >> > >> > a
> > > >> > >> > query
> > > >> > >> > contains the same field list and ORDER BY clause, so it's just the
> > > >> > >> > WHERE
> > > >> > >> > clause that changes. You can do that like this:
> > > >> > >> >     Dim strWhere As String
> > > >> > >> >     Dim strFile As String
> > > >> > >> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 WHERE ("
> > > >> > >> >     Const strcTail = ") ORDER BY Field1;"
> > > >> > >> >     strFile = "C:\MyFolder\MyFile.xls"
> > > >> > >> >     'Build up strWhere as shown in the example above:
> > > >> > >> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere & 
> > > >> > >> > strcTail
> > > >> > >> >     DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,
> > > >> > >> > "Query1",
> > > >> > >> > strFile, True
> > > >> > >> >
> > > >> > >> > Since you want to choose different formats, you code will need to 
> > > >> > >> > use a
> > > >> > >> > Select Case to choose TransferText, TransferDatabase, or whatever.
> > > >> > >> >
> > > >> > >> > If you want to give a File SaveAs dialog to choose the file name, 
> > > >> > >> > use
> > > >> > >> > the
> > > >> > >> > code in this link to do that:
> > > >> > >> >     http://www.mvps.org/access/api/api0001.htm
> > > >> > >> >
> > > >> > >> > -- 
> > > >> > >> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > > >> > >> > Tips for Access users - http://allenbrowne.com/tips.html
> > > >> > >> > Reply to group, rather than allenbrowne at mvps dot org.
> > > >> > >> >
> > > >> > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> > > >> > >> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
> > > >> > >> > > Hi:
> > > >> > >> > >
> > > >> > >> > > I have a database 'query' that has about 200K rows of data.  My 
> > > >> > >> > > user
> > > >> > >> > > population, and any given time, is interested in working with
> > > >> > >> > > (charting,
> > > >> > >> > > excel'ing, powerpointing, etc) just a small subset of the data 
> > > >> > >> > > (for
> > > >> > >> > > example,
> > > >> > >> > > all rows of data for a given month, to work on month end reports 
> > > >> > >> > > (the
> > > >> > >> > > file
> > > >> > >> > > contains ~2 years of data).
> > > >> > >> > >
> > > >> > >> > > Each of these users sends me requests for excel files (sometimes
> > > >> > >> > > access
> > > >> > >> > > table data creation, as well) for their specific needs, based 
> > > >> > >> > > upon a
> > > >> > >> > > fairly
> > > >> > >> > > limited set of what I call requirements and/or filters.  I 
> > > >> > >> > > envision a
> > > >> > >> > > "form"
> > > >> > >> > > that they could use that wherein they would select specific 
> > > >> > >> > > filters
> > > >> > >> > > (like,
> > > >> > >> > > MonthYY of a set of transactions) based upon data that actually
> > > >> > >> > > exists
> > > >> > >> > > (like
> > > >> > >> > > a dropdown).  There would be, maybe, 3-4 of these, with the 
> > > >> > >> > > option of
> > > >> > >> > > exporting to an excel file, or two another access table (like a
> > > >> > >> > > make-table
> > > >> > >> > > query that they can change the data within, however often they 
> > > >> > >> > > want).
> > > >> > >> > >
> > > >> > >> > > Something like this in a form (and I cannot show the dropdowns, 
> > > >> > >> > > but
> > > >> > >> > > you
> > > >> > >> > > get
> > > >> > >> > > the picture, maybe):
> > > >> > >> > >
> > > >> > >> > > Select MonthYear of Data to Export:  <dropdown choices>
> > > >> > >> > > Select Company: <dropdown choices>
> > > >> > >> > > Select Order Status: <dropdown choices>
> > > >> > >> > >
> > > >> > >> > > Output Format (check one):    Excel   X       Access
> > > >> > >> > >
> > > >> > >> > > (GO BUTTON/Cancel)
> > > >> > >> > >
> > > >> > >> > > Is there an "easy" way to do this (note:  I do not want them 
> > > >> > >> > > using
> > > >> > >> > > filters
> > > >> > >> > > on the forms, etc...it needs to be check boxes and dropdown 
> > > >> > >> > > choices
> > > >> > >> > > or I
> > > >> > >> > > will
> > > >> > >> > > never be able to train them all).
> > > >> > >> >
> > > >> > >> >
> > > >> >
> > > >> >
> > > >> > 
> > > 
> > > 
0
Utf
11/8/2007 10:32:00 PM
Great. Sounds like you have a solution.

The filter code goes into the Open event of the form if you ever need to do 
it that way.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"PatK" <PatK@discussions.microsoft.com> wrote in message
news:34503211-C993-43F9-988D-79344081980E@microsoft.com...
> well...I may have solved my problem.   Basically, I just converted they 
> query
> the detail rows were bound to, to a make table action query, and have 
> indexes
> that, using the filter set up you have provide, eliminates the whole query
> process.  So, it brings up the 200K rows "instantly" on the initial form
> load, thus eliminating any concern  I had about the slowness of loading 
> the
> form.
>
> I just need to write the code to purge, and then reload the table each 
> month
> (or add only new records...still ponder which.  But the filter form works
> GREAT (instantly) every time I change the filter.  Working on the export
> now...thanks Allen!
>
> Patk
>
> "PatK" wrote:
>
>> Sorry...one possibly important clarification:  My "detail" data (that 
>> which I
>> am not asking to be presented initially, "IS" bound to a query, but my
>> presumption was since we set the filter parameter to false, that it would 
>> not
>> execute the query.  Maybe that is the problem?
>>
>> "PatK" wrote:
>>
>> > Allen: Could you tell me where I would add this code?  I added it to 
>> > the form
>> > itself (where I have the cmdClick routines, etc), as well as in the 
>> > routine
>> > where the filter where string is created.  But now, it seems to be 
>> > doing two
>> > queries at open, instead of one, taking twice as long.
>> >
>> > Maybe I am not understanding the filter process at all. When I open my 
>> > form,
>> > I see a "running query" status bar pop up in the low left corner of 
>> > Access.
>> > I presume this is not running a query and I have none bound to the 
>> > form.
>> > Rather, that the "filter" itself is the query it is executing, and if 
>> > so, and
>> > I am not asking for any data returned, why is it now executing twice, 
>> > instead
>> > of once, since I added the code you mentioned below (thus, my thinking 
>> > I
>> > stuck it in the wrong place).  It is true that since I added the code, 
>> > I do
>> > not, in fact, get any detail rows (great), but then, what the heck are 
>> > the
>> > queries doing.  My intent was to pop open the form, allow the user to 
>> > set
>> > their filter parameters, and then execute the filter and present the 
>> > data.
>> > Also interestingly, somehow, my filter only seems to work once in a 
>> > row, now.
>> >
>> > Sorry...and again, thanks for any help you may provide.
>> >
>> > for the time being, I will revert my code back, until I figure out the 
>> > issue.
>> >
>> > Cheers!
>> >
>> > patk
>> >
>> > "Allen Browne" wrote:
>> >
>> > > This will cause the form to load without any records:
>> > >
>> > > Private Sub Form_Open(Cancel As Integer)
>> > >     Me.Filter = "(False)"
>> > >     Me.FilterOn = True
>> > > End Sub
>> > >
>> > > A WHERE clause (or filter) is ultimately an expression that evaluates 
>> > > to
>> > > True or False for each record. The expression above evaluates to 
>> > > False for
>> > > all records (since False is always False), so no records load.
>> > >
>> > > If your source query is read-only, or you don't allow new records, 
>> > > you will
>> > > find that the Detail section of your form goes completely blank. If 
>> > > that's a
>> > > problem, see:
>> > >     http://allenbrowne.com/casu-20.html
>> > >
>> > > "PatK" <PatK@discussions.microsoft.com> wrote in message
>> > > news:20AAACBF-3138-4EAA-A338-325777BB5C4D@microsoft.com...
>> > > >I think I have the filter form working ok (THANKS!).  Question: 
>> > > >Once I
>> > > >bound
>> > > > my query to the form, it wants to populate the detail section of 
>> > > > the form
>> > > > immediately, at first opening of the form, before I have chosen any
>> > > > filters.
>> > > > Any way to stop this behavior?  I want it to wait to always require 
>> > > > filter
>> > > > choices, before populating.
>> > > >
>> > > > As it is working now, it does, in fact, return the filtered record 
>> > > > set as
>> > > > I
>> > > > expect, but since I have 200K (and growing rows), the presenation 
>> > > > of the
>> > > > initial filter form take a bit longer than I am comfortable with. 
>> > > > Note:
>> > > > All
>> > > > my filter fields are actually bound to very small tables created 
>> > > > that
>> > > > contain
>> > > > all possible filtered values, or short lists I have provide, so 
>> > > > they won't
>> > > > slow down the setup of the combo boxes, initially).
>> > > >
>> > > > Thanks again, should you have any ideas.....
>> > > >
>> > > > Patk
>> > > >
>> > > > "PatK" wrote:
>> > > >
>> > > >> Now I "am" embarrassed. LOL..thanks John
>> > > >>
>> > > >> "John Spencer" wrote:
>> > > >>
>> > > >> > Me refers to the form or report in which the code is executing.
>> > > >> >
>> > > >> > Me.txtFilterCity is short hand for referring to the control
>> > > >> > txtFilterCity
>> > > >> > that exists on the current form.
>> > > >> > [Forms]![NameOfCurrentForm]![txtFilterCity]
>> > > >> >
>> > > >> > -- 
>> > > >> > John Spencer
>> > > >> > Access MVP 2002-2005, 2007
>> > > >> > Center for Health Program Development and Management
>> > > >> > University of Maryland Baltimore County
>> > > >> > ..
>> > > >> >
>> > > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
>> > > >> > news:9905558E-E192-4EFA-BA94-EEB7585488BB@microsoft.com...
>> > > >> > > quick noob quiestion:  In your sample search code, you have 
>> > > >> > > code
>> > > >> > > like:
>> > > >> > >
>> > > >> > >    If Not IsNull(Me.txtFilterCity) Then
>> > > >> > >        strWhere = strWhere & "([City] = """ & Me.txtFilterCity 
>> > > >> > > & """)
>> > > >> > > AND
>> > > >> > > "
>> > > >> > >    End If
>> > > >> > >
>> > > >> > > Can you tell me what the "Me. prefix on search items like
>> > > >> > > txtFilterCity
>> > > >> > > means?  As I look at the code, I am trying to to determine 
>> > > >> > > what
>> > > >> > > values
>> > > >> > > might
>> > > >> > > be in Me.txtFilterCity, and I am unable to determine the data 
>> > > >> > > source.
>> > > >> > > I
>> > > >> > > am
>> > > >> > > guessing the Me. is the key, but not sure.  I know...really 
>> > > >> > > DUMB
>> > > >> > > question,
>> > > >> > > but I am new to coding in Access.
>> > > >> > >
>> > > >> > > "PatK" wrote:
>> > > >> > >
>> > > >> > >> You have captured what I am trying to do, very well.  Thank 
>> > > >> > >> you for
>> > > >> > >> the
>> > > >> > >> pointers and the sample code.  I shall give 'er a go and let 
>> > > >> > >> you
>> > > >> > >> know how
>> > > >> > >> it
>> > > >> > >> works out!
>> > > >> > >>
>> > > >> > >> Thanks, Allen!
>> > > >> > >>
>> > > >> > >> Patk
>> > > >> > >>
>> > > >> > >> "Allen Browne" wrote:
>> > > >> > >>
>> > > >> > >> > Okay, there's 2 parts to this.
>> > > >> > >> >
>> > > >> > >> > Firstly, you are asking how to create a simple interface 
>> > > >> > >> > where
>> > > >> > >> > users
>> > > >> > >> > can
>> > > >> > >> > enter various criteria. Here's an example of how to do 
>> > > >> > >> > that:
>> > > >> > >> >     Search form - Handle many optional criteria
>> > > >> > >> > at:
>> > > >> > >> >     http://allenbrowne.com/ser-62.html
>> > > >> > >> >
>> > > >> > >> > Once you have worked out how to build the Filter string, 
>> > > >> > >> > you can
>> > > >> > >> > apply
>> > > >> > >> > it as
>> > > >> > >> > the WHERE clause of a query, and then export the query. 
>> > > >> > >> > Typically
>> > > >> > >> > a
>> > > >> > >> > query
>> > > >> > >> > contains the same field list and ORDER BY clause, so it's 
>> > > >> > >> > just the
>> > > >> > >> > WHERE
>> > > >> > >> > clause that changes. You can do that like this:
>> > > >> > >> >     Dim strWhere As String
>> > > >> > >> >     Dim strFile As String
>> > > >> > >> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 
>> > > >> > >> > WHERE ("
>> > > >> > >> >     Const strcTail = ") ORDER BY Field1;"
>> > > >> > >> >     strFile = "C:\MyFolder\MyFile.xls"
>> > > >> > >> >     'Build up strWhere as shown in the example above:
>> > > >> > >> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere 
>> > > >> > >> > &
>> > > >> > >> > strcTail
>> > > >> > >> >     DoCmd.TransferSpreadsheet 
>> > > >> > >> > acExport,acSpreadsheetTypeExcel9,
>> > > >> > >> > "Query1",
>> > > >> > >> > strFile, True
>> > > >> > >> >
>> > > >> > >> > Since you want to choose different formats, you code will 
>> > > >> > >> > need to
>> > > >> > >> > use a
>> > > >> > >> > Select Case to choose TransferText, TransferDatabase, or 
>> > > >> > >> > whatever.
>> > > >> > >> >
>> > > >> > >> > If you want to give a File SaveAs dialog to choose the file 
>> > > >> > >> > name,
>> > > >> > >> > use
>> > > >> > >> > the
>> > > >> > >> > code in this link to do that:
>> > > >> > >> >     http://www.mvps.org/access/api/api0001.htm
>> > > >> > >> >
>> > > >> > >> > -- 
>> > > >> > >> > Allen Browne - Microsoft MVP.  Perth, Western Australia
>> > > >> > >> > Tips for Access users - http://allenbrowne.com/tips.html
>> > > >> > >> > Reply to group, rather than allenbrowne at mvps dot org.
>> > > >> > >> >
>> > > >> > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
>> > > >> > >> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
>> > > >> > >> > > Hi:
>> > > >> > >> > >
>> > > >> > >> > > I have a database 'query' that has about 200K rows of 
>> > > >> > >> > > data.  My
>> > > >> > >> > > user
>> > > >> > >> > > population, and any given time, is interested in working 
>> > > >> > >> > > with
>> > > >> > >> > > (charting,
>> > > >> > >> > > excel'ing, powerpointing, etc) just a small subset of the 
>> > > >> > >> > > data
>> > > >> > >> > > (for
>> > > >> > >> > > example,
>> > > >> > >> > > all rows of data for a given month, to work on month end 
>> > > >> > >> > > reports
>> > > >> > >> > > (the
>> > > >> > >> > > file
>> > > >> > >> > > contains ~2 years of data).
>> > > >> > >> > >
>> > > >> > >> > > Each of these users sends me requests for excel files 
>> > > >> > >> > > (sometimes
>> > > >> > >> > > access
>> > > >> > >> > > table data creation, as well) for their specific needs, 
>> > > >> > >> > > based
>> > > >> > >> > > upon a
>> > > >> > >> > > fairly
>> > > >> > >> > > limited set of what I call requirements and/or filters. 
>> > > >> > >> > > I
>> > > >> > >> > > envision a
>> > > >> > >> > > "form"
>> > > >> > >> > > that they could use that wherein they would select 
>> > > >> > >> > > specific
>> > > >> > >> > > filters
>> > > >> > >> > > (like,
>> > > >> > >> > > MonthYY of a set of transactions) based upon data that 
>> > > >> > >> > > actually
>> > > >> > >> > > exists
>> > > >> > >> > > (like
>> > > >> > >> > > a dropdown).  There would be, maybe, 3-4 of these, with 
>> > > >> > >> > > the
>> > > >> > >> > > option of
>> > > >> > >> > > exporting to an excel file, or two another access table 
>> > > >> > >> > > (like a
>> > > >> > >> > > make-table
>> > > >> > >> > > query that they can change the data within, however often 
>> > > >> > >> > > they
>> > > >> > >> > > want).
>> > > >> > >> > >
>> > > >> > >> > > Something like this in a form (and I cannot show the 
>> > > >> > >> > > dropdowns,
>> > > >> > >> > > but
>> > > >> > >> > > you
>> > > >> > >> > > get
>> > > >> > >> > > the picture, maybe):
>> > > >> > >> > >
>> > > >> > >> > > Select MonthYear of Data to Export:  <dropdown choices>
>> > > >> > >> > > Select Company: <dropdown choices>
>> > > >> > >> > > Select Order Status: <dropdown choices>
>> > > >> > >> > >
>> > > >> > >> > > Output Format (check one):    Excel   X       Access
>> > > >> > >> > >
>> > > >> > >> > > (GO BUTTON/Cancel)
>> > > >> > >> > >
>> > > >> > >> > > Is there an "easy" way to do this (note:  I do not want 
>> > > >> > >> > > them
>> > > >> > >> > > using
>> > > >> > >> > > filters
>> > > >> > >> > > on the forms, etc...it needs to be check boxes and 
>> > > >> > >> > > dropdown
>> > > >> > >> > > choices
>> > > >> > >> > > or I
>> > > >> > >> > > will
>> > > >> > >> > > never be able to train them all).

0
Allen
11/8/2007 11:57:49 PM
I got both the excel and access exports working. Thanks a million for your 
help.  One final question:  After I have "exported"  a tbl to a database, I 
have, ocassionally, gotten an Access error telling me I do not have exclusive 
access to the database.  I am not sure if it is referring to my source DB, or 
my target db.  Is there some hygienic cleanup I should be doing after an 
Access export (or even excel, for that matter).

"Allen Browne" wrote:

> Great. Sounds like you have a solution.
> 
> The filter code goes into the Open event of the form if you ever need to do 
> it that way.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "PatK" <PatK@discussions.microsoft.com> wrote in message
> news:34503211-C993-43F9-988D-79344081980E@microsoft.com...
> > well...I may have solved my problem.   Basically, I just converted they 
> > query
> > the detail rows were bound to, to a make table action query, and have 
> > indexes
> > that, using the filter set up you have provide, eliminates the whole query
> > process.  So, it brings up the 200K rows "instantly" on the initial form
> > load, thus eliminating any concern  I had about the slowness of loading 
> > the
> > form.
> >
> > I just need to write the code to purge, and then reload the table each 
> > month
> > (or add only new records...still ponder which.  But the filter form works
> > GREAT (instantly) every time I change the filter.  Working on the export
> > now...thanks Allen!
> >
> > Patk
> >
> > "PatK" wrote:
> >
> >> Sorry...one possibly important clarification:  My "detail" data (that 
> >> which I
> >> am not asking to be presented initially, "IS" bound to a query, but my
> >> presumption was since we set the filter parameter to false, that it would 
> >> not
> >> execute the query.  Maybe that is the problem?
> >>
> >> "PatK" wrote:
> >>
> >> > Allen: Could you tell me where I would add this code?  I added it to 
> >> > the form
> >> > itself (where I have the cmdClick routines, etc), as well as in the 
> >> > routine
> >> > where the filter where string is created.  But now, it seems to be 
> >> > doing two
> >> > queries at open, instead of one, taking twice as long.
> >> >
> >> > Maybe I am not understanding the filter process at all. When I open my 
> >> > form,
> >> > I see a "running query" status bar pop up in the low left corner of 
> >> > Access.
> >> > I presume this is not running a query and I have none bound to the 
> >> > form.
> >> > Rather, that the "filter" itself is the query it is executing, and if 
> >> > so, and
> >> > I am not asking for any data returned, why is it now executing twice, 
> >> > instead
> >> > of once, since I added the code you mentioned below (thus, my thinking 
> >> > I
> >> > stuck it in the wrong place).  It is true that since I added the code, 
> >> > I do
> >> > not, in fact, get any detail rows (great), but then, what the heck are 
> >> > the
> >> > queries doing.  My intent was to pop open the form, allow the user to 
> >> > set
> >> > their filter parameters, and then execute the filter and present the 
> >> > data.
> >> > Also interestingly, somehow, my filter only seems to work once in a 
> >> > row, now.
> >> >
> >> > Sorry...and again, thanks for any help you may provide.
> >> >
> >> > for the time being, I will revert my code back, until I figure out the 
> >> > issue.
> >> >
> >> > Cheers!
> >> >
> >> > patk
> >> >
> >> > "Allen Browne" wrote:
> >> >
> >> > > This will cause the form to load without any records:
> >> > >
> >> > > Private Sub Form_Open(Cancel As Integer)
> >> > >     Me.Filter = "(False)"
> >> > >     Me.FilterOn = True
> >> > > End Sub
> >> > >
> >> > > A WHERE clause (or filter) is ultimately an expression that evaluates 
> >> > > to
> >> > > True or False for each record. The expression above evaluates to 
> >> > > False for
> >> > > all records (since False is always False), so no records load.
> >> > >
> >> > > If your source query is read-only, or you don't allow new records, 
> >> > > you will
> >> > > find that the Detail section of your form goes completely blank. If 
> >> > > that's a
> >> > > problem, see:
> >> > >     http://allenbrowne.com/casu-20.html
> >> > >
> >> > > "PatK" <PatK@discussions.microsoft.com> wrote in message
> >> > > news:20AAACBF-3138-4EAA-A338-325777BB5C4D@microsoft.com...
> >> > > >I think I have the filter form working ok (THANKS!).  Question: 
> >> > > >Once I
> >> > > >bound
> >> > > > my query to the form, it wants to populate the detail section of 
> >> > > > the form
> >> > > > immediately, at first opening of the form, before I have chosen any
> >> > > > filters.
> >> > > > Any way to stop this behavior?  I want it to wait to always require 
> >> > > > filter
> >> > > > choices, before populating.
> >> > > >
> >> > > > As it is working now, it does, in fact, return the filtered record 
> >> > > > set as
> >> > > > I
> >> > > > expect, but since I have 200K (and growing rows), the presenation 
> >> > > > of the
> >> > > > initial filter form take a bit longer than I am comfortable with. 
> >> > > > Note:
> >> > > > All
> >> > > > my filter fields are actually bound to very small tables created 
> >> > > > that
> >> > > > contain
> >> > > > all possible filtered values, or short lists I have provide, so 
> >> > > > they won't
> >> > > > slow down the setup of the combo boxes, initially).
> >> > > >
> >> > > > Thanks again, should you have any ideas.....
> >> > > >
> >> > > > Patk
> >> > > >
> >> > > > "PatK" wrote:
> >> > > >
> >> > > >> Now I "am" embarrassed. LOL..thanks John
> >> > > >>
> >> > > >> "John Spencer" wrote:
> >> > > >>
> >> > > >> > Me refers to the form or report in which the code is executing.
> >> > > >> >
> >> > > >> > Me.txtFilterCity is short hand for referring to the control
> >> > > >> > txtFilterCity
> >> > > >> > that exists on the current form.
> >> > > >> > [Forms]![NameOfCurrentForm]![txtFilterCity]
> >> > > >> >
> >> > > >> > -- 
> >> > > >> > John Spencer
> >> > > >> > Access MVP 2002-2005, 2007
> >> > > >> > Center for Health Program Development and Management
> >> > > >> > University of Maryland Baltimore County
> >> > > >> > ..
> >> > > >> >
> >> > > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> >> > > >> > news:9905558E-E192-4EFA-BA94-EEB7585488BB@microsoft.com...
> >> > > >> > > quick noob quiestion:  In your sample search code, you have 
> >> > > >> > > code
> >> > > >> > > like:
> >> > > >> > >
> >> > > >> > >    If Not IsNull(Me.txtFilterCity) Then
> >> > > >> > >        strWhere = strWhere & "([City] = """ & Me.txtFilterCity 
> >> > > >> > > & """)
> >> > > >> > > AND
> >> > > >> > > "
> >> > > >> > >    End If
> >> > > >> > >
> >> > > >> > > Can you tell me what the "Me. prefix on search items like
> >> > > >> > > txtFilterCity
> >> > > >> > > means?  As I look at the code, I am trying to to determine 
> >> > > >> > > what
> >> > > >> > > values
> >> > > >> > > might
> >> > > >> > > be in Me.txtFilterCity, and I am unable to determine the data 
> >> > > >> > > source.
> >> > > >> > > I
> >> > > >> > > am
> >> > > >> > > guessing the Me. is the key, but not sure.  I know...really 
> >> > > >> > > DUMB
> >> > > >> > > question,
> >> > > >> > > but I am new to coding in Access.
> >> > > >> > >
> >> > > >> > > "PatK" wrote:
> >> > > >> > >
> >> > > >> > >> You have captured what I am trying to do, very well.  Thank 
> >> > > >> > >> you for
> >> > > >> > >> the
> >> > > >> > >> pointers and the sample code.  I shall give 'er a go and let 
> >> > > >> > >> you
> >> > > >> > >> know how
> >> > > >> > >> it
> >> > > >> > >> works out!
> >> > > >> > >>
> >> > > >> > >> Thanks, Allen!
> >> > > >> > >>
> >> > > >> > >> Patk
> >> > > >> > >>
> >> > > >> > >> "Allen Browne" wrote:
> >> > > >> > >>
> >> > > >> > >> > Okay, there's 2 parts to this.
> >> > > >> > >> >
> >> > > >> > >> > Firstly, you are asking how to create a simple interface 
> >> > > >> > >> > where
> >> > > >> > >> > users
> >> > > >> > >> > can
> >> > > >> > >> > enter various criteria. Here's an example of how to do 
> >> > > >> > >> > that:
> >> > > >> > >> >     Search form - Handle many optional criteria
> >> > > >> > >> > at:
> >> > > >> > >> >     http://allenbrowne.com/ser-62.html
> >> > > >> > >> >
> >> > > >> > >> > Once you have worked out how to build the Filter string, 
> >> > > >> > >> > you can
> >> > > >> > >> > apply
> >> > > >> > >> > it as
> >> > > >> > >> > the WHERE clause of a query, and then export the query. 
> >> > > >> > >> > Typically
> >> > > >> > >> > a
> >> > > >> > >> > query
> >> > > >> > >> > contains the same field list and ORDER BY clause, so it's 
> >> > > >> > >> > just the
> >> > > >> > >> > WHERE
> >> > > >> > >> > clause that changes. You can do that like this:
> >> > > >> > >> >     Dim strWhere As String
> >> > > >> > >> >     Dim strFile As String
> >> > > >> > >> >     Const strcStub = "SELECT Field1, Field2 FROM Table1 
> >> > > >> > >> > WHERE ("
> >> > > >> > >> >     Const strcTail = ") ORDER BY Field1;"
> >> > > >> > >> >     strFile = "C:\MyFolder\MyFile.xls"
> >> > > >> > >> >     'Build up strWhere as shown in the example above:
> >> > > >> > >> >     CurrentDb.QueryDefs("Query1").SQL = strcStub & strWhere 
> >> > > >> > >> > &
> >> > > >> > >> > strcTail
> >> > > >> > >> >     DoCmd.TransferSpreadsheet 
> >> > > >> > >> > acExport,acSpreadsheetTypeExcel9,
> >> > > >> > >> > "Query1",
> >> > > >> > >> > strFile, True
> >> > > >> > >> >
> >> > > >> > >> > Since you want to choose different formats, you code will 
> >> > > >> > >> > need to
> >> > > >> > >> > use a
> >> > > >> > >> > Select Case to choose TransferText, TransferDatabase, or 
> >> > > >> > >> > whatever.
> >> > > >> > >> >
> >> > > >> > >> > If you want to give a File SaveAs dialog to choose the file 
> >> > > >> > >> > name,
> >> > > >> > >> > use
> >> > > >> > >> > the
> >> > > >> > >> > code in this link to do that:
> >> > > >> > >> >     http://www.mvps.org/access/api/api0001.htm
> >> > > >> > >> >
> >> > > >> > >> > -- 
> >> > > >> > >> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> >> > > >> > >> > Tips for Access users - http://allenbrowne.com/tips.html
> >> > > >> > >> > Reply to group, rather than allenbrowne at mvps dot org.
> >> > > >> > >> >
> >> > > >> > >> > "PatK" <PatK@discussions.microsoft.com> wrote in message
> >> > > >> > >> > news:4DE3BA67-94C2-42DD-AB2B-E13772FE4AE6@microsoft.com...
> >> > > >> > >> > > Hi:
> >> > > >> > >> > >
> >> > > >> > >> > > I have a database 'query' that has about 200K rows of 
> >> > > >> > >> > > data.  My
> >> > > >> > >> > > user
> >> > > >> > >> > > population, and any given time, is interested in working 
> >> > > >> > >> > > with
> >> > > >> > >> > > (charting,
> >> > > >> > >> > > excel'ing, powerpointing, etc) just a small subset of the 
> >> > > >> > >> > > data
> >> > > >> > >> > > (for
> >> > > >> > >> > > example,
> >> > > >> > >> > > all rows of data for a given month, to work on month end 
> >> > > >> > >> > > reports
> >> > > >> > >> > > (the
> >> > > >> > >> > > file
> >> > > >> > >> > > contains ~2 years of data).
> >> > > >> > >> > >
> >> > > >> > >> > > Each of these users sends me requests for excel files 
> >> > > >> > >> > > (sometimes
> >> > > >> > >> > > access
> >> > > >> > >> > > table data creation, as well) for their specific needs, 
> >> > > >> > >> > > based
> >> > > >> > >> > > upon a
> >> > > >> > >> > > fairly
> >> > > >> > >> > > limited set of what I call requirements and/or filters. 
> >> > > >> > >> > > I
> >> > > >> > >> > > envision a
> >> > > >> > >> > > "form"
> >> > > >> > >> > > that they could use that wherein they would select 
> >> > > >> > >> > > specific
> >> > > >> > >> > > filters
> >> > > >> > >> > > (like,
> >> > > >> > >> > > MonthYY of a set of transactions) based upon data that 
> >> > > >> > >> > > actually
> >> > > >> > >> > > exists
> >> > > >> > >> > > (like
> >> > > >> > >> > > a dropdown).  There would be, maybe, 3-4 of these, with 
> >> > > >> > >> > > the
> >> > > >> > >> > > option of
> >> > > >> > >> > > exporting to an excel file, or two another access table 
0
Utf
11/9/2007 10:29:02 PM
Oh! Here was the code for the Access export.  Still need to do some cleanup, 
but you get the drift.  Note that the value strCurrentFilter is a Public 
variable that is created in the form processing.  Once I have created you 
strWhere string, I initialize strCurrentFilter with that value, and then when 
the user hits the export to access button, they end up flowing thru the codee 
below.  Anyway..thanks!

Patk


Sub ExportFilteredDataAccess()
'---------------------------------------------------------------------------
' Author:  Pat Klocke
' Version: 1.0
' Datecode: 2007-11-09
'---------------------------------------------------------------------------

Dim sFname As String  'String to store selected file
Dim sWSheet As String 'String to store worksheet to export data in to
Dim sTable As String  'String to store name of table to we are export from
Dim lngFlags As Long  'flag variable
Dim sFilter As String 'Filter for file open routine to limit to xls files
Dim sTableName As String 'Store name of user input table name
 
sWSheet = "Filtered Tickets"                      ' Name of tab in excel 
spreadsheet
sTable = "tbl All Tickets All Types Transformed"  ' Table containing 
potential filtered tickets for export

Const strcStub = "SELECT * FROM [tbl All Tickets All Types Transformed] 
WHERE ("   ' Build front part of query
Const strcTail = ") ORDER BY [Service Call ID];"                             
      ' Build tail of query

' --------------------------------------------------------------------------
' Select the output MS Access target file
' --------------------------------------------------------------------------
    
    MsgBox "In following dialog box, identify the target MS Access DB file 
in which to place exported table."

    sFilter = AddFilterItem(sFilter, "Excel Files (*.XLS)", "*.XLS")
    sFilter = AddFilterItem(sFilter, "Access Files (*.mda, *.mdb)", 
"*.MDA;*.MDB")
    sFilter = AddFilterItem(sFilter, "dBASE Files (*.dbf)", "*.DBF")
    sFilter = AddFilterItem(sFilter, "Text Files (*.txt)", "*.TXT")
    sFilter = AddFilterItem(sFilter, "All Files (*.*)", "*.*")

    sFname = CommonFileOpenSave( _
                Filter:=sFilter, OpenFile:=True, _
                DialogTitle:="Identify output MS Access DB file...", _
                Flags:=ahtOFN_HIDEREADONLY)
    
    sTableName = InputBox("Enter a name for the export Ticket Table: ", 
"Enter Table Name", "Custom Export")

    If IsNull(strCurrentFilter) Then
       GoTo StringError
    Else
       If IsNull(sFname) Then
          GoTo StringError
       End If
    End If
    CurrentDb.QueryDefs("qry 9999 Export").SQL = strcStub & strCurrentFilter 
& strcTail    'Build up strWhere as shown in the example above:
    DoCmd.TransferDatabase acExport, "Microsoft Access", sFname, acTable, 
"qry 9999 Export", sTableName
    
    MsgBox "Access Table Export Complete.  Table " & sTableName & " was 
stored in " & sFname & ".  Please make a note of this information."
    Exit Sub
StringError:
     MsgBox "No Table exported as the Save File is invalid, or Filter does 
not exist. Please Try Again. [EX002]"
End Sub
0
Utf
11/9/2007 10:40:01 PM
Reply:

Similar Artilces:

How to I advance filter to remove rows containing partic string of text?
Is this possible? I know how to filter to include strings of text but I don't know how to filter so that rows containing a certain email address domain name are not visible in the filetered results. Many thanks if anyone's able to assist! Dave Hi one way: use a helper column with a formula like =IF(ISNUMBER(FIND("domain.com",A1)),1,0) and filter with this column >-----Original Message----- >Is this possible? > >I know how to filter to include strings of text but I don't know how >to filter so that rows containing a certain email address domain name &...

Excel Export to MYOB?
Hi Guy's Is it at all possible to export only certain data from xl spreadsheet into MYOB? If so where can I find such info. -- KRAMER Excel cannot export to MYOB format. You should look into the formats that MYOB can import, and see if any of the those can be exported to by Excel. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "KRAMER" <KRAMER@discussions.microsoft.com> wrote in message news:2B789970-5C9F-4830-8B25-96C8FC9CB3DB@microsoft.com... > Hi Guy's > > Is it at all possible to export only ce...

Filter #21
If I have cells with the text strings (ex: One cell will say "Termed w no filings 05" a cell below might say "Temed w/ finals 05") I need t filter through the cells and somehow get all the ones that say "finals somewhere in the cell and highlight them or somehow group the together. Any help would be appreciated..Thanks -- Delain ----------------------------------------------------------------------- Delaina's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2415 View this thread: http://www.excelforum.com/showthread.php?threadid=48489 Use...

Export MS access data to XML
Hello everyone, I am currently working on a microsoft access project, I need to export the data to an XML file, I already have a schema that I need to use. Can you please tell me how to use the existing XSD and XLST files. Thank You -- armele look Take a look at the ExportXML and TransformXML methods. Steve "Armele look" wrote: > Hello everyone, > > I am currently working on a microsoft access project, I need to export the > data to an XML file, I already have a schema that I need to use. Can you > please tell me how to use the existing XSD and XLST files....

Exported Data to excel appearing wrong in Columns
Exported data into an excel spreadsheet from an Access database. The database reports the informaton correctly in reports, but to excel, it doesn't display all the columns correctly. The format for the data is dash, number, alpha, number, number, number, -1A111. I tried formatting it but it made it worse. Can anyone let me know what I may need to do to fix this? How does it display? Show us what it should look like (as in the Access report) and what it looks like in the Excel import. You've mentioned that columns (plural) don't display correctly, but you've only give...

Filtering a Crosstab Query from Combo Boxes on Unbound Form
Hello Everyone, I have a crosstab query in my database that I would like to filter based upon user selections from combo boxes on an unbound form. I would like for the users to be able to make a selection from four combo boxes (cboSelectWeek, cboSelectMonth, cboSelectGroup and cboSelectFocal) and then push a button to see a crosstab query reflecting their selections; I would like for the query to export to excel and be saved as a file of the user's choosing. Also, the user should be able to leave some of the combo boxes null. I have been reading on this site (which has taught...

Internet Activex control with with Visual Filters and Transitions effect applicable
Hello, I write Internet MFC ActiveX control to display image, and try to apply in the web page: ---------------------------------------------------------------------------------------------- <html> <head><title>Test page</title></head> <body> <SCRIPT> var bTranState = 0; function fnToggle() { oDIV2.filters[0].Apply(); if (bTranState) { bTranState = 0; olive.style.visibility="hidden"; } else { translate = 1; olive.style.visibility="visible"; } olive.filters[0].Play(); } </SCRIPT> <BUTTON unclick="e...

Sum of values in a form
Folks, I have read numerous threads on this topic, and understand that in order for my Sum() to work I need to ensure all references are made back to the Record Source. I have endeavoured to do this, but am still getting #Error. I am trying to Sum the revenue each tenant provides in a given date range, to give a total revenue figure. If the date range is a whole month, then the expression just takes the value of Rent PCM, and not (Rent PCM*(12/52)) * No. of Days, which would give some unusual results. My Expression looks like this: =Sum(Nz(IIf([Short Let]=-1,[Rent pcm],IIf...

Exporting Customer Mailing List Data From RMS
The objective is to export customer mailing list data in a format useable to a mailing service company. When you use the Mailing Labels option within Crystal Reports, the city/state/zip data is contained within one field. The preferred format is for that data to be in three separate fields, so that ZIP sorting can be performed. Can someone guide me through the process I need to follow to make this happen? Thanks. Do a customer list report (you will have to add the address fields by editing it with a text editor) from the reports menu and export it to CSV or comma seperated. &quo...

Export GAL to a "csv" file for Internet POP3/IMAP users
Hi all, Can someone suggest me how to export our AD Global Address List to a csv file so that our Internet users connecting to our corporate email server via POP3/IMAP can download the Address List (say from the Webserver Location) and use it. Regards, Sudeep The utility you are looking for is csvde.exe. http://www.microsoft.com/resources/documentation/WindowsServ/2003/enterprise/proddocs/en-us/Default.asp?url=/resources/documentation/WindowsServ/2003/enterprise/proddocs/en-us/csvde.asp or, if you prefer: http://snipurl.com/7kmr William Sudeep Batra wrote: > Hi all, > &g...

Pivot Table Filter Formula
I have a set of payroll data that I need to put into a pivot table, do some calculations, and filter but I can't figure it out. Here is what I have: Name Date Hours John Doe 1/1/10 10 John Doe 1/2/10 8 Jane Doe 1/1/10 9 and so on I want to summarize this data by week (can use the weeknum formula) and only report people who work > a certain number of hours a week. So my pivot table needs to look like this (assume I only want to see people working > 50 hours) Name Week Number Total Hours(using pivot to calc) John Doe 1 ...

Enumerate Controls on a Form
I would like code that would enumerate all controls on a form returning their names and types. Thank you. You should be able to figure it out from the code at: http://www.datastrat.com/Download/FixNames2K.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "tcb" <tbenson@mn.rr.com> wrote in message news:1194476215.293996.56820@o3g2000hsb.googlegroups.com... >I would like code that would enumerate all controls on a form > returning their names and types. > > Thank you. > Try the TOOLS - Analyze - Doc...

Form Question Please
What does it mean when you open a form that collects data and it has a pencil icon on the upper left hand corner when you enter data? How do you enable that pencil icon on the form? Thank you in advance for any help with this. The little pencil simply means that the record is what we call in MS access as a dirty record. This simply means that the record you were viewing has been modified, and access is ready to write that reocrd (form data) back to disk the table that the form is based on. the writing of data will occur when you leave the record, or simply close the form If you mov...

Access 2007 Editing Form Design
Hi - I have just baught Access 2007 and I'm trying to edit a form - but all the fieids seem to be 'grouped' together - so when I change the length, or move one, they all move. Does anyone know how to unlock them. Thanks See this page on my site for assistance: http://accessjunkie.com/faq_12.aspx -- Jeff Conrad - Access Junkie - MVP Alumni SDET - XAS Services - Microsoft Corporation Co-author - Microsoft Office Access 2007 Inside Out Presenter - Microsoft Access 2007 Essentials http://www.accessmvp.com/JConrad/accessjunkie.html Access 2007 Info: http://www.AccessJunkie.com -...

Updating Contacts via Email
Can anyone recommend software that will send info update requests to everyone in my contacts folder and integrate their replies automatically? I found a program called Contacts Clinic, but I can't find any sort of review of it. Does it work? Is there a better alternative? I'm not looking forward to the prospect of calling every number in there to update manually. ...

Outlook 2003 Form Design Mode Bug: Solutions?
Is there a solution to the bug outlined in this KB article: http://support.microsoft.com/kb/823133/en-us ? KZ No. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginner's Guide to Microsoft Office Outlook 2003 Reminder Manager, Extended Reminders, Attachment Options http://www.slovaktech.com/products.htm "Ken Zenachon" <imafellow@gmail.com> wrote in message news:1176480066.954591.89200@p77g2000hsh.googlegroups.com... > Is there a solution to the bug outlined in this KB article: > http://support.microsoft.com/kb/823133/en-us > ? &g...

Graphics Filter
Windows XP, Publisher 2000 Can not insert a .jpg image. From reading several items, I have cleared all temporary internet files and have copied jpegim32.flt from the CD to the proper folder. I have tried several different .jpg files. Earl Are you inserting from the Web? Check your firewall, see if it is preventing insertion. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Earl Partridge" <earlpNG@pearnet.com> wrote in message news:I_Mzh.1001$Jl.196@newsread3.news.pas.earthlink.net... > Windows XP, Publisher 2...

Create Account via workflow
Hi, Is it possible to create an account from an E-Mail that comes into a queue in CRM? We have the need to automatically create an account from an Email that is going to arrive in a Queue created in CRM. The email will contain Address details, Company Names and a few other details needed for the creation of the account. If this is possible, is there any way that CRM could see when an email comes back in from this account to the queue, not to create another clone of this account. Thanks in Advance Simon Hi Simon, Yes it is very much possible but requires custome .net CRM SDK deve...

How can I import from many Word Forms into one Excel Spreadsheet
What I ultimately need to do is take information from 100's of Word forms (completed electronically), and compile it all in to one Excel database. I have tried to save the MS Word form (.dot) to a .txt file and then open the .txt file in Excel. I have also tried to do a query to import the .txt file, but I don't know to compile from many different forms into one database. I am running in circles trying to use the help files included with the software. Can anyone help? Very grateful. Julie 1. Save all the completed Word forms as text (in Word, choose Tools>Options, and...

I'm sending out an e-mail invite and want them to reply in a form
I have a holiday invite to a pot-luck. I want people to: 1) RSVP 2) indicate on a form using a check box what they're going to bring - potatoes, salad, appetizers, etc. Thanks! Outlook doesn't support forms. You could use voting but it only works with outlook, not other mail clients. I'd just ask them to reply with their choice. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - ...

Typeahead feature in data entry form?
I have a simple list that I need others to do data entry for. I want them to use a form but then they lose that nice typeahead feature which saves a lot of time when entering names. Any way I can recover that using VBA, perhaps? ...

How do I export a notebook to another computer?
I'm afraid it's obvious, but I haven't figured it out yet...I just bought a new laptop and I would like to transfer my OneNote Notebooks to the new computer. The old laptop will be retired. The old system is Vista and the new one is running Windows 7. I thought I could simply run a backup saved to DVD and then restore it to the new system, but I haven't been able to get to save. Any suggestions? Thanks in Advance! "Turningleaf" wrote: > I'm afraid it's obvious, but I haven't figured it out yet...I just bought a > new laptop and ...

Change the servicecalendar form
I need to change the service calendar form. This form should be customizeable under Serviceactivity and under Ressource. But when this form is opened the result is the Ressource-form and the Serviceactivity form. I also need to make changes to the sevicescheduling form - is this possible ? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-base...

Sent Items there, but not there, no apparent Filter
Am I missing something? Outlook 2000. In Tools- >Options... the box is checked to Save Mail Messages in Sent Items Folder, and in the Options of a new mail message, it says to Save it in the Sent Items Folder. Yet, after the message is sent, we don't see it. We do see emails up to about a week ago, nothing since then. Yet if we do an Advanced Find, we find the messages, and it says they are in the Sent Items folder. There is only one Personal Folders, no Mailbox. Upon opening the Sent Items, we sort by Date and Subject, but still can't see them. We go to View->Current Vi...

Using Filters #2
For some reason information that i have entered in the cell does not show up when i pull down the filter list. Why is this happening? Is there a break in your data list? Autofilter will only select data below the heading until it reaches a blank row, unless you select the entire list before selecting autofilter. -- Ian -- "SJGLASS" <SJGLASS@discussions.microsoft.com> wrote in message news:001950D2-EBCA-473F-89B8-EE80FA04FE50@microsoft.com... > For some reason information that i have entered in the cell does not show > up > when i pull down the filter list. Why ...