Results 1 to 5 of 5
  1. #1
    Delta729 is offline Advanced Beginner
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95

    Search form with date

    I'm working on building a search form for my db. Main form is frmSeach and subform is frmProjectTaskSearch. The subform is pulling from a table where I have a stored record called ArrivalTime, which is actually a datetime field. However, when searching I'm trying search the whole day. I'm getting a error telling inconsistent formatting. I've tried adding in the format option into the code, but I've still got something wrong. Can anyone help, I've never tried this before.


    Private Sub txtStartDate_AfterUpdate()
    Dim myStartDate As String
    myStartDate = "Select * from tblProjects where (Format([ArrivalTime], "mm/dd/yyyy") = '" & Me.cboOriginator & "')"
    Me.frmProjectTaskSearch.Form.RecordSource = myStartDate
    Me.frmProjectTaskSearch.Form.Requery
    End Sub

    Thanks,


    Dan

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    datetime fields are actually numeric e.g. today (14th June) is 42199 and the time is 23:17 which is expressed as a decimal .9700578704 which is the time in seconds divided by 24 hrs x 60mins x 60 seconds

    So to check - your arrival time consists of a date (say today) and a time (say 23:17) so is stored as 42199.9700478704 and the same time tomorrow will be stored as 42200.9700478704

    you use the format and date functions to express this value in a date/time way and to manipulate the value

    It is not clear from your post what you are trying to achieve - your are searching the whole day - but for what? A specific time, all the times on that day? If the latter and cboOriginator contains a date then your code would be

    myStartDate = "Select * from tblProjects where (datepart([ArrivalTime]) = #" & format(Me.cboOriginator,"mm/dd/yyyy") & "#)"

    Note that when comparing to a textual date it is necessary to a) surround with a # (so access sql knows it is a date) and b) format as the US style of date - month/day/year

    recommend you google 'access date functions' to find out more about all the different ways dates can be presented and manipulated

  4. #4
    Delta729 is offline Advanced Beginner
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    I'm trying to pull all records on the tblProjects table to show on my subform where the [ArrivalTime] was on a date that matches the txtStartDate unbound field on the parent form. Actually, it will be = to or greater than, but initially just trying to get a specific date to come up on the subform.

    Edit: Got it working. I'm a little slow most of the time.

    myStartDate = "Select * from tblProjects where [ArrivalTime] >= #" & Me.txtStartDate & "# "

    Thanks all for your help.

  5. #5
    Delta729 is offline Advanced Beginner
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    Thanks Orange, that looks like the next step after I get the formatting correct for the ArrivalTime.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form to search for data by date range
    By andyt_2005 in forum Forms
    Replies: 3
    Last Post: 08-02-2014, 11:32 AM
  2. Replies: 3
    Last Post: 09-11-2013, 09:49 AM
  3. Replies: 2
    Last Post: 12-16-2012, 01:40 AM
  4. Replies: 4
    Last Post: 05-26-2012, 09:29 AM
  5. Search form with a date range
    By mantro174 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums