Results 1 to 4 of 4
  1. #1
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36

    How to Search for a Date Range in a Text Field? Convert to Date?

    Hi. I have a text field in a table that is a date in the format of m/d/yyyy. I need to be able to search for dates in between a [beginning] and [ending] date. With this being a text field, naturally it does not work correctly in all cases.

    What is the best way to handle? Do I convert it to a date field? If so, what is the best way to do that in the query? If I should keep it in the text format, is there a way I can guarantee that I will get all records? Sorry...new to all of this and trying to learn how to best proceed. (The field name is [Last Start].

    Any guidance please?

    Kindest regards and thanks,


    sren

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In my opinion the best way to handle it is to convert the data type to date/time. You want it to act like a date, so it makes sense that it be one.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why is the field a text type anyway? I would convert in table. To handle in query, try:

    SELECT *, CDate([Last Start]) As LS FROM tablename WHERE CDate([Last Start]) BETWEEN #" & [beginning] & "# AND #" & [ending] & "#";

    I don't recommend popup input parameters in query - too hard to validate. I use a form for entry of criteria into controls. Then either have the query reference the form controls as parameters or use VBA to build a filter string to apply to form or report. I don't use dyanmic query paramaters and therefore use the VBA approach.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    sren is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    36
    Thanks for both replies.

    The field is text as all files are being imported from .txt and brought in as text.

    Let me give what you have suggested as a try. We will also look at building a form. I was pulled from this for another project so it may be some time before I am able to finalize and mark as solved, etc.

    Kindest regards.

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

Similar Threads

  1. Search Date Range with Null Sources
    By MintChipMadness in forum Forms
    Replies: 8
    Last Post: 08-23-2012, 08:56 AM
  2. Convert text date to date value
    By unslog in forum Access
    Replies: 8
    Last Post: 02-01-2012, 12:22 PM
  3. Replies: 1
    Last Post: 08-07-2011, 07:58 AM
  4. how to convert week number to date range
    By qwang1115 in forum Access
    Replies: 1
    Last Post: 02-13-2009, 11:35 AM
  5. Search form with a date range
    By mantro174 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:45 PM

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
  •  
Other Forums: Microsoft Office Forums