Code Stopped Working Help

Hi All,

A Few Months ago i had some help and creating some code that
automatically generated a number for each project which was recieved,

its been working fine untill We hit the Year 2008,

The Code Made up a Value for example
Q586707
Q586807
Q586907 < Last Number for  2007

The Code Then Was Suppose to Start the first number off as

Q000108

which it did, and no when i open the wizard i created it keeps
generating that number its not moving up to Q000208

and you see the number is made up of 2 parts Q0000 08

Heres the code and its on the forms Current Event Procedure

Private Sub Form_Current()

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

End Sub

Whats wrong with the code?
0
dan
1/4/2008 4:30:58 PM
access.formscoding 7493 articles. 0 followers. Follow

8 Replies
668 Views

Similar Articles

[PageSpeed] 47

Your code's logic is faulty. This code step is the problem:

  If Format((Date), "yy") <> Right( _
    DMax("ProjectQNo", "tbl_Projects"), 2) Then
    Me!ProjectQNo = "Q0001" & Format((Date), "yy")

You can see the problem if you open a query on tbl_Projects and sort on the 
ProjectQNo field. You'll see that the maximum value for that field is the 
last "value" for 2007, not the newest value for 2008.

The problem is exacerbated by the fact that you're storing three separate 
data values in a single field: "Q", "0000", and "yy". You should use three 
separate fields to store these atomic data items, and then concatenate them 
together for display purposes. Then you could more easily find the maximum 
value for a single year and get the next value. Then your code would look 
like this:

Private Sub Form_Current()
If Me.NewRecord Then
    Me!Prefix_ProjectQNo = "Q"
    Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
        "tbl_Projects", "Year_ProjectQNo=" & _
        CLng(Format(Date),"yy"))), 0) + 1
    Me!Year_ProjectQNo = Year(Date)
    Me!ProjectQNo = Me!Prefix_ProjectQNo & _
        Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
End If
End Sub



In the meantime, I recommend that you change your code to this (see how 
complicated the parsing gets when you store three separate values in a 
single field?):

Private Sub Form_Current()
If Me.NewRecord Then
    Me!ProjectQNo = "Q" & _
        Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
        "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0"))+1, _
        "0000") & Format(Date, "yy")
End If
End Sub

-- 

        Ken Snell
<MS ACCESS MVP>



<dan.cawthorne@gmail.com> wrote in message 
news:05b722d1-9eca-452c-89ef-2c10ad6fb6a0@1g2000hsl.googlegroups.com...
> Hi All,
>
> A Few Months ago i had some help and creating some code that
> automatically generated a number for each project which was recieved,
>
> its been working fine untill We hit the Year 2008,
>
> The Code Made up a Value for example
> Q586707
> Q586807
> Q586907 < Last Number for  2007
>
> The Code Then Was Suppose to Start the first number off as
>
> Q000108
>
> which it did, and no when i open the wizard i created it keeps
> generating that number its not moving up to Q000208
>
> and you see the number is made up of 2 parts Q0000 08
>
> Heres the code and its on the forms Current Event Procedure
>
> Private Sub Form_Current()
>
> If Me.NewRecord Then
>  If Format((Date), "yy") <> Right( _
>    DMax("ProjectQNo", "tbl_Projects"), 2) Then
>    Me!ProjectQNo = "Q0001" & Format((Date), "yy")
>  Else
>    Me![ProjectQNo] = "Q" & _
>      Mid(DMax("ProjectQNo", "tbl_Projects"), 2, 4) + 1 & _
>      Format((Date), "yy")
>  End If
>  End If
>
> End Sub
>
> Whats wrong with the code? 


0
Ken
1/4/2008 5:05:41 PM
On Fri, 4 Jan 2008 08:30:58 -0800 (PST), "dan.cawthorne@gmail.com"
<dan.cawthorne@gmail.com> wrote:

>Hi All,
>
>A Few Months ago i had some help and creating some code that
>automatically generated a number for each project which was recieved,
>
>its been working fine untill We hit the Year 2008,
>
>The Code Made up a Value for example
>Q586707
>Q586807
>Q586907 < Last Number for  2007
>
>The Code Then Was Suppose to Start the first number off as
>
>Q000108
>
>which it did, and no when i open the wizard i created it keeps
>generating that number its not moving up to Q000208
>
>and you see the number is made up of 2 parts Q0000 08
>
>Heres the code and its on the forms Current Event Procedure
>
>Private Sub Form_Current()
>
>If Me.NewRecord Then
>  If Format((Date), "yy") <> Right( _
>    DMax("ProjectQNo", "tbl_Projects"), 2) Then
>    Me!ProjectQNo = "Q0001" & Format((Date), "yy")
>  Else
>    Me![ProjectQNo] = "Q" & _
>      Mid(DMax("ProjectQNo", "tbl_Projects"), 2, 4) + 1 & _
>      Format((Date), "yy")
>  End If
>  End If
>
>End Sub
>
>Whats wrong with the code?

