Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    7

    Question Unanswered: Between dates/times Help needed

    Hiya
    I'm trying to do a query that would extract all records that are time-stamped between 3pm yesterday and 3pm today (field1). Can anyone help?

    Date field is formatted date time e.g. "01/07/2011 15:25:00"

    Sql:

    SELECT tblMovLog.Field1 AS [Date], tblMovLog.Field2 AS [Requested by], tblMovLog.Field3 AS Reg, tblMovLog.Field4 AS Site, tblMovLog.Field5 AS Dealer, tblMovLog.Field6 AS Sales, tblCurrent.DESCRIPTION

    FROM tblMovLog LEFT JOIN tblCurrent ON tblMovLog.Field3 = tblCurrent.[REG NO]

    WHERE (((tblMovLog.Field4)="Autologic"));


    thank you for any help you can provide, it's driving me crackers!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    WHERE tblMovLog BETWEEN #01/09/2012 15:00:00# AND #02/09/2012 15:00:00#
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    DATE is a reserved word do not use reserved words for table, column or variabel names it can cause chaos
    Try to avoid using spaces in table/column names as well. use either CamelCase or underscores

    modify your where clause and insert the equivalent of
    dim MyDateVar as date
    'using a quirk of access datetimes (the number of clicks since a point in time, and time is the proportion of the day
    MyDateVar = date() 'set the current date
    MyDateVar = MyDateVar + 15/24 ' effectively add the time bracket

    where MyDateColumn between dateadd("d",-1,MyDateVar) and MyDateVar
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2012
    Posts
    7
    thank you weejas, but I'm looking for it to always select between 3pm yesterday and 3pm today regardless of the actual date.

    healdem, Thank you, i'm a novice with VB, I dont even know how to open it from access.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I was providing an example of formatting, although in haste I missed off the field name!

    To get into the VBA Editor from Access, select Modules, add a new module and open it. Or press Alt+F11.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    May 2012
    Posts
    7
    How would i get it to represent something like:

    BETWEEN #date()-1 15:00:00# AND #Date() 15:00:00#

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Look at healdem's example from yesterday. It gives you everything that you need to set up the times.

    Note that delimiting symbols such as double quotes("), single quotes(') and hashes (#) denote literal values - you cannot use functions within them.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

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