Re: "Corrupted" query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 08/07/04 

Date: Fri, 06 Aug 2004 20:05:43 -0400

Instead of " [Firstname]=""" & [Forms]![frmFilterDialog]![FirstName] & """"

Use

  " [Firstname] Like ""*"" & " & [Forms]![frmFilterDialog]![FirstName] & " & ""*"""

That looks complicated, but in order to get one quote mark inside a string, you
need to have two quote marks.

If you run into problems, try using a debug.print statement or a message box to
show the string before you actually use it.

 Msgbox strWhere

Jeff J wrote:
>
> Thank you so much for the If statements below. It works very well except it
> is too strict in filtering. Could you tell me how to modify the strWhere =
> strWhere & statements so that they build a string more like [LastName] =
> "*John*" or some other way to produce a "looser" filtering? Thanks again.
>
> "John Spencer (MVP)" wrote:
>
> > Yeah, with seven fields the query once it is reformatted by Access gets to
> > complex to run.
> >
> > You might try building the SQL on the fly and then setting that as the record
> > source for the report. How skillful are you with VBA?
> >
> > Something you can try,but may be slow is to build a query like
> >
> > Field: First_Name: [FirstName] & ""
> > Criteria: "*" & [Forms]![frmFilterDialog]![FirstName] & "*"
> >
> > You don't need the Is Null criteria, since the calculated field will never be
> > null. So if [Forms]![frmFilterDialog]![FirstName] is blank (or null) you will
> > get all records returned.
> >
> > Another solution is to build your report query without a where clause and then
> > append one. Something likethe following code for a button on your form.
> >
> > Private Sub PrintReport_Click()
> > Dim StrWhere as String
> >
> > 'For each control build a
> > IF Len([Forms]![frmFilterDialog]![FirstName] & "") > 0 then
> > If Len(StrWhere)>0 then
> > StrWhere = strWhere & " AND "
> > end If
> > StrWhere = strWhere & " [FirstName]=""" &
> > [Forms]![frmFilterDialog]![FirstName] & """"
> > End IF
> >
> > IF Len([Forms]![frmFilterDialog]![LastName] & "") > 0 then
> > If Len(StrWhere)>0 then
> > StrWhere = strWhere & " AND "
> > end If
> > StrWhere = strWhere & " [LastName]=""" &
> > [Forms]![frmFilterDialog]![LastName] & """"
> > End IF
> >
> > 'repeat as necessary for your other fields, using the proper field delimiters.
> > Then call the report
> >
> > DoCmd.OpenReport "YourReportName", acPreview, , strWhere
> >
> > 'The strWhere applies the criteria to query.
> >
> > End Sub
> >
> > Jeff J wrote:
> > >
> > > Thank you John but the query DOS NOT work. I guess I might ask how would you create a report that could be created by a user "on the fly"? The way I have done it is an unbound form with several text boxes, corresponding to the fields in a table, a query as I discribed before, a report of a certain format, and a macro that is triggered by the "Ok" button of the unbound form. This macro basically opens the query which uses the variables from the form to filter a database then the report is opened in print preview, the query is closed, and the form is closed. This all worked the day I wrote it but the next day it did not and I thought it might be because of this "formatting" of the query as you call it. Can you think of a better way to do what I am trying here? Is there a better way to modify/create a query than the grid view?
> > > Thanks
> > >
> > > "John Spencer (MVP)" wrote:
> > >
> > > > Access reformats the query into this format. The only way to avoid the
> > > > reformatting is to never open the query in the grid view. IF the query works,
> > > > then don't worry about (until you need to modify it ;-0 ).
> > > >
> > > >
> > > >
> > > > Jeff J wrote:
> > > > >
> > > > > Using Access from Office XP I have written a query as part of a Query by Form operation. I have an unbound form with 7 text boxes and the query has 7 "columns" corresponding to those. The criteria on each column is in the form of two "rows" similar to the following:
> > > > >
> > > > > First row
> > > > > Like "*" & [Forms]![frmFilterDialog]![FirstName] & "*"
> > > > >
> > > > > Second row
> > > > > [Forms]![frmFilterDialog]![FirstName] Is Null
> > > > >
> > > > > When I close and save the query in design view, then immediately re-open it in design view the criteria are "scattered" into dozens of rows of parts of the second row above. Even new columns are added with fragments of the second statement above as the field name. The first row above is not effected. I have tried creating a new database and a new query but the same thing happens. I have applied Office service pack 3. Any ideas?
> > > > > Thanks
> > > >
> >

Relevant Pages

  • RE: Query form coding
    ... I have tried to include the query when using the wizard to design the report ... Dim strSource As String ... ' Remove Filter ...
    (microsoft.public.access.formscoding)
  • RE: Need help with Code Please!!!
    ... MsgBox "The qryCompany\USFNumber query returned no records?", ... vbCritical, "NOTHING TO REPORT" ... Dim qd As QueryDef 'object ref to query item ... Dim myDataSource As String ...
    (microsoft.public.access.formscoding)
  • Re: Use wildcard in combo box with multiple strings
    ... Add the field to the query then. ... filter by it, it has to be in the query... ... use the field to filter what rows are in the RecordSource for the report. ... The string you use as a filter must repeat the name of the field for ...
    (microsoft.public.access.forms)
  • Re: Selecting certain items for a report
    ... recordsource for your report. ... it works well especially when the recordsource for the report is a complex ... Create a new query that only pulls data from the product form - you ... Dim StrWhere As String ...
    (microsoft.public.access.forms)
  • RE: Need variables for rpt parameters
    ... Make the query for your report plain vanilla with no filtering. ... either adds it to the Where string or ingores it if it has no value. ... Dim strWhere As String ...
    (microsoft.public.access.reports)