GENERATING AUTOMATIC

[dan.cawthorne]

I Want To Know If Theres an Easy Solution To Creating an Auto
generated Field

As I don't Really Want to Go Through all My Forms, Query, and Reports
Changing Everything.

At Moment I Have i Field Called "ProjectQNo" Text Type, Where The User
Just Types The Number In,

Though Ive Been Told They Want it Auto generated.

An Example Of a QNo Is

Q563707

Then Next One Would Be

Q563807


As You Notice The Last To Digits Is The Year, They Have Cold Me If it
Makes It Easier They'll Apcept

Q070001

Q070002

Any Suggestions,

I know One Way That Would To Created 3 Columns

Q | Year | No
-------------------
Q 07 0001

If This Is The only Way How Do i get Q Field To Automatically Be
Assigned in a New Record and Same With Year and No.

And Secondly Further on i Have a Project Search Dialog Via ProjectQNo
Which is Based a Query So The User Types The Project QNo and The
Project Is Displayed?

Thanks

Danny

 

[Steve]

Before you are able to find a solution, you need to find out what they want
for a Project Q# for the first project in 2008. If the last project in 2007
is Q563707, do they ant the first project in 2008 to be Q563808 or Q000108.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[dan.cawthorne]

Before you are able to find a solution, you need to find out what they want
for a Project Q# for the first project in 2008. If the last project in 2007
is Q563707, do they ant the first project in 2008 to be Q563808 or Q000108.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Thanks Steve, Good Point Mentioned,

Just Text'd My Director,

and they would prefer the Q000108

Sounds Like You know The Solution here?

You Going to be My Lifesaver

 

[Steve]

Put the following code in the Current event of the form where you enter new
projects:
If Me.NewRecord Then
If Format((Date()),"yy") <>
Right(DMax("[ProjectQNum]","NameOfProjectTable"),2) Then
Me!NameOfProjectQNumFieldOnForm = "Q0001" & Format((Date()),"yy")
Else
Me!NameOfProjectQNumFieldOnForm = "Q" &
Mid(DMax("[ProjectQNum]","NameOfProjectTable"),2,4)+1 &
Format((Date()),"yy")
End If
End If

This should give you Q563807 for the next project in 2007 and Q000108 for
the first project in 2008.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John W. Vinson]

Mid(DMax("[ProjectQNum]","NameOfProjectTable"),2,4)+1 &
Format((Date()),"yy")
End If
End If

This should give you Q563807 for the next project in 2007 and Q000108 for
the first project in 2008.

Actually, it will - but won't give you Q000108 for the second project, since
the DMax() function will return "Q563807" rather than "Q000108" as the largest
existing value. The DMax() will need to be done on Val(Mid([ProjectQNum], 2,
4)) instead.

John W. Vinson [MVP]

 

[dan.cawthorne]

Mid(DMax("[ProjectQNum]","NameOfProjectTable"),2,4)+1 &
Format((Date()),"yy")
End If
End If

This should give you Q563807 for the next project in 2007 and Q000108 for
the first project in 2008.

Actually, it will - but won't give you Q000108 for the second project, since
the DMax() function will return "Q563807" rather than "Q000108" as the largest
existing value. The DMax() will need to be done on Val(Mid([ProjectQNum], 2,
4)) instead.

John W. Vinson [MVP]

Both Thanks for the Help assistance,

Just One Point, Looking into your code.

Right(DMax("[ProjectQNum]","NameOfProjectTable"),2) Then

Should I Have it As Right(DMax("[Project QNo]","Projects"),2) Then

John lighty Confused Me With The New Auto generated Number coming up
the same, i only want the Q000108 to be first in 2008

I Presume This Code Looks at the 5 Projects I have in the database and
Follows the sequence?

 

[dan.cawthorne]

John lighty Confused Me With The New Auto generated Number coming up
the same, i only want the Q000108 to be first in 2008

I Presume This Code Looks at the 5 Projects I have in the database and
Follows the sequence?

Its Broken !!

