I've gone with a couple of VB functions that use the STR function and the 
REPLACE function and create the appropriate SQL for the provider (we have 
to have the SQL usable in SQL Server, but still usable with Access...)

FUNCTION SQL_MONEY(ByVal fld as string, Optional ByVal lDecPlaces as Long 
= 2) as String

DIM sTmp AS STRING
IF sProvider = "Access" THEN
    sTmp = replace(space$(lDecPlaces),' ','0')
    SQL_MONEY = "FORMAT(" & fld & ",'0." & sTmp & "')"
ELSE
    SQL_MONEY = "STR(" & fld & ",14," & lDecPlaces & ")"
END IF

and

FUNCTION SQL_LPAD(ByVal fld as string, Optional ByVal lNoChars as Long = 
2) as String
DIM sTmp AS STRING

IF sProvider = "Access" THEN
    sTmp = replace(space$(lNoChars),' ','0')
    SQL_LPAD = "FORMAT(" & fld & ",'" & sTmp & "')"
ELSE
    SQL_LPAD = "REPLACE(STR(" & fld & "," & lNoChars & ",0),' ','0')"
END IF

These are just added to an inline SQL statement in VB:
sql = "SELECT " & SQL_LPAD("Field1",2) & " as Fld1, ..."


Thanks very much to all of you for your suggestions!

Cheers
Simon



> You can use the STR function to format floats...
>> -------------------------------------------
>>  SELECT format(<field3>,'0.00') as MoneyValue, ...

> > Would I need to use the CONVERT statement here, or is there another 
w> ay? 

> > Simon
>> ---------------------------------------------