Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2015

    Unanswered: Issue with text vs date in Access web app

    I'm very inexperienced at this first please don't laugh too hard at my questions. lol

    I'm building an Access web app to replace a spreadsheet that grew too large. Part of the spreadsheet is in a format that I can't change. The other half, is the part that we edit.

    So, there's a text field that will have a date in it, or it may have text in it.

    I can't set the field in access to date/time as multiple records will be copied/pasted in with multiple data types in this particular field. So it's set to short text.

    Problem is, I want to be able to query or sort by: 1. Those that are blank (easy "Is Null"), 2. Those that have text, and 3. Those that have dates - and then be able to pick dates in a certain range as the return value.

    I can't figure out if this is even possible. I've searched everywhere. Everything related to dates says it must be in a date field, but I've done this before in excel using "if" functions.

    I have ZERO code knowledge and can only work within the access app itself, fyi.

    I thought I could do a query that would only pull from this field if the data was in a date format, and then sort that. Couldn't get that to work.
    I thought maybe I could use a field with a calculate function to pull just the dates into it, so then I could sort. Couldn't get that to work.

    Any suggestions are greatly appreciated!

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    As a general rule : store a date/time value in a column defined as of Date/Time data type. Access has functions to format such values the way you want. It's a common mistake for people coming from the Excel word to confuse a value with its format (i.e. the way it's displayed). If it's not possible and you have to use a Text data type, you can always test (function IsDate()) or convert (function CVDate()) the said value.
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Quote Originally Posted by warnold207 View Post

    1. Those that are blank (easy "Is Null")
    2. Those that have text
    3. Those that have dates
    1. Is Null
    2. Not Is Null And Not IsDate()
    3. IsDate()

    I'd get this part working before tackling the date range part.

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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