Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Queries and JET SQL FAQ

How To

Add custom fields using functions by randysmid
Posted: 6 Mar 03

Did you know that you can create new fields using regular functions in the Query object?   For instance, let's assume you have a field called Batch_date, and you wish to extract only the YYYYMM from it.  Here is what you would place on the field line:
batchYYYYMM: Year([Batch_date])& Month([Batch_date])

In my applications, I use a form to allow user selection of which month/year to select.  So, if the user wishes to select 200203, then I set the criteria line to match the field on the form.  Here is an example, presuming the field on the form is called txtYYYYMM:

With the year and month function, you may need to add the "format" function to get exactly what you are looking for.   Here is an example of how to get this to work:
BatchYYYYMM: Format([Batch_date],"yyyy") & Format([batch_date],"mm")

You will see that the desired format for the date is placed in quotes.  By the way, "mmm" will get the 3 letter abbreviation of the month in text (e.g., "Mar").  If you use "mmmm", you will get the full month name (e.g., "September").  Using "yy" for the year will get you the last two digits of the year (e.g., "03").

You can also use a similar technique to get the full name from separate fields for last and first names.  Here is a code example of how to create a new field formatted as last, comma, first name:
Full_name: [Last_name]+", "+[First_name]

Comments, questions?   rsmith@cta.org

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close