String/Number Problems!

[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.

 

[schasteen]

Are you storing a date or the year? You will need to or else you will have
multiple 0001s in your table and not know which year they are from. With
this stored, you can make a calculated field in a query and then enter your
run number as the criteria and get the result. As far as when you get to
2007 and beyond, you will have to change the DMax to include a where
condition to look at the year of either a date or year you have stored:
Example:
DMax("displayedRunNumber","fields","Year([DateField] = " & Year(Date))

 

[Ken Sheridan]

Brad:

You don't say what the name of the field is which stores the year (I hope
its not called Year!) so I'll call it RunYear for this example.

Finding a run number requires you to concatenate the values of the two
fields. You don't need to create a field in the table for this, and should
not do so as that would be redundancy (a bad thing). You can do it on the
fly at any time in a query, or in the criterion for a DLookup function etc.
As an example a query which prompts you for the number would go like this:

SELECT *
FROM Fields
WHERE RunYear & DisplayedRunNumber = [Enter run number:];

To compute the DisplayedRunNumber value for each year you need to add a
criterion to the DMax function call so it only looks at numbers for the
current year:

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

Note the alternative way of returning the year for the current date using
the Format function. Also note that as RunYear is of text data type the
return value of the Format function must be wrapped in quotes, which are
inserted into the string expression by using pairs of quotes within the
quotes which delimit the literal strings, so the expression:

"RunYear = """ & Format(Date,"yy") & """"

will evaluate today as::

RunYear = "06"

Ken Sheridan
Stafford, England