How can I get access to put only the current year in a field?

  • Thread starter Thread starter Ed Warren
  • Start date Start date
E

Ed Warren

Store the field value as a date (much more useful than just a year).
Access the data through a query in Access e.g. qryYear:
FieldYear:Year([fielddate])
ASP will be happy to show the year.

Ed Warren
 
Hi all,

I'm having problems forcing acces to put only the current year in a field of
each record of my database. At the moment I solved this by changing the
properties of the field as follows: "Format: yyyy" and "Default value:
=Date()", and this works just fine, but when I want to use this field in an
ASP page access returns it as a whole date again instead of only the year.
Any solutions to this?

TIA

Willem
 
Willem said:
Hi all,

I'm having problems forcing acces to put only the current year in a
field of each record of my database. At the moment I solved this by
changing the properties of the field as follows: "Format: yyyy" and
"Default value: =Date()", and this works just fine, but when I want
to use this field in an ASP page access returns it as a whole date
again instead of only the year. Any solutions to this?

TIA

Willem
Click to expand...

If you want a year then use a number field. A Date field will always hold a
complete date (including time).
 
Hi Rick,

I thought of that too, but then I can't make access to automatically enter
the year based on the systemdate.

Thanks anyway,

Regards,

Willem
 
Hi Ed,

I think that's te best solution to my problem. I don't understand your
query-=instructions though.
In my case a simple query to show the number and the date fields of my
database would be:

SELECT [Dossiers].NUMMER, [Dossiers].REG_DAT FROM [Dossiers];



Dossiers is the table,

NUMMER is a numberfield autonumbered by access

REG_DAT is a datefield (as registration date) also autofilled by access



How do I go about to let this query show only the number and the year of the
field REG_DAT?



TIA



Willem




Ed Warren said:
Store the field value as a date (much more useful than just a year).
Access the data through a query in Access e.g. qryYear:
FieldYear:Year([fielddate])
ASP will be happy to show the year.

Ed Warren


Willem said:
Hi all,

I'm having problems forcing acces to put only the current year in a field
of each record of my database. At the moment I solved this by changing
the properties of the field as follows: "Format: yyyy" and "Default
value: =Date()", and this works just fine, but when I want to use this
field in an ASP page access returns it as a whole date again instead of
only the year. Any solutions to this?

TIA

Willem
Click to expand...
Click to expand...
 
Willem said:
Hi Ed,

I think that's te best solution to my problem. I don't understand your
query-=instructions though.
In my case a simple query to show the number and the date fields of my
database would be:

SELECT [Dossiers].NUMMER, [Dossiers].REG_DAT FROM [Dossiers];



Dossiers is the table,

NUMMER is a numberfield autonumbered by access

REG_DAT is a datefield (as registration date) also autofilled by access



How do I go about to let this query show only the number and the year of the
field REG_DAT?



TIA



Willem




Ed Warren said:
Store the field value as a date (much more useful than just a year).
Access the data through a query in Access e.g. qryYear:
FieldYear:Year([fielddate])
ASP will be happy to show the year.

Ed Warren
Click to expand...
Click to expand...

As Ed indicated in his post, you can use:
FieldYear:Year([fielddate])

So, your query would be:
SELECT [Dossiers].NUMMER, RegYear:Year(REG_DAT) FROM [Dossiers];

That way you will only get the year of the registration.
 
Hi Randy,

Sorry, but then Access comes with a syntax error in RegYear:Year(REG_DAT).

Regards,

Willem

Randy Harris said:
Willem said:
Hi Ed,

I think that's te best solution to my problem. I don't understand your
query-=instructions though.
In my case a simple query to show the number and the date fields of my
database would be:

SELECT [Dossiers].NUMMER, [Dossiers].REG_DAT FROM [Dossiers];



Dossiers is the table,

NUMMER is a numberfield autonumbered by access

REG_DAT is a datefield (as registration date) also autofilled by access



How do I go about to let this query show only the number and the year of the
field REG_DAT?



TIA



Willem




Ed Warren said:
Store the field value as a date (much more useful than just a year).
Access the data through a query in Access e.g. qryYear:
FieldYear:Year([fielddate])
ASP will be happy to show the year.

Ed Warren
Click to expand...
Click to expand...

As Ed indicated in his post, you can use:
FieldYear:Year([fielddate])

So, your query would be:
SELECT [Dossiers].NUMMER, RegYear:Year(REG_DAT) FROM [Dossiers];

That way you will only get the year of the registration.
Click to expand...
 
While you'd type RegYear: Year(REG_DAT) into the graphical query builder
grid, the SQL that's generated is actually:

SELECT [Dossiers].NUMMER, Year(REG_DAT) AS RegYear FROM [Dossiers]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Willem said:
Hi Randy,

Sorry, but then Access comes with a syntax error in RegYear:Year(REG_DAT).

Regards,

Willem

Randy Harris said:
Willem said:
Hi Ed,

I think that's te best solution to my problem. I don't understand your
query-=instructions though.
In my case a simple query to show the number and the date fields of my
database would be:

SELECT [Dossiers].NUMMER, [Dossiers].REG_DAT FROM [Dossiers];



Dossiers is the table,

NUMMER is a numberfield autonumbered by access

REG_DAT is a datefield (as registration date) also autofilled by access



How do I go about to let this query show only the number and the year of the
field REG_DAT?



TIA



Willem




"Ed Warren" <[email protected]> schreef in bericht
Store the field value as a date (much more useful than just a year).
Access the data through a query in Access e.g. qryYear:
FieldYear:Year([fielddate])
ASP will be happy to show the year.

Ed Warren
Click to expand...

As Ed indicated in his post, you can use:
FieldYear:Year([fielddate])

So, your query would be:
SELECT [Dossiers].NUMMER, RegYear:Year(REG_DAT) FROM [Dossiers];

That way you will only get the year of the registration.
Click to expand...
Click to expand...
 
Hi Doug,

I wasn't aware of that, now it works fine, thanks!

Regards,

Willem

Douglas J. Steele said:
While you'd type RegYear: Year(REG_DAT) into the graphical query builder
grid, the SQL that's generated is actually:

SELECT [Dossiers].NUMMER, Year(REG_DAT) AS RegYear FROM [Dossiers]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Willem said:
Hi Randy,

Sorry, but then Access comes with a syntax error in
RegYear:Year(REG_DAT).

Regards,

Willem

Randy Harris said:
Hi Ed,

I think that's te best solution to my problem. I don't understand your
query-=instructions though.
In my case a simple query to show the number and the date fields of my
database would be:

SELECT [Dossiers].NUMMER, [Dossiers].REG_DAT FROM [Dossiers];



Dossiers is the table,

NUMMER is a numberfield autonumbered by access

REG_DAT is a datefield (as registration date) also autofilled by access



How do I go about to let this query show only the number and the year
of
the
field REG_DAT?



TIA



Willem




"Ed Warren" <[email protected]> schreef in bericht
Store the field value as a date (much more useful than just a year).
Access the data through a query in Access e.g. qryYear:
FieldYear:Year([fielddate])
ASP will be happy to show the year.

Ed Warren

As Ed indicated in his post, you can use:
FieldYear:Year([fielddate])

So, your query would be:
SELECT [Dossiers].NUMMER, RegYear:Year(REG_DAT) FROM [Dossiers];

That way you will only get the year of the registration.
Click to expand...
Click to expand...
Click to expand...
 
Willem said:
Hi Rick,

I thought of that too, but then I can't make access to automatically
enter the year based on the systemdate.
Click to expand...

Sure you can. Default value of Year(Date()).
 
Back
Top