Results 1 to 7 of 7
  1. #1
    Amerlitong is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Feb 2013
    Posts
    10

    Date Filtering

    I have this table and a Date column(YYYY-MM-DD format) and i assumed this is a TEXT.


    I made this query linked to a textbox(i set the input mask same format as it is in my table).
    This is the code.

    SELECT * FROM tblData WHERE Col_Date LIKE "*" & FORMS!frmMain![txtDate] & "*"


    Please advise.

    P.S.

    the table is imported from excel file.


    Thanks

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by Amerlitong View Post

    ...I made this query linked to a textbox...
    Putting aside, for the moment, that fact that you're trying to match a Text value with a Date value, how did you manage to "link a textbox" to a Query? You cannot use a Query or SQL statement as the Control Source for a Textbox. For this kind of thing you'd have to use the DLookup function.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    Quote Originally Posted by Missinglinq View Post
    Putting aside, for the moment, that fact that you're trying to match a Text value with a Date value, how did you manage to "link a textbox" to a Query? You cannot use a Query or SQL statement as the Control Source for a Textbox. For this kind of thing you'd have to use the DLookup function.

    Linq ;0)>
    If you update a query with a string then execute the query you can use a textbox to make run a query.


    qdf.sql = "select ........"
    qdf.close

    docmd.openquery("query you changed")


    however you right to about the date and string where clause.
    I would change the string.

    SELECT * FROM tblData WHERE Col_Date LIKE "*" & FORMS!frmMain![txtDate] & "*"

    to
    to set the qdf.sql
    "SELECT * FROM tblData WHERE Col_Date = #" & FORMS!frmMain![txtDate] & "#"

    hope this helps

  4. #4
    Amerlitong is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Feb 2013
    Posts
    10
    Quote Originally Posted by Missinglinq View Post
    Putting aside, for the moment, that fact that you're trying to match a Text value with a Date value, how did you manage to "link a textbox" to a Query? You cannot use a Query or SQL statement as the Control Source for a Textbox. For this kind of thing you'd have to use the DLookup function.

    Linq ;0)>
    Sorry if i mentioned it wrong...i just want to say, i have this query that get the parameter from a textbox in a form...

    Cheers

  5. #5
    Amerlitong is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Feb 2013
    Posts
    10
    Quote Originally Posted by alcapps View Post
    If you update a query with a string then execute the query you can use a textbox to make run a query.


    qdf.sql = "select ........"
    qdf.close

    docmd.openquery("query you changed")


    however you right to about the date and string where clause.
    I would change the string.

    SELECT * FROM tblData WHERE Col_Date LIKE "*" & FORMS!frmMain![txtDate] & "*"

    to
    to set the qdf.sql
    "SELECT * FROM tblData WHERE Col_Date = #" & FORMS!frmMain![txtDate] & "#"

    hope this helps
    Thanks mate...i never thought about this # sign...but i have another problem...if i leave the textbox blank it should be filter all the data...

    Please advise...

    Thanks...

  6. #6
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    Do an if statement like this looks at txtDate and if it is null makes it "" if a space was there.. Trim takes away all spaces then checks the length of the variable if it's greater than 0 does what you did before otherwise drop the where clause for all.

    if Len(Trim(Nz(FORMS!frmMain![txtDate],""))) > 0 then
    select * from with #date#
    else
    select * from tblData
    end if

  7. #7
    Amerlitong is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Feb 2013
    Posts
    10
    Thats it...thank you so much mate...i need to do some VBA stuff...

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

Similar Threads

  1. Date Filtering within a Formula
    By JeanZander in forum Access
    Replies: 8
    Last Post: 10-17-2012, 07:00 PM
  2. Filtering forms by date
    By rwest in forum Forms
    Replies: 1
    Last Post: 01-03-2012, 11:23 AM
  3. Filtering by text or date range
    By Ashe in forum Forms
    Replies: 5
    Last Post: 03-07-2011, 03:00 PM
  4. Date Filtering problem
    By TubbyGrey in forum Access
    Replies: 6
    Last Post: 10-22-2010, 10:46 PM
  5. Filtering results by date between two datefields
    By lakylekidd in forum Programming
    Replies: 9
    Last Post: 06-07-2010, 07:42 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