Repeating fields

[Carolinec]

I've just come into a new organisation to discover a very poorly put together
database. Question: You can book a facility using this database but the date
field in just a text field which has a variety of different formats of dates
entered and sometimes has dates like this '16 - 18 September 2005'. In
FileMaker Pro I would just have created a repeating field to hold all the
different dates and therefore force the user to use a particular format. Any
ideas on how to deal with one booking on more than one date?

 

[Duane Hookom]

You could use start and end date fields or create a related table of
individual dates (records) related to the booking table.

Are you also asking about a method to clean up existing records?

 

[Vincent Johns]

You could use start and end date fields or create a related table of
individual dates (records) related to the booking table.

Are you also asking about a method to clean up existing records?

-- Duane Hookom MS Access MVP --

You could define an Update Query to store into your new [StartDate] or
[EndDate] (or both) field a converted version of the date you found in
that field, such as the DateValue() function. Many will convert OK.
The rest you'll need to fix by hand, but you do that just once, which
shouldn't be too difficult if you have only a few dozen that the
function can't handle.

If you have many hundreds of such ill-formed dates, you might export
them (along with a key value for the record) to a text file, use a word
processor to reformat them, then re-import them into your Access Table
using an Update Query.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.