Basic MS Access question

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

Comments

[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!!!

[angels-ember.livejournal.com]

PepsiWithin7: IIf([Difference]>=7,"Yes","No")

The quotation marks around the number 7 were confusing it.

Re: GOT IT!!!

[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!!!

[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!!!

[la-luna-llena.livejournal.com]

You're cool.

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

Re: GOT IT!!!

[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!!!

[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, angels_ember, are a horrible horrible person.

[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.

[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!

[la-luna-llena.livejournal.com]

I love that I post a tech question and women respond.