Help on modifying dates

[Jody]

In date column, trying to take month of the cell and year of the cell
and add a "/1/" between it in Access. So date of 11/4/2005 would return
11/1/2005. I get an error code when I use this code:
Month([dbo_tblEmployees]![HireDate]) & "/1/' &
year([dbo_tblEmployees]![HireDate]).

Error message is: "expression has invalid string. String can be up to
2048 character long, including opening and closing quotation marks." Do
you have any suggestions? Thanks.

 

[Marshall Barton]

In date column, trying to take month of the cell and year of the cell
and add a "/1/" between it in Access. So date of 11/4/2005 would return
11/1/2005. I get an error code when I use this code:
Month([dbo_tblEmployees]![HireDate]) & "/1/' &
year([dbo_tblEmployees]![HireDate]).

Error message is: "expression has invalid string. String can be up to
2048 character long, including opening and closing quotation marks."

Your posted expression has a mix of " and '

Even if that's just a posting typo, the result will be a
string, not a date, which may also be a problem.

If you want a date value as the result, use the DateSerial
function:
DateSerial(Year([dbo_tblEmployees]![HireDate]),
Month([dbo_tblEmployees]![HireDate]).1)

 

[John Vinson]

In date column, trying to take month of the cell and year of the cell
and add a "/1/" between it in Access. So date of 11/4/2005 would return
11/1/2005. I get an error code when I use this code:
Month([dbo_tblEmployees]![HireDate]) & "/1/' &
year([dbo_tblEmployees]![HireDate]).

Error message is: "expression has invalid string. String can be up to
2048 character long, including opening and closing quotation marks." Do
you have any suggestions? Thanks.

You're assuming that Access stores dates as text strings. It doesn't.
A Date/Time value is actually stored as a number, a count of days and
fractions of a day since midnight, December 30, 1899.

To get the first of the month for a particular field value (please
note that Access does not use "cells"; Excel does but that's a
different program!) use

DateSerial(Year([HireDate]), Month([HireDate]), 1)


John W. Vinson[MVP]