I have a table with 19 fields that gets records automatically added to it daily. The source program that adds the data to the database gets its data from scanning documents and identifies each field to be inserted into the database by a two letter identifier at the beginning of the data required. For example MW250 tells the program that MW250 will be inserted into the field for MW.
When someone wishes to search the database for a specific record, they click on a macro that clears a temporary table of all data, appends all the current data from the main table to this temporary table, removes all the two letter designators from each field and then allows the user to select what is needed to find the records. This is great in all fields except one. In one field I want to be able to search a range of values. I have created a query for this so the user simply types in the minimum and maximum range values on a separate form and then the query will find them. The problem is the query for the range value is looking for a numerical value and while the data in that field is only numerical, it is transferred over as text from the source table which cannot be changed.
Is there any way to do an update or append query to change a field type from text to number after all the other queries are done and have it in the format desired?
One more piece to the puzzle. Not all records will have a value in the MW field. Some may be blank.
At this point I have tried several iterations of CInt but always get the error due to the blank fields.
Thanks for any help on this it is greatly appreciated.
When someone wishes to search the database for a specific record, they click on a macro that clears a temporary table of all data, appends all the current data from the main table to this temporary table, removes all the two letter designators from each field and then allows the user to select what is needed to find the records. This is great in all fields except one. In one field I want to be able to search a range of values. I have created a query for this so the user simply types in the minimum and maximum range values on a separate form and then the query will find them. The problem is the query for the range value is looking for a numerical value and while the data in that field is only numerical, it is transferred over as text from the source table which cannot be changed.
Is there any way to do an update or append query to change a field type from text to number after all the other queries are done and have it in the format desired?
One more piece to the puzzle. Not all records will have a value in the MW field. Some may be blank.
At this point I have tried several iterations of CInt but always get the error due to the blank fields.
Thanks for any help on this it is greatly appreciated.
