How do you display as a date

[david.paton]

I have made a table in which I had a field for a date of birth. When I created the table, I made the data type to be a number but used the input mask on the field as 99/99/9999. The reason for the option of the DOB is that we might not have the DOB on file for different people.

I have a search form in which there is a text box to type search criteria in and a list box that displays and narrows the search results as you type. The problem I am having is that their DOB is one of the displayed fields in the list box for each of the records but I think because it has been set as a number field, the dates just appear as an integer in the list box. So instead of having the date 26/05/1943, the date appears as 26051943. I could just change the data type to be a date but that would delete all the dates I have entered in there already and I really do not want to have to enter all of them in again.

Does anyone know if it would be possible, maybe with some coding, to get the list box to display the date field as part of the search results in the format 26/05/1943 instead of 26051943?

 

[sneuberg]

You could put a function like the following in a standard module

Public Function FormatDate(DateNumber As Long) As String

If Len(CStr(DateNumber)) = 10 Then
    FormatDate = Left(CStr(DateNumber), 2) & "/" & Mid(CStr(DateNumber), 3, 2) & "/" & Right(CStr(DateNumber), 4)
Else 'length would be 9 for datenumbers like 6051943 or 6/05/1943
    FormatDate = Left(CStr(DateNumber), 1) & "/" & Mid(CStr(DateNumber), 2, 2) & "/" & Right(CStr(DateNumber), 4)
End If

End Function

Then in the row source query of the listbox box you would used it like

DisplayDate: FormatDate([DateField])

where [DateField] would be this numeric field you are currently using. There are probably simpler ways of doing this using the format function so you might want to wait for input from other forum members before going with this one.

 

[david.paton]

You could put a function like the following in a standard module

Sorry if I sound a bit ignorant, but how do I put in a module?

 

[Galaxiom]

I have made a table in which I had a field for a date of birth. When I created the table, I made the data type to be a number but used the input mask on the field as 99/99/9999.

You went completely wrong when you made this decision. Dates should be stored as Dates. Aside from the other problems you encountered, that Input mask would allow a wide variety of invalid dates to be entered.

The reason for the option of the DOB is that we might not have the DOB on file for different people.

Why not have Null where there is no DOB?

 

[Galaxiom]

I could just change the data type to be a date but that would delete all the dates I have entered in there already and I really do not want to have to enter all of them in again.

Add a date field to the table. Update it with dates converted from the crazy field. Delete the crazy field and rename the new one with the original name.

 

[sneuberg]

Don't worry about the module for now the simpler way to do this is just to use the format function. In the row source query of the listbox you can add

DisplayDate:  Format([DateField], "00/00/0000")

But I agree with Galaxiom that you would be better off fixing this problem now. Which you could do with an update statement like:

UPDATE TableName SET TableName.NewDateField = CDate(Format(Format([DateField],"00\/00\/0000"),"mm/dd/yyyy"));

Where TableName is the name of the table with this number date, DateField is the name of the number date field and NewDateField is the new field that you will add that will have the date type. After you are satisfied the update was done correctly you can delete DateField and rename NewDateField to DateField.

Note if you currently represent no date with 0 then you will probably want to update these to null.

 

[david.paton]

But I agree with Galaxiom that you would be better off fixing this problem now. Which you could do with an update statement like:

UPDATE TableName SET TableName.NewDateFIeld = CDate(Format(Format([DateField],"00\/00\/0000"),"mm/dd/yyyy"));

What do I do with this code, how should I run it? Where do I write it?

 

[Galaxiom]

What do I do with this code, how should I run it? Where do I write it?

It is SQL of a query. You can rebuild it yourself as an Update query in the Query Designer or paste it into the SQL view and change the names to match your tables and fields

However I think the CDate should be applied to your original text date before formatting it to #mm/dd/yyyy#.

 

[sneuberg]

However I think the CDate should be applied to your original text date before formatting it to #mm/dd/yyyy#.

If the query I posted isn't right would you please change the query I posted to be the way it should be. Note that the existing date field is an integer type according to the OP.

 

[david.paton]

