If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple filter query of form
I have a form which receives the input for a multiple entry search. The form
then opens a report to show the restricted data. I cannot get it to work properly! I have added the following coding to each of my search fields on my query. Technician Name field: Forms![Search Selection Dialog]![findtech] or Forms![Search Selection Dialog]![findtech] is Null I have seven of these seach criteria on the search form and it will not restrict the records. Access made additional entries for me (creating additional colums in the query). It worked when I only used two fields for filtering!!! There has to be a better way! |
#2
|
|||
|
|||
Try something along the lines of:
(Like "*" & Forms![Search Selection Dialog]![findtech] & "*") Or Is Null HTH, Nikos |
#3
|
|||
|
|||
I just tried it and with that line I get request for input on the fields I
don't type info in. Those fields I want to include all records and currently when I just enter through those pop-up fields, it does not restrict my records. P "Nikos Yannacopoulos" wrote: Try something along the lines of: (Like "*" & Forms![Search Selection Dialog]![findtech] & "*") Or Is Null HTH, Nikos |
#4
|
|||
|
|||
truepantera wrote: Those fields I want to include all records This is what it should be doing, if you're getting "pop-up fields" (input boxes) it's mosty likely because you have misspelled something in the form control reference, or forgotten parameter declarations (look in Query Parameters). Nikos |
#5
|
|||
|
|||
My SQL
SELECT [Internal Chart Audit].Dateofentry, [Internal Chart Audit].[Technician Name], [Internal Chart Audit].[Scribe Name], [Internal Chart Audit].[Patient Name], [Internal Chart Audit].[Month audited], [Internal Chart Audit].[Examination date] FROM [Internal Chart Audit] WHERE ([Internal Chart Audit].Dateofentry = [Forms]![Search Selection Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) = [Forms]![Search Selection Dialog]![beginningdate] or [Internal Chart Audit].Dateofentry = [Forms]![Search Selection Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) = [Forms]![Search Selection Dialog]![beginningdate] AND [Internal Chart Audit].[Technician Name] = [Forms]![Search Selection Dialog]![findtech] Or [Forms]![Search Selection Dialog]![findtech] Is Null OR [Internal Chart Audit].Dateofentry = [Forms]![Search Selection Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) = [Forms]![Search Selection Dialog]![beginningdate] AND [Internal Chart Audit].[Technician Name] = [Forms]![Search Selection Dialog]![findtech] Or [Forms]![Search Selection Dialog]![findtech] Is Null AND [Internal Chart Audit].[Scribe Name] = [Forms]![Search Selection Dialog]![findscribe] Or [Forms]![Search Selection Dialog]![findscribe] Is Null OR [Internal Chart Audit].Dateofentry = [Forms]![Search Selection Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) = [Forms]![Search Selection Dialog]![beginningdate] AND [Internal Chart Audit].[Technician Name] = [Forms]![Search Selection Dialog]![findtech] Or [Forms]![Search Selection Dialog]![findtech] Is Null AND [Internal Chart Audit].[Scribe Name] = [Forms]![Search Selection Dialog]![findscribe] Or [Forms]![Search Selection Dialog]![findscribe] Is Null AND [Internal Chart Audit].[Patient Name] =[Forms]![Search Selection Dialog]![findpatname] Or [Forms]![Search Selection Dialog]![findpatname] Is Null OR [Internal Chart Audit].Dateofentry = [Forms]![Search Selection Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) = [Forms]![Search Selection Dialog]![beginningdate] AND [Internal Chart Audit].[Technician Name] = [Forms]![Search Selection Dialog]![findtech] Or [Forms]![Search Selection Dialog]![findtech] Is Null AND [Internal Chart Audit].[Scribe Name] = [Forms]![Search Selection Dialog]![findscribe] Or [Forms]![Search Selection Dialog]![findscribe] Is Null AND [Internal Chart Audit].[Patient Name] =[Forms]![Search Selection Dialog]![findpatname] Or [Forms]![Search Selection Dialog]![findpatname] Is Null AND [Internal Chart Audit].[Month audited] =[Forms]![Search Selection Dialog]![findauditmonth] Or [Forms]![Search Selection Dialog]![findauditmonth] Is Null OR [Internal Chart Audit].Dateofentry = [Forms]![Search Selection Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) = [Forms]![Search Selection Dialog]![beginningdate] AND [Internal Chart Audit].[Technician Name] = [Forms]![Search Selection Dialog]![findtech] Or [Forms]![Search Selection Dialog]![findtech] Is Null AND [Internal Chart Audit].[Scribe Name] = [Forms]![Search Selection Dialog]![findscribe] Or [Forms]![Search Selection Dialog]![findscribe] Is Null AND [Internal Chart Audit].[Patient Name] =[Forms]![Search Selection Dialog]![findpatname] Or [Forms]![Search Selection Dialog]![findpatname] Is Null AND [Internal Chart Audit].[Month audited] =[Forms]![Search Selection Dialog]![findauditmonth] Or [Forms]![Search Selection Dialog]![findauditmonth] Is Null AND [Internal Chart Audit].[Workup Qty] =[Forms]![Search Selection Dialog]![findexamdate] Or [Forms]![Search Selection Dialog]![findexamdate] is Null) |
#6
|
|||
|
|||
I am truly amazed how you produced this. This is not what I suggested
two posts ago. Your criteria are a mess, they are repeating all over the place, you are allowing for Null in the wrong place (form controls instead of table fields) etc. Go back to my first reply, see the suggested syntax for criteria and try again. Suggest you srat fresh, rather than editing your existing query. If all goes well, you should get something like: SELECT [Internal Chart Audit].Dateofentry, [Internal Chart Audit].[Technician Name], [Internal Chart Audit].[Scribe Name], [Internal Chart Audit].[Patient Name], [Internal Chart Audit].[Month audited], [Internal Chart Audit].[Examination date] FROM [Internal Chart Audit] WHERE [Internal Chart Audit].Dateofentry = [Forms]![Search Selection Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) = [Forms]![Search Selection Dialog]![beginningdate] AND [Internal Chart Audit].[Technician Name] Like "*" & [Forms]![Search Selection Dialog]![findtech] & "*" Or Is Null AND [Internal Chart Audit].[Scribe Name] Like "*" & [Forms]![Search Selection Dialog]![findscribe] & "*" Or Is Null AND [Internal Chart Audit].[Patient Name] Like "*" & [Forms]![Search Selection Dialog]![findpatname] & "*" Or Is Null AND [Internal Chart Audit].[Month audited] Like "*" & [Forms]![Search Selection Dialog]![findauditmonth] & "*" Or Is Null AND [Internal Chart Audit].[Workup Qty] Like "*" & [Forms]![Search Selection Dialog]![findexamdate] & "*" Or Is Null Note: The trick with the wildcards doesn't work well with numbers and dates. For the dates, I suggest you use a very wide range as default in the form (like 01/01/1900 to 12/31/2999) so you don't mess with nulls (which would requre code to handle correctly), while all your records are inclided; the user will only need change those if they really want a specific range. Likewise on the qty field; Null will work fine with this trick (all records returned), when looking for a particular value you can filter but if you enter, say, 12.85 you will also get records with 612.85, 12.854 etc (search istreated like a string). Again, for true filtering and allowing for Null = all records, you would need code. Nikos |
#7
|
|||
|
|||
Oops! Forgot the parentheses to force condition prioroties:
SELECT [Internal Chart Audit].Dateofentry, [Internal Chart Audit].[Technician Name], [Internal Chart Audit].[Scribe Name], [Internal Chart Audit].[Patient Name], [Internal Chart Audit].[Month audited], [Internal Chart Audit].[Examination date] FROM [Internal Chart Audit] WHERE ([Internal Chart Audit].Dateofentry = [Forms]![Search Selection Dialog]![EndingDate] And ([Internal Chart Audit].Dateofentry) = [Forms]![Search Selection Dialog]![beginningdate]) AND ([Internal Chart Audit].[Technician Name] Like "*" & [Forms]![Search Selection Dialog]![findtech] & "*" Or Is Null) AND ([Internal Chart Audit].[Scribe Name] Like "*" & [Forms]![Search Selection Dialog]![findscribe] & "*" Or Is Null) AND ([Internal Chart Audit].[Patient Name] Like "*" & [Forms]![Search Selection Dialog]![findpatname] & "*" Or Is Null) AND ([Internal Chart Audit].[Month audited] Like "*" & [Forms]![Search Selection Dialog]![findauditmonth] & "*" Or Is Null) AND ([Internal Chart Audit].[Workup Qty] Like "*" & [Forms]![Search Selection Dialog]![findexamdate] & "*" Or Is Null) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query Only Creent Record? | Hulk | Running & Setting Up Queries | 10 | January 25th, 2005 06:39 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
Filter by Form query will not populate fields | Brigitte P | Running & Setting Up Queries | 2 | May 27th, 2004 12:59 PM |