What's wrong is that it's looking at the maximum Q number. The text string
"Q586907" is in fact greater than the string "Q000108" - *YOU* know that the
08 at the end should dominate the preceding five characters, but your code
doesn't!

This kind of "intelligent key" is generally A Bad Idea, for this very reason.
You need some complicated code, and some very inefficient searches, to find
the increment. Could you consider instead JUST using a ProjectDate or
ProjectYear field, and an Integer number that you increment? YOu can
concatenatate the pieces for display purposes, and you'll have just a simple
lookup to find the largest existing number.

             John W. Vinson [MVP]
0
John
1/4/2008 6:37:50 PM
On 4 Jan, 18:37, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Fri, 4 Jan 2008 08:30:58 -0800 (PST), "dan.cawtho...@gmail.com"
>
>
>
> <dan.cawtho...@gmail.com> wrote:
> >Hi All,
>
> >A Few Months ago i had some help and creating some code that
> >automatically generated a number for each project which was recieved,
>
> >its been working fine untill We hit the Year 2008,
>
> >The Code Made up a Value for example
> >Q586707
> >Q586807
> >Q586907 < Last Number for  2007
>
> >The Code Then Was Suppose to Start the first number off as
>
> >Q000108
>
> >which it did, and no when i open the wizard i created it keeps
> >generating that number its not moving up to Q000208
>
> >and you see the number is made up of 2 parts Q0000 08
>
> >Heres the code and its on the forms Current Event Procedure
>
> >Private Sub Form_Current()
>
> >If Me.NewRecord Then
> >  If Format((Date), "yy") <> Right( _
> >    DMax("ProjectQNo", "tbl_Projects"), 2) Then
> >    Me!ProjectQNo = "Q0001" & Format((Date), "yy")
> >  Else
> >    Me![ProjectQNo] = "Q" & _
> >      Mid(DMax("ProjectQNo", "tbl_Projects"), 2, 4) + 1 & _
> >      Format((Date), "yy")
> >  End If
> >  End If
>
> >End Sub
>
> >Whats wrong with the code?
>
> What's wrong is that it's looking at the maximum Q number. The text string
> "Q586907" is in fact greater than the string "Q000108" - *YOU* know that the
> 08 at the end should dominate the preceding five characters, but your code
> doesn't!
>
> This kind of "intelligent key" is generally A Bad Idea, for this very reason.
> You need some complicated code, and some very inefficient searches, to find
> the increment. Could you consider instead JUST using a ProjectDate or
> ProjectYear field, and an Integer number that you increment? YOu can
> concatenatate the pieces for display purposes, and you'll have just a simple
> lookup to find the largest existing number.
>
>              John W. Vinson [MVP]

Thank You Very Much Ken and John for the explanation its makes sense,
to me now,

and also Ken Thanks for the

Private Sub Form_Current()
If Me.NewRecord Then
    Me!ProjectQNo = "Q" & _
        Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
        "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0"))+1,
_
        "0000") & Format(Date, "yy")
End If
End Sub

It Seems to have corrected the problem, but as before all 2008
projects are now been automatically been put to the front of all the
2007 Records.
which you would expect it to.

So the solution would be to split the field into 3 field [Qfield]
[NumberField] and [YearField]

Which i could easily have the 3 field on the New Project Wizard all
next to each other. The Qfield always be a Q and the Year will change
on the year.

So How Would I for the form to increment the Number only, and then
always put the year and Q in the other field.

My Concerns are with the design on my database, Ive Made a great
mistake in having me Primary Key as the [ProjectQno] field
which there fore i have relationships set up from a drawing table, and
clients table and also i have Query Searched on the ProjectQNo

and at current theres about 296 Records in the Projects Table.

Would be Safe to Use the code Ken gave me,

or should i consider changing it?

Regards

Dan

Ps The Increment of Q/0000/08 has the be that way has the company have
had it that way for last 10 Years and not willing to change there ways.
0
dan
1/5/2008 3:22:05 PM
Reply inline...
-- 

        Ken Snell
<MS ACCESS MVP>


<dan.cawthorne@gmail.com> wrote in message 
news:0dfb9b1a-f950-48e8-867b-c1a8d75d9be9@1g2000hsl.googlegroups.com...
> Thank You Very Much Ken and John for the explanation its makes sense,
> to me now,
>
> and also Ken Thanks for the
>
> Private Sub Form_Current()
> If Me.NewRecord Then
>    Me!ProjectQNo = "Q" & _
>        Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
>        "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0"))+1,
> _
>        "0000") & Format(Date, "yy")
> End If
> End Sub
>
> It Seems to have corrected the problem, but as before all 2008
> projects are now been automatically been put to the front of all the
> 2007 Records.
> which you would expect it to.
>
> So the solution would be to split the field into 3 field [Qfield]
> [NumberField] and [YearField]
>
> Which i could easily have the 3 field on the New Project Wizard all
> next to each other. The Qfield always be a Q and the Year will change
> on the year.
>
> So How Would I for the form to increment the Number only, and then
> always put the year and Q in the other field.