I have decided to just make a new field and copy and paste the dates across.

 

[Galaxiom]

UPDATE TableName SET TableName.NewDateField = Format(CDate(Format([DateField],"00\/00\/0000")),"\#mm\/dd\/yyyy\#")

 

[Galaxiom]

I have decided to just make a new field and copy and paste the dates across.

I doubt that would work. Besides it would be very laborious.

 

[sneuberg]

UPDATE TableName SET TableName.NewDateField = Format(CDate(Format([DateField],"00\/00\/0000")),"\#mm\/dd\/yyyy\#")

That doesn't work if NewDateField is a date field. This is what I get.

 

[sneuberg]

I've attached the test database.

 

[Galaxiom]

That doesn't work if NewDateField is a date field.

I expect two of your original values are invalid as dates.

 

[sneuberg]

I expect two of your original values are invalid as dates.

They are 26051943 and 18041949. You can test this in the database I attached. I think the problem is that in the expression

Format(CDate(Format([DateField],"00\/00\/0000")),"\#mm\/dd\/yyyy\#")

the last format converts the result to text. If I change the NewDateField to a text field the update query runs without errors and insert dates delimited by pound signs. Doesn't the outermost function have to be CDate if the result is going into a date field?

Edit: I think this expression would be what you would want if it were being concatenated into a SQL statement. Maybe in a case like.

CurrentDb.Execute "UPDATE TableName SET TableName.NewDateField = " & Format(CDate(Format([OldDateField],"00\/00\/0000")),"\#mm\/dd\/yyyy\#")  & ";"

 

[Galaxiom]

They are 26051943 and 18041949. You can test this in the database I attached. I think the problem is that in the expression

Format(CDate(Format([DateField],"00\/00\/0000")),"\#mm\/dd\/yyyy\#")

the last format converts the result to text.

Pure SQL commands are wholly text. The engine only know that text is a date from the # delimiters.

I think this expression would be what you would want if it were being concatenated into a SQL statement. Maybe in a case like.

CurrentDb.Execute "UPDATE TableName SET TableName.NewDateField = " & Format(CDate(Format([OldDateField],"00\/00\/0000")),"\#mm\/dd\/yyyy\#")  & ";"

Yes. I created the expression then unthinkingly pasted it into the right side of the original query instead of concatenating as you have shown.

The first deepest Format converts the integer into a date string using the Regional date format. The CDate converts it to a data datatype then the outer Format converts it to the delimited date string.

Some elements could probably be skipped but this would rely on Access making implicit datatype conversions. Generally safer to be explicit.

 

[david.paton]

I got the date working by creating a new field with the time/date data type but now I have another display issue. The phone numbers are also displayed in search results but they appear as 0294036034 but I want them to appear as (02) 8604 8545. I have tried changing the input mask but that does nothing. I am not sure what to do here.

 

[Galaxiom]

The phone numbers are also displayed in search results but they appear as 0294036034 but I want them to appear as (02) 8604 8545.

My opinion on phone numbers goes against the received wisdom (dogma) of "storing numbers that are not calculated as text".

Store them as Long Integer and use this format string:

(00) 0000 0000

This works for all Australian phone numbers but will format mobile numbers with the parentheses too, which is unconventional, though the mobile area code actually is 4. (The preceding 0 in all numbers is the trunk access code and not really part of the number which becomes obvious when dialling from overseas.)

My full preference is to use separate numeric fields for the International Code, Area Code and Phone Number. As far as I can tell this works on any phone number system in the world.

 

[david.paton]

I have encountered another problem. I want the search box that allows you to search for info about the widows to be able to also allow you to type in the legatees name in the search box and to have the widows that are assigned to that legatee appear in the search results.


I have the legatees info stored in another table and use a joining table as 1 legatee can have many widows, but each widow can be assigned to any of the legatees.


I have been trying a lot of suggestions I have found on the internet but they have not been working. I would upload it but if I try and delete all the sensitive information, all the related items that use the deleted field change so I am not sure how to delete the data and still have everything remain the same, or even if you can do that.

Thanks