Date restrictor on a form

  • Thread starter Thread starter chuckW
  • Start date Start date
C

chuckW

Hi,

I want to create a form in which a user can first select a
customer, then a start date and then an end date. The
from will then query a transaction that that has customer
names, transaction dates, products, amounts and revenue.
There is a separate customer table that I can use to
select my customers. What I want to do is to have the
form build a query that selects the customer in my
transaction file and then writes a date between statement
and then return all transactions that fulfill the query.
It would be nice but not totally necessary to have the
Active X calendar objects used to select a start and end
date.

Any ideas on how to do this?

Thanks,

Chuck
 
It's not clear from your message how you want to display the results of this
query, so I will concentrate on how you would build up the query itself.

Your form will probably have an unbound combo box for showing the list of
customers and selecting the customer ID. Assuming you use ID numbers as the
primary key for your customers then set up this box to display the customer
names, but store the customer ID. Call the combo box cboCustomer or
something similar.

You will need two text boxes for the start and end dates - call these
txtStartDate and txtEndDate, or something simlar.

You will need a command button to build the query, and then do whatever you
need to display the results. It would be a good idea to disable this button,
and add some code to the AfterUpdate events of your customer and date
controls to enable the button once all the data has been completed. The
click event will need to build the query. The code for this would include
something like this (untested):

Dim strWhere as String

strWhere = "CustomerID = " & Me.cboCustomer
strWhere = strWhere & " AND TransactionDate >= " &
Format(Me.txtStartDate,"\#mm\/dd\/yyyy\#")
strWhere = strWhere & " AND Transaction Date <= " &
Format(Me.txtEndDate,"\#mm\/dd\/yyyy\#")

This will give you the where clause (without the word where) of a SQL query.
How you use this will depend on how you display the results. You could open
a separate form or report, and use strWhere as the where condition (eg
DoCmd.OpenForm "NameOfForm",,,strWhere), or you could use it to filter the
form you are looking at, or you could use it as part of a complete SQL
string and then use this as the reord source of a form or report.

Hope this helps
 
Just create the query you want and put the criteria for customer equal to
the cutomer on the form,

Forms!frmSearch!cmbCustomerID

and the date betwen the start and end dates

Between Forms!frmSearch!txtStartDate and Forms!frmSearch!txtEndDate

Kelvin
 
Back
Top