Inbetween dates (2k)

Topic

New Reply

I’m trying to open a form where the info displayed is inbetween two dates. I’ve got two text boxes, one asking for the starting date, the other asking for the ending date. Could someone show me the code to be able to do this?

Reply | Quote

Replies

Name the text boxes you are using to store your dates, for example: txtBegDate and txtEndDate.

In your query for the criteria use: Between Forms!frmName!txtBegDate and Forms!frmName!txtEndDate

Replace frmName with the name of your form containing the input text boxes.

Reply | Quote

I can get it working in the query, but I want to do it through code. This is what I have:

DoCmd.OpenForm “Sort”, , , [Date of Offense] > txtDate1.Value And < txtDate2.Value

Reply | Quote

Try the below in the Form_Open event of your subform. Don’t forget to change MyTableName to the table name where your records are stored.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dtBeginningDate As Date
Dim dtEndingDate As Date

Set db = CurrentDb

dtBeginningDate = CDate(Forms!DateEntryForm!Text0.Value)
dtEndingDate = CDate(Forms!DateEntryForm!Text2.Value)

strSQL = “SELECT MyTableName.*”
strSQL = strSQL & ” FROM MyTableName”
strSQL = strSQL & ” WHERE (((MyTableName.DATE) Between #” & dtBeginningDate & “# And #” & dtEndingDate & “#));”

Me.RecordSource = strSQL

Reply | Quote

Try the below in the Form_Open event of your subform. Don’t forget to change MyTableName to the table name where your records are stored.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dtBeginningDate As Date
Dim dtEndingDate As Date

Set db = CurrentDb

dtBeginningDate = CDate(Forms!DateEntryForm!Text0.Value)
dtEndingDate = CDate(Forms!DateEntryForm!Text2.Value)

strSQL = “SELECT MyTableName.*”
strSQL = strSQL & ” FROM MyTableName”
strSQL = strSQL & ” WHERE (((MyTableName.DATE) Between #” & dtBeginningDate & “# And #” & dtEndingDate & “#));”

Me.RecordSource = strSQL

Reply | Quote

Correction:

Try the below in the Form_Open event of your subform. Don’t forget to change MyTableName to the table name where your records are stored and the name of your form and the name of your text boxes.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dtBeginningDate As Date
Dim dtEndingDate As Date

Set db = CurrentDb

dtBeginningDate = CDate(Forms!MyFormName!MyTextBoxName.Value)
dtEndingDate = CDate(Forms! MyFormName!MyTextBoxName.Value)

strSQL = “SELECT MyTableName.*”
strSQL = strSQL & ” FROM MyTableName”
strSQL = strSQL & ” WHERE (((MyTableName.DATE) Between #” & dtBeginningDate & “# And #” & dtEndingDate & “#));”

Me.RecordSource = strSQL

Reply | Quote

its giving me an error when i try to run, saying that DAO.Database is not allowed

Reply | Quote

someone suggested using:

Dim Date1 As Date
Dim Date2 As Date

Date1 = txtDate1.Value
Date2 = txtDate2.Value

DoCmd.OpenForm “Sort”, acNormal, , “Between #” & Date1 & “# and #” & Date2 & “#”

But this is popping up with another error. Thought this might open some new waves of thought.

Reply | Quote

The problem with this is that you don’t say what field is supposed to be between the dates.

Here is some similar code, copied from a form where it works.

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = “[date of Event] Between #” & Format(Me![txtDate1], “mm/dd/yyyy”) & ” # and #” & Format(Me![txtDate2], “mm/dd/yyyy”) & “#”
stDocName = “frmTesting”
DoCmd.OpenForm stDocName, , , stLinkCriteria

I am not sure that you need to wrap the dates in the format function. Here in Australia we do, because we normally use dates in dd/mm/yyyy format.

Reply | Quote

The problem with this is that you don’t say what field is supposed to be between the dates.

Here is some similar code, copied from a form where it works.

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = “[date of Event] Between #” & Format(Me![txtDate1], “mm/dd/yyyy”) & ” # and #” & Format(Me![txtDate2], “mm/dd/yyyy”) & “#”
stDocName = “frmTesting”
DoCmd.OpenForm stDocName, , , stLinkCriteria

I am not sure that you need to wrap the dates in the format function. Here in Australia we do, because we normally use dates in dd/mm/yyyy format.

Reply | Quote

someone suggested using:

Dim Date1 As Date
Dim Date2 As Date

Date1 = txtDate1.Value
Date2 = txtDate2.Value

DoCmd.OpenForm “Sort”, acNormal, , “Between #” & Date1 & “# and #” & Date2 & “#”

But this is popping up with another error. Thought this might open some new waves of thought.

Reply | Quote

Have you set a reference to a DAO library?

Open any module. On the menu bar select Tools then References…

Reply | Quote

Have you set a reference to a DAO library?

Open any module. On the menu bar select Tools then References…

Reply | Quote

Get rid of:

‘Dim db As DAO.Database
‘Dim rst As DAO.Recordset

‘Set db = CurrentDb

Reply | Quote

Get rid of:

‘Dim db As DAO.Database
‘Dim rst As DAO.Recordset

‘Set db = CurrentDb

Reply | Quote

its giving me an error when i try to run, saying that DAO.Database is not allowed

Reply | Quote

Correction:

Try the below in the Form_Open event of your subform. Don’t forget to change MyTableName to the table name where your records are stored and the name of your form and the name of your text boxes.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dtBeginningDate As Date
Dim dtEndingDate As Date

Set db = CurrentDb

dtBeginningDate = CDate(Forms!MyFormName!MyTextBoxName.Value)
dtEndingDate = CDate(Forms! MyFormName!MyTextBoxName.Value)

strSQL = “SELECT MyTableName.*”
strSQL = strSQL & ” FROM MyTableName”
strSQL = strSQL & ” WHERE (((MyTableName.DATE) Between #” & dtBeginningDate & “# And #” & dtEndingDate & “#));”

Me.RecordSource = strSQL

Reply | Quote

Try this:

DoCmd.OpenForm “Sort”, , , “[Date of Offense] > = ” & CDate(Me.[txtDate1]) & ” And [Date of Offense] <= " & CDate(Me.[txtDate2])

This assumes that [Date of Offense] is, in fact, a date field rather than date-formatted text, and contains a general date. Keep in mind that if you are using any kind of non-US date format on your machine, you'll run into problems comparing dates in what amounts to a SQL string, since SQL requires US date format of month/day/year. If the textboxes on your form are unbound, the values in them will be strings, even if they look like dates, and CDate will convert them to actual dates in the system date format of the machine.

Reply | Quote

Try this:

DoCmd.OpenForm “Sort”, , , “[Date of Offense] > = ” & CDate(Me.[txtDate1]) & ” And [Date of Offense] <= " & CDate(Me.[txtDate2])

This assumes that [Date of Offense] is, in fact, a date field rather than date-formatted text, and contains a general date. Keep in mind that if you are using any kind of non-US date format on your machine, you'll run into problems comparing dates in what amounts to a SQL string, since SQL requires US date format of month/day/year. If the textboxes on your form are unbound, the values in them will be strings, even if they look like dates, and CDate will convert them to actual dates in the system date format of the machine.

Reply | Quote

I can get it working in the query, but I want to do it through code. This is what I have:

DoCmd.OpenForm “Sort”, , , [Date of Offense] > txtDate1.Value And < txtDate2.Value

Reply | Quote

Name the text boxes you are using to store your dates, for example: txtBegDate and txtEndDate.

In your query for the criteria use: Between Forms!frmName!txtBegDate and Forms!frmName!txtEndDate

Replace frmName with the name of your form containing the input text boxes.

Reply | Quote