Beginning and Ending date Help

[rlangford]

Hi I am trying to get a form to search a date range (so this is also kind of both a form and query question). I would like it to look sort of like this build. I use 2007 access.

Something saying

Specific Date: __/__/____ Calendar button here
Check Box Here Ending Date:__/__/____ Calendar button here

So I would like it to work like this. If I don't enter a date it searches all the dates. If I do enter a date it would search only that one date. If I enter a first date and then select the check box this would allow me to select the second date making a range of dates that would query different things.

Another thing is I have 3 other combo boxes that are queried with this that I want to include all of them filtering if I want together.

Any advice or nudging in the right direction would be great. Thanks so much all!

 

[Wiz47]

Hi I am trying to get a form to search a date range (so this is also kind of both a form and query question). I would like it to look sort of like this build. I use 2007 access.

Something saying

Specific Date: __/__/____ Calendar button here
Check Box Here Ending Date:__/__/____ Calendar button here

So I would like it to work like this. If I don't enter a date it searches all the dates. If I do enter a date it would search only that one date. If I enter a first date and then select the check box this would allow me to select the second date making a range of dates that would query different things.

Another thing is I have 3 other combo boxes that are queried with this that I want to include all of them filtering if I want together.

Any advice or nudging in the right direction would be great. Thanks so much all!

 ([Inspection_Date] Between Forms!frm_Search_Db!txtStart_Date And Forms!frm_Search_Db!txtEnd_Date)

To search between two dates, I use the above query on my search form called frm_Search_db. txtStart_Date is the name of the textbox I use to display the start date (unbound) and conversely txtEnd_Date is the end date of the search. This info is used to call up a report for all records covering the dates between the two listed.

 

[pbaldy]

I don't think that addresses the problem at hand Wiz.

My tendency with numerous optional parameters is to build the SQL in code:

  If IsNumeric(Me.txtResNum) Then
    strSQL = strSQL & " AND ResNum = " & Me.txtResNum
  End If

  If IsDate(Me.txtFromDate) And IsDate(Me.txtToDate) Then
    strSQL = strSQL & " AND ResDate Between " & Format(Me.txtFromDate, conJetDate) & " And " & Format(Me.txtToDate, conJetDate)
  ElseIf IsDate(Me.txtFromDate) Then
    strSQL = strSQL & " AND ResDate = " & Format(Me.txtFromDate, conJetDate)
  End If

  If Len(Me.txtPostCode & vbNullString) > 0 Then
    strSQL = strSQL & " AND ResPostCode = '" & Me.txtPostCode & "'"
  End If

I would use the finished SQL string either in a wherecondition to open a form/report or perhaps set the source of a form or listbox.

 

[Wiz47]

I don't think that addresses the problem at hand Wiz.

My tendency with numerous optional parameters is to build the SQL in code:

  If IsNumeric(Me.txtResNum) Then
    strSQL = strSQL & " AND ResNum = " & Me.txtResNum
  End If

  If IsDate(Me.txtFromDate) And IsDate(Me.txtToDate) Then
    strSQL = strSQL & " AND ResDate Between " & Format(Me.txtFromDate, conJetDate) & " And " & Format(Me.txtToDate, conJetDate)
  ElseIf IsDate(Me.txtFromDate) Then
    strSQL = strSQL & " AND ResDate = " & Format(Me.txtFromDate, conJetDate)
  End If

  If Len(Me.txtPostCode & vbNullString) > 0 Then
    strSQL = strSQL & " AND ResPostCode = '" & Me.txtPostCode & "'"
  End If

I would use the finished SQL string either in a wherecondition to open a form/report or perhaps set the source of a form or listbox.

I use a separate "search" form that builds the query based on answers from combo boxes. The code snippet I posted was just the between for the dates. I'm sure you'd recognize what I am doing. I just thought it would be too much code at once for the poster to digest.

SELECT [tbl_IDS_MAIN ].*
FROM tbl_IDS_MAIN
WHERE (([BldgNumber] Like Forms!frm_Search_Db!txtBldg_Number Or Forms!frm_Search_Db!txtBldg_Number Is Null)=True) And (([Unit_Name] Like Forms!frm_Search_Db!txtUnit_name Or Forms!frm_Search_Db!txtUnit_Name Is Null)=True) And ([Inspection_Date] Between Forms!frm_Search_Db!txtStart_Date And Forms!frm_Search_Db!txtEnd_Date)
ORDER BY [Unit_name];

 

[pbaldy]

I know what you're doing, but that method doesn't account for leaving one or both date textboxes empty.

 

[Wiz47]

I know what you're doing, but that method doesn't account for leaving one or both date textboxes empty.

That's true. I'd have to account for that with additional code in the query.

 

[jdraw]

I think you may have missed something with this code

SELECT [tbl_IDS_MAIN ].*
FROM tbl_IDS_MAIN
WHERE (([BldgNumber] Like Forms!frm_Search_Db!txtBldg_Number Or Forms!frm_Search_Db!txtBldg_Number Is Null)=True) And (([Unit_Name] Like Forms!frm_Search_Db!txtUnit_name Or Forms!frm_Search_Db!txtUnit_Name Is Null)=True) And ([Inspection_Date] Between Forms!frm_Search_Db!txtStart_Date And Forms!frm_Search_Db!txtEnd_Date)
ORDER BY [Unit_name];

Did you mean = or did you want to look for a partial string

eg Like Forms!frm_Search_Db!txtBldg_Number & "*"

 

[Wiz47]

I think you may have missed something with this code



Did you mean = or did you want to look for a partial string

eg Like Forms!frm_Search_Db!txtBldg_Number & "*"

On certain fields, I wanted to allow for a partial string, so used "like" instead of "=".

For example, I wanted to be able to enter a partial bldg # so bldg# 12 or 1201 would both be picked up in the search. For my purposes, it was necessary to do that.

 

[rlangford]

Sorry I'm kind of getting more confused from this. How about this does anyone have a sample database that I could download and look at with a setup that address the question that I asked? Thanks again all!

 

[pbaldy]

There's a sample here that demonstrates one way to use dynamic SQL:

http://www.baldyweb.com/BuildSQL.htm