By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

query selecting criteria from a form for a report

P: 98
this may not make any sense, but here goes...
i have a form based on a query that pulls its paramater criteria from drop down boxes on a form.
im using a date range, and i can get this to run correctly, but i would like the reoprt to show an infinite date range if the criteria(start date and end date) is left blank. does anyone have any idea what im talking about?
Feb 20 '09 #1
Share this Question
Share on Google+
9 Replies


100+
P: 675
I used textboxes here, but you could replace them with comboboxes. You did not supply enough information for me to fully understand all your conditions.
The code below queries a table, in this case with three fields, Key, YourText, and YourDate from a table named tDate. There are two textbox controls on the form, txtDateStart and txtDateEnd, and a command button to build the query and assign it to the RecordSource of the form. If both textboxes are blank, form will show an "Infinite Range". I hope this is what you are after.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDateTest_Click()
  2. Dim strSQL As String
  3. Dim strWHERE As String
  4. If Nz(txtDateStart, "") <> "" Then
  5.     If Nz(txtDateEnd, "") <> "" Then
  6.         'Two dates entered
  7.         strWHERE = " WHERE ([YourDate] BETWEEN #" & _
  8.                 txtDateStart & "# AND #" & txtDateEnd & "#)"
  9.     Else
  10.         'One date entered, start date
  11.         strWHERE = " WHERE ([YourDate] > #" & txtDateStart & "#)"
  12.     End If
  13. Else
  14.     If Nz(txtDateEnd, "") <> "" Then
  15.         'One date entered, end date
  16.         strWHERE = " WHERE ([YourDate] < #" & txtDateEnd & "#)"
  17.     Else
  18.         'No dates entered
  19.         strWHERE = ""
  20.     End If
  21. End If
  22. strSQL = "SELECT tDate.Key, tDate.YourText, tDate.YourDate FROM tDate " _
  23.            & strWHERE
  24. Me.RecordSource = strSQL
  25. End Sub
Feb 21 '09 #2

100+
P: 675
I reread my answer, and see you want the query for a report, not your form. OK, either make declare strSQL as global, so you can assign it to Me.RecordSource in the Report_Open event, or build the string in Report_Open, referring back to your main form. Use Forms!fMain.txtDateStart and so forth. Assign to Me.RecordSource there.
Feb 22 '09 #3

NeoPa
Expert Mod 15k+
P: 31,419
Hi Trixx.

Drop in the current SQL (or at least the references you use to the date controls in the question) and we'll see if we can't use the Nz() function to produce an infinitely extended set of criteria when the controls have no items selected.
Feb 22 '09 #4

P: 98
i totally understand what your code does. i am just very unclear how i add it to the query and the form i have already built.

i have been using
Expand|Select|Wrap|Line Numbers
  1. Between [Forms]![frm_Reporting].[Begin] And [Forms]![frm_Reporting].[End]
  2.  
in the query, not in sql view but on the design view.
would there be away to incorporate this into the query only? either using an expression or sql?
Feb 22 '09 #5

