Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    Unanswered: Wildcards in Access dates

    The below query works fine in Access 2000. However when I try to use a wildcard character instead of specifying the year in the WHERE clause i get a syntax error.

    Can anyone help?



    SELECT Count(qryCulmAbsencesToDate.[Absences to Date]) AS [Number of Absences],
    qryCulmAbsencesToDate.FirstName, qryCulmAbsencesToDate.MiddleName,
    qryCulmAbsencesToDate.LastName
    FROM qryCulmAbsencesToDate
    WHERE (((qryCulmAbsencesToDate.RollDate) Between #9/1/2002# And #9/30/2002#))
    GROUP BY qryCulmAbsencesToDate.FirstName, qryCulmAbsencesToDate.MiddleName,
    qryCulmAbsencesToDate.LastName;
    Last edited by docaithil; 01-30-03 at 09:16.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    datetime datatypes cannot use wildcards

    your options are to convert the RollDate to a string and use wildcards there, or -- and in the example you gave, this might be better -- change the query as follows:

    where month(RollDate) = 9


    rudy

  3. #3
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Other option

    Parameter query?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  4. #4
    Join Date
    Jan 2003
    Posts
    5
    Thanks Rudy.

    Using "where month(RollDate) = 9" worked fine.

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Year

    What about if the data covers more than 1 year, or does that still work?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does month(RollDate) = 9 still work if the data covers more than one year? of course -- it selects data from september for all years

    if you wanted data just from august 2001 through july 2002, you would code

    where
    year(RollDate) = 2001 and month(RollDate) >= 8
    or
    year(RollDate) = 2002 and month(RollDate) <= 7

    neat, eh?

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Yes

    ...That's what I was hinting at.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •