Should be simple update queries

[pablotx]

Thanks in advance. I feel I should know how to fix the following 2 issues, but can't quite get it.

1) I have a table with Dates in a text field. (mmddyyyy). I want to change the format so that it will display as (mm/dd/yyyy). So I have tried to change the field property to a Date field, but no luck.

2) On a report I have fields for City and State. I am only using this certain database to do a form letter for a bunch of folks. All I want to do is add a comma to the end of the city field for all of the records in the table.

I am thinking an update table could clear up at least the first issue.

Thanks again.

 

[Mike375]

A date field won't accept something like 01082009.

Make a calculated field in a query and assuming the name of your text field that has mmddyyyy is [abc]

Left([abc],2) & "" & "/" & "" & Mid([abc],3,2) & "" & "/" & "" & Right([abc],4)

That will turn 01082009 into 01/08/2008

You can then copy/paste the resulting column into the text field and then change it to a date field.

 

[pablotx]

Thanks so much. Now what about the comma issue?

 

[Mike375]

You can do that a similar way.

Say the field with City is [city] then either created a calculated field in a query as follows

[city] & "" & ","

Or on a Report you might use an unbound textbox and for its data source

=[city] & "" & ","

The space or lack of spaces between the "" determines the gap

As a side note you are better off not to upate the city field with the results of [city] & "" & "," as that would mean future data entry would need the , added to the entry.