Ive Adapted To Code To Suite My Fields and Table Names Code Used Is
Below, (Though I Did Make The Mistake When Creating The Tables In the
beginning Of Putting a Space Between The Project and QNo So Fields
Name In the Table "Projects" is [Project QNo]


Code:

If Me.NewRecord Then
If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")
End If
End If

End Sub

But On Each Line I Get a Complie Error

If Format((Date()),"yy") <> (Expected: Expression)

Right(DMax("[Project QNum]","Projects"),2) Then (Expected: End of
Statement)

Me!Project QNo = "Q" & (Expected: =)

Mid(DMax("[Project QNo]","Projects"),2,4)+1 & (Expected: =)

Format((Date()),"yy") (Expected: =)

Any Suggestions Whats Up

 

[Douglas J. Steele]

John lighty Confused Me With The New Auto generated Number coming up
the same, i only want the Q000108 to be first in 2008

I Presume This Code Looks at the 5 Projects I have in the database and
Follows the sequence?

Its Broken !!

Ive Adapted To Code To Suite My Fields and Table Names Code Used Is
Below, (Though I Did Make The Mistake When Creating The Tables In the
beginning Of Putting a Space Between The Project and QNo So Fields
Name In the Table "Projects" is [Project QNo]


Code:

If Me.NewRecord Then
If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")
End If
End If

End Sub

But On Each Line I Get a Complie Error

If Format((Date()),"yy") <> (Expected: Expression)

Right(DMax("[Project QNum]","Projects"),2) Then (Expected: End of
Statement)

Me!Project QNo = "Q" & (Expected: =)

Mid(DMax("[Project QNo]","Projects"),2,4)+1 & (Expected: =)

Format((Date()),"yy") (Expected: =)

Any Suggestions Whats Up

Looks as though you're the victim of line wrap issues.

If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then

is supposed to be a single line. Similarly,

Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")

is all supposed to be one line.

In an attempt to avoid line wrap issues, I've rewritten your code with line
continuation characters:

If Me.NewRecord Then
If Format((Date()),"yy") <> _
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

 

[dan.cawthorne]

Its Broken !!

Ive Adapted To Code To Suite My Fields and Table Names Code Used Is
Below, (Though I Did Make The Mistake When Creating The Tables In the
beginning Of Putting a Space Between The Project and QNo So Fields
Name In the Table "Projects" is [Project QNo]

Code:

If Me.NewRecord Then
If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")
End If
End If

End Sub

But On Each Line I Get a Complie Error

If Format((Date()),"yy") <> (Expected: Expression)

Right(DMax("[Project QNum]","Projects"),2) Then (Expected: End of
Statement)

Me!Project QNo = "Q" & (Expected: =)

Mid(DMax("[Project QNo]","Projects"),2,4)+1 & (Expected: =)

Format((Date()),"yy") (Expected: =)

Any Suggestions Whats Up

Looks as though you're the victim of line wrap issues.

If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then

is supposed to be a single line. Similarly,

Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")

is all supposed to be one line.

In an attempt to avoid line wrap issues, I've rewritten your code with line
continuation characters:

If Me.NewRecord Then
If Format((Date()),"yy") <> _
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

Thanks For Getting Back Intouch with me,

Ive Also Tried your Code, What are Line Wraps? What Causes Them?

Well The First 2 Lines Don't Show The Any Compile Errors Now

But From Here It Does

Me!Project QNo = "Q0001" & Format((Date()),"yy")

Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

 

[Douglas J. Steele]

Looks as though you're the victim of line wrap issues.

If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then

is supposed to be a single line. Similarly,

Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")

is all supposed to be one line.

In an attempt to avoid line wrap issues, I've rewritten your code with
line
continuation characters:

If Me.NewRecord Then
If Format((Date()),"yy") <> _
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

Thanks For Getting Back Intouch with me,

Ive Also Tried your Code, What are Line Wraps? What Causes Them?

Line Wrap is what happens in the responses when you try to display too many
characters in the response.

Most newsgroup clients limit how long a line of text can be. For example, my
settings allow a line of text to be no longer than 76 characters. When you
type more that that number of characters on a single line, the extra
characters will be put on another line.

Well The First 2 Lines Don't Show The Any Compile Errors Now

But From Here It Does

Me!Project QNo = "Q0001" & Format((Date()),"yy")

Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

Since you've got a space in the name (which isn't recommended, by the way),
you need to put square brackets around the names:


Me![Project QNo] = "Q0001" & Format((Date()),"yy")
Else
Me![Project QNo] = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If

Sorry I didn't notice that before: since I never use spaces in names, it's
not something I think to look for.

 

[dan.cawthorne]

Looks as though you're the victim of line wrap issues.
If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then
is supposed to be a single line. Similarly,
Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")
is all supposed to be one line.
In an attempt to avoid line wrap issues, I've rewritten your code with
line
continuation characters:
If Me.NewRecord Then
If Format((Date()),"yy") <> _
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

Thanks For Getting Back Intouch with me,

Ive Also Tried your Code, What are Line Wraps? What Causes Them?

Line Wrap is what happens in the responses when you try to display too many
characters in the response.

Most newsgroup clients limit how long a line of text can be. For example, my
settings allow a line of text to be no longer than 76 characters. When you
type more that that number of characters on a single line, the extra
characters will be put on another line.

Well The First 2 Lines Don't Show The Any Compile Errors Now

But From Here It Does

Me!Project QNo = "Q0001" & Format((Date()),"yy")

Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

Since you've got a space in the name (which isn't recommended, by the way),
you need to put square brackets around the names:

Me![Project QNo] = "Q0001" & Format((Date()),"yy")
Else
Me![Project QNo] = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If

Sorry I didn't notice that before: since I never use spaces in names, it's
not something I think to look for.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)- Hide quoted text -

