Export field as a number when exporting to excel

[Glowackattack]

I have a query that when i export to an excel file has a calculated field that shows up as a "number stored as text" error, is there a way i can format that field so that when it exports it is exported as a number??

Thanks for your help in advance!!

 

[boblarson]

What is your query's SQL?

When you use FORMAT it turns it into a string, so that may be your problem.

 

[Glowackattack]

SELECT Tbl_Funds.Cusip, Tbl_UVs.SDIO, Tbl_Funds.[Fund Name], Tbl_UVs.UV_Date, Tbl_UVs.UV, DLookUp("[UV]","[Tbl_UVs]","[UV_Date]=#" & DateAdd("D",-1,DateAdd("M",-1,DateAdd("D",1,[UV_Date]))) & "# and [SDIO]='" & Forms!Frm_Funds!SDIO & "'") AS [Prior Month UV], ([UV]/[Prior Month UV])-1 AS [One Month Return]
FROM Tbl_Funds LEFT JOIN Tbl_UVs ON Tbl_Funds.SDIO = Tbl_UVs.SDIO
WHERE (((Tbl_UVs.SDIO)=[Forms]![Frm_Funds]![SDIO]))
ORDER BY Tbl_UVs.UV_Date;
[CODE]

The "DLookUp("[UV]","[Tbl_UVs]","[UV_Date]=#" & DateAdd("D",-1,DateAdd("M",-1,DateAdd("D",1,[UV_Date]))) & "# and [SDIO]='" & Forms!Frm_Funds!SDIO & "'") AS [Prior Month UV]" field is what is giving me the error, the field is basically pulling a value for one month before the current value on the field.

 

[boblarson]

Give this a try:

CDbl(DLookUp("[UV]","[Tbl_UVs]","[UV_Date]=#" & DateAdd("D",-1,DateAdd("M",-1,DateAdd("D",1,[UV_Date]))) & "# and [SDIO]='" & Forms!Frm_Funds!SDIO & "'")) AS [Prior Month UV]

 

[Glowackattack]

The field is actually pulling a "double" data type, is there a similar converter for that??

Thanks very much for the quick responses.

 

[Glowackattack]

Oops, beat me to it.

Thanks very much.

 

[boblarson]

Oops, beat me to it.

Thanks very much.

Yeah, I kind of went "wonky" for a moment and had CDate in my mind for some reason. Not sure why but then went back quickly to change it.

 

[Glowackattack]

Thats great, thanks!!

CDbl(DLookUp("[UV]","[Tbl_UVs]","[UV_Date]=#" & DateAdd("D",-1,DateAdd("M",-1,DateAdd("D",1,[UV_Date]))) & "# and [SDIO]='" & Forms!Frm_Funds!SDIO & "'")) AS [Prior Month UV]

just for completeness in case anyone else needs it.