Solved

Microsoft Access Date format

Posted on 2016-09-14
18
57 Views
Last Modified: 2016-09-16
i have a expression but when i create other query, i get this error.
would you advise me?
Snap936.jpgSnap937.jpg
0
Comment
Question by:Hiroyuki Tamura
  • 5
  • 4
  • 3
  • +4
18 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41798594
Best guess without seeing the data types for tblDate is that the Date field is a date/time data type, but your query (view) is returning a string (this is the return data type of the format function) which causes the issue when you attempt to join the two.
1
 
LVL 19
ID: 41798599
1. Date is a reserved word and should not be used for a name
http://allenbrowne.com/AppIssueBadWord.html

2. Date is stored internally as a double-precision number (exponential format -- 1.23 x 10^^n) and is, therefore, not accurate for exact comparisons (including linking)

3. Dates can also have a time component so they are not whole numbers

4. please paste in your equation because the screenshot does not show it all

5. names for calculated fields CANNOT be the same as a name in any source (ie: Date -- already a bad name anyway, as mentioned above)

6. using the FORMAT function converts values to a string (text) -- instead, use the format PROPERTY (right-click in a column and set Format on the Property Sheet)
1
 
LVL 75
ID: 41798609
In a Join ... the Data Types must be EXACTLY the same .... check that.
2
 

Author Comment

by:Hiroyuki Tamura
ID: 41798632
Data Type looks same but expression might be causing the error.
Snap944.jpgSnap943.jpg
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41798642
Even though the two fields may look the same, because the Date column in your WorkHour query is a string (text) data type, it cannot be used when joining to a Date/Time data type.

If you are using the format to remove the time component of the date/time, replace it with DateValue([Start Time])
0
 
LVL 75
ID: 41798649
"Data Type looks same but expression might be causing the error."
You need to open table in Design view ....
0
 

Author Comment

by:Hiroyuki Tamura
ID: 41798650
i'm getting mismatch error from this expression;
Date: DateValue(Format([Start Time],"yyyy/mm/dd"))
0
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 50 total points
ID: 41798661
Replace:

Format([Start Time], "yyyy/mm/dd")

With:

DateValue([Start Time])
0
 

Author Comment

by:Hiroyuki Tamura
ID: 41798673
getting error
Snap945.jpg
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 50 total points
ID: 41798689
maybe [Start Time] is not filled out.  On the grid:

field --> [Start Time]
criteria --> Is Not Null

check the Total cell under Hour (which is another reserved word! Don't use Function names for your names. Use Hr instead) -- perhaps GroupBy
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
ID: 41798693
1. STOP!!!!! formatting the date.  The Format() function converts the datetime data type to a string.
2. We asked to look at the data type NOT the data.  Please open the tables in design view and click on the relevant fields so we can see the actual data type.  Based on the picture you posted, the two data types are almost certainly different.  the one that is yyyy/mm/dd is a string which is the result of your Format() expression.
3. Does the field contain time?  If it does, then you will probably not be able to join on it due to floating point differences as others have pointed out.
4. If the field value includes a time, then you need to use the DateValue() function as Shawn suggested.  DO NOT FORMAT THE DATE.  Use the expression he gave you.
4. DO NOT name the calculated field Date.  Date is the name of a function and should never be used as a  field name.
5.  If you have set the format property of the field in the table, REMOVE the setting.  NEVER format data at the table level.  All you are doing is obfuscating the actual value.  Format is a FINAL function.  It should only be done when you need to display a field for human consumption.  For queries and code, NEVER format anything unless you are working with disparate data types and you need to convert one data type to a different one so you can compare two unlike data type fields.
6. Hour is also a Function name.  DO NOT use it as a column name.
1
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 50 total points
ID: 41798701
**** Attention ***
Open both tables in Design, look at the 'Date' field ... and confirm (or not) that the Data Types are identical ... which should be DateTime
1
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 50 total points
ID: 41798704
in addition to Pat's comments (we are all just trying to help -- and quite passionate about Access) --  The error you are getting is most likely due to a problem in an underlying query that is a source for this one

In your screen shot, when the date is aligned on the left that means it is no longer a date/time data type ... it has become a string.  Mostly likely due to use of the Format function ... re-read what Pat wrote about this. A Date/Time is stored internally as a number and will line up on the right in Datasheet View (unless you change the column formatting).

 ... and please don't call things "Date" -- what kind of date is it? Qualify the name and then it (probably) won't be a reserved word anymore either.

Joe also mentioned data type ...
1
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 50 total points
ID: 41798724
95 % probable issue is different data types ... like DateTime >> Text
1
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 50 total points
ID: 41798733
your last screen shot only showed the grid -- not the top with what (tables/queries) went in.  Please post the SQL for ALL the queries you are using for all the steps and what they are called.  In Query Design, change the View to SQL View and copy and paste -- then we can help you better, thanks.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 50 total points
ID: 41798778
Some further explanation of how computers (and database systems) store dates, and display dates may ease some of your confusion.
Humans see dates as strings ( 14-Sep-2016 or 14/09/2016 or Wednesday, 14th September 2016)
But that does a binary computer no good.
So an abstraction is done.

Day Zero in MS Access is 30-Dec-1899.
Today is Day 42627 -- you can see this by putting this code in a Click event of a command button
MsgBox CDbl(Now)
The decimal part of that number is the time, expressed as a fraction of a day
0.333333... is 8 AM, 0.5 is 12 PM, 0.99999999999... will be the barest hair before midnight 11:59:59.9999...PM
It is these numbers that Access is using, when your data design is correct and you understand what you are calculating.

When you store Date and Time data in Access, you should be storing one Date datatype value for the date and time, and not one for the date, and one for the time.
You'll quickly see why if you put this in a Click Event of a button
MsgBox Format(CDate(0.49), "dd-mmm-yyyy hh:nn:ss AMPM")
You certainly don't want that 30-Dec-1899 showing to users!

So there are many functions associated with wrangling these numbers INTO the things we to see
DateValue(SomeValueStoredAsADate) shears of the time portion and gives just the date -- and that will display according to how you've either explicitly formatted a control or your system's defualt.
TimeValue(SomeValueStoredAsADate) does the same.

We can use the Format() function to wrangle a date datatype to display however we would like -- but this transforms it's output into a string.

We think you have made the mistake of storing these formatted strings in one table and date values in another.  When you go to JOIN those fields, you will get the error you see now.

The BEST way to fix this is to understand the Date datatype and fix your conceptual problems.  There can be other bandaids applied if you can't or won't do this -- but other problems will rear their heads later on if you don't.

Does this make sense to you, now?
1
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41799287
Data Type looks same but expression might be causing the error.

They don't look the same. One is left justified (default for text), the other is right justified (default for date).

If you wish to join on the datepart only of [Start Time] and this is Date but can be Null, use Fix:

    Date: Fix([Start Time])

If [Start Time] is Text, convert with CVDate which also accepts Null values:

    Date: Fix(CVDate([Start Time]))

/gustav
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 50 total points
ID: 41800219
And Int([SomeDate]) if [SomeDate] can be Null ... if only a Date is being stored :-)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now