NeoPa
Expert Mod 15k+
P: 31,419
Try this :
Expand|Select|Wrap|Line Numbers
  1. Between Nz([Forms]![frm_Reporting].[Begin],#1/1/1900#)
  2.     And Nz([Forms]![frm_Reporting].[End],#12/31/9999#)
Feb 22 '09 #6

P: 98
this is what ended up working for me, but i honestly dont know how.
the vba makes more sense to me(most of it) because i can see where it sets every scenario up. i just am still just a novice and am unable to get the vba portion to work correctly.


Thank you all
Expand|Select|Wrap|Line Numbers
  1. Between Nz([Forms]![frm_Reporting].[Begin]) And Nz([Forms]![frm_Reporting].[end])
  2.  
Feb 23 '09 #7

P: 98
@trixxnixon

This only works if i leave all other criteria off,
Expand|Select|Wrap|Line Numbers
  1.  
  2. [Document Control Database 2007 Main Table].[Tracking Number], 
  3. [Document Control Database 2007 Main Table].Status, 
  4. [Document Control Database 2007 Main Table].[Date Submitted], 
  5. [Document Control Database 2007 Main Table].[Date Assigned], 
  6. [Document Control Database 2007 Main Table].[Date Started], 
  7. [Document Control Database 2007 Main Table].[Date Completed], 
  8. [Document Control Database 2007 Main Table].SLA, 
  9. Workingdays2(DateValue([Date Submitted]),[Date Completed])-nz([sumofdays],0) AS DIH,
  10. [Document Control Database 2007 Main Table].[Due Date], 
  11. Workingdays2(DateValue([Date Submitted]),[Date Completed]) AS Lead, 
  12. [Document Control Database 2007 Main Table].BusinessCritical, 
  13. ([Document Control Database 2007 Main Table].meetings+[Document Control Database 2007 Main Table].research+[Document Control Database 2007 Main Table].Development+[Document Control Database 2007 Main Table].revision+[Document Control Database 2007 Main Table].approval+[Document Control Database 2007 Main Table].Presentation+[Document Control Database 2007 Main Table].upload) AS SrvTrk, 
  14. [practice pending query].sumofdays, 
  15. [Document Control Database 2007 Main Table].Type, 
  16. [Document Control Database 2007 Main Table].[Assigned Document Team], 
  17. [Document Control Database 2007 Main Table].[Communication Specialists]
  18.  
  19. FROM [Document Control Database 2007 Main Table] LEFT JOIN [practice pending query] ON [Document Control Database 2007 Main Table].[Tracking Number] = [practice pending query].[Tracking Number]
  20.  
  21. WHERE
  22.  ((([Document Control Database 2007 Main Table].Status)="completed") AND
  23.  (([Document Control Database 2007 Main Table].[Date Completed]) Between Nz([Forms]![frm_Reporting].[Begin]) And Nz([Forms]![frm_Reporting].[end])) AND
  24.  (([Document Control Database 2007 Main Table].Type)=Nz([Forms]![frm_Reporting].[type])) AND
  25.  (([Document Control Database 2007 Main Table].[Assigned Document Team])=Nz([Forms]![frm_Reporting].[Initiator])) AND
  26.  (([Document Control Database 2007 Main Table].[Communication Specialists])=Nz([Forms]![frm_Reporting].[specialist])));
  27.  
from what little i understand about sql this should produce results that meet all the criteria selected including if the field on the form is left blank. It only works if i leave the criteria (where statement) for the completed date. the solution seems like it would be something simple.
Feb 23 '09 #8

NeoPa
Expert Mod 15k+
P: 31,419
I (strongly) suspect that your version only works when you don't test the end date criteria fully. I can see how leaving the replacement value out of the beginning date could work, as this would naturally return a 0 which, when treated as a date, will resolve to a very early date. A 0 for the end date however, will surely cause many records to be excluded that should not. It will run. I don't think it'll return the correct records in all cases though.

Let me post the WHERE clause as I think it should be. That way you can choose whether or not you wish to use that version :
Expand|Select|Wrap|Line Numbers
  1. WHERE
  2.  (([Document Control Database 2007 Main Table].Status='completed') AND
  3.   ([Document Control Database 2007 Main Table].[Date Completed] Between Nz([Forms]![frm_Reporting].[Begin],#1/1/1900#) And Nz([Forms]![frm_Reporting].[end],#12/31/9999#)) AND
  4.   ([Document Control Database 2007 Main Table].Type=Nz([Forms]![frm_Reporting].[type])) AND
  5.   ([Document Control Database 2007 Main Table].[Assigned Document Team]=Nz([Forms]![frm_Reporting].[Initiator])) AND
  6.   ([Document Control Database 2007 Main Table].[Communication Specialists]=Nz([Forms]![frm_Reporting].[specialist])));
Feb 23 '09 #9

NeoPa
Expert Mod 15k+
P: 31,419
Just to explain the difference :
Nz({A}, {B}) will return the value of {A}, unless that value is Null, in which case it returns the value of {B}.

If no end date is selected, you want for the period that is being matched to extend indefinitely. If the end date is Null therefore, you want a value which reflects as far into the future as is practical. The same is true, but in reverse, for the start date.
Feb 23 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.