Due to the fact that the database I have set up is used by both remote and office workers, I use forms and import excel spreadsheets to keep the data updated. If I create a table by importing a spreadsheet, and then convert one of those fields to a lookup field, I find that it deletes the date that was already in that field. How do I prevent this from happening?

Sinead,

You could create the lookup at the form level, rather than at the table level. That way the underlying table would remain the same as imported and the form could modify the data where required.

Regards

Richard

At the risk of sounding obtuse - do you mean typing in the data from a list of options at form level, or referencing a previously created lookup table at form level?

Hi

Both your options above sound like they would work.
Perhaps it would help to know why you are creating a lookup for a date field? What are you looking up?

Thanks

Richard

My apologies, I've reread my initial query and I was referring to deleting the data not the date.

ok , thanks.

So you are importing a speadsheet into a table, and then creating a lookup on that field. My thought is that you are using the LOOKUP WIZARD from the data types. By using this the data is deleted at the last step to maintain the data integrity.

So, here is the workaround.

Instead of using the data type, go to the field properties area, and click on the LOOKUP tab.

1. Choose combo box
2. Row Source type will appear and be TABLE / QUERY
3. ROW SOURCE - click on the ....'s next to the arrow.
4. This will open a query window, which you can choose the table and field that you want to lookup.
5. Once the query is built, simply close the query using the X at the top left of the query window, and click YES on the message box
6. This is add an SQL statement like "Select [field] from [table].
7. The last step, if required, is to set the last field in the LOOKUP properties area "LIMIT TO LIST" to either YES or NO, depending on your requirements.

Once this is all done, then you can change to the table view, and save the table.
This should get you around the issue.

Let me know how you do.

Regards

Richard