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
> Date Code Within A Form, Access 2016    
 
   
Thomas Whiteside
post Mar 31 2020, 06:21 AM
Post#1



Posts: 7
Joined: 30-March 20



Hi everyone. I'm new here so I hope I'm in the right place! I'm using Access 2016 (although it masquerades as A2019?).

I have a simple form that I'm trying to return records based on a field formatted as Date/Time. My date type is Date/Time with a Default Value of Now(), so it's automatically inserted with every new record. So, the field contains a Date/Time formatted as: 31/03/2020 08:48:36, for example.

I can't tell you the mess I've got myself into! My code looks something like this: ... Where (((ArchiveDate.DateTime) = Format(Me.ArchiveDate,ddmmyyyy hhmmss))));". I have tried Between and Having statements and nothing works!

I'd appreciate any help you can give me. Many thanks.

Tom.
Go to the top of the page
 
BentBrain
post Mar 31 2020, 06:34 AM
Post#2



Posts: 563
Joined: 10-February 03
From: Thailand


Hi Thomas
Have you looked at this site on how to write your query when dealing with dates

https://support.office.com/en-us/article/ex...89-5fc961f21762

Note: You can also use the Between operator to filter for a range of values, including the end points. For example, Between #2/2/2012# and #2/4/2012# is the same as >=#2/2/2012# and <=#2/4/2012#.

Share your SQL string for your query and the community will give you some help.

Regards
BentBrain

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
Jeff B.
post Mar 31 2020, 07:00 AM
Post#3


UtterAccess VIP
Posts: 10,479
Joined: 30-April 10
From: Pacific NorthWet


I'll offer the observation that what you see in a form (format) and what is stored in an Access table (datatype) are not the same. Plus, based on your example, your display format may be European date, where Access, I suspect by default, tries to use American date format.

Are those differences contributing to the issue?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
orange999
post Mar 31 2020, 07:21 AM
Post#4



Posts: 2,098
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Thomas,
Can you tell us the purpose of the Date/Time field in the Search/Select form? As a date/timestamp it may be needed. But it is somewhat rare for a selection of records to be constrained to minutes and seconds.
If you need to find records for a given Month, or given Day or Date range, then Access has intrinsic functions that may help. (eg.Day(), Month() ..). Perhaps there are some options if we knew the types of Searches/Selects and results you anticipate.
As JeffB said, storage and presentation are not the same.


--------------------
Good luck with your project!
Go to the top of the page
 
GroverParkGeorge
post Mar 31 2020, 08:10 AM
Post#5


UA Admin
Posts: 37,239
Joined: 20-June 02
From: Newcastle, WA


First, Welcome to UtterAccess.

Okay, we have to review a couple of basic concepts in order to sort out the current problem.

ALL dates in Access are stored as Date with time. ALL of them, regardless of whether you specify the time portion or not.

Dates are stored internally as a number, in the form of a double.

The part of the number to the left of the decimal is the number of days elapsed since the start date, which in Access is 12/31/1899. In other words, ALL DATES are stored as the number of days since December 31, 1899. Today, for example is March 31, 2020, or day 43921.

The part of the number to the right of the decimal is the elapsed time since midnight of the date. In other words, 6:00AM is .25 or one quarter of the 24 hours in a day (6/24 = .25)

All of that is important to understand before you start working with dates.

Next, the question of how to do calculations on dates.

When you use Format() on a date, you CONVERT that to a string. That means we do NOT want to use Forma() in date comparisons. We want to use the date functions that work directly with dates as dates.

I have a handful of YouTube videos that discuss various aspects of this, which I suggest instead of rehashing it all again here.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Larry Larsen
post Mar 31 2020, 08:25 AM
Post#6


UA Editor + Utterly Certified
Posts: 24,573
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Tom

How dependent on having both Date & Time data with in your field, you can separate this values..

This link that will explain the issue we have here in the UK about creating the right format to couple with dates..

International Dates in Access

