CHECK EXCEL HEADER RECORD BY NAME:
During one of my contract roles, the team needed a way to validate a header record from an Excel spreadsheet import. End users were submitting data via spreadsheets, which might contain 5 columns - or sometimes might contain 100 columns of data. This column header from their spreadsheet needed to be compared against a table that had over 200 columns, and we needed to make sure the end user's column names matched what was in the import table. I'm using a TABLE DEFINITION for this function, which was to create a blank copy of the import table. Screen shot of the code is below:
During one of my contract roles, the team needed a way to validate a header record from an Excel spreadsheet import. End users were submitting data via spreadsheets, which might contain 5 columns - or sometimes might contain 100 columns of data. This column header from their spreadsheet needed to be compared against a table that had over 200 columns, and we needed to make sure the end user's column names matched what was in the import table. I'm using a TABLE DEFINITION for this function, which was to create a blank copy of the import table. Screen shot of the code is below:
AVOID SCIENTIFIC NOTATION FROM SPREADSHEET IMPORTS:
At one contract role I had the pleasure of dealing with scientific notation from importing an Excel spreadsheet into a table. I came up with this solution, by creating a "temp" table. Screen shot of the code is below:
At one contract role I had the pleasure of dealing with scientific notation from importing an Excel spreadsheet into a table. I came up with this solution, by creating a "temp" table. Screen shot of the code is below:
TRIM TABLE FIELDS:
Use a Table Definition to trim all fields in a table. This is faster than creating an update query and using the 'TRIM' function on every single field. Two functions are used here... 1st named "fnTrim" and the 2nd named "fnTrimTableFields". Remember to pass in the name of the table (sTableName). Screen shot of the code is below:
Use a Table Definition to trim all fields in a table. This is faster than creating an update query and using the 'TRIM' function on every single field. Two functions are used here... 1st named "fnTrim" and the 2nd named "fnTrimTableFields". Remember to pass in the name of the table (sTableName). Screen shot of the code is below:
TRUNCATE TABLE:
The code below acts just like the truncate table function in SQL Server. The functions "fnWarningsOFF" and "fnWarningsON" are in another module that is just using the DoCmd to turn the warnings on or off.
The code below acts just like the truncate table function in SQL Server. The functions "fnWarningsOFF" and "fnWarningsON" are in another module that is just using the DoCmd to turn the warnings on or off.
