Complicated query date calculation... (1 Viewer)

[Nightowl4933]

I've got a calculated date in a Query that I'm unable to sort by the result of that calculation.

The SQL is:

SELECT tblAllApplications.[Initial Planning Application Reference], tblAllApplications.PlanningAuthority, tblAllApplications.[Date of Application] AS [Date], tblAllApplications.Granted, tblAllApplications.Received, tblAllApplications.Limit, tblAllApplications.Expiry, Format(IIf([Expiry]>DateAdd("yyyy",IIf([Limit]>0,[Limit],0),[Granted]),[Expiry],DateAdd("yyyy",IIf([Limit]>0,[Limit],0),[Granted])),"dd mmm yyyy") AS CalcDate
FROM tblAllApplications
WHERE (((tblAllApplications.ExpiryOption)=2 Or (tblAllApplications.ExpiryOption)=3));

...but when I try to Order by the result, it just won't do it.

I've attached an image of the result (without the Sort) but, to give a bit more context, [CalcDate] is either [Granted] + [Limit] (in years) OR [Expiry], but only one or the other.

Thanks.

 

[Minty]

You are trying to sort it by a text version of the date - which will not work the way you want it. You would have to essentially run another query against this one or include another calculation which leaves the date in a date format that you can sort on..

 

[Nightowl4933]

Hi Minty,

I did add another Expr1 to the Query formatting the field like this:

Sorted:Format([CalcDate],'Medium Date') but, when I ran the Query, it asks for the Parameter value of CalcDate, which is what puzzled me.

And, if I hide the calculated field, I get the same error - without trying to sort them.

Pete

 

[plog]

CalcDate isn't a field in the tblAllApplications, so the query has no idea what you mean when you try and reference it. You can't calculate a field and then immediately use it at the same time.

However, you're going down the wrong path even if you could do that. Like Minty said, you are not creating dates, you are creating strings (Format() returns strings, not dates, even when you use the 'Medium Date' argument). Strings sort differently than dates ("12/1/2016" comes before "1/31/2015").

You most likely want to use the CDate function or DateValue function to generate your dates.

However still, your ultimate issue (not sorting at all) is most likely because of unexpected data:

DateAdd("yyyy",IIf([Limit]>0,[Limit],0),[Granted])

If [Granted] is Null or not a date, then that whole thing craps out and returns an error because you can't add years to a Null value. That's most likely the cause of your original issue. Check your data to see if you have any [Granted] values that are null or non-dates.

 

[Nightowl4933]

Hi plog,

The results of the calculation seem to be misleading me, then, because they all have [Granted] dates, and adding Is Not Null to tht field gives the same number of results.

[Limit] is the number (Double) of years I want to add to the [Granted] date, but only if [Expiry] Is Null.

Sounded simple to me!

Edit: I used CDate() on the [Expiry] field, which resulted in an #Error on some of the dates. Doing the same with the [Granted] field didn't. That's where there are Null values leading to the problems you've identified!

 

[Nightowl4933]

I can see why this is a problem! I've split out the different date calculations and this is what I've got left. I'm a bit confused at some of the calculations, though. The top line show 12 years added to 22/06/2011 as being 22/12/2023

 

[plog]

What's the specific code behind E & X?

 

[Nightowl4933]

E: CDate([Expiry]) and X: DateAdd("yyyy",[Limit],[Granted])

I was using the wrong date in Column 1 - sorry.

Pete

 

[plog]

Now post another picture of your query that includes the Expiry and Granted fields as well.

 

[Nightowl4933]

'ere you go...

 

[plog]

Everything looks right to me. What is the issue with the last image you uploaded? Where do you think it is incorrect?

 

[Nightowl4933]

Nope, everything's fine - but not what I want to achieve!

I would like a field that combines these and can be sorted by date order.

 

[plog]

Please demonstrate with data what you hope to achieve. I will need 2 sets of data:

A. Starting sample data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results. Using the data from A, show what you hope to end up with.

 

[Nightowl4933]

Hi plog,

Example database attached, as requested

All (!) I would like is a single column of calculated dates (i.e. Z showing the combination of E and X), that can be sorted as a date and in order. It's my intention to use conditional formatting in a form to highlight those dates that are 1 year before today as amber and 6 months before today as red - but I can do that bit!

Thanks awfully,

Pete

 

[arnelgp]

see if this is what you want.

 

[Nightowl4933]

see if this is what you want.

That's exactly what I want! I knew I was complicating it too much!

Thanks again, my friend - and thank you, plog.

Pete