Teresa,
I would suggest that the combobox have AfterUpdate with code behind the
form that updates two invisible controls called something like MinValue
and MaxValue:
Select Case combo1
Case "0000-0999"
MinValue = "0000"
MaxValue = "0999"
Case "1000-1999"
MinValue = "1000"
MaxValue = "1999"
End Select
And then for the criteria for the field in question in the query:
Between Forms!MyForm!MinValue And Forms!MyForm!MaxValue
And the rest of this answer is more about the query by form concept in
general but not necessarily using your particular control names.
Here's something I put together that may give you some ideas about
making time periods and other criteria dynamic.
The free downloadable sample database at
www.bullschmidt.com/access
uses the query by form concept so that on the invoices dialog one can
optionally choose a rep, a customer, and perhaps a date range, click on
a button that says "Input," and then have the invoice form open up
showing all the invoices that match the criteria.
And here is how the query by form concept can work.
On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *
Also on the invoices dialog there is a command button called cmdInput
to open the invoices form with the following code behind the OnClick
property:
DoCmd.OpenForm "frmInv"
And of course there could be a button to open a report the same way:
DoCmd.OpenReport "rptInv", acViewPreview
The invoices form (frmInv) has RecordSource property of qryInv.
And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And
[Forms]![frmInvDialog]![InvDateMax]
And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]
And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]
One related point is that you probably wouldn't want to allow blanks
(i.e. Nulls) in fields that are going to be used with Like in any
criteria for that field. Otherwise the blanks wouldn't be shown.
For example (based on what is entered into a last name search field):
Like 'Smith' would show Smith records
Like '' would show no records (probably not what one would want)
Like '*' would show all records
And to counter that I like to have the search fields have a
DefaultValue of * and not allow the search fields to be blank.
Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips
<<
I am new to Access and have been looking for answers to my problem on
the internet and have not found it yet. I hope someone can give me a
hint. Thanks so much.
I have a form (called: myform) in which there is a combo (combo1) and a
button (button1). In properties for combo1, I use "Value List" for Row
Source Type and in Row Source : "0000-0999";"1000-1999". So when the
form first load, in the combo1, there are 2 values in the drop-down
list which are: 0000-0999 and the second one is: 1000-1999. What I
want to do is that when I select the "0000-0999" value and click on the
'button1' button, a form(form1) will pops up in which there are many
columns and one column will have value from 0001 to 0999. The data are
stored in a table called: table1 (with fields like serial_no,
part_name, etc) .
I would appreciate a lot if anyone can give me a hint or show me where
to check for similar example.