- Show quoted text -

Thanks, I Give That a Try,

Yeh I Went Back this Morning and Renamed the Fields With Out The
Spaces, Had a Slight Knock on effect With Some SQL's, Well All Of
them, Its a Lesson Worth

Learnt, They Say You Learn the Hard Way

Well Thanks For Every One Whos Helping,

Lets See If It Works

 

[dan.cawthorne]

Looks as though you're the victim of line wrap issues.
If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then
is supposed to be a single line. Similarly,
Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")
is all supposed to be one line.
In an attempt to avoid line wrap issues, I've rewritten your code with
line
continuation characters:
If Me.NewRecord Then
If Format((Date()),"yy") <> _
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

Thanks For Getting Back Intouch with me,

Ive Also Tried your Code, What are Line Wraps? What Causes Them?

Line Wrap is what happens in the responses when you try to display too many
characters in the response.

Most newsgroup clients limit how long a line of text can be. For example, my
settings allow a line of text to be no longer than 76 characters. When you
type more that that number of characters on a single line, the extra
characters will be put on another line.

Well The First 2 Lines Don't Show The Any Compile Errors Now

But From Here It Does

Me!Project QNo = "Q0001" & Format((Date()),"yy")

Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

Since you've got a space in the name (which isn't recommended, by the way),
you need to put square brackets around the names:

Me![Project QNo] = "Q0001" & Format((Date()),"yy")
Else
Me![Project QNo] = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If

Sorry I didn't notice that before: since I never use spaces in names, it's
not something I think to look for.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)- Hide quoted text -

- Show quoted text -

Back Again, Short lived that was,

Got No Complie Errors Now, Good Sign,

Used The Code

Private Sub Form_Current()

If Me.NewRecord Then
If Format((Date), "yy") <> Right( _
DMax("[ProjectQNo]", "Projects"), 2) Then
Me![ProjectQNo] = "Q0001" & Format((Date), "yy")
Else
Me![ProjectQNo] = "Q" & _
Mid(DMax("[Project QNo]", "Projects"), 2, 4) + 1 & _
Format((Date), "yy")
End If
End If

End Sub

Whats Happening Now Though When I Open The Project Input form (Date
Entery Set Yes)

The ProjectQNo Field is Displaying Q07 its Missing the 5638 (Q563807)

