10/14/03 vs 10/14/2003 Format

[pbrown2]

The tables have been formatted for Date/Time. However, some of the PC's read the date as 10/14/03 and others as 10/14/2003. This is causing errors with the queries and reports. The DB is located on a network drive so I do not see how some PC's are reading the date one way and some are reading it the other.
I have tried re-creating from a blank database the tables, however the same problem is occuring. Also looked through the tools/options but could not find any differences between the two PC's.
Does anyone have any ideas as to what is causing the date to be read differently? If nothing can be done, is there away to "force" the date to be one way or another (only thing is that the data for the date is imported in from a text file that is a system download which can not be changed)?

Thank you for any and all help,

PBrown

 

[KenReay]

Hi

This is a setting in Control Panel, local settings

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pbrown2]

Is there anyway to imput mask the data being downloaded as a type of "catch all"? Thereby, going around each PC's default date.

Thank you for any and all help,

PBrown

 

[docmeizie]

Hmmm I could of swore I posted a reply in here ... unless you posted this question somewhere else also.

If I take a peek in your Windows, to fix a problem, does that make me a "Peeping Tom"? Hmmmmmmmmmmv

 

[psemianonymous]

Dates should evaluate the same no matter how they *appear*. If you're storing them as text, don't. If you still have to store them as text, then when you're using them in queries, use a calculated value that does the following:

Expr1: CDate([YourDateFieldStoredAsText]

 

[pbrown2]

What is happening is that there is a crosstab query which looks up OverTime with each date becoming a column that holds the total OT/day. Then a Summary query Looks at each new column and performs a calculation of dividing each day by 100.
The problem is that when the dates are read in the cross tab, some PC's read and display it as /2003 and others as /03. Therefore in the query when an expresion refers to [XX/XX/03]/100 the PC's that dispaly it as /2003 can not find the field.


Thank you for any and all help,

PBrown

 

[psemianonymous]

In that case, put

CrosstabHeader: Format(CDate([myfield]), "mm/dd/yyyy&quot

to guarantee the formatting will be identical.

 

[Dalain]

As foolio12 mentioned above you should use a date type data field. This makes any calculations easier with less errors.

To help you understand how access uses dates this is how it is explaned in the Access Help File

Date expression
Any expression that can be interpreted as a date, including date literals, numbers that look like dates, strings that look like dates, and dates returned from functions. A date expression is limited to numbers or strings, in any combination, that can represent a date from January 1, 100 to December 31, 9999.

Dates are stored as part of a real number. Values to the left of the decimal represent the date; values to the right of the decimal represent the time. Negative numbers represent dates prior to December 30, 1899.

Access then uses this number to control how the date is displayed. This is why you can display so many different date formats The problem is when access converts date information from a date field to a text field. Instead of using the windows default system format, it defaults to a General Date format 4/3/93, 05:34:00 PM (mm/dd/yy, hh:nn AM/PM)

So if you are using a date in a SQL statement or the CDate function, you may get some weird results. For example if my PC's date setting is DD/MM/YY and Me.FPDateExp = 03/09/003, where 09 is the month of Sep.

When using RunSQL you must convert the date type date into text. In this example when access converts the Date value into text it is displayed as 09/03/03,

Example
DoCmd.RunSQL ("UPDATE FPSettings SET FPSettings.ExportDate = #" & Me.FPDateExp & "#;&quot

When converted back to a date value will be seen as 9 Mar 03. (General Date format mm/dd/yy)

To solve the problem, use the format function. Use the three m's (mmm) which is the month converted to letters as this will ensure the 03 is displayed as Mar, which when converted to the number 03, not 09. Also the four y's (yyyy)since 2003 must be the Year value, (Year 03 ?? not so sure).

DoCmd.RunSQL ("UPDATE FPSettings SET FPSettings.ExportDate = #" & Format(Me.FPDateExp, "dd mmm yyyy hh:nn&quot & "#;&quot

Here is a simple function that I use to solve the problem.
Example

DoCmd.RunSQL ("UPDATE FPSettings SET FPSettings.ExportDate = #" &DtoC(Me.FPDateExp) & "#;&quot

Place this code it in a global module.

Function DtoC(InpDate As Date)
'This function converts a date type data to a String type data
Dim strmonth As String
Dim NewDate As String

Select Case Month(InpDate)
Case 1
strmonth = "Jan"
Case 2
strmonth = "Feb"
Case 3
strmonth = "Mar"
Case 4
strmonth = "Apr"
Case 5
strmonth = "May"
Case 6
strmonth = "Jun"
Case 7
strmonth = "Jul"
Case 8
strmonth = "Aug"
Case 9
strmonth = "Sep"
Case 10
strmonth = "Oct"
Case 11
strmonth = "Nov"
Case 12
strmonth = "Dec"
End Select
DtoC = Day(InpDate) & "-" & strmonth & "-" & Year(InpDate)

End Function



Pierre

 

[ccma]

I know that this post is old but I am having the same problem. The data gets inputed into a table and the data type for the Date field is set to Date. When I run the data on my PC the dates after being "crosstabed" remain as imputed 06/01/04 but on 2 other PCs they show 06/01/2004. When I force the month format in the table to be mm/dd/yy - when the data is manipulated in the cross tab queries it goes back to the mm/dd/yyyy format. The database is on the server and there are only three total users... myself, a department head, and my supervisor. I have Windows 98 and they have XP as their operating systems but have Access version 97 on their PCs. I'm using the suggested formula (foolio12)Format(CDate([myfield]),"mm/dd/yyyy")
Just wondering why this would happen... Thanks

 

[Hillary]

I had this problem too. I'm not sure if it's XP/98 or the Office version but one defaults to yy while the other defaults to yyyy. I put m/d/yy in the format of the date field in the table to fix our problem.

Hillary

 

[ccma]

(formerly ccolon) I failed to mention that in my table I did have the data type set to date and the format set to short date (if that has anything to do with it).
This makes me nervous, due to the fact that we are slowly in the process of upgrading everyone to XP. I am always creating new databases and the majority of them have different types of reports which includes cross tab queries. Is this just a random thing that happens or will this happen every time?

 

[Dalain]

Take a look at my previous post. If any one is using a format other than mm/dd/yy, access can miss read your date. To solve the problem ensure the system date format is in mm/dd/yy. and whenever you display a date in a report or on the screen, then modify you format to dd/mmm/yy. In an SQL statement try my example.

Dalain

 

[Dalain]

Just a clarification.

No matter what format you have selected for your System date, at one point or another ACCESS will READ the Date in a mm/dd/yy format. (Microsoft is a US product. The US default date format is mm/dd/yy)

So it is best to leave it in mm/dd/yy format for your Tables and only change it when you need it displayed, like in your Forms & Reports.

Dalain