Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

Microsoft Access Date format

i have a expression but when i create other query, i get this error.
would you advise me?
Snap936.jpgSnap937.jpg
0
Hiroyuki Tamura
Asked:
Hiroyuki Tamura
  • 5
  • 4
  • 3
  • +4
10 Solutions
 
Shaun KlineLead Software EngineerCommented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
In a Join ... the Data Types must be EXACTLY the same .... check that.
2
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Hiroyuki TamuraField EngineerAuthor Commented:
Data Type looks same but expression might be causing the error.
Snap944.jpgSnap943.jpg
0
 
Shaun KlineLead Software EngineerCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Data Type looks same but expression might be causing the error."
You need to open table in Design view ....
0
 
Hiroyuki TamuraField EngineerAuthor Commented:
i'm getting mismatch error from this expression;
Date: DateValue(Format([Start Time],"yyyy/mm/dd"))
0
 
Shaun KlineLead Software EngineerCommented:
Replace:

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

With:

DateValue([Start Time])
0
 
Hiroyuki TamuraField EngineerAuthor Commented:
getting error
Snap945.jpg
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
PatHartmanCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
**** 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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
95 % probable issue is different data types ... like DateTime >> Text
1
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
Nick67Commented:
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
 
Gustav BrockCIOCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And Int([SomeDate]) if [SomeDate] can be Null ... if only a Date is being stored :-)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 4
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now