Fill multiple date fields in the form with the first value

[Chieltje]

I have a table where I use 3 date fields in, all with different views.

Field 1: Date as dd/mm/yyyy
Field 2: Quarter: q (shows the same date, but then as quarter of the year.
Field 3: Year: YYYY

Now I have a form that fills all of my fields, but is there a simple way banghead to populate the other two fields Quarter and Year from the value I entered in Date?
So if I change Field "Date", Fields "Quarter" and "Year" updates as well.

Maybe I don't need to do it in the form, but in the table?
I'm not that good with VB- scripts, and I'm not sure If it is possible to auto populate the table field Quarter and Year directly in the table itself.

Tnx in advance.

 

[CJ_London]

in field 2 put this as your controlsource - change the name of field1 to the real name of field1 - I'm assuming Jan-Mar is 1, Apr-Jun is 2 etc

=((month([field1])-1)\3)+1

and for field3

=Year([Field1])

You do not want to be storing these values in your table.

Note that Date and Year are reserved words, do not use these as field names (although you can for a label caption)

 

[CJ_London]

have corrected the month calc in my original post

 

[Chieltje]

Ty so much! I tried, but its not working. I changed it in the table right away, with calculated fields.

So I made a field for the quarter =([Data]) and changed the view to 'q'
Same with Years (Renamed the column ) and then in 'YYYY'


Now that only masks the date, but I need it hard coded in the table, so when you want to make a report, you can use a selection query to select certain rows depending on the expressions you enter, wich will be the quarter and what year.

For example I want a report of the first quarter of 2015, I get a pop-up asking me to enter the quarter. Then the next pop-up shows, asking what year. If you only mask the date with 'q' and 'YYYY' you can't enter '1' and '2015'.

Field 1 named: Data Contains value: 19-9-2015 Mask: <empty>

Field 2 named: Kwart Contains value: 19-9-2015 Mask: q

Field 3 named: Jaar Contains value: 19-9-2015 Mask: YYYY

It needs to be in the table hard coded without a mask.

Now I got the same form fields, but I can't occupy the two other fields with the hard coded numbers 3 and 2015 and write them away in the table...

 

[CJ_London]

Personally I would just do this in the form and report, there is no benefit of having it in the table.

You may want to read more about them here - the bottom section

http://allenbrowne.com/casu-14.html

 

[Chieltje]

Why I need it hard-coded is because of the query I"m running. A total noob must click on a report and only has to awnser 2 questions from my query: what year, and what quarter they want the overview from. Now I don't know how to learn 12 people how to query and mess up or fix the database in a short time....:banghead:

 

[shoji]

Chieltje,

I think you are not explaining your question clearly.
But let me try to understand your question.
The user will open a query. Then, the query has 2 filters(year and quarter) as they are prompted. When the user enters these filters, the query shows the result based on the filters. Did I understand you correctly? Maybe not. But you can get some ideas from my answer.

On the query in design view, create a new field, say, "y", and type the following:

y: Year([Field 1])

and in the Criteria cell, enter:

[What Year?]

Now, create another field, "q" and enter in the Field cell:

a: DatePart("q", [Field 1])

And in the Criteria cell,

[Which Quarter?]

Now open this query and it will prompt you to enter the year and quarter. Upon getting the responses, it will show the result records.

Maybe you are doing differently, but you DO NOT NEED to create separate fields for Year and Quarter on the table.

 

[Chieltje]

Yep, that did the trick.
Only needed to change the , in a: DatePart("q", [Field 1]) to a ; like a: DatePart("q"; [Field 1]) but that is probably because I'm using Access 2013.

Ty for your help 2 thumbs up!