selecting data from date and viewing in a table

[michellerobinso]

in tblquery i have a field date.

it its formated to =Date()

i also have two more fields

1- month
2- year.

what i want is to extract the month and year from the date field.

but the thing is, i was it to be visiable when you are in the table "view".

but i dont know how to format this.

 

[neileg]

in tblquery i have a field date.

it its formated to =Date()

This doesn't makew sense. Do you mean the default value is Date()?

i also have two more fields

1- month
2- year.

what i want is to extract the month and year from the date field.

Why? You are simply storing duplicate data.

but the thing is, i was it to be visiable when you are in the table "view".

but i dont know how to format this.

You don't view data in table view. You should do this in a form or report where you have full control of the formats. What you want is easy, doing it at a table level is neither easy or wise.

 

[michellerobinso]

it has to be like this because the month and the year make up part of the ID.


if i format it in the form, if you run a query, the field is blank. so i dont understand

 

[neileg]

it has to be like this because the month and the year make up part of the ID.

You can use the Year() and Month() functions to extract these values from your date any time you need them. You don't need to store these.

if i format it in the form, if you run a query, the field is blank. so i dont understand

I'm sorry, I don't understand what you are saying. I don't think you are using the term 'format' in the way I would use it. Try explaining again.

 

[michellerobinso]

yes i think your right. i must not be doing it right.

could you please explain how to exctract the year and month using the () that you posted. when i have tried to do it the way i think it should be it wont have any of it!!.

in my form i have used =DatePart("yyyy",[DATE]) to display the date and the same for the month. but i have also created queries to run reports and a search facility like attached and this doesnt display the month and year.

 

[neileg]

Mmm...

You shouldn't use some words as object names because they have specific meaning in Access. These are called reserved words. So don't use Date or Name.

A query to extract the month and year would look like this:

SELECT Table1.Number, Table1.MyName, Table1.MyDate, Month([MyDate]) AS Mth, Year([MyDate]) AS Yr
FROM Table1;

In your Formqry you have used the same text box to capture criteria for three different datatypes, text, number and date. That's not really going to work and you end up with a datatype mismatch error.

 

[michellerobinso]

i knew something wasnt right and didnt know what to do.

i apriciate you help

 

[michellerobinso]

A query to extract the month and year would look like this:

SELECT Table1.Number, Table1.MyName, Table1.MyDate, Month([MyDate]) AS Mth, Year([MyDate]) AS Yr
FROM Table1;

.

ok, one last thing, where does this code go????

 

[neileg]

ok, one last thing, where does this code go????

This isn't code, it's SQL. If you open a new query and switch to SQL view you can paste this in.

 

[michellerobinso]

thank you for your help on this.

ive never used a query like this before so i apolpgise for my lack of knowlege.

do i link this query to the frmquery now where the data will be viewed?