If I might amplify on this a little. There is a risk with date parameters
that they might be interpreted incorrectly due to international date format
variations. Literal date/time values must be in US format or otherwise
internationally unambiguous. In a query this is handled by declaring the
parameters as DateTime. In code to cater for this use the Format function to
format the dates as US short date format.
Also a more reliable way of defining a date range is to look for dates on or
greater than the start date, and less than the day after the end date. This
caters for unconstrained data which might inadvertently include non-zero
times of day.
So a more solid solution would be to use something along the lines of the
following code in the AfterUpdate event procedure of each date text box:
Dim strDateFrom as String, strDateTo As String
strDateFrom = "#" & Format(Nz(txtDateFrom,0),"mm/dd/yyyy") & "#"
strDateTo = "#" & Format(Nz(txtDateTo,VBA.Date),"mm/dd/yyyy") & "# + 1"
Me.cboCustomers.RowSource = _
"SELECT CustomerName" & _
" FROM YourTable " & _
" WHERE YourDate >= " & strDateFrom & _
" AND YourDate < " & strDateTo" & _
" ORDER BY CustomerName"
Me.cboCustomers.Requery
where cboCustomers is the combo box's name, txtDateFrom and txtDateTo are
the text boxes' names and YourDate is the name of the date field in the table
YourTable.
Note that the Nz function is used here to return values of 0 (which is 30
December 1899 in Access's date/time implementation) and the current date
(with the VBA.Date function) if a text box is Null. As well as preventing a
runtime error arising from a Null, this in effect makes each text box
optional. So, if txtDateFrom is left Null all rows from 30 December 1899 up
to the date entered in txtDateTo will be returned. Similarly, if txtDateTo
is left Null all rows from the date entered in txtdateFrom up to the current
date will be returned.
Remember that if any table, field or control names include spaces or special
characters you have to wrap them in square brackets [like this].
Ken Sheridan
Stafford, England
Pietro said:
Perfect!
I'll try it.
Thank you very much
JK said:
Oops typo
Me.ComboBox.RowSource="SELECT ..your fields, including To/From .... FROM
....your tabe ... WHERE [From]=>" & Me.From " AND [TO]<=" & Me.To & " ORDER
BY ...whatever ...."
Regards/JK
JK said:
Me.ComboBox.RowSource="SELECT ..your fields, including To/From .... FROM
...your tabe ... WHERE [From]=" & Me.From " AND [TO]=" & Me.To & " ORDER
BY ...whatever ...."
(the Whole Row Source must be a string)
Regards/JK
Hellow ,
I have a form that contains 2 txtboxes "From" and "To" that display
dates,
I have a combo box that 's supposed to display "Customer name",
I want to set the combobox RowSource to display the customers names
that
ranges between "From" text box and "To" text box.
Regards
