Opening a query with a filter in Access 2007

Since under Access 2007 reports can no longer be exported to Excel, I'm 
attempting to open the query that the report is based on so that I can 
export the query to Excel. I'm wondering, though, about the best way to open 
the query since I need to apply a filter. It was easy enough to do in the 
OpenReport call by using the WhereCondition parameter, but OpenQuery has no 
such parameter. What is the best way to filter a query through code?

Thanks,

Carl Rapson


0
Carl
5/31/2007 8:10:59 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
1678 Views

Similar Articles

[PageSpeed] 52

A fairly simple workaround is to save the query you want to use for 
exporting. Then write its SQL property before you do the export.

The idea is to switch the query to SQL View, and spit it into 2 strings so 
you can patch the WHERE clause between them. This kind of thing:

Dim strWhere As String
Const strcStub = "SELECT * FROM Table1 WHERE ("
Const strcTail = ") ORDER BY Table1.ID;"

strWhere = "SomeField = 99"
Currentdb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail
DoCmd.TransferSpreadsheet ...

-- 
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.

"Carl Rapson" <mr.mxyzptlk@newsgroups.nospam> wrote in message
news:%23w8Ma$7oHHA.3368@TK2MSFTNGP02.phx.gbl...
> Since under Access 2007 reports can no longer be exported to Excel, I'm 
> attempting to open the query that the report is based on so that I can 
> export the query to Excel. I'm wondering, though, about the best way to 
> open the query since I need to apply a filter. It was easy enough to do in 
> the OpenReport call by using the WhereCondition parameter, but OpenQuery 
> has no such parameter. What is the best way to filter a query through 
> code?
>
> Thanks,
>
> Carl Rapson 

0
Allen
6/1/2007 2:54:34 AM
Thanks Allen, I'll take a look at that. I guess I'm going to have to get 
used to working more with Queries in Access 2007. Reports seem to have been 
"dumbed down" in some ways.

Carl Rapson

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:emtmxg$oHHA.588@TK2MSFTNGP06.phx.gbl...
>A fairly simple workaround is to save the query you want to use for 
>exporting. Then write its SQL property before you do the export.
>
> The idea is to switch the query to SQL View, and spit it into 2 strings so 
> you can patch the WHERE clause between them. This kind of thing:
>
> Dim strWhere As String
> Const strcStub = "SELECT * FROM Table1 WHERE ("
> Const strcTail = ") ORDER BY Table1.ID;"
>
> strWhere = "SomeField = 99"
> Currentdb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail
> DoCmd.TransferSpreadsheet ...
>
> -- 
> 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.
>
> "Carl Rapson" <mr.mxyzptlk@newsgroups.nospam> wrote in message
> news:%23w8Ma$7oHHA.3368@TK2MSFTNGP02.phx.gbl...
>> Since under Access 2007 reports can no longer be exported to Excel, I'm 
>> attempting to open the query that the report is based on so that I can 
>> export the query to Excel. I'm wondering, though, about the best way to 
>> open the query since I need to apply a filter. It was easy enough to do 
>> in the OpenReport call by using the WhereCondition parameter, but 
>> OpenQuery has no such parameter. What is the best way to filter a query 
>> through code?
>>
>> Thanks,
>>
>> Carl Rapson
> 


0
Carl
6/1/2007 3:03:53 PM
Reply:

Similar Artilces:

