Add custom fields using functions

Hi,
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:
[Forms]![BatchReports]![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