Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculating dates

Discussion in 'Information Technology' started by Gina, Jul 28, 2006.

  1. Gina

    Gina
    Expand Collapse
    Guest

    I have a 'Start Date'. I am trying to automate this process. Below is a
    script that might help you to understand what I am trying to achieve. The
    'between' function is what I would like to have automated. The 'end user'
    would always know today's date, but I want the computer (script) to be able
    to calculate it.

    This works if we just use the Between without the Date() function. However,
    I would like it to be automated.

    I need a date range of the prior month and todays date be greater than the
    last day of the prior month.

    Between [Beginning of Prior Month] and [End of Prior Month] and Date()>[End
    of Prior Month]
    --
    Gina
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Calculating the Rarity of a Fingerprint Breaking News Dec 13, 2010
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    Pacific Oil slicks spotted in search for missing Malaysia Airlines plane (Live updates) Breaking News Mar 8, 2014
    India Candidates flock to Dera Sacha Sauda Breaking News Jan 17, 2012
    India Seven SAD Candidates of SGPC Win Unopposed Breaking News Aug 27, 2011

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Gina,

    Today's date will *always* be after the end of the prior month. Unless
    I am missing the meaning here.

    Can you give some examples to illustrate what you mean. And also say
    where/how you are using this expression - is it in the criteria of a query?

    --
    Steve Schapel, Microsoft Access MVP

    Gina wrote:
    > I have a 'Start Date'. I am trying to automate this process. Below is a
    > script that might help you to understand what I am trying to achieve. The
    > 'between' function is what I would like to have automated. The 'end user'
    > would always know today's date, but I want the computer (script) to be able
    > to calculate it.
    >
    > This works if we just use the Between without the Date() function. However,
    > I would like it to be automated.
    >
    > I need a date range of the prior month and todays date be greater than the
    > last day of the prior month.
    >
    > Between [Beginning of Prior Month] and [End of Prior Month] and Date()>[End
    > of Prior Month]
     
  4. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Beginning of prior month will be DateSerial(Year(Date), Month(Date) - 1, 1)
    End of prior month will be DateSerial(Year(Date), Month(Date), 0)

    The check Date()>[End of Prior Month] is unnecessary. Since the prior month
    is always earlier than today, that will always be true.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Gina" <Gina@discussions.microsoft.com> wrote in message
    news:13337B63-653A-4A9B-A92A-BB09B5FF8F24@microsoft.com...
    >I have a 'Start Date'. I am trying to automate this process. Below is a
    > script that might help you to understand what I am trying to achieve. The
    > 'between' function is what I would like to have automated. The 'end user'
    > would always know today's date, but I want the computer (script) to be
    > able
    > to calculate it.
    >
    > This works if we just use the Between without the Date() function.
    > However,
    > I would like it to be automated.
    >
    > I need a date range of the prior month and todays date be greater than the
    > last day of the prior month.
    >
    > Between [Beginning of Prior Month] and [End of Prior Month] and
    > Date()>[End
    > of Prior Month]
    > --
    > Gina
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Aaahhh!! *Now* I understand the question :).

    --
    Steve Schapel, Microsoft Access MVP

    Douglas J. Steele wrote:
    > Beginning of prior month will be DateSerial(Year(Date), Month(Date) - 1, 1)
    > End of prior month will be DateSerial(Year(Date), Month(Date), 0)
     
  6. Gina

    Gina
    Expand Collapse
    Guest

    Douglas, thank you for your quick response, it is much appreciated.

    I'm a little confused... is this how it should look? I tried to make it a
    'between statement'. I'm using it in a query in the date field. The program
    added the Quotes and then errors out stating 'data mismatch'. It's a select
    query and its pulling from only one table.

    SELECT [History Table].[Acct #], [stat SC 995].[SC to 995] AS [New Status],
    [History Table].[Reason Code #], [History Table].[Closure Type], [History
    Table].[Start Date]
    FROM [stat SC 995], [History Table]
    WHERE ((DateSerial(Year("Date"),Month("Date")-1,1) Between
    DateSerial(Year("Date"),Month("Date")-1,1) And
    DateSerial(Year("Date"),Month("Date"),0)))
    WITH OWNERACCESS OPTION;

    --
    Gina


    "Douglas J. Steele" wrote:

    > Beginning of prior month will be DateSerial(Year(Date), Month(Date) - 1, 1)
    > End of prior month will be DateSerial(Year(Date), Month(Date), 0)
    >
    > The check Date()>[End of Prior Month] is unnecessary. Since the prior month
    > is always earlier than today, that will always be true.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Gina" <Gina@discussions.microsoft.com> wrote in message
    > news:13337B63-653A-4A9B-A92A-BB09B5FF8F24@microsoft.com...
    > >I have a 'Start Date'. I am trying to automate this process. Below is a
    > > script that might help you to understand what I am trying to achieve. The
    > > 'between' function is what I would like to have automated. The 'end user'
    > > would always know today's date, but I want the computer (script) to be
    > > able
    > > to calculate it.
    > >
    > > This works if we just use the Between without the Date() function.
    > > However,
    > > I would like it to be automated.
    > >
    > > I need a date range of the prior month and todays date be greater than the
    > > last day of the prior month.
    > >
    > > Between [Beginning of Prior Month] and [End of Prior Month] and
    > > Date()>[End
    > > of Prior Month]
    > > --
    > > Gina

    >
    >
    >
     
  7. Gina

    Gina
    Expand Collapse
    Guest

    Douglas,

    Co-worker looked at it and was able to tell that open/close paraenthesis
    were needed. Thanks again for all you help. Couldn't have done without you.

    -- Between DateSerial(Year(Date()),Month(Date())-1,1) And
    DateSerial(Year(Date()),Month(Date()),0)

    Gina


    "Douglas J. Steele" wrote:

    > Beginning of prior month will be DateSerial(Year(Date), Month(Date) - 1, 1)
    > End of prior month will be DateSerial(Year(Date), Month(Date), 0)
    >
    > The check Date()>[End of Prior Month] is unnecessary. Since the prior month
    > is always earlier than today, that will always be true.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Gina" <Gina@discussions.microsoft.com> wrote in message
    > news:13337B63-653A-4A9B-A92A-BB09B5FF8F24@microsoft.com...
    > >I have a 'Start Date'. I am trying to automate this process. Below is a
    > > script that might help you to understand what I am trying to achieve. The
    > > 'between' function is what I would like to have automated. The 'end user'
    > > would always know today's date, but I want the computer (script) to be
    > > able
    > > to calculate it.
    > >
    > > This works if we just use the Between without the Date() function.
    > > However,
    > > I would like it to be automated.
    > >
    > > I need a date range of the prior month and todays date be greater than the
    > > last day of the prior month.
    > >
    > > Between [Beginning of Prior Month] and [End of Prior Month] and
    > > Date()>[End
    > > of Prior Month]
    > > --
    > > Gina

    >
    >
    >
     
  8. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Gina,

    This won't work. I think the field you are applying the criteria to was
    called Start Date, in which case the query's Where clause will be like
    this...
    WHERE [Start Date] Between DateSerial(Year(Date()),Month(Date())-1,1)
    And DateSerial(Year(Date()),Month(Date()),0)

    By the way, as an aside, it is not a good idea to use # as part of the
    name of a field.

    --
    Steve Schapel, Microsoft Access MVP

    Gina wrote:
    > Douglas, thank you for your quick response, it is much appreciated.
    >
    > I'm a little confused... is this how it should look? I tried to make it a
    > 'between statement'. I'm using it in a query in the date field. The program
    > added the Quotes and then errors out stating 'data mismatch'. It's a select
    > query and its pulling from only one table.
    >
    > SELECT [History Table].[Acct #], [stat SC 995].[SC to 995] AS [New Status],
    > [History Table].[Reason Code #], [History Table].[Closure Type], [History
    > Table].[Start Date]
    > FROM [stat SC 995], [History Table]
    > WHERE ((DateSerial(Year("Date"),Month("Date")-1,1) Between
    > DateSerial(Year("Date"),Month("Date")-1,1) And
    > DateSerial(Year("Date"),Month("Date"),0)))
    > WITH OWNERACCESS OPTION;
    >
     

Share This Page