Results 1 to 8 of 8
  1. #1
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107

    query not pulling dates from open form


    Below is the SQL code of a query that is the row source of a combo box on a subform (frmOrderDetailEntry) of a form (frmOrderHeaderEntry). The data is pulling correctly for all textboxes that contain alphanumeric data, but it is not for the textbox (OrdRecdDateTxt) that contains a date. It shows NULL in the query even though there is a valid date in the textbox. I have verified (repeatedly) that the spelling of the form and textbox are correct. Can someone help me pinpoint what is wrong in either my query or my form that might cause this error?

    Thanks.

    Code:
    SELECT CustNumber, BBIItemNumber, PlanogramCode, PriceBeginningDate, PriceEndingDate, SpecialPrice
    FROM tblSalesSpecialPricing
    WHERE (((CustNumber)=[forms]![frmOrderHeaderEntry]![CustNumberTxt]) AND ((BBIItemNumber)=[BBItemNumberCombo]) AND ((PlanogramCode) Is Not Null) AND ((PriceBeginningDate)<=[forms]![frmOrderHeaderEntry]![OrdRecdDateTxt]));

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are the date values actually of the date data type or are they text (in the underlying table)?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Quote Originally Posted by Micron View Post
    Are the date values actually of the date data type or are they text (in the underlying table)?
    The field in the table is data type DATE/TIME.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Does the field on the form have a date format specified?

  5. #5
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Quote Originally Posted by aytee111 View Post
    Does the field on the form have a date format specified?
    The text box on the form had no format (although the date picker was visible when the text box had the focus). I assigned the text box the format of Short Date. It made no change in the query. The value is still NULL.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm surprised if it works without including the full Forms!frmName reference : e.g. ((BBIItemNumber)=[BBItemNumberCombo])
    Also find it odd that you actually see "NULL" instead of nothing at all. The only other thing I can think of at the moment is that the date textbox isn't updated when you're invoking the query (i.e. the value hasn't been committed yet). Suggest you test the sql by pasting it into a new test query and in design view, substitute actual values where you have form control references. If it still doesn't work, it isn't a control naming issue, nor is it likely a date issue since Access will add date delimiters in design view if the field is date type as you say. If it puts "" around it, for some reason it thinks the date is text. Other than that, consider posting a zipped copy of your db, unless someone else nails it first.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Last thing I can think of to try! In the query, change the date on the form to a date
    ((PriceBeginningDate)<=CDate([forms]![frmOrderHeaderEntry]![OrdRecdDateTxt])));

  8. #8
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Quote Originally Posted by aytee111 View Post
    Last thing I can think of to try! In the query, change the date on the form to a date
    ((PriceBeginningDate)<=CDate([forms]![frmOrderHeaderEntry]![OrdRecdDateTxt])));
    That worked! The query is now pulling the date in as it is on the form.

    For testing purposes, I created an alias field in my query and set it equal to [forms]![frmOrderHeaderEntry]![OrdRecdDateTxt]. There appeared to be nothing there. I set my alias field to a SWITCH function in which I checked for NULL, "" (empty), " " (one space). It came back none of the above (I assumed NULL incorrectly). I then set my alias field to len([forms]![frmOrderHeaderEntry]![OrdRecdDateTxt]). It came back 9, which was the length of the date in the field on the form (5/25/2017)! I added SPACE(9) to my SWITCH function and it again came back negative. I then set the alias field to CDATE([forms]![frmOrderHeaderEntry]![OrdRecdDateTxt]) and it came back correctly.

    Short answer is the query apparently was reading the data as text instead of a date. I just thought I would share some of my testing results because I thought they were curious.

    Thanks aytee111 and Micron for your help.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-28-2016, 08:43 AM
  2. Calendar is not pulling up any dates past 2011
    By AdamLeeZ in forum General Chat
    Replies: 3
    Last Post: 11-06-2015, 05:16 PM
  3. docmd.open form between dates condition
    By Ruegen in forum Programming
    Replies: 6
    Last Post: 09-22-2013, 11:23 PM
  4. in between dates pulling all years
    By mejia.j88 in forum Queries
    Replies: 2
    Last Post: 04-25-2012, 10:49 AM
  5. Replies: 0
    Last Post: 07-27-2009, 07:51 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