Form sends criteria to query w/list box

[ptw78]

I have a query that receives it's criteria from a form. The form has 2 text boxes which are for dates(start & end date). It also has 3 dropdown list boxes. 2 of the boxes have about 10 entries that are from a table, and one of the list box has 2 entries which are I put in manually. This information is feed to a query once the submit button is clicked. It all works if i have everything filled out. eg. the dates and a selection from each drop down list. What I want is if the dropdown lists have nothing selected is to have the query pull everything from that field. So if only the dates are filled out but nothing in the dropdown list then it pulls everything from that date range. Here is the way the query criteria reads for the dropdown lists. Bus as I said inorder for it to work a selection has to be made for each one, and I would like it to still work if no selection is made. Thanks
[Forms].[ReportCriteria].[1stProcessors]
[Forms].[ReportCriteria].[2ndProcessors]
[Forms].[ReportCriteria].[Decision]

 

[Golom]

The statement of your problem is reasonably clear but you need to post the SQL view of your query.

Without that we can offer nothing more than a general template for handling these things and that may not be appropriate for your situation.

 

[ptw78]

sorry about that.

SELECT FHAReferrals.Date, FHAReferrals.Account_Number, FHAReferrals.[1st Review Processor], FHAReferrals.[2nd Review Processor], FHAReferrals.Decision, FHAReferrals.[Reason Code], FHAReferrals.[Deptartment Code]
FROM FHAReferrals
WHERE (((FHAReferrals.Date) Between [Forms].[ReportCriteria].[txtStartDate] And [Forms].[ReportCriteria].[txtEndDate])) OR (((FHAReferrals.[1st Review Processor])=[Forms].[ReportCriteria].[1stProcessors]) AND ((FHAReferrals.[2nd Review Processor])=[Forms].[ReportCriteria].[2ndProcessors]) AND ((FHAReferrals.Decision)=[Forms].[ReportCriteria].[Decision]));

 

[ptw78]

actually this is the way i had it originally.

SELECT FHAReferrals.Date, FHAReferrals.Account_Number, FHAReferrals.[1st Review Processor], FHAReferrals.[2nd Review Processor], FHAReferrals.Decision, FHAReferrals.[Reason Code], FHAReferrals.[Deptartment Code]
FROM FHAReferrals
WHERE (((FHAReferrals.Date) Between [Forms].[ReportCriteria].[txtStartDate] And [Forms].[ReportCriteria].[txtEndDate]) AND ((FHAReferrals.[1st Review Processor])=[Forms].[ReportCriteria].[1stProcessors]) AND ((FHAReferrals.[2nd Review Processor])=[Forms].[ReportCriteria].[2ndProcessors]) AND ((FHAReferrals.Decision)=[Forms].[ReportCriteria].[Decision]));

 

[dhookom]

You and use "OR formcontrol is Null" like the following which assumes you want to apply the date range regardless of the other values.

WHERE 
FHAReferrals.Date Between [Forms].[ReportCriteria].[txtStartDate] And 
  [Forms].[ReportCriteria].[txtEndDate] AND 
(FHAReferrals.[1st Review Processor]=[Forms].[ReportCriteria].[1stProcessors] OR 
  [Forms].[ReportCriteria].[1stProcessors] Is Null) AND 
(FHAReferrals.[2nd Review Processor]=[Forms].[ReportCriteria].[2ndProcessors] OR
  [Forms].[ReportCriteria].[2ndProcessors] Is Null) AND 
(FHAReferrals.Decision=[Forms].[ReportCriteria].[Decision] OR
  [Forms].[ReportCriteria].[Decision] Is Null);

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Something like

SELECT [Date], Account_Number, [1st Review Processor], [2nd Review Processor], 
       Decision, [Reason Code], [Deptartment Code]

FROM FHAReferrals

WHERE [Date] Between [Forms].[ReportCriteria].[txtStartDate] And [Forms].[ReportCriteria].[txtEndDate] 

  AND ([1st Review Processor] = [Forms].[ReportCriteria].[1stProcessors] 
       [red]OR Trim$([Forms].[ReportCriteria].[1stProcessors] & "") = "")[/red]

  AND ([2nd Review Processor] = [Forms].[ReportCriteria].[2ndProcessors] 
       [red]OR Trim$([Forms].[ReportCriteria].[2ndProcessors] & "") = "")[/red]

  AND (Decision = [Forms].[ReportCriteria].[Decision] 
      [red]OR Trim$([Forms].[ReportCriteria].[Decision] & "") = "")[/red]

Note that "Date" is a reserved word in Access so it needs to be in square brackets (assuming that it is a field name and not a reference to the Date function.)

 

[ptw78]

thx, so is this basically a parameter query

 

[Golom]

so is this basically a parameter query

Yeah ... I suppose.

Do you have a question about parameter queries or was that just a general observation?

 

[ptw78]

well I dont know much about them, that was all. But when i go to put in dates, anything for 2010 does not pull up, i can pull up the data for 2009, but 2010 no matter how i put the date format in, is blank when i run it.

 

[Golom]

One thing you could try is a PARAMETERS statement

PARAMETERS [Forms].[ReportCriteria].[txtStartDate] DateTime, 
           [Forms].[ReportCriteria].[txtEndDate] DateTime;

[blue] ... the Select statement ...[/blue]

This will force the conversion to DateTime fields. I have seen instances where the conversion from a text field sometimes gets side-tracked and it converts to a number or remains as text rather than becoming a DateTime.

 

[ptw78]

I have my issued figured out, thx