UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Same Start And End Date Shows Not All Records, Access 2016    
 
   
masterYoda16
post Apr 10 2018, 02:19 PM
Post#1



Posts: 30
Joined: 8-January 16



HI UA,

I am trying to see if I can get some assistance: I have a form where two unbound textboxes are for start and end dates.

My problem is that if I used the same date for start and end date, I get only few records, but if I check the table, it shows more records for that date. Now the interesting thing is, if I have the end date as in tomorrow' date, then all the records that were created today showed up.

Is this a formatting error????


Go to the top of the page
 
theDBguy
post Apr 10 2018, 02:26 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,961
Joined: 19-June 07
From: SunnySandyEggo


Hi,

You could say that, I guess. The real problem is your data probably include a time component but you're only using dates in your query criteria, which means it has a zero (0) time component.

For example, if you say give me all records from 1/1/2018 through 1/2/2018, you're basically saying return records from 1/1/2018 00:00 AM through 1/2/2018 00:00 AM. So, if a record has a date and time of 1/2/2018 07:00 AM, it won't be included in the result.

To include the whole day, you could use the following day's date or include the time component up to midnight. For example: 1/1/2018 through 1/2/2018 11:59 PM.

Hope it makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
masterYoda16
post Apr 10 2018, 02:33 PM
Post#3



Posts: 30
Joined: 8-January 16



TheDbGuy,

I knew it has something to do with the date/time. I just wanted to see if this has to do with something else, maybe converting date/time to certain time zone etc etc...Thank you for pointing out.


Go to the top of the page
 
theDBguy
post Apr 10 2018, 02:38 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,961
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
masterYoda16
post Apr 12 2018, 02:55 PM
Post#5



Posts: 30
Joined: 8-January 16



TheDBGuy,

Ok thought I got this covered, but somehow it still not showing the records I need.

query#1

[DateField1]--I added DateValue[DateFiedl1]
Where: Between[Forms]![frmReport]![txtStartDate]!And[Forms]![frmReport]![txtEnDate]

------------

query#2

has all the other fields that report is based on

-----------

Am I missing something here???
Go to the top of the page
 
theDBguy
post Apr 12 2018, 03:03 PM
Post#6


Access Wiki and Forums Moderator
Posts: 71,961
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Can you post a sample database with test data? What is your Regional Settings?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th April 2018 - 07:59 PM