Re: [MS_AccessPros] How do I make the query I need?

 

Excelent John, it worked great.

Now, I'm gonna set the defautl value of the Interval field to 12, but I want to set the 6 month option to change when I double click the 12 in the field instead of typing the number 6. What should be the event procedure for it to work this way?

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Edgar-
>
> If the user is entering the interval, 183 isn't intuitive. If the interval is
> always a number of months, have them enter months instead (in this case, 6
> instead of 183), then use:
>
> DateAdd("m", [Interval], [ServDate])
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> xtintores@...
> Sent: Sunday, June 26, 2011 5:14 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] How do I make the query I need?
>
> Hey Walter, I got an idea out of your sugestion... You see, a year actually has
> 365.25 days, that's why every 4 years feb has an extra day, so 365.25 divided by
> 2 is 182.625, meaning I can round it of to 183 and it should work as you said.
>
>
>
> Thanks for the light on the matter
>
> Edgar
>
> Sent from my BlackBerryR smartphone
>
>
>
> -----Original Message-----
>
> From: "nkpberk" <wgriffin48@...>
>
> Sender: MS_Access_Professionals@yahoogroups.com
>
> Date: Sat, 25 Jun 2011 23:04:58
>
> To: <MS_Access_Professionals@yahoogroups.com>
>
> Reply-To: MS_Access_Professionals@yahoogroups.com
>
> Subject: Re: [MS_AccessPros] How do I make the query I need?
>
>
>
> Edgar;
>
> 6 months is (on a non leap year) is 182.5 days based on the average month being
> 30.466666 days long. Use 182 or 183 days for your interval,run some tests to see
> which is most accurate and test a few leap year dates as well, This should be a
> bunch more accurate.
>
> Walter Griffin
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "rivera_vazquez_edgar"
> <xtintores@> wrote:
>
> >
>
> > John,
>
> > I tried it and here is what i get...
>
> > I put the service date as febuary 1st, and the nest service date should be six
> months latter (august), I used the 180 as interval but it tells me that the next
> service date is in july (31st)to be excact. I figured that its adding 180 days
> to febuary first, but the thing is that since febuary has 28 days, it takes me
> to the end of july and not august, wich is realy the date needed (six months
> latter).
>
> >
>
> > Any advice?
>
> >
>
> > Edgar
>
> >
>
> >
>
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
>
> > >
>
> > > Edgar-
>
> > >
>
> > > If the underlying field is Date/Time, adding a number of days *will* give
> you
>
> > > the correct month in the future. The month display using Format has no
> bearing
>
> > > on what's actually stored.
>
> > >
>
> > > John Viescas, author
>
> > > Microsoft Office Access 2010 Inside Out
>
> > > Microsoft Office Access 2007 Inside Out
>
> > > Building Microsoft Access Applications
>
> > > Microsoft Office Access 2003 Inside Out
>
> > > SQL Queries for Mere Mortals
>
> > > http://www.viescas.com/
>
> > > (Paris, France)
>
> > >
>
> > >
>
> > >
>
> > > -----Original Message-----
>
> > > From: MS_Access_Professionals@yahoogroups.com
>
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
>
> > > xtintores@
>
> > > Sent: Saturday, June 25, 2011 1:53 PM
>
> > > To: MS_Access_Professionals@yahoogroups.com
>
> > > Subject: Re: [MS_AccessPros] How do I make the query I need?
>
> > >
>
> > > Ok John, but the problem I see doing it that way is for example, if the
> service
>
> > > was done in febuary 1st, and the next service is 6 months latter, and the
>
> > > interval says 180 days, than it would realy show up to be july for the next
>
> > > service date. Keep in mind that both the service date and the next service
> date
>
> > > are both formated to "mmm" to show only the month of service scince the
> service
>
> > > can be done at any time during that month. This is because the NFPA code
> says
>
> > > service should be done every so many months, sometimes monthly. The thing is
>
> > > adding days to a date won't allways give me the right month for text
> service.
>
> > >
>
> > >
>
> > >
>
> > > Any sugestions?
>
> > >
>
> > > Sent from my BlackBerryR smartphone
>
> > >
>
> > >
>
> > >
>
> > > -----Original Message-----
>
> > >
>
> > > From: "John Viescas" <john@>
>
> > >
>
> > > Sender: MS_Access_Professionals@yahoogroups.com
>
> > >
>
> > > Date: Sat, 25 Jun 2011 08:11:02
>
> > >
>
> > > To: <MS_Access_Professionals@yahoogroups.com>
>
> > >
>
> > > Reply-To: MS_Access_Professionals@yahoogroups.com
>
> > >
>
> > > Subject: RE: [MS_AccessPros] How do I make the query I need?
>
> > >
>
> > >
>
> > >
>
> > > Edgar-
>
> > >
>
> > >
>
> > >
>
> > > That is correct as long as Interval contains a number of days. You can
> always
>
> > >
>
> > > add an integer value to a date/time to get another date/time value - a date
>
> > >
>
> > > either in the future (add a positive interval) or a date in the past (a
> negative
>
> > >
>
> > > interval).
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > > John Viescas, author
>
> > >
>
> > > Microsoft Office Access 2010 Inside Out
>
> > >
>
> > > Microsoft Office Access 2007 Inside Out
>
> > >
>
> > > Building Microsoft Access Applications
>
> > >
>
> > > Microsoft Office Access 2003 Inside Out
>
> > >
>
> > > SQL Queries for Mere Mortals
>
> > >
>
> > > http://www.viescas.com/
>
> > >
>
> > > (Paris, France)
>
> > >
>
> > >
>
> > >
>
> > > -----Original Message-----
>
> > >
>
> > > From: MS_Access_Professionals@yahoogroups.com
>
> > >
>
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
>
> > >
>
> > > rivera_vazquez_edgar
>
> > >
>
> > > Sent: Saturday, June 25, 2011 12:33 AM
>
> > >
>
> > > To: MS_Access_Professionals@yahoogroups.com
>
> > >
>
> > > Subject: [MS_AccessPros] How do I make the query I need?
>
> > >
>
> > >
>
> > >
>
> > > Hello to all.
>
> > >
>
> > >
>
> > >
>
> > > I'm working with my main form but need a little help. The form is called
>
> > >
>
> > > frmCustomerDetail. In this form I have a few tabs: General Info, Contacts,
> and
>
> > >
>
> > > Addresses. In the Address tab I have a sub-form using my 'tblInventory',
> wich
>
> > >
>
> > > has the inventory found in that specific address, so far so good.
>
> > >
>
> > >
>
> > >
>
> > > Now I need a sub-form within the 'frmInventory subform' using the
> 'tblServices'.
>
> > >
>
> > > The catch is that the 'tblServices' has the following fields: IdService(PK),
>
> > >
>
> > > IdInventory(FK), ServDate (formated as NOW() ), Service (to identify the
> service
>
> > >
>
> > > done to that specific item), Interval (to state how often that item should
>
> > >
>
> > > recieve service, expressed in days), and now I need to make another field
> wich
>
> > >
>
> > > takes the ServDate value and adds the Interval value to get the NxtServDate
>
> > >
>
> > > (next service date for that item).
>
> > >
>
> > >
>
> > >
>
> > > Here is what I think I should do:
>
> > >
>
> > >
>
> > >
>
> > > Make a query with all the fields in tblService and add a field in that query
> for
>
> > >
>
> > > the next service date. For example: NxtServDate: [ServDate] + [Interval]
>
> > >
>
> > >
>
> > >
>
> > > Is this correct, or is there a better way to do this?
>
> > >
>
> > >
>
> > >
>
> > > As always, Edgar
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > > ------------------------------------
>
> > >
>
> > >
>
> > >
>
> > > Yahoo! Groups Links
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > > [Non-text portions of this message have been removed]
>
> > >
>
> > >
>
> > >
>
> > > ------------------------------------
>
> > >
>
> > > Yahoo! Groups Links
>
> > >
>
> >
>
>
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (9)

Recent Activity:

• New Members 9
• New Files 4

Visit Your Group

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___