Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376

    Should be simple But I can't get it to work.

    Hello all,



    This should be very simple but for some reason I am missing something. I have a database that I have been using for a long time and created many different queries in it but this one I just can't get to work, I am sure it is something very simple I am overlooking.

    The attached database has 1 table, 1 form and 1 report from the large one I created and use daily. I want to be able to query by date but when I do, I get no results, as soon as I take out the date criteria, I get the results but I need the date in there. There are no results from today so any dates will be from yesterday or before.

    Ideally I would like to enter a persons initials and a date and have the results show. If I put [Enter Initials] in the criteria for "Insp By", I get results for all entries for that person, but regardless of what I put in the criteria for date the results are empty, I would like to be able to use [Enter Date] in the "LabelDate" criteria and get the results for that person on that date.

    What am I missing?

    DateIssue.zip


    Thanks
    Dave

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    TBH I have no clue either.? I tried UK and USA date formats. Nothing

    FWIW >= works on single date and BETWEEN also works. However if you ever find out why, please post back with the reason.

    EDit:
    It does work. I just tried DB and 09/07/2018 and that produced records, so check your data.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    I just tried the same thing that you did and still got no results. I know it has to do with the date but I just don't know why.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Dave14867 View Post
    I just tried the same thing that you did and still got no results. I know it has to do with the date but I just don't know why.
    I do now. Your date field has timevalues in it.

    Add another field in the query as TimeValue(labeldate) and see the results.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Seems you mucked it up around October?
    Try for dates before then and it should work?

    That explains why my previous criteria worked, and also BETWEEN.

    HTH
    Attached Thumbnails Attached Thumbnails label.png   label report.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    If I look at the table, I don't see the time values in there, just a date

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Dave14867 View Post
    If I look at the table, I don't see the time values in there, just a date
    That was what was confusing me, but that is just the format?

    Change the format to General and then look at it and expand the date column in Oct 20.

    You were storing Date() and must have changed it to Now() around then.?

    Use DateValue on the field for what the date you want.
    However this data will likely affect other portions of your DB?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by Dave14867 View Post
    If I look at the table, I don't see the time values in there, just a date
    You have the field formatted as short date in the table.

    What part of Upstate NY?
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  9. #9
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Is there a way I can get just the date into another column?

    Southern Finger Lakes area

  10. #10
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You could use DateValue(), but then I had problems with entering a date.?
    I have got it working with Int(Labeldate) and then criteria of DateValue([Enter Date])

    The Enter Date was just for testing.

    You might be better off cleaning the table?

    On a test copy try updating Label Date with Int(LabelDate)

    Experts might have better ideas?

    Code:
    SELECT tblLabels.*, tblLabels.[Insp By], Int([LabelDate]) AS Expr2, TimeValue([labeldate]) AS Expr1
    FROM tblLabels
    WHERE (((Int([LabelDate]))=DateValue([Enter Date])));
    Attached Thumbnails Attached Thumbnails IntDate.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    This appears to clear out the time element.?

    Code:
    UPDATE tblLabels SET tblLabels.LabelDate = DateValue(tblLabels.LabelDate)
    WHERE ((Int(tblLabels.LabelDate)<>tblLabels.LabelDate));
    Attached Thumbnails Attached Thumbnails Capture.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Southern Finger Lakes area
    Just over the border from albany in Ma.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  13. #13
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    Moke,

    Driven thru that area many times on way to NH and back.

  14. #14
    Dave14867's Avatar
    Dave14867 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Location
    Upstate NY
    Posts
    376
    welshgasman, where did you put the code?

  15. #15
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Quote Originally Posted by Dave14867 View Post
    welshgasman, where did you put the code?
    Open the QBE and dont select any tables. Then click on the SQL view and paste the code into it. Run it and your done.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 04-17-2018, 05:17 PM
  2. Simple calculation won't work
    By wcrimi in forum Queries
    Replies: 5
    Last Post: 11-27-2015, 03:47 PM
  3. Listbox will work when multiselect=simple
    By ro88y09 in forum Forms
    Replies: 3
    Last Post: 02-25-2015, 11:10 AM
  4. Replies: 4
    Last Post: 05-25-2014, 02:01 AM
  5. Replies: 2
    Last Post: 02-22-2012, 07:14 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