Entering Dates into Form

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

Hi,

I have a Form called SelectCustomer with a combo box
called ComboCustomer in which as sales rep can select one
of their customers. I also have two reports in my Form
below the combo box called 2003Sales and 2004Sales. They
are called CmdReport2003 and CmdReport2004. They are
based on a query which has VBA code in the customer name
field. This code is

Like IIf(IsNull([Forms]![SelectCustomer]!
[ComboCustomer]),"*",[Forms]![SelectCustomer]!
[ComboCustomer])

Right now I can select a customer from my combo box and
then click on one of the reports. I will get all
transactions for a given customer for 2003 or 2004
depending on which report I click. What I want to do is
to also create two text boxes in which a sales rep can
plug in a beginning and ending date. Below would be a
button for a report which would give you all sales for a
customer based on the dates you select. Can anyone tell
me how to do this?

Thanks,

Chuck
 
Chuck,

Well, here's one approach:

Place two text boxes on your form. One we'll
name 'txtStartDate' and the other 'txtEndDate'. Format
both of these text boxes as Short Date. You will
eventually need to check to be sure the use has provided
a value in each of these text boxes, but for now you will
just provide dates in these two boxes for testing.

Change the query to the following:

SELECT YourTblName.*, YourTblName.YourCustomerField,
YourTblName.YourDateField
FROM YourTblName
WHERE (((YourTblName.YourCustomerField)=[Forms]!
[YourFormName]![YourCustomerComboBoxName]) AND
((YourTblName.YourTransactionDateField
) Between [Forms]![YourFormName]![txtStartDate] And
[Forms]![YourFormName]![txtEndDate]));

You can do this by copying this statement and pasting it
into the sql view a query (if you create the query you
will be able to test for data with out actually opening
the report, then you can later just select the query as
the row source for the report) or you can just place the
sql string into the row source of the report. In any case
just use the sql statement above, substituting your form
name and your table and field names.

That should cause the report to show only records for the
selected Custome where the transaction date is between
the two date values.

HTH

Byron
 
Back
Top