Results 1 to 12 of 12
  1. #1
    tdsummers is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    6

    Recall Records by date in a form

    I have a database with a little over 5000 entries for various customers. I am looking for a way to enter date from the main form and go to the first entery for that date.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you have an unbound form ,put a textbox, TxtDate
    Make a query that looks at this textbox ,
    select * from table where [dateField] = forms!myForm!txtDate

    A button would open the query,
    docmd.openquery "qMyQuery"

    If you want only 1 record,the Max date,then you need 2 queries,
    Q1=select ID, Max([dateFld] from table.

    then Q2: uses Q1 and the data table joined on ID

  3. #3
    tdsummers is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    6
    I am getting the following error; Check the subquery's syntax and enclose the subquery in parentheses.

    select * from [Master Table] where [Date Reported=Forms![Master Form][txtDateEnter]

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    DateReported doesn't have a closing ]. Are you doing this in VBA or is this the actual query?

  5. #5
    tdsummers is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    6
    I would to run a query form the form and have it go to the date input.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That should work. It will show all records for that date, sort it in the order that you want and show the top 1.

  7. #7
    tdsummers is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    6
    Is there a method, instead of displaying the results of a query, I can just have the form go to that date?

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure what you mean - do you want the data to be displayed on the form instead of in a query? A subform will do that, without the query. Set the record source to the table name (Master table) and link the main form to the subform using the date.

  9. #9
    tdsummers is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    6
    What I am looking to do is to input a date and have the form go to the first record for that date, for example if I input 5/1/2016 I would like my form to display the first record for that date

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Have Master Table as the record source for the form. Add a date field as an unbound field. When the user enters the date, in the AfterUpdate event, filter the form:
    Me.Filter="DateReported=#" & me!userentereddate & "#"
    Me.FilterOn=True
    Me.Requery

  11. #11
    tdsummers is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    6
    I am still not able to have the form open to a specified date, I have tried the below. Any Ideas

    Quote Originally Posted by aytee111 View Post
    Have Master Table as the record source for the form. Add a date field as an unbound field. When the user enters the date, in the AfterUpdate event, filter the form:
    Me.Filter="DateReported=#" & me!userentereddate & "#"
    Me.FilterOn=True
    Me.Requery

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    We need more information, a step-by-step of the process. Include any code and/or queries where applicable.

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

Similar Threads

  1. How to recall information previously entered
    By Accu-Grind in forum Forms
    Replies: 4
    Last Post: 11-07-2015, 06:34 AM
  2. Recall previous values on a form
    By Thermalmonster in forum Programming
    Replies: 12
    Last Post: 07-28-2014, 01:54 PM
  3. Recall max value from Table
    By Thermalmonster in forum Programming
    Replies: 2
    Last Post: 07-27-2014, 01:38 AM
  4. Filter form records by Date with Combo box
    By jhrBanker in forum Forms
    Replies: 2
    Last Post: 07-31-2012, 01:19 PM
  5. Sorting a form's records in order by date.
    By slikbaz in forum Access
    Replies: 3
    Last Post: 11-20-2010, 01:11 AM

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