Query

  • Thread starter Thread starter RED
  • Start date Start date
R

RED

I am trying to set up a query calculating total doors and
cumulative doors by month for my cabinetry database. My
problem is I'm not able to set a criteria to only include
those doors for this year. I have tried reading many of
the posts and they sound like greek. If someone
knows "ACCESS for dummys" and thinks they can put this in
remedial terms, I would love to hear it.
Also, I have a problem with inputing of dates in one
table. The input mask is "2"9/-9"KS"9999, i.e. 23-0KS7654.
(23 being the year 2003). I am able to use this for all
years but 2002 or 22. It will not recognize this year.
Any thoughts.
 
Assuming your dates are stored in the standard mm/dd/yyy format, try this in
your qeury.

1) create a new field Year : format([date],"yy")
This will show you the year of the field date. Change "date" to the name of
your field
2) in the criteria put format(now(),"yy")
This will give you the current year.

Now, your query will only show the data where the year of the field date
matches the current year. For the input mask problem, try addind ";0" to
the end of the mask.

Good luck.

Kelvin
 
I am trying to set up a query calculating total doors and
cumulative doors by month for my cabinetry database. My
problem is I'm not able to set a criteria to only include
those doors for this year. I have tried reading many of
the posts and they sound like greek.
Click to expand...

Here's a suggestion in English; apologies if it is oversimplified, but
I don't want to err on the side of complexity!

Open a Query in design view.
In a vacant Field cell type:

YearNum: Year([datefield])

where [datefield] is the name of the Date/Time field containing the
date. (See below).

On the Criteria line under this calculated field type

=Year(Date())

This will limit records to those generated during the current year
(which might be a problem if you're running the query in January).
If someone
knows "ACCESS for dummys" and thinks they can put this in
remedial terms, I would love to hear it.
Also, I have a problem with inputing of dates in one
table. The input mask is "2"9/-9"KS"9999, i.e. 23-0KS7654.
(23 being the year 2003). I am able to use this for all
years but 2002 or 22. It will not recognize this year.
Any thoughts.
Click to expand...

This must be a Text field, not a Date/Time field... right? What are
you actually typing into the field? What is this composite field being
used FOR? If it's the "date" field which you're trying to use in the
query, no wonder you're having trouble: there is no way that Access
can ascertain that 23 is 2003. (Question: are you building yourself a
decade Y2K bug? What's the value for 2000, and what will it be in
2010?)
 
Back
Top