When creating a select statement or account code validation query the same syntax can be used.

All entries from an Excel worksheet.

In the example statement the account information required was everything in the worksheet called sheet1. When the settings were tested there was an error message returned stating that the worksheet name was not recognized. The worksheet name entered needs to be exactly the same as in the workbook. In the example a space was included which was not in the workbook.

 

Select Statement used when entered correctly for example: select * from [sheet1$]
Please note that the worksheet name is ended with a $ symbol.

 

No headings used in the Excel worksheet.

If there are no headings used in the Excel file the fields are referred to as F1, F2 F3 etc. When the users see a list of account codes the headings will show as F1, F2 F3 as shown in the example below.

To show a descriptive name in the heading you need to amend to Select Statement to include the names, for example:
select F1 as [Work Order], F2 as [Cost Centre], F3 as [Department] from [Sheet1$]

The users will now see a lost of account codes with headings.

 

Select specific Excel fields.

In the example below the field Nominated Owner is not required.

Amend the select statement to include the fields that you require. In the example below the field headings Code, Project & Department have been inserted in the statement. Click Test Settings to check your statement. Only the required fields should be shown, as per the example.

Select Statement used for example - select [code],[project],[department] from [sheet1$].

Rename existing field headings

You can display a different field name on the website from the field name contained in the file. For example you might want to present to the end user the field name "Account Code" instead of "Code". All or just one field can be re-named.

Example of one field being re-named:

select [code] as [Account Code], [project], [department] from [sheet1$] where [Number]={0}

Example of all fields being re-named:

select [Number] as [Project Number],[Name] as [Project Name], [description] as [Project Description] from [sheet1$] where [Number]={0}

The syntax where [Number]={0} needs to be added when creating a query for the account code validation. It shows which field contains the account code that will be verified with the code entered by the user. The field name used needs to be the original name that is contained in the file.

If you are creating a statement query where [Number]={0} is not required the user will select an account code from a list.

Select F1 as [Work Order], F2 as [Cost Centre], F3 as [Description] from Repro.csv where F1={0}