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
> Can't Specify A Date In A Date Field, Access 2010    
 
   
outOfMyDepth
post Jan 18 2018, 09:57 AM
Post#1



Posts: 113
Joined: 21-May 03
From: leicester UK


hi Guys

I've imported some raw data from an Excel file that includes [Order Date]
This is stored as a General Date in Access and the same in Excel.
Here's my confusion.
I have created a select query based on another query that uses data from the original table. One of the 'where' conditions is on the Order Date.
If I enter a 'between' condition (start date and end date) it appears to work fine.
If I change the condition to a single date it comes up with nothing - see =#2/1/2017# after WHERE line below.

SELECT qryAllData.[Order Number], qryAllData.[Order Date], qryAllData.[Order Time], qryAllData.[Order Day], qryAllData.[Total Order Value], qryAllData.[Consignment Status], qryAllData.[Consignment Shipped], qryAllData.[Shipped Time], qryAllData.[Shipped Day], qryAllData.[Age in Days]
FROM qryAllData
WHERE (((qryAllData.[Order Date])=#2/1/2017#) AND ((TimeValue([qryAllData].[Order Date])) Between #12/30/1899# And #12/30/1899 14:59:59#) AND ((Weekday([Order Day])) Between 2 And 6))
GROUP BY qryAllData.[Order Number], qryAllData.[Order Date], qryAllData.[Order Time], qryAllData.[Order Day], qryAllData.[Total Order Value], qryAllData.[Consignment Status], qryAllData.[Consignment Shipped], qryAllData.[Shipped Time], qryAllData.[Shipped Day], qryAllData.[Age in Days];

If I change the condition to WHERE (((qryAllData.[Order Date]) Between #1/1/2017# And #10/27/2017#).... everything works
Go to the top of the page
 
doctor9
post Jan 18 2018, 10:04 AM
Post#2


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


outOfMyDepth,

Is it possible that the data includes a time component as well?

#2/1/2017 8 AM# is not equal to #2/1/2017#. Date/Time data is stored as the number of days since 12/31/1899. The number of hours/minutes/seconds are stored as the fractional portion of the number. So, if #2/1/2017# is 42767, then #2/1/2017 8AM# would be 42767.25 since 8am is 1/4th of the way through a day.

If you are only interested in the date portion, try using the CLng() function on the stored value to strip out the time portion.

WHERE CLng([Order Date])=#2/1/2017#

Hope this helps,

Dennis
Go to the top of the page
 
outOfMyDepth
post Jan 18 2018, 10:17 AM
Post#3



Posts: 113
Joined: 21-May 03
From: leicester UK


Thanks Doctor9

I will try it now as you are correct, the date contains the time as well. I tried formatting the field to a short date but it obviously still stores the time and displays the date. I've gone through reams of Access reference books trying to find the function that does what you have taken five minutes of your time to do.

Respect.
Go to the top of the page
 
doctor9
post Jan 18 2018, 10:22 AM
Post#4


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


outOfMyDepth,

Remember that the Format of the data doesn't actually change the stored value - it only affects how that value is displayed. Also, you need to be careful with the Format() function as that will always return a String value.

Oh, and I made a minor error; 8am is 0.3333 of the way through the day, not .25 - but that's just a minor thing.

Dennis
Go to the top of the page
 
outOfMyDepth
post Jan 18 2018, 10:35 AM
Post#5



Posts: 113
Joined: 21-May 03
From: leicester UK


Oh dear, that doesn't seem to work either when I put it in the query

Never mind, back to manual cut and past.
Go to the top of the page
 
doctor9
post Jan 18 2018, 10:47 AM
Post#6


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


outOfMyDepth,

Can you post the SQL that isn't working?

Dennis
Go to the top of the page
 
GroverParkGeorge
post Jan 18 2018, 01:16 PM
Post#7


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

"Format" as a date is not the same thing as CONVERT to a date or select just the Date part of a date/time field.

Have you tried DateValue([YourDateFieldNameGoesHere])?
Go to the top of the page
 
outOfMyDepth
post Jan 19 2018, 03:01 AM
Post#8



Posts: 113
Joined: 21-May 03
From: leicester UK


Thanks Both

DateValue has done the trick. The data is stored as 'General Date' so didn't need converting but needed time stripping out.

As always UtterAccess delivers
Go to the top of the page
 
ZapDude
post May 21 2018, 01:07 PM
Post#9



Posts: 292
Joined: 27-February 06
From: Long Beach CA


Just to clarify, a Date/Time value is simply a glorified Single or Double Number in which the Date is the number of days that have passed since December 30, 1899 (Day=0), while Time is a fraction of a number that represents the fraction of time elapsed between the 24-hour period of midnight and midnight of next day. You can convert between date/times to single or double numbers using CSng or CDble, and single/double numbers to date/times using CDate. (Double numbers are needed for milliseconds or shorter time periods than 1/1000 of a second.)

Edit: Today's date, May 21, 2018, at 12:00 Noon's decimal value is 43241.5 as noon is exactly halfway between midnight and midnight (next day).
This post has been edited by ZapDude: May 21 2018, 01:10 PM
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 04:18 AM