And If I Close The Form, and Reopen The Same Q07 is Displayed But if
Try Close The Form It Throws a Wobbly Cause Q07 is All Ready Used?

 

[John W. Vinson]

Whats Happening Now Though When I Open The Project Input form (Date
Entery Set Yes)

The ProjectQNo Field is Displaying Q07 its Missing the 5638 (Q563807)

And If I Close The Form, and Reopen The Same Q07 is Displayed But if
Try Close The Form It Throws a Wobbly Cause Q07 is All Ready Used?

I'd actually suggest using the form's BeforeInsert event (which fires when you
first start using the record) rather than the Current event. Also, Steve's
suggestion assumes that the Q numbers sort in ascending order by date entered.
They don't, since the year is at the end of the number, not the beginning! The
maximum Q number will NOT be the most recent.

Try:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim vMax As Variant
Dim iMax As Integer
vMax = DMax("[ProjectQNo]", "[Projects]", _
"[ProjectQNo] LIKE '*" & Format(Date, "yy") & "'')
If IsNull(vMax) Then
' first number this year
Me!ProjectQNo = "Q0001" & Format(Date, "yy")
Else
' extract number from found maximum value
iMax = Val(Mid(vMax, 2, 4))
If iMax = 9999 Then ' all possible Q numbers used already
MsgBox "Go home, you're done for the year", vbOKOnly
Cancel = True
Else
Me!ProjectQNo = "Q" & Format(iMax + 1, "0000") _
& Format(Date, "yy")
End If
End If

End Sub

John W. Vinson [MVP]

 

[dan.cawthorne]

news:[email protected]...

Looks as though you're the victim of line wrap issues.
If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then
is supposed to be a single line. Similarly,
Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")
is all supposed to be one line.
In an attempt to avoid line wrap issues, I've rewritten your code with
line
continuation characters:
If Me.NewRecord Then
If Format((Date()),"yy") <> _
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If
Thanks For Getting Back Intouch with me,
Ive Also Tried your Code, What are Line Wraps? What Causes Them?

Line Wrap is what happens in the responses when you try to display too many
characters in the response.

Most newsgroup clients limit how long a line of text can be. For example, my
settings allow a line of text to be no longer than 76 characters. When you
type more that that number of characters on a single line, the extra
characters will be put on another line.

Well The First 2 Lines Don't Show The Any Compile Errors Now
But From Here It Does
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If

Since you've got a space in the name (which isn't recommended, by the way),
you need to put square brackets around the names:

Me![Project QNo] = "Q0001" & Format((Date()),"yy")
Else
Me![Project QNo] = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If

Sorry I didn't notice that before: since I never use spaces in names, it's
not something I think to look for.

- Show quoted text -

Back Again, Short lived that was,

Got No Complie Errors Now, Good Sign,

Used The Code

Private Sub Form_Current()

If Me.NewRecord Then
If Format((Date), "yy") <> Right( _
DMax("[ProjectQNo]", "Projects"), 2) Then
Me![ProjectQNo] = "Q0001" & Format((Date), "yy")
Else
Me![ProjectQNo] = "Q" & _
Mid(DMax("[Project QNo]", "Projects"), 2, 4) + 1 & _
Format((Date), "yy")
End If
End If

End Sub

Whats Happening Now Though When I Open The Project Input form (Date
Entery Set Yes)

The ProjectQNo Field is Displaying Q07 its Missing the 5638 (Q563807)

And If I Close The Form, and Reopen The Same Q07 is Displayed But if
Try Close The Form It Throws a Wobbly Cause Q07 is All Ready Used?

Forget The Last Post, I forgot I Forgot To Take, the Space out in the
Last Line, Its Working Great!

Questions

1. John W Vinson Was Talking About Modifing the code, with
Val(Mid([ProjectQNo], 2,4)) What Is it and Where Does It Go? Is It
advisable?

2. Refering Back To Steve Comment Second Reply, If I Was To Have The
Numbering System To Carrying On Instead of Resetting to 000108 for the
new Year Is there Much involved in Changing the Code?

