Welcome to SPN

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

Sign Up Now!

Select Value as DateTime in Query

Discussion in 'Information Technology' started by jwgoerlich@gmail.com, Nov 9, 2005.

  1. jwgoerlich@gmail.com

    jwgoerlich@gmail.com
    Expand Collapse
    Guest

    Hello,

    I am dynamically building a DateTime value in a query, like so:

    CDate(Month([DateAlert]) & "-" & Day([DateAlert]) & "-" & Year(Now()))
    AS DateSerial

    My problem is that the resulting field is a string. Is there a way to
    force a field to be a DateTime type?

    Thanks in advance,

    J Wolfgang Goerlich
     
  2. Loading...


  3. Ken Snell [MVP]

    Ken Snell [MVP]
    Expand Collapse
    Guest

    First, the bad news. Do not use DateSerial as the alias name. DateSerial is
    a reserved word in ACCESS because it's the name of a built-in VBA function.
    See these articles for more information:

    List of reserved words in Access 2002 and Access 2003
    http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

    List of Microsoft Jet 4.0 reserved words
    http://support.microsoft.com/?id=321266

    Special characters that you must avoid when you work with Access databases
    http://support.microsoft.com/?id=826763


    Now the good news. DateSerial just happens to do exactly what you want! Try
    this:

    DateSerial(Year(Now()), Month([DateAlert]), Day([DateAlert])) AS TheDate

    This will be a date/time data type and you can then format it however you
    wish for display.

    --

    Ken Snell
    <MS ACCESS MVP>



    <jwgoerlich@gmail.com> wrote in message
    news:1131498781.907910.109460@g43g2000cwa.googlegroups.com...
    > Hello,
    >
    > I am dynamically building a DateTime value in a query, like so:
    >
    > CDate(Month([DateAlert]) & "-" & Day([DateAlert]) & "-" & Year(Now()))
    > AS DateSerial
    >
    > My problem is that the resulting field is a string. Is there a way to
    > force a field to be a DateTime type?
    >
    > Thanks in advance,
    >
    > J Wolfgang Goerlich
    >
     
  4. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    DateSerial is a function name, so try using that function and renaming your
    alias:
    DateSerial(Year(Date()), Month([DateAlert]), Day([DateAlert])) AS MyDate

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <jwgoerlich@gmail.com> wrote in message
    news:1131498781.907910.109460@g43g2000cwa.googlegroups.com...
    > Hello,
    >
    > I am dynamically building a DateTime value in a query, like so:
    >
    > CDate(Month([DateAlert]) & "-" & Day([DateAlert]) & "-" & Year(Now()))
    > AS DateSerial
    >
    > My problem is that the resulting field is a string. Is there a way to
    > force a field to be a DateTime type?
    >
    > Thanks in advance,
    >
    > J Wolfgang Goerlich
     
  5. jwgoerlich@gmail.com

    jwgoerlich@gmail.com
    Expand Collapse
    Guest

    > Do not use DateSerial as the alias name. DateSerial is a reserved
    > word in ACCESS because it's the name of a built-in VBA function.


    Ah ha! That explains it. Makes me wonder why I picked that as name,
    maybe some fragment of memory. Much obliged for the links.

    I still have a problem using:
    DateSerial(Year(Now()), Month([DateAlert]), Day([DateAlert])) AS
    DateReport

    The problem is a data type mismatch when I attempt to filter the
    results. This, for example, works:
    (DateAlert.DateAlert)=#1/12/2009#

    This fails with "Data type mismatch in criteria expression":
    DateSerial(Year(Now()),Month([DateAlert]),Day([DateAlert])))=#1/12/2005#

    Any ideas?

    J Wolfgang Goerlich
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Typecast it:
    CVDate(DateSerial(Year(Date()), Month([DateAlert]), Day([DateAlert])))
    AS MyDate

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <jwgoerlich@gmail.com> wrote in message
    news:1131536751.348883.120130@g14g2000cwa.googlegroups.com...
    >> Do not use DateSerial as the alias name. DateSerial is a reserved
    >> word in ACCESS because it's the name of a built-in VBA function.

    >
    > Ah ha! That explains it. Makes me wonder why I picked that as name,
    > maybe some fragment of memory. Much obliged for the links.
    >
    > I still have a problem using:
    > DateSerial(Year(Now()), Month([DateAlert]), Day([DateAlert])) AS
    > DateReport
    >
    > The problem is a data type mismatch when I attempt to filter the
    > results. This, for example, works:
    > (DateAlert.DateAlert)=#1/12/2009#
    >
    > This fails with "Data type mismatch in criteria expression":
    > DateSerial(Year(Now()),Month([DateAlert]),Day([DateAlert])))=#1/12/2005#
    >
    > Any ideas?
    >
    > J Wolfgang Goerlich
     
  7. jwgoerlich@gmail.com

    jwgoerlich@gmail.com
    Expand Collapse
    Guest

    Thank you. Yes, the value needs to be typecast. Both CDate and CVDate
    do not work, however.

    J Wolfgang Goerlich

    Allen Browne wrote:
    > Typecast it:
    > CVDate(DateSerial(Year(Date()), Month([DateAlert]), Day([DateAlert])))
    > AS MyDate
     
  8. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Wolfgang, I have never seen an Access query into an Access (JET) table where
    a calculation typecast by CVDate() was not recognised as a date.

    When you view the output of this query as a datasheet, does the field left
    align (like text), or right-align (like a number or date)?

    Do you want to post the entire SQL statement?

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <jwgoerlich@gmail.com> wrote in message
    news:1131540660.063518.237700@o13g2000cwo.googlegroups.com...
    > Thank you. Yes, the value needs to be typecast. Both CDate and CVDate
    > do not work, however.
    >
    > J Wolfgang Goerlich
    >
    > Allen Browne wrote:
    >> Typecast it:
    >> CVDate(DateSerial(Year(Date()), Month([DateAlert]),
    >> Day([DateAlert])))
    >> AS MyDate
     
  9. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Allen Browne wrote:
    > Typecast it:
    > CVDate(DateSerial(Year(Date()), Month([DateAlert]), Day([DateAlert])))
    > AS MyDate


    Why cast? I thought DATESERIAL returns a DATETIME anyhow e.g.

    SELECT TYPENAME(DATESERIAL(2009, 1, 12)),
    DATESERIAL(2009, 1, 12) = #1/12/2009#
     
  10. jwgoerlich@gmail.com

    jwgoerlich@gmail.com
    Expand Collapse
    Guest

    > When you view the output of this query as a datasheet, does the field left
    > align (like text), or right-align (like a number or date)?


    The field is right-aligned and for all practical purposes appears to be
    a date. The problem only occurrs when I filter the results.

    > Do you want to post the entire SQL statement?


    Sure. Here it is:

    SELECT
    DateAlert.DateAlert,
    [Person].[FirstName] & IIf([Person].[MiddleName] Is Not Null," " &
    [Person].[MiddleName] & " "," ") & [Person].[LastName] AS Person,
    DateAlert.Comment,
    CVDate(DateSerial(Year(Date()),Month([DateAlert]),Day([DateAlert])))
    AS DateReport
    FROM
    (DateAlert INNER JOIN Contact ON DateAlert.KeyContact =
    Contact.KeyContact)
    INNER JOIN
    Person ON Contact.KeyContact = Person.KeyContact
    WHERE
    (((DateAlert.DateAlert) Is Not Null) AND
    ((DateAlert.Alert)=1) AND
    ((Contact.Enabled)=1) AND
    ((Person.KeyPersonType)=1));

    The purpose for this is to get a list of anniversaries, birthdays, and
    other date alerts from a given time period (e.g. today, this week, this
    month). I have a report that compares DateReport to a given date range
    and lists the results.

    J Wolfgang Goerlich
     
  11. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    The query looks fine to me, and it is presenting as if JET understands the
    type correctly. The only slightly ususual feature is a table and a field
    that have the same name. You might need to be sure that Name AutoCorrect is
    turned off, so Access doesn't get confused about that.

    You say if fails when you filter it: How are you doing that? Are you
    entering a literal value in the Criteria row in place of:
    Is Not Null
    or are you entering something like:
    [Forms].[Form1].[Text0]



    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    <jwgoerlich@gmail.com> wrote in message
    news:1131543412.096809.81330@o13g2000cwo.googlegroups.com...
    >> When you view the output of this query as a datasheet, does the field
    >> left
    >> align (like text), or right-align (like a number or date)?

    >
    > The field is right-aligned and for all practical purposes appears to be
    > a date. The problem only occurrs when I filter the results.
    >
    >> Do you want to post the entire SQL statement?

    >
    > Sure. Here it is:
    >
    > SELECT
    > DateAlert.DateAlert,
    > [Person].[FirstName] & IIf([Person].[MiddleName] Is Not Null," " &
    > [Person].[MiddleName] & " "," ") & [Person].[LastName] AS Person,
    > DateAlert.Comment,
    > CVDate(DateSerial(Year(Date()),Month([DateAlert]),Day([DateAlert])))
    > AS DateReport
    > FROM
    > (DateAlert INNER JOIN Contact ON DateAlert.KeyContact =
    > Contact.KeyContact)
    > INNER JOIN
    > Person ON Contact.KeyContact = Person.KeyContact
    > WHERE
    > (((DateAlert.DateAlert) Is Not Null) AND
    > ((DateAlert.Alert)=1) AND
    > ((Contact.Enabled)=1) AND
    > ((Person.KeyPersonType)=1));
    >
    > The purpose for this is to get a list of anniversaries, birthdays, and
    > other date alerts from a given time period (e.g. today, this week, this
    > month). I have a report that compares DateReport to a given date range
    > and lists the results.
    >
    > J Wolfgang Goerlich
     
  12. jwgoerlich@gmail.com

    jwgoerlich@gmail.com
    Expand Collapse
    Guest

    > You say if fails when you filter it: How are you doing that?

    Programmatically, from a report open event.

    Me.FilterOn = False
    Me.Filter = "DateReport=#" & DateStart & "#"
    Me.FilterOn = True

    I can duplicate this on the original query by putting date value in the
    Criteria row. Note that using DateAlert, which is a static valid
    DateTime, works in both these scenarios.

    Thanks for looking at this, appreciate the help.

    J Wolfgang Goerlich
     
  13. jwgoerlich@gmail.com

    jwgoerlich@gmail.com
    Expand Collapse
    Guest

    Allen Browne wrote:
    > The query looks fine to me, and it is presenting as if JET understands the
    > type correctly.


    Got it! I think Access is pre-fetching the data and building the
    DataReport alias, then removing the rows with Null DateAlert values.

    As a work-around, I added a Update query to disable all Null DateAlert
    rows _before_ previewing the report. My code is as follows:

    Dim sql As String
    Dim rep As String

    DoCmd.SetWarnings False

    sql = "Update DateAlert Set Alert=0 Where DateAlert Is Null;"
    rep = "What is happening"

    DoCmd.RunSQL sql
    DoCmd.OpenReport rep, acViewPreview, , , , sArgument

    DoCmd.SetWarnings False

    This was a strange problem for me. Greatly appreciate all the help!

    J Wolfgang Goerlich
     

Share This Page