Microsoft Office Tutorials and References
In Depth Information
Creating Controlled Numbers
Case 5¶
Me.sfrmPeriod.Form.RecordSource = qryQuarter¶
Case 6¶
Me.sfrmPeriod.Form.RecordSource = qryYear¶
End Select¶
End Sub¶
The real work is done in the query. In general, using the FORMAT function
provides formatting as desired.
The snag? When the fields should be sorted by date, the format does not force a
prefix zero in front of the single-digit months, which causes a result with a
sequence such as 20041, 200410, 200411, 200412, 20042.
To overcome this, the often-used RIGHT provides a prefixed zero:
RIGHT(00&[Month],2).
This first places two zeroes (just to be sure) in front of the month and then
takes the last two characters of the result to always show a two-digit month.
For readability, the abbreviation of the period has been added to the Period
field in the subform.
Creating Controlled Numbers
This procedure creates ControlID numbers.
Acs
Example file:
A017 .mdb with form
frmControlledNumbers
Scenario: When designing an order system, coded keys
are often used. The problem is that they are not limitless.
When an order number is defined, for instance, as the
year + three digits, the maximum number of orders will be
999. When the system gets to 1,000 or more orders,
you’re in trouble. Using the AutoNumber is possible, but
has some limitations. The moment the database is made
replicatable for asynchronous use, the AutoNumber
changes from the default +1 into a randomly created
number.
 
Search JabSto ::




Custom Search