If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Custom date format not working in ACC2003
I am working in a country where dates are printed d/m/y. I find that users
often don't have their Regional date settings set correctly so in Access projects we have always hardcoded the date format in reports as "dd/mm/yyyy" or "dd/mm/yy". I've done this in projects for years using mostly Access 97 without a problem. Now we've "upgraded" [sic] to Access 2003 and it doesn't work. No matter what I do, the date fields in reports always format with the default regional date. e.g. for a Text box in a report Control Source: TranDate Format: dd/mm/yy The format drop-down box is showing only the default date and time formats, so I guess the system is recognising that the underlying data type is a date. But it won't act if we enter a custom date format. Likewise "ddd mmm" and other such variants don't work either. My work-around solution has been to change the Control Source to =Format([Trandate],"dd/mm/yy") but that's not satisfactory and will give an error if the date is a null value. Plus it's a real pain to go through all the reports in all our projects and fix up what should be proper behaviour. Any suggestions to fix this? |
#2
|
|||
|
|||
Custom date format not working in ACC2003
Hi David.
I've worked with Access for 13 years in a dmy country, but do not experience the problem you describe. IME, if a text box is bound directly to a date/time field in a table, you can set the Format property of the control on the form/report or of the field in the table, and it will display as instructed. (The only exception I know if is the nonsense option for Display 4-digit Years which overrides everything.) OTOH, Jet 4 (Access 2000 and later) is *much* worse than JET 3.5 at recognising the data type of a calculated field. Even if the field is generated using something like DateSerial() that obviously outputs dates, you still have to explicitly typecast with CVDate() before you can trust JET 4 to recognise it correctly. The clue that it has it wrong is that the datasheet view of the query outut left-aligns the field like text, instead of right-aligning it like a date. And certainly, once JET fails to understand the type, setting the Format of the control won't rescue it. The techniques I use to ensure Access interprets my dates correctly are described he International Date Formats in Access at: http://allenbrowne.com/ser-36.html Despite the fact that the Access documentation states that CVDate() exists just for backward compatibility (with Access 2, which did not have a Date type in VBA), it's a life-saver: the function accepts and returns Nulls, yet JET understands the type correctly. BTW, on a philosophical level, I always respect the user's date settings. A savvy user is rightly annoyed if I force my view of the universe on him, and an ignorant user won't learn if I try to fix his issues for him. If you are interested in locating all text boxes that are formatted as dd/mm/yy and clearing the format programmatically, the function below illustrates how to do it. (You would also need to clear the Format of the Field in the TableDef.) Public Function FixDates() Dim accObj As AccessObject Dim strDoc As String For Each accObj In CurrentProject.AllForms strDoc = accObj.Name DoCmd.OpenForm strDoc, acDesign, windowmode:=acHidden If FixDateSub(Forms(strDoc)) 0 Then Forms(strDoc).Visible = True Else DoCmd.Close acForm, strDoc End If Next For Each accObj In CurrentProject.AllReports strDoc = accObj.Name DoCmd.OpenReport strDoc, acDesign, windowmode:=acHidden If FixDateSub(Reports(strDoc)) 0 Then Reports(strDoc).Visible = True Else DoCmd.Close acReport, strDoc, acSaveYes End If Next End Function Private Function FixDateSub(obj As Object) As Long Dim ctl As Control Dim lngKt As Long For Each ctl In obj.Controls If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then If ctl.Format = "dd/mm/yy" Then Debug.Print obj.Name & "." & ctl.Name ctl.Format = vbNullString lngKt = lngKt + 1 End If End If Next FixDateSub = lngKt End Function -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "David A" wrote in message ... I am working in a country where dates are printed d/m/y. I find that users often don't have their Regional date settings set correctly so in Access projects we have always hardcoded the date format in reports as "dd/mm/yyyy" or "dd/mm/yy". I've done this in projects for years using mostly Access 97 without a problem. Now we've "upgraded" [sic] to Access 2003 and it doesn't work. No matter what I do, the date fields in reports always format with the default regional date. e.g. for a Text box in a report Control Source: TranDate Format: dd/mm/yy The format drop-down box is showing only the default date and time formats, so I guess the system is recognising that the underlying data type is a date. But it won't act if we enter a custom date format. Likewise "ddd mmm" and other such variants don't work either. My work-around solution has been to change the Control Source to =Format([Trandate],"dd/mm/yy") but that's not satisfactory and will give an error if the date is a null value. Plus it's a real pain to go through all the reports in all our projects and fix up what should be proper behaviour. Any suggestions to fix this? |
#3
|
|||
|
|||
Custom date format not working in ACC2003
Allen,
That's it! "Display 4-digit Years". Seeing that option in General settings I assumed it was a useful one to check. Obviously not. Unchecking that option fixed the problem. So what does that option do? David "Allen Browne" wrote in message ... Hi David. I've worked with Access for 13 years in a dmy country, but do not experience the problem you describe. IME, if a text box is bound directly to a date/time field in a table, you can set the Format property of the control on the form/report or of the field in the table, and it will display as instructed. (The only exception I know if is the nonsense option for Display 4-digit Years which overrides everything.) OTOH, Jet 4 (Access 2000 and later) is *much* worse than JET 3.5 at recognising the data type of a calculated field. Even if the field is generated using something like DateSerial() that obviously outputs dates, you still have to explicitly typecast with CVDate() before you can trust JET 4 to recognise it correctly. The clue that it has it wrong is that the datasheet view of the query outut left-aligns the field like text, instead of right-aligning it like a date. And certainly, once JET fails to understand the type, setting the Format of the control won't rescue it. The techniques I use to ensure Access interprets my dates correctly are described he International Date Formats in Access at: http://allenbrowne.com/ser-36.html Despite the fact that the Access documentation states that CVDate() exists just for backward compatibility (with Access 2, which did not have a Date type in VBA), it's a life-saver: the function accepts and returns Nulls, yet JET understands the type correctly. BTW, on a philosophical level, I always respect the user's date settings. A savvy user is rightly annoyed if I force my view of the universe on him, and an ignorant user won't learn if I try to fix his issues for him. If you are interested in locating all text boxes that are formatted as dd/mm/yy and clearing the format programmatically, the function below illustrates how to do it. (You would also need to clear the Format of the Field in the TableDef.) Public Function FixDates() Dim accObj As AccessObject Dim strDoc As String For Each accObj In CurrentProject.AllForms strDoc = accObj.Name DoCmd.OpenForm strDoc, acDesign, windowmode:=acHidden If FixDateSub(Forms(strDoc)) 0 Then Forms(strDoc).Visible = True Else DoCmd.Close acForm, strDoc End If Next For Each accObj In CurrentProject.AllReports strDoc = accObj.Name DoCmd.OpenReport strDoc, acDesign, windowmode:=acHidden If FixDateSub(Reports(strDoc)) 0 Then Reports(strDoc).Visible = True Else DoCmd.Close acReport, strDoc, acSaveYes End If Next End Function Private Function FixDateSub(obj As Object) As Long Dim ctl As Control Dim lngKt As Long For Each ctl In obj.Controls If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then If ctl.Format = "dd/mm/yy" Then Debug.Print obj.Name & "." & ctl.Name ctl.Format = vbNullString lngKt = lngKt + 1 End If End If Next FixDateSub = lngKt End Function -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "David A" wrote in message ... I am working in a country where dates are printed d/m/y. I find that users often don't have their Regional date settings set correctly so in Access projects we have always hardcoded the date format in reports as "dd/mm/yyyy" or "dd/mm/yy". I've done this in projects for years using mostly Access 97 without a problem. Now we've "upgraded" [sic] to Access 2003 and it doesn't work. No matter what I do, the date fields in reports always format with the default regional date. e.g. for a Text box in a report Control Source: TranDate Format: dd/mm/yy The format drop-down box is showing only the default date and time formats, so I guess the system is recognising that the underlying data type is a date. But it won't act if we enter a custom date format. Likewise "ddd mmm" and other such variants don't work either. My work-around solution has been to change the Control Source to =Format([Trandate],"dd/mm/yy") but that's not satisfactory and will give an error if the date is a null value. Plus it's a real pain to go through all the reports in all our projects and fix up what should be proper behaviour. Any suggestions to fix this? |
#4
|
|||
|
|||
Custom date format not working in ACC2003
Microsoft introduced that option in the goldrush days of Y2k.
It forces the year to display as 4 digits in dates, and so messes up (overrides) specific date formats. The results are really dumb. For example, if you do use a date/time field for credit card expiry, and you want that to print as mmyy, you get 6 digits, and if you built your text box wide enough for 4 digits, the last 2 digits are missing in the display. Utter junk. Fortunately you can turn it off. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "David A" wrote in message ... Allen, That's it! "Display 4-digit Years". Seeing that option in General settings I assumed it was a useful one to check. Obviously not. Unchecking that option fixed the problem. So what does that option do? David "Allen Browne" wrote in message ... Hi David. I've worked with Access for 13 years in a dmy country, but do not experience the problem you describe. IME, if a text box is bound directly to a date/time field in a table, you can set the Format property of the control on the form/report or of the field in the table, and it will display as instructed. (The only exception I know if is the nonsense option for Display 4-digit Years which overrides everything.) OTOH, Jet 4 (Access 2000 and later) is *much* worse than JET 3.5 at recognising the data type of a calculated field. Even if the field is generated using something like DateSerial() that obviously outputs dates, you still have to explicitly typecast with CVDate() before you can trust JET 4 to recognise it correctly. The clue that it has it wrong is that the datasheet view of the query outut left-aligns the field like text, instead of right-aligning it like a date. And certainly, once JET fails to understand the type, setting the Format of the control won't rescue it. The techniques I use to ensure Access interprets my dates correctly are described he International Date Formats in Access at: http://allenbrowne.com/ser-36.html Despite the fact that the Access documentation states that CVDate() exists just for backward compatibility (with Access 2, which did not have a Date type in VBA), it's a life-saver: the function accepts and returns Nulls, yet JET understands the type correctly. BTW, on a philosophical level, I always respect the user's date settings. A savvy user is rightly annoyed if I force my view of the universe on him, and an ignorant user won't learn if I try to fix his issues for him. If you are interested in locating all text boxes that are formatted as dd/mm/yy and clearing the format programmatically, the function below illustrates how to do it. (You would also need to clear the Format of the Field in the TableDef.) Public Function FixDates() Dim accObj As AccessObject Dim strDoc As String For Each accObj In CurrentProject.AllForms strDoc = accObj.Name DoCmd.OpenForm strDoc, acDesign, windowmode:=acHidden If FixDateSub(Forms(strDoc)) 0 Then Forms(strDoc).Visible = True Else DoCmd.Close acForm, strDoc End If Next For Each accObj In CurrentProject.AllReports strDoc = accObj.Name DoCmd.OpenReport strDoc, acDesign, windowmode:=acHidden If FixDateSub(Reports(strDoc)) 0 Then Reports(strDoc).Visible = True Else DoCmd.Close acReport, strDoc, acSaveYes End If Next End Function Private Function FixDateSub(obj As Object) As Long Dim ctl As Control Dim lngKt As Long For Each ctl In obj.Controls If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then If ctl.Format = "dd/mm/yy" Then Debug.Print obj.Name & "." & ctl.Name ctl.Format = vbNullString lngKt = lngKt + 1 End If End If Next FixDateSub = lngKt End Function "David A" wrote in message ... I am working in a country where dates are printed d/m/y. I find that users often don't have their Regional date settings set correctly so in Access projects we have always hardcoded the date format in reports as "dd/mm/yyyy" or "dd/mm/yy". I've done this in projects for years using mostly Access 97 without a problem. Now we've "upgraded" [sic] to Access 2003 and it doesn't work. No matter what I do, the date fields in reports always format with the default regional date. e.g. for a Text box in a report Control Source: TranDate Format: dd/mm/yy The format drop-down box is showing only the default date and time formats, so I guess the system is recognising that the underlying data type is a date. But it won't act if we enter a custom date format. Likewise "ddd mmm" and other such variants don't work either. My work-around solution has been to change the Control Source to =Format([Trandate],"dd/mm/yy") but that's not satisfactory and will give an error if the date is a null value. Plus it's a real pain to go through all the reports in all our projects and fix up what should be proper behaviour. Any suggestions to fix this? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
the date format is not working ,sort by date doesn't work. | Rosa Campos | General Discussion | 1 | September 12th, 2005 10:52 PM |
Date Format in Custom Header | Radsri- | General Discussion | 2 | September 7th, 2005 03:57 PM |
Query for 'confirmation' | rogge | Running & Setting Up Queries | 8 | April 19th, 2005 03:26 PM |
How can I force capitalization in a custom date format in Access? | spkelly | General Discussion | 4 | December 11th, 2004 12:57 PM |
Importing and defining a different date format | Jim T | General Discussion | 2 | November 2nd, 2004 08:19 PM |