la_luna_llena: (make mistakes)
[personal profile] la_luna_llena

I am trying to calculate the difference between 2 dates in an MS Access Query, and then have that difference impact the addition of a value to a new column.

I have [PepsiDate] and [VisitDate] from a couple of
For example, the first date is [PepsiDate] and the second date is [VisitDate].  If [VisitDate] - [PepsiDate] is within 7 days, I want to update a new column [PepsiWithin7] to say 'Yes.'

Is there a way to create this populating? or for the new field to maybe just subtract dates the way I've described?

x-posted to [livejournal.com profile] ms_access 

Date: 2009-08-27 05:45 pm (UTC)
From: [identity profile] angels-ember.livejournal.com
I managed to get the date difference:

Go in your Design Query, add VisitDate & PepsiDate, in a new column right click in the field section and pick Zoom, then type Difference: [VisitDate]-[PepsiDate]

Theoretically you could then do the following, but I think that I'm setting something up wrong and Access maybe isn't recognizing [Difference] as a number, but here's what I did so far:

New field, zoom -
PepsiWithin7: IIf([Difference] >= "7","Yes","No")

GOT IT!!!

Date: 2009-08-27 06:21 pm (UTC)
From: [identity profile] angels-ember.livejournal.com
PepsiWithin7: IIf([Difference]>=7,"Yes","No")

The quotation marks around the number 7 were confusing it.

Re: GOT IT!!!

Date: 2009-08-27 06:45 pm (UTC)
From: [identity profile] la-luna-llena.livejournal.com
cool! thanks! you've just saved my afternoon, most likely.

Also, I didn't realize about Zoom and was poking around in those tiny boxes. this is much easier.

Re: GOT IT!!!

Date: 2009-08-27 07:19 pm (UTC)
From: [identity profile] angels-ember.livejournal.com
Zoom really helps. That's one of the things that I learned from the most awesome MS software instructor ever, at MATC.

I actually love solving problems, so this was a nice break for me from my more monotonous work.

Re: GOT IT!!!

Date: 2009-08-27 07:20 pm (UTC)
From: [identity profile] la-luna-llena.livejournal.com
You're cool.

New tag -- Database. Because reading RaceFail all day doesn't pay the bills.

Re: GOT IT!!!

Date: 2009-08-27 08:30 pm (UTC)
From: [identity profile] angels-ember.livejournal.com
Yeah, maybe I'm a pisspoor example of a human being because I'd rather learn something to enhance my career than study the ways that our society fails at being good...but I've realized that I don't care all that much.

I discovered that getting pissed off every day about something new was a waste of energy. And more often than not I was as annoyed with the GroupThink and assumptions drawn as I was with the Fail.

Re: GOT IT!!!

Date: 2009-08-27 10:38 pm (UTC)
From: [identity profile] la-luna-llena.livejournal.com
And more often than not I was as annoyed with the GroupThink and assumptions drawn as I was with the Fail.

Yes, you're only a good person if you're putting in as much energy as the MOST INVOLVED PERSON! Also, theoretical and sociological explanations are always superior to scienctific and biological ones. You, [livejournal.com profile] angels_ember, are a horrible horrible person.

Edited Date: 2009-08-27 10:38 pm (UTC)

Date: 2009-08-27 06:04 pm (UTC)
From: [identity profile] tahari.livejournal.com
Yes, with an IF THEN visual basic scripting. Which should be accessible from the menu. I don't know exactly how to do it but I've found usually someone has a solution posted somewhere that's google-able. Dates in MS applications are tricky because they are not stored as proper dates. I ran into something similar in Excel once, but I've forgotten about it by now.

Date: 2009-08-27 06:27 pm (UTC)
From: [identity profile] angels-ember.livejournal.com
IF THEN works great in Excel, but not so much in Access Queries. Access uses an IIF Expression rather than an IF Statement. Though admittedly I don't have much experience with VB scripting, so maybe I'm missing what you're trying to say. (Sadly, I'm actually excited about taking Access Advanced this semester.)

I use IF THEN regularly for my work in Excel to help the sales reps keep track of their customers' expiring contracts. Don't want to know how much my life would suck if I hadn't figured out how to make Excel do 90% of my work for me!

Date: 2009-08-27 06:46 pm (UTC)
From: [identity profile] la-luna-llena.livejournal.com
I love that I post a tech question and women respond.

Profile

la_luna_llena: (Default)
la_luna_llena

November 2009

S M T W T F S
1234567
891011121314
1516 1718192021
22232425262728
2930     

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Jul. 26th, 2017 08:46 pm
Powered by Dreamwidth Studios