Formating.

[simon mills]

Hello everyone.
I have a multi user database that is used across 2 sites. In one of the sites the regional settings for 'short date format' is set to dd/mm/yy - the other site its set to dd/mm/yyyy. All the data that its the tables are set to dd/mm/yy. When the second site try to use the reports and queries they permanently error due to the fact that the date look up box is setting its self to dd/mm/yyyy as per its short date format. The IT manager there refuses to change this as its - and I quote 'its not y2k compliant !!!! - anyway. I cant change the data in the table to yyyy as its a text feed and Im not in control of that.

I planned on using a hidden text box to format the date to dd/mm/yy and hence bypass the regional settings, but this queries arent recognising the new foramt and are returning null and blank tables. Im now completely lost and stuck. Can anyone help ??

 

[llkhoutx]

Your IT manager is right.

What you say your are doing is ok, you don't give enough but to indicate what the problem is.

Remember, a date is a date is a date. In designing an mdb, you should only worry about the format of a date when it's displayed, not when it's stored in a table. If a date has been stored in a table as dd/mm/yy, it's a faulty design; it's not a true date. Dates aren't stored that way. However, they can be displayed that way.

 

[The_Doc_Man]

It depends on what you asked your IT manager to do. If you asked him/her to change the system-wide default date format, you asked for the wrong thing. You might look up the CDate function as a way convert dates from text to real dates.

You only THINK you can't change the incoming dates. But if you do the import you can change the dates on the fly and adjust their displayed format locally. That shouldn't tick off your IT manager.

 

[simon mills]

I know he's right, but as it wont effect us again till 2100 Im not overily concerned i'll be in a box or 115, I just want my database to work.

Sorry, its too early for humour, on a serious note,
I have tried the Cdate function and then to format the resulting date into dd/mm/yy, but still a blank table. I think Im going to have to do an update query on the 5 table to change the /05 to /2005, just dont like ammending/changing system data thats all.

Thanx for the input tho.

 

[neileg]

Aren't you doing this the wrong way round? You should be ensuring the date stored in your table is a date/time datatype, not text. Dates in Access are stored as decimal numbers, not in a date format. If your table truly holds the date with a two digit year, it must be held as text. If you can't easily change the data in the table, you can create a select query that calculates the true date based on this text value, and use this query instead of the table. This way, since the local date format applies to everything, there should be no conflict.

 

[Pat Hartman]

I know he's right, but as it wont effect us again till 2100 Im not overily concerned

Not true. It affects us now since in order to provide maximum flexibility, Access allows the entry of two-digit dates and makes an assumption as to what the century should be. For example years 0-29 are assumed to be 21st century. Years 30-99 are assumed to be 20 century. That means that if you need to enter a DOB for a senior citizen, you are most likely going to need to enter all 4 digits in order for the entered date to be correct. If you just entered 2/3/27, Access would assume Feb. 3, 2027 NOT 1927. As we get closer to 2030, Office will need to change it's assumptions.

The Y2K "problem" also affects applications that have historical data that goes back to before 2000 since '00 would sort before '99 which would be incorrect.

In any event, the place to solve the problem is at the feed. If you use TransferText to import the data, you can create an import spec and that will allow you to specify that you want a text date in the format of dd/mm/yy to a REAL date. Assuming that none of the "yy" data will be assigned to the wrong century, you won't have any problems.

 

[simon mills]

I was completely un aware of the assumptions that access makes.

I have used a hidden text box and then =format(cdate[],"dd/mm/yy") - this appears to be working now. The cdate alone didnt, but after the format it started too. The test will be on going to ensure its working.

Thanx again for the input ..... and all future designs/builds will use yyyy. Im hoping that this Db is a short term answers as we look to build a web page to do what access is at the moment as the number of users is going to eventually grow to over 250, from the 10 - 20 we currently have.

 

[Pat Hartman]

and all future designs/builds will use yyyy

I think you missed the point. Fields that are defined as the Date/Time data type are NOT stored as text. They are stored as serial numbers. The serial number is a double precission number with the integer portion representing the number of days since Dec 30, 1899 and the decimal portion representing time of day. How a date looks to humans is irrelevant. So formatting the year as two positions or four makes absolutely NO difference to what is stored. It is simply that when you use only two digits, you run the risk of entering the wrong date due to the assumption you are forcing Access to make.

The other thing you should know is that formatting a date with the Format() function turns it into a string. That means that the formatted date will act like a string rather than a date. It will sort like a string which is the thing that usually throws people.

If you want to format a date on a form, use the format property of the control. Do NOT format it in the form's RecordSource query.