new quick question

  • Thread starter Thread starter bomantas
  • Start date Start date
B

bomantas

Last time I got help from fofa it helped a lot. But due to my lack o
knowledge I need some quick help. I needed to sort by months and h
gave me this formula > =Month([Date]) to be in one of my text boxes this worked but I want it to be in th
table (it was working in a form). Do I use this same formula? Wher
in the table do I put the formula? I tried the validation rule bu
this did not work. Where can I put this so that it will work?
Thanks
-Mar
 
Marc,

You cannot use calculated fields in tables.
If you want to use this for filtering or reporting, make a select query
based on the table, include the calculated field there and use the query
rather than the table as your recordsource.
If, for some reason, you still want to keep the month in your table, then
what you do depends on how you enter new data: if manually, use a form with
a calculated text box on it; if you import data from an external source,
include the calculated filed in your append query.

HTH,
Nikos

bomantas said:
Last time I got help from fofa it helped a lot. But due to my lack of
knowledge I need some quick help. I needed to sort by months and he
gave me this formula > =Month([Date]) to be in one of my text boxes this
Click to expand...
worked but I want it to be in the
 
in message > > Last time I got help from fofa it
helped a lot. But due to my lack of > knowledge I need some quick help. I
needed to sort by months and he > gave me this formula > =Month([Date]) to
be in one of my text boxes this worked but I want it to be in the > table
(it was working in a form). Do I use this same formula? Where > in the table
do I put the formula? I tried the validation rule but > this did not work.
Where can I put this so that it will work? > Thanks > -Marc > >
------------------------------------------------ > ~~ Message posted from
Click to expand...
http://www.ExcelTip.com/ > ~~View and post usenet messages directly from
http://www.ExcelForum.com/ >

You could create a field in the table and bind your text box to it, but you
really shouldn't. If I'm reading your post correctly, [Date] refers to a
date field in the table, no? If so, you already have the data you need
stored, and storing a month 'number' seperately is bad practice: you don't
want to store calculated values in the database, especially if the values
used in the calculation are already present. If you store the month
'number' seperately, then you have to make sure that value is changes in the
event that [Date] changes. This gets messy. just store [Date], and any
time you need to sort on the month, just use the =Month([Date]) function to
extract the data you need.


HTH,

Chuck Cain

bomantas said:
Last time I got help from fofa it helped a lot. But due to my lack of
knowledge I need some quick help. I needed to sort by months and he
gave me this formula > =Month([Date]) to be in one of my text boxes this
Click to expand...
worked but I want it to be in the
 
Thanks to Nikos and Chuck for the help. Why I have month and dat
seperate is for the exact date and sort by months. Is there a way i
the query to leave just the date,(take out the months), and sort b
just the months in the date? So if the field reads 1/24/04 I can use
query to just have it sort by Jan? Thanks again Nikos and Chuck.
really do appreciate all of the help that you gave to me.
-Mar
 
Marc,

If the reason why you need the month is just to sort on in a query, then you
definitely don't need it in the table. In your query design use the first
available field in the grid and, where you would normally have a field name,
type in
Expr1: Month([Date])
and set the sorting to ascending or descending, whichever you need. You can
also set a criterion (e.g. 1, if you only want January, etc). You can even
hide the field (untick Show) if you don't need it in the query output. Also,
you can change Expr1 (access default name for calculated fields) to a
meaningful name, if you do want it so show.

HTH,
Nikos
 
Back
Top