How to split a date into Year, month and day?

[Neovecchi]

Hi there,

We are trying to split a date into a year (2004) that goes into a year field, a month in a month field and a day in a day field. This have to happen in a query, does anyone know how to do it?

Thanks a lot.

Greetings,

Neovecchi

 

[THWatson]

Neovecchi
Assuming that you have a field in your query that shows the full date (e.g. 1/1/2004). For purposes of this example, I will call the field DateShown

Put 3 other columns in your query, using these expressions.
Day([DateShown])
Month([DateShown])
Year([DateShown])

This will extract the DateShown into 3 separate fields.

If you want to show the Month as a name rather than a number (e.g. January rather than 1), right click in the Criteria row and in the Format block put mmmm.

Tom

 

[SteveR77]

Neovecchi,


You are looking for the DatePart function -

For the 4 digit year - DatePart("yyyy",Now())
For the month of the year - DatePart("m",Now())
For the day of the day - DatePart("yyyy",Now())


The Now function can be replaced with the name of your field in a query where the date is stored.


Hope this helps,

Steve

 

[SteveR77]

Opps! The day should have been

DatePart("d",Now())

 

[Neovecchi]

Hi there,

Thanks a lot. But I have still a question. Where do I have to put the lines? And is this sql or VBA?

Thanks again, I really appreciate it.

Greetings,

Neovecchi

 

[SteveR77]

Neovecchi,

The code that was provided would become 3 new fields in a query.

 

[Neovecchi]

Hi there,

First of all thanks for the perfect information.

I will now try to explain what we are doing because I think we need some more help from you. We are trying to implement a consumption registration using facility cards and a java controlling program that uses a database. In this database we need to check a certain period (ex. 01-2004) so that we can "write" the number of consumptions in the field "TotalConsumptions". Every month the consumptions are send to administration, they can now lower the check from our employee's salary. My question is with my questions before: Now that we have seperate the date, we would like the month and year field to be combined in a field named "Period"( 01-2004 ). This field will be checked by the java program and if it exists it needs to higher the number with for example 8(these are the numbers of consumptions of that day) and adds the new totalconsumptions in the TotalConsumptions. How do we have to do this? And do you have any ideas that may come to use further in our project?

Thanks in advance.

Thanks,

Neovecchi

Thanks Neovecchi

 

[dhookom]

You would create a calculated column in your query:
PeriodConsumptions: Format([DateField],"mm-yyyy&quot
Your query would group by this field (and possibly others) and sum the number of consumptions.

I'm not sure how/if this will work if you are calling it from Java.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.