parameter query woes 1

[uscitizen]

i have a table i want to query (ultimately i would use the select query's result in a report but that will have to wait)...

there are a few fields on this table but the ones i'm interested in are called 'Submission', 'Date Submitted' and 'Appvl Date'. the first is completed via a combo box control on the user's entry form. the last two are clearly chronological.

i would like to design my query to be so versatile that the user could specify or not specify which 'Submission' value gets entered. i would like the user to have the ability to specify a range of 'Date Submitted' values or none at all and therefore scan the entire table. lastly, i would like to include records with values of 'Appvl Date' that might or might not be null.

If we looked at the design window, we would find four rows:

Submission

[Enter Specific or Blank for All]
[Enter Specific or Blank for All] Is Null
[Enter Specific or Blank for All] Is Null
[Enter Specific or Blank for All]

under

Date Submitted

Between [Earliest Dte Submitted] And [Last Dte Submitted]
Between [Earliest Dte Submitted] And [Last Dte Submitted]
Between [Earliest Dte Submitted] And [Last Dte Submitted]
Between [Earliest Dte Submitted] And [Last Dte Submitted]

under

Appvl Date

Is Null
Is Not Null
Is Not Null
Is Null

is there any apriori reason to think this design's going to do what i want?




“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[dhookom]

If this is for a report, I would create text boxes on a form for the user to enter the values to search for or leave empty if they don't want to filter on that field. You can then use code to create a where clause for the OpenReport line.

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtEarlySubmit) Then
   strWhere = strWhere & " AND [Date Submitted] >=#" & _
       Me.txtEarlySubmit & "# "
End If
If Not IsNull(Me.txtLastSubmit) Then
   strWhere = strWhere & " AND [Date Submitted] <=#" & _
       Me.txtLastSubmit & "# "
End If
'continue with adding other criteria to strWhere
DoCmd.OpenReport "rptYours", acPreview, , strWhere

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

yeah, i guess i could but they're used to the parameter query concept.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[dhookom]

The "parameter query concept" is so yesterday. Entries on form controls allows you to set defaults, use combo and list boxes, check for integrity, see all your criteria on one screen, reuse the same criteria as long as the form stays open, ...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

you mean olde fashioned? "Entries on form controls allows you to set defaults, use combo and list boxes, check for integrity, see all your criteria on one screen, reuse the same criteria as long as the form stays open, ... " when appropriate, has a place, definitely.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[dhookom]

By "yesterday" I meant old fashioned. Years ago, I used lots of parameter queries. I don't think I have used any lately since they are so "featureless".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[uscitizen]

use forms when you don't want "featurelessness". when it doesn't make much of a nevermind, use parameter queries.

thanks.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln