Custom ID

[RTimberlake]

Is there a way to create a custom ID that will pull as "mmyy" from Date field?

 

[Sprinks]

If you don't care about displaying a leading zero for the first 9 months, set
a textbox' ControlSource to:

= datepart("m",[YourDateField])& Mid(datepart("yyyy",[YourDateField]),3,2)

If you want a consistent four-character string, it's probably most easily
done with a custom function in the form's Code Module:

Private Function MyDate(dteD As Date) As String
MyDate = Trim(DatePart("m", [dteD]) & Mid(DatePart("yyyy", [dteD]), 3, 2))
If Len(MyDate) = 3 Then
MyDate = "0" & MyDate
End If
End Function

Then set the textbox' ControlSource to:

=MyDate([YourDateField])

Hope that helps.

Sprinks

 

['69 Camaro]

Try:

Me!CustomID.Value = Format(Me!txtSomeDate.Value, "mmyy") &
Me!txtCustomize.Value

.. . . where CustomID is the text box bound to the CustomID field,
txtSomeDate is the text box bound to the SomeDate field, and txtCustomize is
the text box displaying whatever numbering/lettering scheme you are using for
customizing your system. The Me!txtCustomize.Value in the syntax above could
also be replaced by some variable that calculates a value, or by a DLookup( )
or DMax( ) function that looks up a value in a table to use for CustomID.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Sprinks]

For some reason, I keep forgetting how flexible the Format() function is.
Thanks for reminding me.

Sprinks

 

[John Vinson]

Is there a way to create a custom ID that will pull as "mmyy" from Date field?

Yes; use the Form's BeforeInsert event (or whatever other event would
be appropriate) and set the ID using an expression like

Me!txtID = Format(Date("mmyy"))

Note that this assumes a Text type ID so you'll get 0306 rather than
306; it also assumes that this is in fact a good choice of ID, a
matter on which I have my doubts (you won't EVER have two records in
the same month?)

John W. Vinson[MVP]

 

['69 Camaro]

I came from the Unix world, where C is king. In C, everything has to be
formatted for display, so the Format( ) function is usually one of the first
things I think of when manipulating strings in VBA.

Gunny

 

[RTimberlake]

I will have multiple records in the same month. I am using this in a database
that will be used for evaluations. The evaluations are done each month by up
to 12 people per evaluee. I need to be able to get the average scores for the
month. I tried to set up a query that would pull from a normal date feild but
that is proving ot be problamatic.

Here is another question. When I pull my averages I then need that value to
be divided by 10. Where would I put a formula that would accomplish this?

Thank you for your help. I have learned more from these discussion groups
than any book on Access. Thank you for your time and expertise.

RT

 

[Sprinks]

I totally missed (but John was onto) that you wanted to USE this value,
rather than merely display it on the screen.

Simply create a query with 2 calculated fields using the DatePart()
function, and the field or fields you wish to average. Switch to Totals view
(View, Totals), Grouping By the month and year, and setting the other(s) to
Avg.

Calculated Field Expressions:

Month: DatePart("m",[YourDateField])
Year: DatePart("yyyy",[YourDateField])

Hope that helps.

Sprinks

 

[John Vinson]

I need to be able to get the average scores for the
month. I tried to set up a query that would pull from a normal date feild but
that is proving ot be problamatic.

Well, let's solve the problem rather than creating a redundant,
inaccurate, hard to work with field!

If you want to select records for a given month, based on a date/time
field in the table, you can use a criterion

= DateSerial([Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:] + 1, 1)

and make it a totals query.

John W. Vinson[MVP]

 

[RTimberlake]

John--Thank you for your help. It works beautifully.
Rebecca

I need to be able to get the average scores for the
month. I tried to set up a query that would pull from a normal date feild but
that is proving ot be problamatic.

Well, let's solve the problem rather than creating a redundant,
inaccurate, hard to work with field!

If you want to select records for a given month, based on a date/time
field in the table, you can use a criterion

= DateSerial([Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:] + 1, 1)

and make it a totals query.

John W. Vinson[MVP]