I provided sample code for this solution in my first reply to your post. 
Here is the code example again:

Private Sub Form_Current()
If Me.NewRecord Then
    Me!Prefix_ProjectQNo = "Q"
    Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
        "tbl_Projects", "Year_ProjectQNo=" & _
        CLng(Format(Date),"yy"))), 0) + 1
    Me!Year_ProjectQNo = Year(Date)
    Me!ProjectQNo = Me!Prefix_ProjectQNo & _
        Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
End If
End Sub

The above assumes that you use a Number data type (Field Size is Long 
Integer) for the 2-digit year value.





>
> My Concerns are with the design on my database, Ive Made a great
> mistake in having me Primary Key as the [ProjectQno] field
> which there fore i have relationships set up from a drawing table, and
> clients table and also i have Query Searched on the ProjectQNo
>
> and at current theres about 296 Records in the Projects Table.
>
> Would be Safe to Use the code Ken gave me,
>
> or should i consider changing it?
>
> Regards
>
> Dan
>
> Ps The Increment of Q/0000/08 has the be that way has the company have
> had it that way for last 10 Years and not willing to change there ways. 


0
Ken
1/5/2008 5:31:59 PM
On 5 Jan, 17:31, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> Reply inline...
> --
>
>         Ken Snell
> <MS ACCESS MVP>
>
> <dan.cawtho...@gmail.com> wrote in message
>
> news:0dfb9b1a-f950-48e8-867b-c1a8d75d9be9@1g2000hsl.googlegroups.com...
>
>
>
> > Thank You Very Much Ken and John for the explanation its makes sense,
> > to me now,
>
> > and also Ken Thanks for the
>
> > Private Sub Form_Current()
> > If Me.NewRecord Then
> >    Me!ProjectQNo = "Q" & _
> >        Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
> >        "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0"))+1,
> > _
> >        "0000") & Format(Date, "yy")
> > End If
> > End Sub
>
> > It Seems to have corrected the problem, but as before all 2008
> > projects are now been automatically been put to the front of all the
> > 2007 Records.
> > which you would expect it to.
>
> > So the solution would be to split the field into 3 field [Qfield]
> > [NumberField] and [YearField]
>
> > Which i could easily have the 3 field on the New Project Wizard all
> > next to each other. The Qfield always be a Q and the Year will change
> > on the year.
>
> > So How Would I for the form to increment the Number only, and then
> > always put the year and Q in the other field.
>
> I provided sample code for this solution in my first reply to your post.
> Here is the code example again:
>
> Private Sub Form_Current()
> If Me.NewRecord Then
>     Me!Prefix_ProjectQNo = "Q"
>     Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
>         "tbl_Projects", "Year_ProjectQNo=" & _
>         CLng(Format(Date),"yy"))), 0) + 1
>     Me!Year_ProjectQNo = Year(Date)
>     Me!ProjectQNo = Me!Prefix_ProjectQNo & _
>         Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
> End If
> End Sub
>
> The above assumes that you use a Number data type (Field Size is Long
> Integer) for the 2-digit year value.

Assume I had to create following fields

[Prefix_ProjectQNo]
[Number_ProjectQNo]
[Year_ProjectQNo]

in the tblprojects

the problem is i getting complie errors in the sample code you gave me

following lines that are giving me compile errors are as

   Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo",_
        "tbl_Projects", "Year_ProjectQNo=" &_
        CLng(Format(Date),"yy"))), 0) + 1

and

  Me!ProjectQNo = Me!Prefix_ProjectQNo & _
        Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy"
0
dan
1/7/2008 10:03:50 AM
For the first code block, you need a space in front of the trailing _ 
character (the line continuation character); you don't have that in your 
code:

   Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
        "tbl_Projects", "Year_ProjectQNo=" & _
        CLng(Format(Date),"yy"))), 0) + 1


