How can I append a table and set a date field to a specific date

[JWCrosby]

Not sure I worded the subject clearly, but here's what I'd like to do: I
want to append a table with 12 new records. Each record has a date field and
the new date field would be the first of the month, with all 12 months
represented. Here's what the appended records would look like (the Link
Field column represents the link to another table (arbitrary number used
here), since this is a one-to-many setup. I've also left out all the other
fields in the records.):

LinkField DateField
298 1/1/2008
298 2/1/2008
298 3/1/2008
298 4/1/2008
....
298 12/1/2008

The year would always be the same year for all 12 new records.

Jerry

 

[Jeff Boyce]

I'm not sure I understand what having this data/table would allow you to do?

If you only need the first day of each month, you can generate/calculate
that 'on the fly' in a query, using the DateSerial() function.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could set up a Month Table that holds the month numbers 1-12, then
join your append query to this table to get the months. E.g.:

PARAMETERS this_year Integer;
SELECT S.LinkField, CDate(M.month_nbr & "/1/" & this_year) AS DateField,

... etc. ...

FROM SourceTable As S, MonthTable As M
WHERE ... criteria ...

The parameter, this_year, would be 2008, 2009, 2010, etc. and would be
added by the user when she ran the query.

The FROM clause shows a set up for a Cartesian product - all rows
(Records) from the MonthTable are matched to whatever rows from the
SourceTable that are indicated in the WHERE clause's criteria.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSUGwpYechKqOuFEgEQIOpQCcDzNzo8Sb0Ax3NnDop/iER8hygSAAn1pu
HHcyagG3yXd6EgrHtX5lpijX
=21HV
-----END PGP SIGNATURE-----