Formatting problem

[DMT Dave]

Hi Guy's can anyone help on this one i am finding rather bizarre!!


Dim iSort as Integer

iSort = "99"


Set rs = CurrentDb.OpenRecordset("SELECT tblStock.SortNo, tblStock.PONumber, tblStock.LiftType, tblStock.LiftNo, tblStock.StartQty, tblStock.AllocatedQty, tblStock.NewQty FROM tblStock WHERE SortNo < " & iSort & " ORDER BY SortNo;")

Although the iSort is set to integer and the field is set to long integer and the cells on the excel file template is set to Number with 0 decimals

The sortNo cells are 01/01/1900 ?

Can this be reformatted before the file gets exported to prevent another user accidentally sending in that format ?

.Worksheets(1).Cells(3, 3).CopyFromRecordset rs
'.Worksheets(1).Range("C3:C50").NumberFormat = "Number" SOMETHING LIKE THIS ????

Much appreciated

[June7]

Try:

.Worksheets(1).Range("C3:C50").NumberFormat = "0"

[DMT Dave]

Hi June7, thank you i am going to try that and post back

Kindest

[rpeare]

you can do an isnumeric check to verify the value is numeric then change it to a number (forcing it to be if MS Access is reading it as a string) with the CINT or CDBL.

If you are defining iSort = "99" you are defining it as a string in your code

Dim iSort

iSort = 99

the " marks will cause mayhem.

You can also do

debug.print "SELECT tblStock.SortNo, tblStock.PONumber, tblStock.LiftType, tblStock.LiftNo, tblStock.StartQty, tblStock.AllocatedQty, tblStock.NewQty FROM tblStock WHERE SortNo < " & iSort & " ORDER BY SortNo;"

to see how your SQL string looks before you try to execute it. I'll bet it's throwing ' or " marks around your isort value or your isort isn't what you think it is