Free Data Process/Query Tool
If you use Excel everyday and spend lots of time on data/text processing, then you will find the this tool (UDQ Add-in) is very powerful and can speed up your daily data processing work. Currently the tool is free for everyone. Please feel free to distribute this tool to your friends/colleagues if you think it is helpful to your work. The following is a list of features/functionality of the tool: 1> Query/Import Data from Multiple External Data/Text/ASCII Files (Can useful import data from hundreds of files within minutes) 2> Query/Import Data from Multiple Closed Excel Files (Can...

impossible to access the "news" from outlook 2003
I wanted to set-up my newsgroups access In outlook 2003. I followed the documented procedure (toolbar/standard/customize) from the help menu to add the "News" selection to the "GO" menu. It worked but when I exited Outlook and got back later the "news" menu disappeared. I tried to reinstate it following the same installation procedure but the News option is no more available in the "toolbar/customize list". Looks like a bug. Has anybody faced the same issue ? Is there a workaround to access the Newsgroup interface ? Thanks, Laurent conflicts ...

OWA to POP, IMAP: OWA access, need pop3, IMAP, or Forwarding
Hi, I have recently been given a new email address by my school, which is only accessible through exchange and OWA. The thing is, I already have an exchange server, and 2 pop accounts already setup into my single profile, which is linked to my blackberry -- everything is already well integrated. Adding this profile or OWA screws everything up. Is there a peice of software I can run that reads my OWA account and forwards it to POP or IMAP? The new OWA won't even let me forward the mail. Thanks for the help. Jeff. On 4 Dec 2006 07:16:39 -0800, "Jeff Sherwin" <subtle@gmai...

Compatibility Pack for Office 2007
This may be a stupid question, but... We are in the process of migrating from Office 2003 to Office 2007. We had deployed the Compatibility Pack to all our Office 2003 users a year or more ago so they could read documents created outside our company. My question is... Is there any need or value to keep the Compatibility Pack installed after installing Office 2007? Thanks, Tom In article <OP$RtKsmKHA.6096@TK2MSFTNGP02.phx.gbl>, Tom K wrote: > This may be a stupid question, but... > > We are in the process of migrating from Office 2003 to Office 200...

advance filters
I would like to know if anybody knows how to use advance filters if you want to select only nubers that begins with 572. For example I want to select all the number in a list that star with 572.... I need to do it with advance filters because I have to use more filters in others columns. Thank you. Manuel Hi Manuel, Perhaps you could add a helper column and filter on the helper column? Assume the relevant data to be in cells B2:B100: Insert a helper C column In Cell C2 enter the formula =Left(B2, 3) = 572 Copy down to C100 Filter on column C using TRUE as the filter condition. -...

Outlook: Can't open JPEG files
I have Outlook 2000 an unable to open JPEG files. What setting has to be changed to open these files? ...

2007 Schema Prep #2
I extended my AD last night for 2007 and have a few questions to verify it was successful: I have a root domain (domain.local) that houses my schema master and a child domain (domain.net) that has Exchange 2003. I ran setup /prepareAD then ran setup /preparedomain: domain.net and both said they completed successfully. However, I was expecting to see the new OU (Microsoft Exchange Security Groups) in my child domain. It is present in my root domain. Is that all correct? In my child domain I now have a new global security group call Exchange Install Domain Servers. Last thing, the D...

8192 rows limit when opening wk1 files ?
Hello, I am opening a .wk1 file, which contains around 60K rows, but only the first 8192 rows appear in Excel. Is there any way to have all the rows in the Excel file ? Thanks, Paul "paul" wrote... >I am opening a .wk1 file, which contains around 60K rows, >but only the first 8192 rows appear in Excel. > >Is there any way to have all the rows in the Excel file ? WK1 as in Lotus 1-2-3 Release 2.x native file format? Such .WK1 files support a single worksheet of 256 columns and 8,192 rows. PERIOD. Did you generate this '.WK1' file using a program other then...

GUID/Outlook format link giving error "Unable to open the seleted folder or item"
Hi, I have created custom forms in multiple public Outlook folders and when I send the GUID type link to the user, they receive an error: "Unable to open the seleted folder or item". The same happens if I send the Outlook type link. However, if I ask the user to go to the folder and find the appropriate posting, then the form opens and after that the link works, too. Is this due to some Exchange server setting? My clients are not the type that tolerate opening the form the long way, they want a one click solution. Please help, this is driving my team crazy. Any ideas are welcome...

Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer. I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time progra...

Formula query #2
I am trying to identify a formula which will add and then display th number of times a number appears in a range. For example, the range looks like this .......A...........B............C 1...1110......1110.......1114 2...1110..... 1111.......1111 3...1111......1112.......1110 4...1111......1113.......1111 The numbers which appear in the applicable range e.g. 1110, could b any number between 1110 and 9999 and as such I cannot simply predefin which number to look for. I am hoping the output could like as follows: .......A........B 1....1110....4 2....1111....5 3....1112....1 4.....

disabling queries or dataranges
I have a large spreadsheet with many embedded queries pulling from an access database that i want to publish to lots of different people. how do i keep the queries from trying to refresh when those people open the book, besides cutting and pasting all the values from one spreadsheet into another one without data ranges? I want the spreadsheet to ask me whether it should refresh on opening, and it does, but i want to be able to disable this and send it on without lot's of work. excel 2000. thanks. -- johnlengacher -------------------------------------------------------------------...

query will not write to excel
I have set up a query to a Foxpro .dbf file in a database from excel. When I tell Excel to import the data it it appears to be working but never seems to return the data. Running the same query via msquery.exe returns the data with no problem. Can anyone tell me what the problem is ? ...

how to open port in windows server 2003
hi , i have an exchange server 2003(sp1) with windows server 2003 (sp1) the scenarion is like that my mails are going out side through SMTP filter which is installed on another machine in this software the only port 25 is open, internaly my mails are working fine and i can also receive mails from another domain like hotmail and yahoo , but problem i am facing is that from out side of my network means if i want to check my mails from my home i am facing the problem is that i can send mail but can not receivce mail this happend because i am using smtp protocol for sending mails and pop3 ...

Passing an argument from a query to a command
I am trying to send a report using an email address in a DB. I wrote the following code for it. Private Sub Reminder_Letter_Command_Click() On Error GoTo Err_Reminder_Letter_Command_Click Dim stDocName As String Dim SendTO As String stDocName = "BG Reminder Letter" RunQuery MsgBox "This is After RunQuery " & SendTO SendTO = [Queries]![BG email only from email from ae code].EmailAddress MsgBox "This is SendTO 2 Value ==== " & SendTO DoCmd.OpenReport stDocName, acViewNormal DoCmd.SendObject acSend...

Deny Access to Mailbox
Is there any way to deny access to a mailbox for specific users? Essentially my problem is that I want to prevent dom admin A from being able to read dom admin B email from either Outlook or directly from the server. Thanks. neither domain admin should be able to access the other's mailbox...what version of Exchange? do they have SA explicitly or via a group? "Tyler" <anonymous@discussions.microsoft.com> wrote in message news:008b01c3b8f4$20379370$a301280a@phx.gbl... > Is there any way to deny access to a mailbox for specific > users? > > Essentially my ...

Form opens to blank record
I have a front page (a form) and my main database linked via a command button - it keeps opening on a particular record and I want it to open onto a blank record - how do I go about changing this? Thanks C (P.S. only a shakey understanding of code so if answer includes this - go easy on me and explain! Thanks) When you say: " I have a front page (a form) and my main database linked via a command button..." you are not talking about the use of Microsoft Office FrontPage, are you? http://www.amazon.com/Microsoft-FrontPage-2003-OLD-VERSION/dp/B0000AZJV8 I *thi...

problems with toolbar-can't access certan commands i.e.hyperlink
can't access some commands - was working fine - now some commands are in grey - the commands disappeared from the pull down in the commands bar. Help magnus wrote: > can't access some commands - was working fine - now some commands are in grey > - the commands disappeared from the pull down in the commands bar. The hyperlink option may only appear when you are creating a Web Publication in certain versions of Publisher. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Trial version? You may have run out of time. Some pulldowns only work when you are creating a ...

Word problems 2003 and 2007 version
hey everyone both on my main computer and my brothers laptop word isnt working! its so annoying! on my desktop PC i have windows xp and word 2003. after getting a virus which stopped the internet from working i had to do a system restore - which got rid of the virus. but when i click on word it says that "this shortcut only applies to installed products", and when i go to word through program files it says "this product is not installed on this user" - but there is no setup thing i can press - will i need the disc maybe? on my bro's laptop he got the sa...

Unmatched Query Question
I have a database that I'm using to pull the names of students who have applied for graduation from an Excel spreadsheet, that is updated weekly, into an Access Table. Right now, I'm using an unmatched query to pull Student IDs who have not previously appeared on the Excel spreadsheet. At first, I was a little worried about this because if a student applies for graduation with a dual degree, their name shows up on the Excel Spreadsheet twice (see example below). 111000 Pig Porky BA.PSY 111000 Pig Porky BFA.FP Fortunately, as long as the student's ID number isn't on the Eval S...

Outlook 2003 won't open in ie but outlook express does
I just upgraded my harddrive form 120 gb to 400 gb using segates disk utility and when all was done eveything except system restore and the read email link from ie 7 worked fine. OL express would open and run ok but OL 2003 would not, no error just won't open. Doesnt' work in firefox either. Have uninstalled and reinstalled Office, no luck. Have unistalled ie7, no go either. Got system restore to work again by diabling the service from the managment console then starting it from the system restore tab in my computers/proporties. There seems to be some connection from ie to outlook...

SpellCheck Will it work on the Access 2007 Runtime?
I have a program that I have compiled. It has the following. Private Sub cmdSpellCheck_Click() Me.Call_Notes.SetFocus RunCommand acCmdSpelling End Sub Is this going to work on a runtime or will I need a full version of Access to use it? You can simulate using the runtime of A2007 quite easily for testing. To do it, change the extension of the file from accdb to accdr. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Avid Fan" <me@privacy.net> wrote in message news:ulRyIhQ4KHA.4804@TK2MSFTNGP04.phx.gbl... >I have a program t...

Restricting Domain Logon w/OWA Access
I have multiple general mailboxes in exchange 2003 and would like to deny local logon access to the domain while allowing owa access to these e-mail accounts. I tried solving the problem by allowing only logons to the domain controller and the exchange server from these general mailboxes, but I receive access denied while attempting to log onto owa. Please help... ...

Switching X Y axis in PP 2007
How do make the vertical axis the horizonal one and vice versa? -- etf Does Switch Row/Column on the Chart Tools Design tab do what you want? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "etf" <etf@discussions.microsoft.com> wrote in message news:28669E0E-1FF7-47CB-9ED2-1769248A651E@microsoft.com... > How do make the vertical axis the horizonal one and vice versa? > -...

Newbee needs help Combo Box access 2007
I have a access 2000 project that would all me to create a combo box the added the following code , so the values from the combo box would be stored in the current form . also when typing the code below access 2000 would bring up the code string example when typing me.s it would bring up the value "shipper" from the field list is this feature gone or just turned off on my copy Example of code From Access 2000 Private Sub Combo 40_AfterUpdate(cancel As Integer) me.shipper = me.combo40.Column(0) me.Address = me.combo40.Column(1) error This error occurs whe...