Access 2007 - Working With Dates & Calculations

[ladyck3]

I am creating a DB in Access 2007 but in 2003 format.
I have two date columns which are editable, but I have others that calculate off of these two fields.

I have this all working in Excel right now but want to put this into a DB.

The Excel calculation I am using first is to take the first editable date in Column K, wo show the date one week prior in Column J using: =DATE(YEAR(K2),MONTH(K2),DAY(K2)-7)

In Column L, I want to show the number of weeks to come or past from Column K using: =ROUNDUP((K2-TODAY())/7,0)

So the spreadsheet shows:

Col J Col K Col L
8/4/2009 8/11/2009 35

The second editable date is the same thing only instead of the Col J formula I need to make it -14 to show 2 weeks prior to the editable date, no sweat.

I can do this in Excel but don't know how to calculate this in Access or where to put the calculation. I create a Query but am not sure where the calucation goes.... does it go in the "Criteria" row in the query?

I'm a novice, I know enough to be kinda dangerous.

PLEASE HELP.

Laurie


LadyCK3
aka: Laurie

 

[SkipVought]

Hi Laurie,

Why would you generate ANY derived column in a table? That's what you do in a query of that data.


BTW, the column K date is just
[tt]
=K2-7
[/tt]
nothing else needed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Ok, Skip....you have to read what is in my head, don't you know that by now hehehehe

I am currently using Excel, those calculations are what I use in Excel.

In Access, I was pretty sure it had to be a query where the calculations had to be entered but where... so I use

=[field]-7

As the calculation you are stating but where does that formula get entered in the query screen? At the bottom, if I know just enough to be dangerous but what area in the "Criteria" line at the bottom where, for example the check box is to either show or hide a field?

See where I'm going with this... I know how to get my desired results in Excel but have no idea how to translate what I need to do in "Access" Calculation speak....

The x weeks from the date field labeled TA I have two editable fields "TA" and "SS" the master fields, as it were and the calculations are made from those changeable/editable dates.

I'm trying =[TA]-7 in the Criteria field but this is not working... running the query comes up with no records using this. SO I'm all eager to learn

Laurie


LadyCK3
aka: Laurie

 

[MajP]

If you try the help file and type in "calculated field" you will get a pretty good discussion on "creating calculated field in a query". Then type in "Expression Builder".

 

[ladyck3]

Well at least I see data, but I get a NAME? error

=Round(([TA]-[CurrentDate])/7))

TA is a date in the future, I want a field that will show number of weeks to get to that date....

For instance,the result should be 8/30/2009 should come up to like 35 weeks (sorry I don't have my spreadsheet open right now, but I think that is the data).

So how close am I? TA = Technical Approval Date

Thanks in advance...

Laurie


LadyCK3
aka: Laurie

 

[SkipVought]

[tt]
=Round(([TA]-[red]Date()[/red])/7))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Skip,

Thank you, this works in a 'form' if I entered it in the field.....

How do I make this owrk when I open a table (is it possible) because the fields in table view, are empty.... same with the query... if I enter a formula in the query, then click RUN, the query is totally empty, if I take the formula out, and run the query, the data is there... except for in the calculating fields....

Am I stuck to using these in the form only?

Thanks in advance....



LadyCK3
aka: Laurie

 

[PHV]

What are the SQL codes of the working and not working queries ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ladyck3]

I just wanted to let you know, that I finally found what I was looking for and if this will help another novice then I'm so happy to share.....

http://www.brainstorminc.com/cbt/microsoft/help.php?file=access8

This is a video that shows where to enter a calculation in a query (in Design view) and how to set it to Expression to get it to work... following this has resolved my issue.

SO SIMPLE, but I had no clue....

Thanks everyone for your feedback or suggestions and to PHV, I don't know SQL... I'm an Excel junkie.... and do most everything that I possibly can in Excel, when I need to use a DB I go to Access.... not sure I have the room to learn SQL... maybe after a format

Thanks again Skip 'n all

Laurie


LadyCK3
aka: Laurie