Custom formating of dates

[moriak]

Hi

I am looking for a way to format a date in a textfield troughtout my application. I created a module containing a Function that receive a Date as parameter and returns a string. I put : =formatCustomDate([dbColumnName]) in the source field. Everything works fine when the date is not null. However if the date in the DB is null, empty, etc. The function is not even executed and I get a #Error in the field.

Why is the function not executed? Is there another way to do that? The reason I want to do that is because I want to avoid using the formating embeded in Access. I need the Function to return mm/dd/yyyy (00/00/0000).

Thanks

Jonathan

Here is the code I used to do that:

'*********************************************************
' Function formatCustomDate(dbDate as String) As String
'*********************************************************

Public Function formatCustomDate(dbDate As String) As String

'MsgBox dbDate

On Error GoTo err_Wrong_Date_Type

Dim formatedDate As String

If IsNull(dbDate) Or IsEmpty(dbDate) Or dbDate = CStr("") Then
formatedDate = Empty
Else
formatedDate = Format(dbDate, "mm\/dd\/yyyy")
End If

'MsgBox formatedDate

'MsgBox formatedDate
formatCustomDate = formatedDate

err_Wrong_Date_Type:
If Err.Number <> 0 Then
formatedDate = Empty
formatCustomDate = formatedDate
'MsgBox formatedDate
'MsgBox Err.Number & ":" & Err.Description
Else
formatedDate = Format(dbDate, "mm\/dd\/yyyy")
'MsgBox formatedDate
formatCustomDate = formatedDate
End If

End Function

 

[ScottGem]

Try using NZ:

=Nz(formatCustomDate([dbColumnName]),"")

But why not just use:

=Format([datefield],"mm/dd/yyyy")

instead of going through a custom function?

 

[moriak]

Hi

Thanks for the reply.

I tried =Format([datefield],"mm/dd/yyyy") before.

But if I type that in the source field, access seems to try to filter the special caracters. So it automaticly transform it to something like:

=Format([datefield],""mm"/dd"/yyyy"")
Maybe it must be specified differently?

Nz did not work, It still give me an #Error in the field.

Thanks

 

[boblarson]

I think it's the way you are using it. You keep saying that you put your function into the "source" field, which does not make sense. Which "source" field are you talking about. Where EXACTLY are you putting this?

Is this the value of a text box on a bound form? Is it the input mask you are trying to set for a table's column? Are you talking about the CONTROL SOURCE for a control? If you are talking about the control source, then you are doing it incorrectly. The control source is either a bound field or something that you display in the field. Now, if you are trying to format a field that is bound to that textbox, then use the FORMAT property of the item and NOT the control source to try to format the item.

 

[ScottGem]

try typing mm/dd/yyyy in the Format property

 

[moriak]

Sorry for the delay, I don't usualy work on the weekends

I think it's the way you are using it. You keep saying that you put your function into the "source" field, which does not make sense. Which "source" field are you talking about. Where EXACTLY are you putting this?

It is a Text Field, bound to a column, in a form. Yes I am talking about the control source.

The control source is either a bound field or something that you display in the field. Now, if you are trying to format a field that is bound to that textbox, then use the FORMAT property of the item and NOT the control source to try to format the item.

try typing mm/dd/yyyy in the Format property

I want to avoid the Format property, since it will use Regional Settings from windows to format the date.

My app has to run on 10 computers with different regional settings, that I cannot change. So I created unbound text fields. I validate the dates when the save button is pressed with using my functions. If all dates are valid (in unbound fields), I parse the 00/00/0000 format myself, create a valid date object and copy each date in a hidden bound field. And then I docmd.save.

No problem with that.

It is only when I try to display that date in read-only fields, reports, read-only subforms that I have an hardtime formating Null dates.

Does the context help?

Thanks again for the replies

 

[boblarson]

Sorry, but it CANNOT be used on a Control Source. You MUST use the format property and if you use the one that ScottGem showed you, it will NOT be based on regional settings. It will format as he has shown. If you use "ShortDate" or something like that, then THAT one IS based on Regional Settings, but not "mm/dd/yyyy" - THAT one will format EXACTLY as shown.

 

[ScottGem]

Two points. As Bob said, the Format Property is NOT restricted to Regional Settings. Using one of the preset formats is, but if you type in the formatting code you want it will override the Regional settings.

Second, Access stores dates as a serial number counting the days from 12/30/1899. How the date is displayed is a matter of formatting, but using a Date/Time format means the date is stored as a long Integer.

 

[moriak]

Ok, I got the point, no functions in the Source property.

I tried to put the format mask mm/dd/yyyy which bring us to the reason why I used a function instead of the Format property.

(in a field bound to a date column)
When I put mm/dd/yyyy and then leave the field / press enter. Access 2000 automaticly changes the format I entered to mm"/dd/yyyy/".

Then I checked in Access Help and on MSDN. (same as access help)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acproFormatDate_HV05187261.asp

They state that if I want to use a seperator in my date Format, I should use ".

I tried with mm"/"dd"/"yyyy. When I hit enter, same thing, Access changes it to mm"/dd/yyyy".

bummer

 

[ScottGem]

How does the field display with the way Access changed it?

 

[Bat17]

It sounds like you are putting it into the Mask not the Format field of the control.

Peter

 

[moriak]

Yes I was putting the mm/dd/yyyy in the Format field.

I found the problem: I use Access in french therefor I need to use the format in french:

mm/jj/aaaa instead of mm/dd/yyyy

m= mois (month)
j= jours (day)
a= année (year)

It is working now. Sorry about that.
Thanks a lot for the support and your patience