Order by date created in VBA?

[rudeboymcc]

Hi. I'm writing a query in vba (first time) and i've got the recordset i want, but i'm having trouble sorting it by the date.

I've created a Date (Dim CurrentDate As Date), and assigned it liek this:

CurrentDate = DateAdd("m", 1, Me.ContractStartDate)

i'm putting CurrentDate into the sql query like this:

strSql = "SELECT Tenancies.ID, Tenancies.Rent, Tenancies.ContractStartDate As PeriodFrom, Format('" & CDate(CurrentDate) & "', 'dd/mm/yyyy') AS PeriodTo,

and the Order by clause:

ORDER BY PeriodTo;

Problem is, it orders it first by the date, then month, then year. I need it reversed, to order it by year, month then date.

anyone have any ideas? I could just change dd/mm/yyyy to yyyy/mm/dd but thats not very user friendly

 

[neileg]

 ORDER BY Format(CDate(CurrentDate), 'yyyy/mm/dd')

 

[rudeboymcc]

Thanks for the suggestion but I get error '3351' : The ORDER BY expression (Format(CDate(CurrentDate),'yyyy/mm/dd')) includes fields that are not selected by the query. only those fields requested in the first query can be included in an Order BY expression.

I tried Format(CDate(PeriodFrom),'yyyy/mm/dd')) as the field in the query is called Period From, but it gives the same error

 

[stopher]

Either
Just add CurrentDate to your query and do the following (no need to format if it is an unformatted date datatype):
ORDER BY CurrentDate

Or
ORDER BY CDATE([PeriodTO])

hth
Chris

 

[rudeboymcc]

I already have added CurrentDate to the query:

Format('" & CurrentDate & "', 'dd/mm/yyyy') AS PeriodTo

if i just put:

CurrentDate As PeriodTo

I get error "Too few parameters.Expected 1".

and ORDER BY CDATE(PeriodTo) gives the same error "includes fields that are not selected by the query. only those fields requested in the first query can be included in an Order BY expression." I don't see how that will work anyway - isn't CDate a VBA command? I tried "ORDER BY " & CDATE(PeriodTO) & " but PeriodTo is in teh SQL statement only so it doesnt' know what to look for.

Is there a way i can tell teh sql query to look at the previous field? I'm thinking maybe i can add the date as yyyy.mm.dd and sort it, and then the next field will be the same date reversed?

 

[stopher]

Sorry, I re-read your first post. It looks like you are trying to use a function to get PeriodTo. I think this is causing problems and not necessary.

try this:

SELECT Tenancies.ID, Tenancies.Rent, Tenancies.ContractStartDate AS PeriodFrom, Format(DateAdd("m",1,[contractstartdate]),'dd/mm/yy') AS PeriodTo
FROM Tenancies 
ORDER BY Tenancies.ContractStartDate

Note that I've ordered by ContractStartDate since this is the underlying field.

Why are you wanting to format your date in the query? By doing this you are switching it from a date to a string. Surely it is better to leave it as a date then format in your form or report.

hth
Chris

 

[Pat Hartman]

I'm not sure if your issue is resolved but the problem which was never made clear is that you cannot sort by formatted dates unless they are formatted in year, month, day order because formated dates are text strings and text strings are sorted character by character, left to right where as numeric fields (dates are stored as numeric fields) are decimal aligned and zero filled internally to sort correctly. When sorting on a date, ALWAYS sort on the unformatted date field so that Jet will handle it as a numeric field as it should.

 

[rudeboymcc]

Thanks for the help guys but thsi took me too many hours to try and work out. In the end, I made my query an append query, and added the date to a table where the field was set to "date/time", and so everything after that could be sorted very easily and i could see the date as dd/mm/yyyy.

i know it's a badly coded workaround but it works for now, and has been added on my list of things to improve int eh future

 

[Pat Hartman]

Your entire problem was caused because you formated the date which caused it to be converted to a string. All you needed to do was to add the unformatted date to the query so you could sort by it. You didn't even need to include it in the selected fields.

 

[rudeboymcc]

That's not quite true though - i started with just the fields as they are from the table and it was ordering them by day first, then month, then year. that's why i formatted it - to reverse it to yyyy/mm/dd.

 

[LPurvis]

Then it sounds like you might
a) be in the UK (due to date order - or another similarly sensible country lol ;-)
b) have your "Date" field as nothing of the sort but is probably Text type.

In your earlier code example - there would be no error as a text based date value will be implicitly coerced by VBA into the date variable if it fits the format (i.e. would respond to an explicit request of CDate).

Have you verified the data type of the field in question?

 

[rudeboymcc]

I'm in the UK so that's where i'm going wrong

and yes the field data type is definately Date/time

 

[LPurvis]

Unless you've coerced the type into Text - there's no reason why your Date field would sort in anything other than Date order.
(You've perhaps coerced it somewhere along the way without realising - Pat was talking about sorting on the field itself - not the tiniest bit of formatting at all).

What is the very bare bones query based on the table that you have that sorts incorrectly?

 

[rudeboymcc]

I think you're forgetting that this data is not in a table. This is a Sql query in VBA and i'm just displaying the results. so the date type of the field will not have any effect until the data is added.

 

[LPurvis]

Ummm.... Huh? :-s

What is the source of the data?
This "SQL query" you refer to... what is it?
Displaying the results... where?

 

[rudeboymcc]

I created a temporary query in vba, and half the values were from tables. the other values were calculated in vba (including the date i wanted to order), and so when i viewed the query, some of the values were just strings from vba, which don't order well by date.

 

[LPurvis]

Are you talking about back to the original question:

strSql = "SELECT Tenancies.ID, Tenancies.Rent, Tenancies.ContractStartDate As PeriodFrom, Format('" & CDate(CurrentDate) & "', 'dd/mm/yyyy') AS PeriodTo,

That would, absolutely by Text.
(I'm confused though... as surely that date - fixed as it is in the resultant SQL - is unchanging by definition. So why would you want to sort by it?

Anyway - to see it as a date in the SQL

strSql = "SELECT Tenancies.ID, Tenancies.Rent, Tenancies.ContractStartDate As PeriodFrom, #'" & Format(CurrentDate, "yyyy-mm-dd") & "# AS PeriodTo,"

But as I say... how does that vary by row?