3. I Notice that if the form is closed by mistake, with out filling in
the form, the the ProjectQNo Is Still Added, So I Though About Adding
A Save Prompt or Do Not Save Action on The Cancel Button,

That Doesnt Work Code Used Was

DoCmd.Close acForm, "Project_Input_Wizard", acSaveNo But Don't Do
anything

 

[dan.cawthorne]

Looks as though you're the victim of line wrap issues.
If Format((Date()),"yy") <>
Right(DMax("[Project QNum]","Projects"),2) Then
is supposed to be a single line. Similarly,
Me!Project QNo = "Q" &
Mid(DMax("[Project QNo]","Projects"),2,4)+1 &
Format((Date()),"yy")
is all supposed to be one line.
In an attempt to avoid line wrap issues, I've rewritten your code with
line
continuation characters:
If Me.NewRecord Then
If Format((Date()),"yy") <> _
Right(DMax("[Project QNum]","Projects"),2) Then
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If
Thanks For Getting Back Intouch with me,
Ive Also Tried your Code, What are Line Wraps? What Causes Them?
Line Wrap is what happens in the responses when you try to display too many
characters in the response.
Most newsgroup clients limit how long a line of text can be. For example, my
settings allow a line of text to be no longer than 76 characters. When you
type more that that number of characters on a single line, the extra
characters will be put on another line.
Well The First 2 Lines Don't Show The Any Compile Errors Now
But From Here It Does
Me!Project QNo = "Q0001" & Format((Date()),"yy")
Else
Me!Project QNo = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
End If
Since you've got a space in the name (which isn't recommended, by the way),
you need to put square brackets around the names:
Me![Project QNo] = "Q0001" & Format((Date()),"yy")
Else
Me![Project QNo] = "Q" & _
Mid(DMax("[Project QNo]","Projects"),2,4)+1 & _
Format((Date()),"yy")
End If
Sorry I didn't notice that before: since I never use spaces in names, it's
not something I think to look for.

Back Again, Short lived that was,

Got No Complie Errors Now, Good Sign,

Used The Code

Private Sub Form_Current()

If Me.NewRecord Then
If Format((Date), "yy") <> Right( _
DMax("[ProjectQNo]", "Projects"), 2) Then
Me![ProjectQNo] = "Q0001" & Format((Date), "yy")
Else
Me![ProjectQNo] = "Q" & _
Mid(DMax("[Project QNo]", "Projects"), 2, 4) + 1 & _
Format((Date), "yy")
End If
End If

End Sub

Whats Happening Now Though When I Open The Project Input form (Date
Entery Set Yes)

The ProjectQNo Field is Displaying Q07 its Missing the 5638 (Q563807)

And If I Close The Form, and Reopen The Same Q07 is Displayed But if
Try Close The Form It Throws a Wobbly Cause Q07 is All Ready Used?

Forget The Last Post, I forgot I Forgot To Take, the Space out in the
Last Line, Its Working Great!

Questions

1. John W Vinson Was Talking About Modifing the code, with
Val(Mid([ProjectQNo], 2,4)) What Is it and Where Does It Go? Is It
advisable?

2. Refering Back To Steve Comment Second Reply, If I Was To Have The
Numbering System To Carrying On Instead of Resetting to 000108 for the
new Year Is there Much involved in Changing the Code?

3. I Notice that if the form is closed by mistake, with out filling in
the form, the the ProjectQNo Is Still Added, So I Though About Adding
A Save Prompt or Do Not Save Action on The Cancel Button,

That Doesnt Work Code Used Was

DoCmd.Close acForm, "Project_Input_Wizard", acSaveNo But Don't Do
anything

Sorry John i Didn't See Your Reply There!!!

 

[David W. Fenton]

Mid(DMax("[ProjectQNum]","NameOfProjectTable"),2,4)+1 &
Format((Date()),"yy")
End If
End If

This should give you Q563807 for the next project in 2007 and
Q000108 for the first project in 2008.

Actually, it will - but won't give you Q000108 for the second
project, since the DMax() function will return "Q563807" rather
than "Q000108" as the largest existing value. The DMax() will need
to be done on Val(Mid([ProjectQNum], 2, 4)) instead.

And that is an indication that this one data field should be split
into two fields, or if the "Q" is always there, just leave it out
entirely.

 

[David W. Fenton]

Mid(DMax("[ProjectQNum]","NameOfProjectTable"),2,4)+1 &
Format((Date()),"yy")
End If
End If

This should give you Q563807 for the next project in 2007 and
Q000108 for the first project in 2008.

Actually, it will - but won't give you Q000108 for the second
project, since the DMax() function will return "Q563807" rather
than "Q000108" as the largest existing value. The DMax() will need
to be done on Val(Mid([ProjectQNum], 2, 4)) instead.

I should read further back in the thread -- it's 3 fields, not 2, as
the last two digits indicate the year.

 

[dan.cawthorne]

On Wed, 25 Jul 2007 00:39:52 GMT, "Steve"

Mid(DMax("[ProjectQNum]","NameOfProjectTable"),2,4)+1 &
Format((Date()),"yy")
End If
End If
This should give you Q563807 for the next project in 2007 and
Q000108 for the first project in 2008.

Actually, it will - but won't give you Q000108 for the second
project, since the DMax() function will return "Q563807" rather
than "Q000108" as the largest existing value. The DMax() will need
to be done on Val(Mid([ProjectQNum], 2, 4)) instead.

I should read further back in the thread -- it's 3 fields, not 2, as
the last two digits indicate the year.

Hello David,

Na It is actualy One Field still,

It Just Auto Generates Next Quotation Number But There that Fussy
The Company, They Want The "Q" Letter in Front as You See, We Also
Have Contract Numbers and Order Numbers,So it Splits them out,

as i did mention in a recent reply the code, resents the QNo back to
0001 for every year Which I think is a good idea, but one of directors
is been funny and thinks we should just carry the sequence but change
the end date for new year.

 

[Douglas J. Steele]

Hello David,

Na It is actualy One Field still,

It Just Auto Generates Next Quotation Number But There that Fussy
The Company, They Want The "Q" Letter in Front as You See, We Also
Have Contract Numbers and Order Numbers,So it Splits them out,

David's point is that you're storing 3 pieces of information in one field,
which violates database normalization principles. Each piece of information
should be stored in a single field.

You can always create a query that concatenates the 3 separate fields into 1
for display purposes, and use the query wherever you would otherwise have
used the table.

 

[David W. Fenton]

@private.emailaddress> wrote:

Mid(DMax("[ProjectQNum]","NameOfProjectTable"),2,4)+1 &
Format((Date()),"yy")
End If
End If

This should give you Q563807 for the next project in 2007 and
Q000108 for the first project in 2008.

Actually, it will - but won't give you Q000108 for the second
project, since the DMax() function will return "Q563807" rather
than "Q000108" as the largest existing value. The DMax() will
need to be done on Val(Mid([ProjectQNum], 2, 4)) instead.

I should read further back in the thread -- it's 3 fields, not 2,
as the last two digits indicate the year.

Na It is actualy One Field still,

My point is that it *shoulc* be two or three fields, as you're
storing 3 independent pieces of information in one field:

Q + 5638 + 07

I don't know if Q is always the same or if the letter can differe,
but it's clear that 5638 is a number sequence, and 07 is an
independent number sequence. That means that those are independent
attributes and belong in separate fields.

Of course, you can then create a composite index on the 3 fields to
make sure the concatenated value is unique.

It Just Auto Generates Next Quotation Number But There that
Fussy The Company, They Want The "Q" Letter in Front as You See,
We Also Have Contract Numbers and Order Numbers,So it Splits them
out,

You don't have to store it if it's always there -- you just have to
display it.

as i did mention in a recent reply the code, resents the QNo back
to 0001 for every year Which I think is a good idea, but one of
directors is been funny and thinks we should just carry the
sequence but change the end date for new year.

Either way, you've got two independent sequences stored in one
field, and that's WRONG -- it's denormalized data.