Excel Microsoft Query problem

[PhilPPhillips]

I am creating a spreadsheet that directy queries my Macola database (MS SQL server).

My question is how do you bring in a Macola date field that is recognized as a date by excel?

If I use this expression in my select statement I get a text field in Excel that looks like a date.

substring(convert(varchar(8),IMINVTRX_SQL.trx_dt),5,2)+'/'+substring(convert(varchar(8),IMINVTRX_SQL.trx_dt),7,2)+'/'+substring(convert(varchar(8),IMINVTRX_SQL.trx_dt),3,2) AS 'trx_dt'



If I use this next expression I get a date field in excel. But, because a query using this expression cannot be shown graphically in the SQL designer I can't use parameters in the query. And I need parameters so that that the spreadsheet can be refreshed periodically with a new date range.

cast(convert(varchar(8),IMINVTRX_SQL.trx_dt,112) as datetime)

Hope this makes sense, any idea? Thanks ...

 

[kscheeler]

My solution would be to first bring in and query on the date in a numerical format. Macola stores all of it's dates in Progression in a YYYYMMDD format as you can see. I would prompt for the date that way and once it's in Excel, set up a new date in another column.

Kevin Scheeler

 

[vbajock]

I am not an Excel VBA expert, but you should be able to plug this function in a VBA module somewhere and then use it in the query column e.g. ConvertMacDate([OEORDLIN].[REQUEST]) as MacDate would be the header line on your query grid.

Public Function ConvertMacDate(ByVal passdate As Long) As Date
On Error GoTo err_h

procname = "ConvertMacdate"

ConvertMacDate = Format(Mid$(Trim$(str$(passdate)), 5, 2) + "/" + Mid$(Trim$(str$(passdate)), 7, 2) + "/" + Mid$(Trim$(str$(passdate)), 3, 2), "SHORT DATE&quot
Exit Function

err_h:
'*****
ConvertMacdate=0
End Function