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 [ profile] ms_access 

Date: 2009-08-27 05:45 pm (UTC)
From: [identity profile]
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")


Date: 2009-08-27 06:21 pm (UTC)
From: [identity profile]
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]
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]
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]
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]
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]
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, [ 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]
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]
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]
I love that I post a tech question and women respond.


la_luna_llena: (Default)

November 2009

1516 1718192021

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Sep. 23rd, 2017 12:03 am
Powered by Dreamwidth Studios