On Jul 22, 10:18*pm, "Access Developer" <accde...@gmail.com> wrote:
> Instead of giving us code that doesn't work, could you describe verbally
> what you have, and what you want? *Examples are not sufficient as a
> definition (e.g., "starts from E1685 and finishes at E2504") ... I might
> assume what you want is "the character 'E' prepended to a
> monotonically-increasing integer", but obviously from the function you show,
> it is not that simple.
>
> What is clear is that the value is not "atomic" in normalization language-- *
> it is composed of multiple component parts, and such values are most often
> 'harder to work with'. *It's usually simpler to store each of the component
> parts separately and construct the multi-part value when needed for display
> to humans. *Often such a value is a "heritage" definition -- something that
> was used with manual procedures, never changed when the procedures were
> automated, and nobody ever realized that life could be made simpler by
> storing the component parts separately. *If not a "heritage" defintion,then
> it would be a "heritage" idea, from the mind of someone whose thinking is
> "locked in the past" (the human reading it should be able to determine
> multiple items of information from a single code).
>
> --
> *Larry Linson, Microsoft Office Access MVP
> *Co-author: "Microsoft Access Small Business Solutions", published by Wiley
> *Access newsgroup support is alive and well in USENET
> comp.databases.ms-access
>
> "Dan" <dan.cawtho...@gmail.com> wrote in message
>
> news:89ec1310-93ac-4520-9d42-(E-Mail Removed)...
>
>
>
>
>
>
>
> > Hi,
>
> > I Have a little code written on aform load
>
> > Which basicly looks up ProjectQNo Column, then adds the next number along
> > in that column new record at moment the table starts from E1685 and
> > Finishes at E2504
>
> > For some unknown reason when i open the form it thinks the next number
> > should be E2412 - but this has already been used.
>
> > the forms code is
>
> > If Me.NewRecord Then
> > * *Me!ProjectQNo = "E" & _
> > * * * *Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
> > * * * *"Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0")) + 1, _
> > * * * *"0000")
>
> > any body got any ideas? or is there a simpler code? i could use?
>
> > Thanks
>
> > Dam Cawthorne
Larry,
Thank you for your reply and explanation,
Basicly the code was designed originally to provide the following
format "Q 0000 YY" - I totally understand your logic using 3 Columns
to separate each component, the problem was I had already built the
database, forms etc and it would of been too pain full to go back at
insert 2
additional columns.
the way the code works is on a input form only, is it looks at the
ProjectQNo Column in tbl_Projects, Determines what the highest Q0000YY
number is? and then inserts the next number into the new record.
I Have recently changed the format of the ProjectQNo to "E0000" In an
Ideal world i should of created a column "Prefix" which Stores E then
ProjectQNo for the number increment, Hover-ever using the access Auto
feature does not give me the flexibility to change the number if its
to be changed.
Quick Solution for me is to store E0000 in one column. which is
automatic generated via a form.
Long term,
I would like to be able to create a table_prefix which stores the
format of the code so in the future if the ProjectQNo format is
changed it would be simple to change.
Regards
Dan
|
Similar Threads
