Combining a Number and a String

[brad.goldberg]

2 Part question for you all, seems kinda tricky to me, but many of you
are access gods compared to myself.

Here's the deal... I have a label on my form called Run Number:

Next to it I have a small text box that displayed the current truncated
year "06". Immediately next to it, there is a second text box that
displayes the actual run number, for example "0004", this number is
effectively being stored as a string (I think, see code below) The run
number increments in each new record, the 06 will only update at the
begining of the new year. I originally made it this way because the
user only sees "060004", it makes no difference to them if its actually
two text boxes or not.

1 Part: problem is I now realize that I need to be able to go back and
find a particular run number, such as search for "060004". I need to
create a seperate field that adds those to fields together (well not
add but "join" them together).

The code for the year is a Default Value set in the table to:

=Right(Str(Year(Now())),2)

The code for the run number is a beforeupdate code in the form.. People
have questioned this but I like it because the user doesnt get a Run
Number until they complete the entire form, basically they need the
number before they go home so it forces them to fill out the record
right. The code is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then

Me!displayedRunNumber = Format(CLng(Nz(DMax("displayedRunNumber",
"fields"), "0")) + 1, "0000")

End If
End Sub

Part 2 Problem: I just realized... come 2007 the number 06 will update
to 07, but the run number will just keep incrementing. I need it to
reset down to 0001, so the new number will appear 070001. I realize
this may require some code revising.

I would be really greatful if someone could walk me through these
changes, much of the work seen from above was the result of much help
from many people in these forms. So if you have an idea on how to fix
these problems please be specific because I am very new to all this
access stuff, but I must admit its fun.

Thanks so much in advance

Brad G.

[Bob Quintal]

br***********@gmail.com wrote in
news:11*********************@75g2000cwc.googlegrou ps.com:

2 Part question for you all, seems kinda tricky to me, but
many of you are access gods compared to myself.

Here's the deal... I have a label on my form called Run
Number:

Next to it I have a small text box that displayed the current
truncated year "06". Immediately next to it, there is a second
text box that displayes the actual run number, for example
"0004", this number is effectively being stored as a string (I
think, see code below) The run number increments in each new
record, the 06 will only update at the begining of the new
year. I originally made it this way because the user only sees
"060004", it makes no difference to them if its actually two
text boxes or not.

1 Part: problem is I now realize that I need to be able to go
back and find a particular run number, such as search for
"060004". I need to create a seperate field that adds those to
fields together (well not add but "join" them together).

The code for the year is a Default Value set in the table to:

=Right(Str(Year(Now())),2)

The code for the run number is a beforeupdate code in the
form.. People have questioned this but I like it because the
user doesnt get a Run Number until they complete the entire
form, basically they need the number before they go home so it
forces them to fill out the record right. The code is as
follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then

Me!displayedRunNumber =
Format(CLng(Nz(DMax("displayedRunNumber",
"fields"), "0")) + 1, "0000")

End If
End Sub

Part 2 Problem: I just realized... come 2007 the number 06
will update to 07, but the run number will just keep
incrementing. I need it to reset down to 0001, so the new
number will appear 070001. I realize this may require some
code revising.

I would be really greatful if someone could walk me through
these changes, much of the work seen from above was the result
of much help from many people in these forms. So if you have
an idea on how to fix these problems please be specific
because I am very new to all this access stuff, but I must
admit its fun.

Thanks so much in advance

Brad G.

Part 1: to concatenate two text strings, just use the
concatenation operator &
text1 & text2 will give text1text2

For part 2, you put a test in the DMAX function so that it
returns the max for the given year.
Dmax("displayedRunNumber","table", "yearfield = '" & mid(cstr
(year(now())),3,2) & "'"

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com