In the second code block, there is a ) missing from the end of the second 
line, and a ( missing after the Date:

Me!ProjectQNo = Me!Prefix_ProjectQNo & _
       Format(Me!Number_ProjectQNo,"0000") & Format(Date(),"yy")

-- 

        Ken Snell
<MS ACCESS MVP>



<dan.cawthorne@gmail.com> wrote in message 
news:4e73682d-d5bb-4548-9d1c-bc95a64d6e5f@i12g2000prf.googlegroups.com...
> On 5 Jan, 17:31, "Ken Snell \(MVP\)"
> <kthsneisll...@ncoomcastt.renaetl> wrote:
>> Reply inline...
>> --
>>
>>         Ken Snell
>> <MS ACCESS MVP>
>>
>> <dan.cawtho...@gmail.com> wrote in message
>>
>> news:0dfb9b1a-f950-48e8-867b-c1a8d75d9be9@1g2000hsl.googlegroups.com...
>>
>>
>>
>> > Thank You Very Much Ken and John for the explanation its makes sense,
>> > to me now,
>>
>> > and also Ken Thanks for the
>>
>> > Private Sub Form_Current()
>> > If Me.NewRecord Then
>> >    Me!ProjectQNo = "Q" & _
>> >        Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
>> >        "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0"))+1,
>> > _
>> >        "0000") & Format(Date, "yy")
>> > End If
>> > End Sub
>>
>> > It Seems to have corrected the problem, but as before all 2008
>> > projects are now been automatically been put to the front of all the
>> > 2007 Records.
>> > which you would expect it to.
>>
>> > So the solution would be to split the field into 3 field [Qfield]
>> > [NumberField] and [YearField]
>>
>> > Which i could easily have the 3 field on the New Project Wizard all
>> > next to each other. The Qfield always be a Q and the Year will change
>> > on the year.
>>
>> > So How Would I for the form to increment the Number only, and then
>> > always put the year and Q in the other field.
>>
>> I provided sample code for this solution in my first reply to your post.
>> Here is the code example again:
>>
>> Private Sub Form_Current()
>> If Me.NewRecord Then
>>     Me!Prefix_ProjectQNo = "Q"
>>     Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
>>         "tbl_Projects", "Year_ProjectQNo=" & _
>>         CLng(Format(Date),"yy"))), 0) + 1
>>     Me!Year_ProjectQNo = Year(Date)
>>     Me!ProjectQNo = Me!Prefix_ProjectQNo & _
>>         Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
>> End If
>> End Sub
>>
>> The above assumes that you use a Number data type (Field Size is Long
>> Integer) for the 2-digit year value.
>
> Assume I had to create following fields
>
> [Prefix_ProjectQNo]
> [Number_ProjectQNo]
> [Year_ProjectQNo]
>
> in the tblprojects
>
> the problem is i getting complie errors in the sample code you gave me
>
> following lines that are giving me compile errors are as
>
>   Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo",_
>        "tbl_Projects", "Year_ProjectQNo=" &_
>        CLng(Format(Date),"yy"))), 0) + 1
>
> and
>
>  Me!ProjectQNo = Me!Prefix_ProjectQNo & _
>        Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy" 


0
Ken
1/8/2008 3:13:45 AM
On 8 Jan, 03:13, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> For the first code block, you need a space in front of the trailing _
> character (the line continuation character); you don't have that in your
> code:
>
>    Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
>         "tbl_Projects", "Year_ProjectQNo=" & _
>         CLng(Format(Date),"yy"))), 0) + 1
>
> In the second code block, there is a ) missing from the end of the second
> line, and a ( missing after the Date:
>
> Me!ProjectQNo = Me!Prefix_ProjectQNo & _
>        Format(Me!Number_ProjectQNo,"0000") & Format(Date(),"yy")
>
> --
>
>         Ken Snell
> <MS ACCESS MVP>
>
> <dan.cawtho...@gmail.com> wrote in message
>
> news:4e73682d-d5bb-4548-9d1c-bc95a64d6e5f@i12g2000prf.googlegroups.com...
>
> > On 5 Jan, 17:31, "Ken Snell \(MVP\)"
> > <kthsneisll...@ncoomcastt.renaetl> wrote:
> >> Reply inline...
> >> --
>
> >>         Ken Snell
> >> <MS ACCESS MVP>
>
> >> <dan.cawtho...@gmail.com> wrote in message
>
> >>news:0dfb9b1a-f950-48e8-867b-c1a8d75d9be9@1g2000hsl.googlegroups.com...
>
> >> > Thank You Very Much Ken and John for the explanation its makes sense,
> >> > to me now,
>
> >> > and also Ken Thanks for the
>
> >> > Private Sub Form_Current()
> >> > If Me.NewRecord Then
> >> >    Me!ProjectQNo = "Q" & _
> >> >        Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
> >> >        "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0"))+1,
> >> > _
> >> >        "0000") & Format(Date, "yy")
> >> > End If
> >> > End Sub
>
> >> > It Seems to have corrected the problem, but as before all 2008
> >> > projects are now been automatically been put to the front of all the
> >> > 2007 Records.
> >> > which you would expect it to.
>
> >> > So the solution would be to split the field into 3 field [Qfield]
> >> > [NumberField] and [YearField]
>
> >> > Which i could easily have the 3 field on the New Project Wizard all
> >> > next to each other. The Qfield always be a Q and the Year will change
> >> > on the year.
>
> >> > So How Would I for the form to increment the Number only, and then
> >> > always put the year and Q in the other field.
>
> >> I provided sample code for this solution in my first reply to your post.
> >> Here is the code example again:
>
> >> Private Sub Form_Current()
> >> If Me.NewRecord Then
> >>     Me!Prefix_ProjectQNo = "Q"
> >>     Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
> >>         "tbl_Projects", "Year_ProjectQNo=" & _
> >>         CLng(Format(Date),"yy"))), 0) + 1
> >>     Me!Year_ProjectQNo = Year(Date)
> >>     Me!ProjectQNo = Me!Prefix_ProjectQNo & _
> >>         Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
> >> End If
> >> End Sub
>
> >> The above assumes that you use a Number data type (Field Size is Long
> >> Integer) for the 2-digit year value.
>
> > Assume I had to create following fields
>
> > [Prefix_ProjectQNo]
> > [Number_ProjectQNo]
> > [Year_ProjectQNo]
>
> > in the tblprojects
>
> > the problem is i getting complie errors in the sample code you gave me
>
> > following lines that are giving me compile errors are as
>
> >   Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo",_
> >        "tbl_Projects", "Year_ProjectQNo=" &_
> >        CLng(Format(Date),"yy"))), 0) + 1
>
> > and
>
> >  Me!ProjectQNo = Me!Prefix_ProjectQNo & _
> >        Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy"

