Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Could someone help me with this SQL.
    I need to be able to select different years.
    The following code works when if I don't use a variable in the WHERE statement.

    Dim intYear As Integer
    Dim ThisDate As Date

    intYear = Year(Date)
    ThisDate = "01/01/" & intYear

    Set myset = CurrentDb.OpenRecordset("SELECT aqryEmpAbs.* FROM aqryEmpAbs " & _
    "WHERE aqryEmpAbs.StartDate > ThisDate:", DB_OPEN_SNAPSHOT)

    Mary

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='Marytk' post='774772' date='12-May-2009 12:03']Could someone help me with this SQL.
    I need to be able to select different years.
    The following code works when if I don't use a variable in the WHERE statement.

    Dim intYear As Integer
    Dim ThisDate As Date

    intYear = Year(Date)
    ThisDate = "01/01/" & intYear

    Set myset = CurrentDb.OpenRecordset("SELECT aqryEmpAbs.* FROM aqryEmpAbs " & _
    "WHERE aqryEmpAbs.StartDate > ThisDate:", DB_OPEN_SNAPSHOT)

    Mary[/quote]

    You cannot include the variable within the quote marks around your string.
    try this.

    Set myset = CurrentDb.OpenRecordset("SELECT aqryEmpAbs.* FROM aqryEmpAbs " & _
    "WHERE aqryEmpAbs.StartDate >" & ThisDate, DB_OPEN_SNAPSHOT)
    Regards
    John



  3. #3
    New Lounger
    Join Date
    May 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='774773' date='11-May-2009 21:09']You cannot include the variable within the quote marks around your string.
    try this.

    Set myset = CurrentDb.OpenRecordset("SELECT aqryEmpAbs.* FROM aqryEmpAbs " & _
    "WHERE aqryEmpAbs.StartDate >" & ThisDate, DB_OPEN_SNAPSHOT)[/quote]

    Just what I needed

    Thanks so much

    Mary

  4. #4
    New Lounger
    Join Date
    May 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Marytk' post='774774' date='11-May-2009 21:16']Just what I needed

    Thanks so much

    Mary[/quote]


    I still have a problem.
    The variable ThisDate is correct and has a value of 01/01/2009 but all the records are being picked up.
    Shouldn't this limit the records in MySet to anything with a StartDate larger than 01/01/09?

    Mary

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='Marytk' post='774777' date='12-May-2009 13:02']I still have a problem.
    The variable ThisDate is correct and has a value of 01/01/2009 but all the records are being picked up.
    Shouldn't this limit the records in MySet to anything with a StartDate larger than 01/01/09?

    Mary[/quote]
    Try this:
    "WHERE aqryEmpAbs.StartDate >" & Format(ThisDate, "mm/dd/yy"), DB_OPEN_SNAPSHOT)

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='patt' post='774787' date='12-May-2009 15:03']Try this:
    "WHERE aqryEmpAbs.StartDate >" & Format(ThisDate, "mm/dd/yy"), DB_OPEN_SNAPSHOT)[/quote]

    "WHERE aqryEmpAbs.StartDate >#" & Format(ThisDate, "mm/dd/yy") & "#", DB_OPEN_SNAPSHOT)

    The format functions converts the date into a string, so it needs to be enclosed in # marks.
    What country are you in? What is your normal date format?
    Regards
    John



  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='johnhutchison' post='774789' date='12-May-2009 14:19']"WHERE aqryEmpAbs.StartDate >#" & Format(ThisDate, "mm/dd/yy") & "#", DB_OPEN_SNAPSHOT)

    The format functions converts the date into a string, so it needs to be enclosed in # marks.
    What country are you in? What is your normal date format?[/quote]
    Oops thanks John.

    That's what happens when you use air code.

  8. #8
    New Lounger
    Join Date
    May 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank for the help.

    I'm in Canada and use mm/dd/yy but I'm a beginner and didn't expect the format to be changed to something that wouldn't work.

    I learned a lot from this post.

    Thanks again.

    Mary

Posting Permissions

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