Excel/MS Query: Change format of date and dollar amount

[althea]

I have an excel spreadsheet and am getting data from my SQL database. In MS Query I have a query to get 3 fields from a database but need it to look better.

When I try and use the 'format' next to date field, it gives me an error about 'does not recognize format command'.

Do I add the format(tablename.fieldname,"mm/dd/yyyy") in the 'select', 'where' and 'order by' areas of the statement?Currently I have it only in the select part of the statement.Thanks!

 

[SkipVought]

Geoff or someone else will have to help with exactly what need to be in your 2 parameters, in the SQL Server syntax as dates are tricky.

In the Parameter Window, I would reference the 2 cells and then display the values as needed, which means that your user may need to enter the two dates in 2 other cells and then convert the data in the 2 parameter cells. Or you may even need to do the query in VBA, which is what I often do with parameters as you have much more control and many more options.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[althea]

Skip,
Thanks so much. I'm going to try and have the user enter the two dates in the cells and then convert the data to 'number' format in the parameter cells. Do you think the easiest way to do this is a function or paste special?
Of course when i do this because of how the date is treated, 12/15/10 turns into 40527.

 

[xlbo]

Don't bother converting the dates to numbers. SQL Server doesn't use the same principles as Excel

You are best off treating the dates as strings - either way, the best and most consistent format to provide dates to SQL sever is:

yyyy-MM-dd

Whether passsed as a string or a date it should work

If you want to allow your users to enter dates in m/dd/yyyy format then do as Skip suggests and have them enter that date into a cell then have the parameter cell set up as:

=TEXT(Z1,"yyyy-mm-dd")

Where the user has entered the date into Z1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question