Sorry to be a real pain,

Ive got the rid of the code block on the second part of the code,

But still cant get ride of the code block on the first part, I Noticed
you placed a space between the , and _ from this  Me!Number_ProjectQNo
= Nz(DMax("Number_ProjectQNo",_  to  Me!Number_ProjectQNo =
Nz(DMax("Number_ProjectQNo", _

but still does now work,

second question once ive sorted that issue, is im trying to sort my
records so all the 2008 projects appear at the end of the 2007 but
this not happening, Now that have a field called "Year_ProjectQNo" i
thought i could sort this field then sort by Number_ProjectQNo but i
think i missing something.

also to save my self a lot of hassel in changing the design on my
database ive decided to keep me field "projectQNo"

so once a Q has been generated in the field [Prefix_ProjectQNo] and
number in [Number_ProjectQNo] the the year in
[Year_ProjectQNo] I want it to generate the Q001008 in the field. How
is this done.

Regards

Dan.
0
dan
1/9/2008 10:56:33 AM
Post the full code in your procedure. Let's see what you have right now.

-- 

        Ken Snell
<MS ACCESS MVP>


<dan.cawthorne@gmail.com> wrote in message 
news:0f2f30b6-1514-4433-bf57-e6d386fa00a0@k39g2000hsf.googlegroups.com...
> On 8 Jan, 03:13, "Ken Snell \(MVP\)"
> <kthsneisll...@ncoomcastt.renaetl> wrote:
>> For the first code block, you need a space in front of the trailing _
>> character (the line continuation character); you don't have that in your
>> code:
>>
>>    Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
>>         "tbl_Projects", "Year_ProjectQNo=" & _
>>         CLng(Format(Date),"yy"))), 0) + 1
>>
>> In the second code block, there is a ) missing from the end of the second
>> line, and a ( missing after the Date:
>>
>> Me!ProjectQNo = Me!Prefix_ProjectQNo & _
>>        Format(Me!Number_ProjectQNo,"0000") & Format(Date(),"yy")
>>
>> --
>>
>>         Ken Snell
>> <MS ACCESS MVP>
>>
>> <dan.cawtho...@gmail.com> wrote in message
>>
>> news:4e73682d-d5bb-4548-9d1c-bc95a64d6e5f@i12g2000prf.googlegroups.com...
>>
>> > On 5 Jan, 17:31, "Ken Snell \(MVP\)"
>> > <kthsneisll...@ncoomcastt.renaetl> wrote:
>> >> Reply inline...
>> >> --
>>
>> >>         Ken Snell
>> >> <MS ACCESS MVP>
>>
>> >> <dan.cawtho...@gmail.com> wrote in message
>>
>> >>news:0dfb9b1a-f950-48e8-867b-c1a8d75d9be9@1g2000hsl.googlegroups.com...
>>
>> >> > Thank You Very Much Ken and John for the explanation its makes 
>> >> > sense,
>> >> > to me now,
>>
>> >> > and also Ken Thanks for the
>>
>> >> > Private Sub Form_Current()
>> >> > If Me.NewRecord Then
>> >> >    Me!ProjectQNo = "Q" & _
>> >> >        Format(CLng(Nz(DMax("Mid(ProjectQNo,2,4)", "tbl_Projects", _
>> >> >        "Right(ProjectQNo,2)='" & Format(Date, "yy") & "'"), "0"))+1,
>> >> > _
>> >> >        "0000") & Format(Date, "yy")
>> >> > End If
>> >> > End Sub
>>
>> >> > It Seems to have corrected the problem, but as before all 2008
>> >> > projects are now been automatically been put to the front of all the
>> >> > 2007 Records.
>> >> > which you would expect it to.
>>
>> >> > So the solution would be to split the field into 3 field [Qfield]
>> >> > [NumberField] and [YearField]
>>
>> >> > Which i could easily have the 3 field on the New Project Wizard all
>> >> > next to each other. The Qfield always be a Q and the Year will 
>> >> > change
>> >> > on the year.
>>
>> >> > So How Would I for the form to increment the Number only, and then
>> >> > always put the year and Q in the other field.
>>
>> >> I provided sample code for this solution in my first reply to your 
>> >> post.
>> >> Here is the code example again:
>>
>> >> Private Sub Form_Current()
>> >> If Me.NewRecord Then
>> >>     Me!Prefix_ProjectQNo = "Q"
>> >>     Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo", _
>> >>         "tbl_Projects", "Year_ProjectQNo=" & _
>> >>         CLng(Format(Date),"yy"))), 0) + 1
>> >>     Me!Year_ProjectQNo = Year(Date)
>> >>     Me!ProjectQNo = Me!Prefix_ProjectQNo & _
>> >>         Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy")
>> >> End If
>> >> End Sub
>>
>> >> The above assumes that you use a Number data type (Field Size is Long
>> >> Integer) for the 2-digit year value.
>>
>> > Assume I had to create following fields
>>
>> > [Prefix_ProjectQNo]
>> > [Number_ProjectQNo]
>> > [Year_ProjectQNo]
>>
>> > in the tblprojects
>>
>> > the problem is i getting complie errors in the sample code you gave me
>>
>> > following lines that are giving me compile errors are as
>>
>> >   Me!Number_ProjectQNo = Nz(DMax("Number_ProjectQNo",_
>> >        "tbl_Projects", "Year_ProjectQNo=" &_
>> >        CLng(Format(Date),"yy"))), 0) + 1
>>
>> > and
>>
>> >  Me!ProjectQNo = Me!Prefix_ProjectQNo & _
>> >        Format(Me!Number_ProjectQNo,"0000") & Format(Date),"yy"
>
> Sorry to be a real pain,
>
> Ive got the rid of the code block on the second part of the code,
>
> But still cant get ride of the code block on the first part, I Noticed
> you placed a space between the , and _ from this  Me!Number_ProjectQNo
> = Nz(DMax("Number_ProjectQNo",_  to  Me!Number_ProjectQNo =
> Nz(DMax("Number_ProjectQNo", _
>
> but still does now work,
>
> second question once ive sorted that issue, is im trying to sort my
> records so all the 2008 projects appear at the end of the 2007 but
> this not happening, Now that have a field called "Year_ProjectQNo" i
> thought i could sort this field then sort by Number_ProjectQNo but i
> think i missing something.
>
> also to save my self a lot of hassel in changing the design on my
> database ive decided to keep me field "projectQNo"
>
> so once a Q has been generated in the field [Prefix_ProjectQNo] and
> number in [Number_ProjectQNo] the the year in
> [Year_ProjectQNo] I want it to generate the Q001008 in the field. How
> is this done.
>
> Regards
>
> Dan. 


0
Ken
1/12/2008 10:54:03 PM
Reply:

Similar Artilces:

Why does copy and paste between eMails not work?
Copy no longer copies to the clipboard. Is there a setting I have deleted somewhere? Using Windows 7 and Office 2007 proffessional. Does it work if you restart outlook? Do you have any addins installed that could be erasing the clipboard? I've seen this happen with addins and contacts, but not email. to test, open the new message form before copying so you just need to copy and switch to the other message. (http://www.slipstick.com/Contacts/clipboard.htm) -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Cent...

Formula Help (to many expresions)
Could one of you give me a hand with this... I'm trying to put a formula in a spreadsheet that has too many expressions in it. I understand there is a limit to the number of equations that can be in a formula but there must be a way around the cap. Or maybe another way to write the formula? What I am trying to say in the formula is that if... If X is less than 09 then B1 = what's in cell C2 If X is less than 25 then B1 = what's in cell C3 If X is less than 51 then B1 = what's in cell C4 The expression I have written looks like this... =IF(X<10,"N/A",IF(X<...

VB code for Macro
I have set up a rule on my InBox to check for specific words and move emails to my Work folder. Now I review emials in my Work folder and drag and drop them into 1 of 4 folders based on a number 1-4. After clicking on the folder, I need to perform the following on each of the four folders: Click on first email in the folder Clt+A (to select all the emails in the folder) Ctl+C (to copy) Drag the selections to a folder name HH Click #_Button (customized button set to send an email) Ctl+v (to paste the contents in the body of the email) Click Send Steps without the comments: Click folde...

Outlook and Hotmail -- now works again! For some not all?
Received from MS: <<Recently, Hotmail� announced that ... Hotmail will no longer allow new e-mail accounts to be accessed via Microsoft� Office Outlook� and Outlook Express. [BUT....] <<We are pleased to inform you that because you are an existing and valued customer, at this time your current Hotmail and MSN account(s) are exempt from this restriction and you will be able to continue enjoying access to those accounts from Outlook or Outlook Express. However, any new Hotmail or MSN accounts you create will not be accessible via Outlook or Outlook Express.>> ....an "...

No email stationery when through code
Hi I am creating email via code from access using below; Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Set objOutlook = CreateObject("Outlook.Application") Set objOutlookMsg = objOutlook.CreateItem(olMailItem) objOutlookMsg.To = "recepient@mydomain.com" objOutlookMsg.Subject = "Email Subject" objOutlookMsg.Body = "Dear abc..." objOutlookMsg.Display The problem is that the default signature that is set in outlook doe snot come up in the email. How can I fix it? Thanks Regards Am Sun, 17 Sep 2006 21:11:45 +0100 schri...

Help with ShowFilter Macro
I'm trying to use this ShowFilter UDF written by Tom Ogilvy (see bottom of post). It says to use... =showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) ....in a cell to show the criteria for Column B. For one thing, I don't understand the cells B2 and B3 business. What is supposed to be in those cells? I would like this function to appear in the cell directly above or below the Autofilter menu cell. How do I change the function if the Autofilter criteria menu is in, cell A3, for example, and I want the criteria (this function) to appear just above, in cell A2? After trying all so...

How to view the code for excel built-in functions?
Is it possible? -For example the function PMT(). thanks. No, the code is compiled, so it would likely be less than useful anyway. About the best you can do is check out the equations used in Help (see "PV"). In article <OSU3OXOBGHA.1676@TK2MSFTNGP09.phx.gbl>, "serdar" <s@s.com> wrote: > Is it possible? -For example the function PMT(). > thanks. ...

mscvr71.dll help
How do I make my VS.net 2003 software not depend on msvcr71.dll? Thanks. Use static linking. I don't know where to set the option in VS7, but it used to be under Code Generation where you selected the desired C runtime library to use. In VS6 we had a choice between a dynamically-linked runtime and a statically-linked runtime. I've not had to make this choice under VS7 so I'm not sure where, in their overly-clever and completely gratuitous reimplementation of the user interface, this has been hidden. joe On Sat, 21 Feb 2004 22:24:56 GMT, anonymous@coolgroups.com wrote: &g...

Help with automating file name
I have the following code that exports the below query to excell. I would like the files name to include the month and date. How would I format this? DoCmd.OutputTo acOutputQuery, "qryShopOrderSqFtShippedSummaryExport",_ acFormatXLS, "W:\Cokato\Production\ProdRoomRpt.xls" -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 Hi, Matt. > I would > like the files name to include the month and date. Uh, . . . the date _always_ includes the month, unless you're r...

Please help with last formula for order form.
I am able to accomplish this with 1 column by the formulas below. Cell H160 is the subtotal: =IF(SUM(H72:H111)>0,SUM(H72:H111),"") Cell H166 the total: =IF(SUM(H160)>0,SUM((H160*H163)+H160),"") Cell H163 is for Tax. I am almost finished creating an order form. I would like to get the SUM of 3 different columns that are separated. I am not able auto fill strait down the column, because the information is separated in groups with titles, and the cells are not identically sized. I tried varations of this formula: =IF(SUM(H72:H111)+(116:131)+(135:154)>0,SUM ((H72:H...

Rule to forward mail to external address doesn't work
It might be an XCON issue but I didn't find the corresponding group so I post my questions here. I set up a rule that when an email arrives if my name is in the "to" or "cc" field then forward the email to another external SMTP address for example an hotmail address. however this rule never works through. It's a Exchange 2003 SP1 on Windows 2003 SP1 I have no problem to manually forward the email to that external SMTP address; If forward to an mailbox in our Exchange Org, it works; I turned on diagnostic for Transport, no error or warning logged; I tracked mes...

Need Hyperlink from Menu Page to work in email
I have a workbook with several tabs. First tab is a "menu" type page (summary of other pages). Currently I have hyperlinks to all the other pages from the menu page. What I want to be able to do is copy the menu page into an email and enable the users to click on the existing hyperlinks (whatever adjustments needed) to go directly to their page of the workbook. Using Excel 2007. Please advise how to do this and what changes may be need for both options to work. ...

SmartList Restrictions help
I built a SmartList that is based on the Year-to-Date Transaction Open file, and has the Account Master linked to it. I want to restrict it to accounts that begin with 36, 38, or 2504. I tried adding a restriction that says "Account Number:Account_Segment_Pool1 begins with 36 OR 38 OR 2504", but I got no results when I did it that way. I also set up 3 separate restrictions, but that didn't work either. Is this possible? I can't find much information about how to write restrictions in SL Builder. ...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

Works in 2003 but not 2007
When I reference the below function in 2003 I get function displayed... now in 2007 I get #NAME? Any idea what I need to change/set in 2007 so it will run this function? Function formulaText(x As Range) 'returns the text equivalent of formula in upper left cell in range x 'example: =formulatext(a1) returns the formula found in cell a1 of active worksheet 'example: =formulatext(a1:b5) returns the formula found in cell a1 of active worksheet formulaText = x.Cells(1, 1).Formula End Function Thanks, Mark Mark, It works fine for me. Are you stored in a stan...

help plz
my account has been inactive how to i reacctivate it? What account and what does this have to do with Outlook? "heather" <tracyliepke@yahoo.com> wrote in message news:066001c36c53$bb68d180$a501280a@phx.gbl... > my account has been inactive how to i reacctivate it? ...

Help with queries
Hi Guys, This is the first time after school that I am trying to use ms access at work and i need ur help in creating a query. Any help will be highly appreciated!! Here is what I need... I have relatively small ms acces database with about 1000 I have 3 colums date ipaddress sitename 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 34.3.3.3 A 12/09 33.4.4.4 B 12/09 33.4.4.55 C What i need is if an ipadress is recorded more t...

extraction code from celd
Hi :) I have a Excel problem :confused: MY QUESTION IS: if A5 = "JhoN FreD SmitH ChonG then A6 = "JNFDSHCG" I would like have got solution ;) please !!! thank you ver much nando4000@latinmail.co ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com You could create a User Defined Function: '=============================== Function GetCode(rng As Range) As String Dim i As Integer Dim str As String i = Len(rng.Value) For i = 1 To i str = Mid(rn...

Need help to choose loyalty program integrated with website
Hello. My name is Alex and I am working for franchise company using RMS system. We are looking for loyalty program integrated with web store. We have 12 franchisee stores using RMS and they are all conneted to our HQ system in main office. We want customers to earn point for each sales and redeem their points only at our website. (not on off-line store) Can anybody recommend best solution for our plan? Thank you. ...

Can't Make this Work
I create a new Datasheet form from tlPB and get the "FIRST SQL VIEW". I then select DirectoryID and from the Row Source, I get the "SECOND SQL VIEW". I then select DeptID and from the Row Source, I get the "THIRD SQL VIEW". DirectoryID and DeptID are both Combo Boxes. Problem I want only those DeptID's that are related to the DirectoryID that has been entered in the Datasheet. Can someone show me how this is done? -------------------------------------------------------- FIRST SQL VIEW SELECT tblPB.PBID, tblPB.DirectoryID, tblPB.DeptID FROM t...

Auto-Complete stopped?
Hi, I am using Outlook on Win 2k. I started the computer today and somehow Outlook stopped automatically completing the addresses when I type them in. I looked at the "Contacts" sections, and it only has a few outdated entries, are these the data Outlook uses when completing? Is there anything that I might have done that had deleted the entries? And is there anyway to retrieve it? Thanks. What version of Outlook are you using? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, ...

Request Responses Not Working Properly
I am running Outlook 2003 sp2. When I send a meeting request out to a large group of people I uncheck the option to request responses. For the most part it works except, I continually get one response back out of the several hundred that I send. The only difference that I can see between this user and all others is that he is responding back to the meeting request from his pda, which is connecting via owa. If I respond directly from owa, it works fine. I tested using another pda and experienced the same problem. Any ideas? ...

Help And Advive For A Novice
HELP AND ADVICE FOR A NOVICE This Is Not A Question About Using Excell But How To Download E -books I Have Purchased 2 E-books (a)f1 Excell And (b) Financial Statements. Encountered Difficulty In Downloading Because The Instructions Were Not Very Clear For Novice And Message Flagged Up "zip File Corrupted" I Have Utilised My Permitted Downloads. Therfore I Have Not In A Position To Use The E-books -- Joe Williams ------------------------------------------------------------------------ Joe Williams's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23...

Can I use oulook from home and have access at work?
When I set up my outlook I lose all my inbox messages online and would not be able to read my email from work. Is there a way to have my inbox available on outlook and remain online? Just to clarify, are you asking how to set up Outlook at home to receive email from work? If that's your question, you need to ask your IT Admin or Exchange Admin if you can use OWA and how that's done. That wouldn't have email coming into your Outlook account but it is a form of Outlook, Outlook Web Access. This may or may not be allowed. Your IT staff would of course have all kinds of securi...

formula help #42
What formula would I use to search down a column find a name and report the number in the next column, this would be multiple times, the numbers to be added together. The added number reported then to be multiplied by another number and then to be subtracted from another fixed number in a specific cell. Thanks in advance Jason You can sum the corresponding cells matched without having a dedicated column of numbers. =SUMIF(A1:A100,"Name",B1:B100) =(SUMIF(A1:A100,"Name",B1:B100)*AnotherNumber)-SpecificCell HTH, Paul -- "Boenerge" <Boenerge@discussions...