HTH's
thumbup.gif


--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
cheekybuddha
post Mar 31 2020, 08:50 AM
Post#7


UtterAccess Moderator
Posts: 12,804
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

From your date format it looks like you're in the UK or somewhere that has a local date format of dd/mm/yyyy.

When passing dates as an input or criteria to SQL you must format it in an unambiguous format - the best options being ISO (yyyy-mm-dd) or US Date format (mm/dd/yyyy)

So you can use this:
CODE
... Where ArchiveDate.DateTime = Format(Me.ArchiveDate, "\#yyyy\-mm\-dd hh:nn:ss\#");


If you are looking to match the date only then use:
CODE
... Where ArchiveDate.DateTime >= Format(Me.ArchiveDate, "\#yyyy\-mm\-dd\#") AND  ArchiveDate.DateTime < Format(Me.ArchiveDate + 1, "\#yyyy\-mm\-dd\#");


hth,

d

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


Regards,

David Marten
Go to the top of the page
 
Thomas Whiteside
post Apr 12 2020, 06:17 AM
Post#8



Posts: 7
Joined: 30-March 20



Thanks BentBrain – I think this site has been brilliant and I’ve learned so much about dates. I haven’t used Access since A97 and never really explored date/time then anyway, so never learned anything about it but I think that’s all changed now! US formatting was the key and solves my immediate problem – I will post the SQL string when I’m happy it’s good, if for no other reason to see how it can be improved. Thanks again.

Go to the top of the page
 
Thomas Whiteside
post Apr 12 2020, 06:17 AM
Post#9



Posts: 7
Joined: 30-March 20



Hi Jeff, many thanks for your help with this… I would never have thought of US formatting in a million years! In fact having tried at least 10 different scenarios I still thought ‘what has formatting to do with it’! Anyway, you are correct that I am in London and format dates in the European style. So why on earth would I want to format anything by the US style? Okay, so I tried it and it worked correctly first time!! So, what I see in my tables is not what is stored! Simple! Any wonder I’m totally demented and so very lucky I’m in isolation because I can pull my hair out and scream until my heart’s content without anyone hearing me!! I love Access!
Go to the top of the page
 
Thomas Whiteside
post Apr 12 2020, 06:18 AM
Post#10



Posts: 7
Joined: 30-March 20



Thank you for your help in this. Yes I do need to include dates and times because it’s critical to when documents are accessed and archived, etc. Yes, US formatting was the key and the query now works perfectly… I’ll post it later…
Go to the top of the page
 
Thomas Whiteside
post Apr 12 2020, 06:18 AM
Post#11



Posts: 7
Joined: 30-March 20



Hi George and thank you for your help. Yes I was aware that Access stores date and time as a decimal but I wasn’t aware that formatting it would convert it to a string. I need to return all documents inputted in any one day (at the moment – this might change in the future but what I’m learning now will go a long way to teaching me how to change this later!) So I need to return records between 00:00:00 and 23:59:59 in any one day… Formatting seems to be the key and having formatted in US style everything is now working perfectly, although I’m fairly sure there’s a neater solution than formatting (but not critical!).
Go to the top of the page
 
Thomas Whiteside
post Apr 12 2020, 06:19 AM
Post#12



Posts: 7
Joined: 30-March 20



Hi Larry and thanks for your help. I read the text in your link – wow dates aren’t that easy! Anyway, the formatting was the key and I should have guessed that I’d have to format the date in the US style! I’ll post the SQL as soon as I’m happy with it and see if it can be improved…
Go to the top of the page
 
Thomas Whiteside
post Apr 12 2020, 06:19 AM
Post#13



Posts: 7
Joined: 30-March 20



Hi David and thanks for your help… Yes, I’m in London and the solution was to use the US forming instead of the UK formatting – simple really!! I’ll post the final SQL string when I’ve reached the limit of my knowledge on the matter (which should be in about 5 minutes!).
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 06:57 PM