summing up decimals

I've just created a view that summs up percent value, that is saved in my SQL back-end as Decimal (5,4).
It resulted in the datatype (DECIMAL (38, 4)). Those 37 non-necessary digits were read by the Access database as Text, not number. If the number is treated as Text, then that causes ceveral problems to the access database: for example, it cannot format it as percent.
Since I don't really need those 37 digits, I've changed it to Decimal (6,4) (with an extra digit just to be on the safe side ; the sum shouldn't anyway exceed the 100%) and re linked the table. That sort this problem out.

Enter Parameter Value when exporting to Excel

The following problem is quite common, I believe, but without a simple solution.

I've create a query which depends on a parameter from the end user (being entered via a form). It runs beautifully.

But on "Export to Excel" (a command that does DoCmd.TransferSpreadsheet) it asks for the parameter value again.

The solution:

dictate the parameter to the query.

That means that instead of placing the parameter in the query, and placing the parameter there (Let's say, SELECT quantity, ..., FROM orders WHERE quantity > Forms!msg_quantity! txt_quantity)

You would place it in the VBA code like that:

Private Sub CreateQuery()

Dim qdf As DAO.QueryDef

Dim sSQL As String

sSQL = "SELECT quantity, ..., FROM orders WHERE quantity > " & Forms!msg_quantity!txt_quantity

Set qdf = CurrentDb.QueryDefs(Me.Form.Caption)

qdf.SQL = sSQL 'This saves the query

qdf.Close

End Sub

It is slower, of course, but if the client really needs exporting to Excel then the client can get it without the unusable "enter parameter value" text box. Trying to hide the text box didn't work in this case.

This way the parameter value is known to the query on export time.