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?
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.
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.
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?
You would create a calculated column in your query:
PeriodConsumptions: Format([DateField],"mm-yyyy"
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.