Query By Form

[TechTutors]

Hello,

I am attempting to formulate a QBF based on the instructions provided by the
following link:

http://support.microsoft.com/default.aspx/kb/209645/

However, I am attemping this with a form that has 20 areas for criteria.

When I build the query with those 20 different parameters, the entire Access
program freezes on me.

However, I have attempted using fiewer parameters (about 3).

Is there a limit to the amount of QBF Parameters I can use?... If not, what
is causing the system to freeze and crash? Is there a way to resolve this?

Any help would be greatly appreciated.

-Many Thanks!

 

[Allen Browne]

That approach gets really messy and inefficient to execute when you have
lots of criteria.

For an alternative approach, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[TechTutors]

Thanks a lot... I've read through it and it seems to be exactly what I'm
looking for!

I hope it works! ;-)

 

[TechTutors]

I'm sorry... One more question...

I need for the results of the criteria to be able to be analyzed in Excel...
Is it as easy as putting in the command button with in the Form Footer?:

DoCmd.OutputTo acOutputForm, frmNameOfForm, , , True

I ask because I don't know how this can integrate with your VB.

Thanks!

 

[Ofer Cohen]

You need to export the query you created with the filter to Excel, that way
the filtered data will be tranfered to excel

'Transfer the query you created to c: drive
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "QueryName",
"c:\XlsFileName.xls"

 

[TechTutors]

I'm sorry, but I'm having problems with the following:

'Transfer the query you created to c: drive
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "QueryName",
"c:\XlsFileName.xls"

I've tried creating a command button with that code, but I keep getting
errors:

I've put the button in the header and the footer (not the details since it
would repeat for each record).

I'm not exactly sure what to do next.

This is the code after changing the necessary values:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
qry_QBF_*****_Final,
C:\Documents and Settings\*****\My Documents

 

[TechTutors]

Also, the VB code suggests that I may be able to use two criteria fields for
extra filtering. Please advise is this code "should" function:

If Not IsNull(Me.txtPrice1) Then
strWhere = strWhere & "([Price] = " & Me.txtPrice1 & ") AND ([Price]
= " & Me.txtPrice2 & ")"
End If

 

[John Spencer]

First, the name of the query and the pathname must be strings (enclosed in
quotes) or variables that are strings (no quotes needed).
Second, I belieive you also need the document name along with the pathname

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
"qry_QBF_*****_Final",
"C:\Documents and Settings\*****\My Documents\YourExcelFileName.xls"


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

 

[John Spencer]

That SHOULD function although is will return NO records unless txtPrice1 and
txtPrice2 are the same value. You probably want to to use OR instead of
AND. Also you probably need to include a space before the first reference
to the Price field.

strWhere = strWhere & " ([Price] = " & Me.txtPrice1 & ") OR ...
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Also, the VB code suggests that I may be able to use two criteria fields
for
extra filtering. Please advise is this code "should" function:

If Not IsNull(Me.txtPrice1) Then
strWhere = strWhere & "([Price] = " & Me.txtPrice1 & ") AND
([Price]
= " & Me.txtPrice2 & ")"
End If

You need to export the query you created with the filter to Excel, that
way
the filtered data will be tranfered to excel

'Transfer the query you created to c: drive
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "QueryName",
"c:\XlsFileName.xls"

 

[TechTutors]

Unfortunately, this code still isn't working. The VB remains in RED

 

[John Spencer]

Well, I suspect that you have not put the entire call in one line or
alternatively used line continuation characters.

You can do this (with space underscore as a line continuation)
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel3, _
"qry_QBF_*****_Final", _
"C:\Documents and Settings\*****\My Documents\YourExcelFileName.xls"

Or you can do the following ALL ON ONE LINE
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
"qry_QBF_*****_Final", "C:\Documents and Settings\*****\My
Documents\YourExcelFileName.xls"

By the way this is going to fail unless you change the "*****" to something
that is legal for a folder name
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..