If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
more dates!!!
How is this any different than the current code?
-----Original Message----- Hi, .... WHERE myField BETWEEN Nz( Nz( StartDate, EndDate), date() ) AND Nz( Nz( EndDate, StartDate), date() ) Hoping it may help, Vanderghast, Access MVP "brigid" wrote in message ... The user inputs a start date and an end date in a form, and the query displays data in between those 2 dates. If the user enters a start date but no end date, by default the end date = start date and therefore that day's data is displayed. If the user enters an end date but no start date, by default the start date = today's date, and therefore data for today's date through the end date is displayed. However, if the user leaves both dates blank, I would like both dates to default to today's date. However, no data whatsoever is being displayed. I don't understand why because the start date should be defaulted to today's, and when the end date is null it looks to the start date. The code is as follows: SELECT [calculated dates].calculatedFromDate, [calculated dates].calculatedToDate, [calculated dates].[Blotter Entry Date] FROM [calculated dates] WHERE ((([calculated dates].calculatedFromDate)=IIf ([Forms]![range]![start] Is Null,Date(),[Forms]! [range]! [start]) And ([calculated dates].calculatedFromDate) =IIf ([Forms]![range]![end] Is Null,[Forms]![range]![start], [Forms]![range]![end])) AND (([calculated dates].calculatedToDate)=IIf([Forms]![range]![start] Is Null,Date(),[Forms]![range]![start]) And ([calculated dates].calculatedToDate)=IIf([Forms]![range]![end] Is Null,[Forms]![range]![start],[Forms]![range]![end]))); . |
#2
|
|||
|
|||
more dates!!!
Hi,
It as a much simpler look. :-) Admit it, it is a nice start... but more seriously... Your code seems to fail (accordingly to what you specify) if start IS NULL (and so, I assume, in this case, end field is also a null). Your code is somewhat equivalent to: fromTested = Nz(start, date() ) AND fromTested = Nz(end, start) AND upToTested = Nz(start, date() ) AND upToTested = Nz(end, start) Watch the second and fourth clauses, when start is null, and probably end is also null, once the iif (or Nz) are evaluated, that gives us: fromTested = date() AND fromTested = NULL AND upToTested = date() AND upToTested = NULL which may evaluate, finally, to NULL, or to false (but NEVER to TRUE). The proposed code also replace those NULL by today date, in this scenario (end IS NULL, start IS NULL). The proposed code is a little bit like rewriting the fourth clause to: upToTested = Nz( Nz(end, start) , Date( ) ) as example (and also the second clause should be submitted to the same modification). The proposed code also looks for the possible (is it?) case where start is null and end is not, it then uses end (first and third clause). Hoping it may help, Vanderghast, Access MVP "brigid" wrote in message ... How is this any different than the current code? -----Original Message----- Hi, .... WHERE myField BETWEEN Nz( Nz( StartDate, EndDate), date() ) AND Nz( Nz( EndDate, StartDate), date() ) Hoping it may help, Vanderghast, Access MVP "brigid" wrote in message ... The user inputs a start date and an end date in a form, and the query displays data in between those 2 dates. If the user enters a start date but no end date, by default the end date = start date and therefore that day's data is displayed. If the user enters an end date but no start date, by default the start date = today's date, and therefore data for today's date through the end date is displayed. However, if the user leaves both dates blank, I would like both dates to default to today's date. However, no data whatsoever is being displayed. I don't understand why because the start date should be defaulted to today's, and when the end date is null it looks to the start date. The code is as follows: SELECT [calculated dates].calculatedFromDate, [calculated dates].calculatedToDate, [calculated dates].[Blotter Entry Date] FROM [calculated dates] WHERE ((([calculated dates].calculatedFromDate)=IIf ([Forms]![range]![start] Is Null,Date(),[Forms]! [range]! [start]) And ([calculated dates].calculatedFromDate) =IIf ([Forms]![range]![end] Is Null,[Forms]![range]![start], [Forms]![range]![end])) AND (([calculated dates].calculatedToDate)=IIf([Forms]![range]![start] Is Null,Date(),[Forms]![range]![start]) And ([calculated dates].calculatedToDate)=IIf([Forms]![range]![end] Is Null,[Forms]![range]![start],[Forms]![range]![end]))); . |
#3
|
|||
|
|||
more dates!!!
I completely understand and appreciate your suggestion.
It makes sense to me. However, when I use the code so it appears as: WHERE ((([calculated dates].calculatedFromDate) Between Nz (Nz([Forms]![range]![start],[Forms]![range]![end]),Date ()) And Nz(Nz([Forms]![range]![end],[Forms]![range]! [start]),Date())) AND (([calculated dates].calculatedToDate) Between Nz(Nz([Forms]![range]! [start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]! [range]![end],[Forms]![range]![start]),Date()))); it still doesn't return any values when the start date and end date are both entered as null. It also doesn't return any dates when the start date is null and the end date is entered. Did I interperet your advice (which again I truly appreciate)? -----Original Message----- Hi, It as a much simpler look. :-) Admit it, it is a nice start... but more seriously... Your code seems to fail (accordingly to what you specify) if start IS NULL (and so, I assume, in this case, end field is also a null). Your code is somewhat equivalent to: fromTested = Nz(start, date() ) AND fromTested = Nz(end, start) AND upToTested = Nz(start, date() ) AND upToTested = Nz(end, start) Watch the second and fourth clauses, when start is null, and probably end is also null, once the iif (or Nz) are evaluated, that gives us: fromTested = date() AND fromTested = NULL AND upToTested = date() AND upToTested = NULL which may evaluate, finally, to NULL, or to false (but NEVER to TRUE). The proposed code also replace those NULL by today date, in this scenario (end IS NULL, start IS NULL). The proposed code is a little bit like rewriting the fourth clause to: upToTested = Nz( Nz(end, start) , Date( ) ) as example (and also the second clause should be submitted to the same modification). The proposed code also looks for the possible (is it?) case where start is null and end is not, it then uses end (first and third clause). Hoping it may help, Vanderghast, Access MVP "brigid" wrote in message ... How is this any different than the current code? -----Original Message----- Hi, .... WHERE myField BETWEEN Nz( Nz( StartDate, EndDate), date() ) AND Nz( Nz( EndDate, StartDate), date() ) Hoping it may help, Vanderghast, Access MVP "brigid" wrote in message ... The user inputs a start date and an end date in a form, and the query displays data in between those 2 dates. If the user enters a start date but no end date, by default the end date = start date and therefore that day's data is displayed. If the user enters an end date but no start date, by default the start date = today's date, and therefore data for today's date through the end date is displayed. However, if the user leaves both dates blank, I would like both dates to default to today's date. However, no data whatsoever is being displayed. I don't understand why because the start date should be defaulted to today's, and when the end date is null it looks to the start date. The code is as follows: SELECT [calculated dates].calculatedFromDate, [calculated dates].calculatedToDate, [calculated dates].[Blotter Entry Date] FROM [calculated dates] WHERE ((([calculated dates].calculatedFromDate)=IIf ([Forms]![range]![start] Is Null,Date(),[Forms]! [range]! [start]) And ([calculated dates].calculatedFromDate) =IIf ([Forms]![range]![end] Is Null,[Forms]![range]! [start], [Forms]![range]![end])) AND (([calculated dates].calculatedToDate)=IIf([Forms]![range]! [start] Is Null,Date(),[Forms]![range]![start]) And ([calculated dates].calculatedToDate)=IIf([Forms]![range]![end] Is Null,[Forms]![range]![start],[Forms]![range]! [end]))); . . |
#4
|
|||
|
|||
more dates!!!
Hi,
Who is NULL? I assumed, up to now, it was FORMS!... that were, but if the field name "calculatedFromDate" or "calculatedToDate" is the potential NULL value, then, indeed, you have to "decorate" them with a Nz too: WHERE ( Nz(calculatedFromDate, Date() ) BETWEEN ... AND ... ) AND ( Nz(calculatedToDate, Date() ) BETWEEN ... AND ... ) Hoping it may help, Vanderghast, Access MVP wrote in message ... I completely understand and appreciate your suggestion. It makes sense to me. However, when I use the code so it appears as: WHERE ((([calculated dates].calculatedFromDate) Between Nz (Nz([Forms]![range]![start],[Forms]![range]![end]),Date ()) And Nz(Nz([Forms]![range]![end],[Forms]![range]! [start]),Date())) AND (([calculated dates].calculatedToDate) Between Nz(Nz([Forms]![range]! [start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]! [range]![end],[Forms]![range]![start]),Date()))); it still doesn't return any values when the start date and end date are both entered as null. It also doesn't return any dates when the start date is null and the end date is entered. Did I interperet your advice (which again I truly appreciate)? -----Original Message----- Hi, It as a much simpler look. :-) Admit it, it is a nice start... but more seriously... Your code seems to fail (accordingly to what you specify) if start IS NULL (and so, I assume, in this case, end field is also a null). Your code is somewhat equivalent to: fromTested = Nz(start, date() ) AND fromTested = Nz(end, start) AND upToTested = Nz(start, date() ) AND upToTested = Nz(end, start) Watch the second and fourth clauses, when start is null, and probably end is also null, once the iif (or Nz) are evaluated, that gives us: fromTested = date() AND fromTested = NULL AND upToTested = date() AND upToTested = NULL which may evaluate, finally, to NULL, or to false (but NEVER to TRUE). The proposed code also replace those NULL by today date, in this scenario (end IS NULL, start IS NULL). The proposed code is a little bit like rewriting the fourth clause to: upToTested = Nz( Nz(end, start) , Date( ) ) as example (and also the second clause should be submitted to the same modification). The proposed code also looks for the possible (is it?) case where start is null and end is not, it then uses end (first and third clause). Hoping it may help, Vanderghast, Access MVP "brigid" wrote in message ... How is this any different than the current code? -----Original Message----- Hi, .... WHERE myField BETWEEN Nz( Nz( StartDate, EndDate), date() ) AND Nz( Nz( EndDate, StartDate), date() ) Hoping it may help, Vanderghast, Access MVP "brigid" wrote in message ... The user inputs a start date and an end date in a form, and the query displays data in between those 2 dates. If the user enters a start date but no end date, by default the end date = start date and therefore that day's data is displayed. If the user enters an end date but no start date, by default the start date = today's date, and therefore data for today's date through the end date is displayed. However, if the user leaves both dates blank, I would like both dates to default to today's date. However, no data whatsoever is being displayed. I don't understand why because the start date should be defaulted to today's, and when the end date is null it looks to the start date. The code is as follows: SELECT [calculated dates].calculatedFromDate, [calculated dates].calculatedToDate, [calculated dates].[Blotter Entry Date] FROM [calculated dates] WHERE ((([calculated dates].calculatedFromDate)=IIf ([Forms]![range]![start] Is Null,Date(),[Forms]! [range]! [start]) And ([calculated dates].calculatedFromDate) =IIf ([Forms]![range]![end] Is Null,[Forms]![range]! [start], [Forms]![range]![end])) AND (([calculated dates].calculatedToDate)=IIf([Forms]![range]! [start] Is Null,Date(),[Forms]![range]![start]) And ([calculated dates].calculatedToDate)=IIf([Forms]![range]![end] Is Null,[Forms]![range]![start],[Forms]![range]! [end]))); . . |
#5
|
|||
|
|||
more dates!!!
No the fields aren't what I'm checking for as NULL. The
user enters a start date and and end date through a form, and they have the option to leave either of those blank. That is where I'm stuck. -----Original Message----- Hi, Who is NULL? I assumed, up to now, it was FORMS!... that were, but if the field name "calculatedFromDate" or "calculatedToDate" is the potential NULL value, then, indeed, you have to "decorate" them with a Nz too: WHERE ( Nz(calculatedFromDate, Date() ) BETWEEN ... AND ... ) AND ( Nz(calculatedToDate, Date() ) BETWEEN ... AND ... ) Hoping it may help, Vanderghast, Access MVP wrote in message ... I completely understand and appreciate your suggestion. It makes sense to me. However, when I use the code so it appears as: WHERE ((([calculated dates].calculatedFromDate) Between Nz (Nz([Forms]![range]![start],[Forms]![range]![end]),Date ()) And Nz(Nz([Forms]![range]![end],[Forms]![range]! [start]),Date())) AND (([calculated dates].calculatedToDate) Between Nz(Nz([Forms]![range]! [start],[Forms]![range]![end]),Date()) And Nz(Nz ([Forms]! [range]![end],[Forms]![range]![start]),Date()))); it still doesn't return any values when the start date and end date are both entered as null. It also doesn't return any dates when the start date is null and the end date is entered. Did I interperet your advice (which again I truly appreciate)? -----Original Message----- Hi, It as a much simpler look. :-) Admit it, it is a nice start... but more seriously... Your code seems to fail (accordingly to what you specify) if start IS NULL (and so, I assume, in this case, end field is also a null). Your code is somewhat equivalent to: fromTested = Nz(start, date() ) AND fromTested = Nz(end, start) AND upToTested = Nz(start, date() ) AND upToTested = Nz(end, start) Watch the second and fourth clauses, when start is null, and probably end is also null, once the iif (or Nz) are evaluated, that gives us: fromTested = date() AND fromTested = NULL AND upToTested = date() AND upToTested = NULL which may evaluate, finally, to NULL, or to false (but NEVER to TRUE). The proposed code also replace those NULL by today date, in this scenario (end IS NULL, start IS NULL). The proposed code is a little bit like rewriting the fourth clause to: upToTested = Nz( Nz(end, start) , Date( ) ) as example (and also the second clause should be submitted to the same modification). The proposed code also looks for the possible (is it?) case where start is null and end is not, it then uses end (first and third clause). Hoping it may help, Vanderghast, Access MVP "brigid" wrote in message ... How is this any different than the current code? -----Original Message----- Hi, .... WHERE myField BETWEEN Nz( Nz( StartDate, EndDate), date() ) AND Nz( Nz( EndDate, StartDate), date() ) Hoping it may help, Vanderghast, Access MVP "brigid" wrote in message ... The user inputs a start date and an end date in a form, and the query displays data in between those 2 dates. If the user enters a start date but no end date, by default the end date = start date and therefore that day's data is displayed. If the user enters an end date but no start date, by default the start date = today's date, and therefore data for today's date through the end date is displayed. However, if the user leaves both dates blank, I would like both dates to default to today's date. However, no data whatsoever is being displayed. I don't understand why because the start date should be defaulted to today's, and when the end date is null it looks to the start date. The code is as follows: SELECT [calculated dates].calculatedFromDate, [calculated dates].calculatedToDate, [calculated dates]. [Blotter Entry Date] FROM [calculated dates] WHERE ((([calculated dates].calculatedFromDate) =IIf ([Forms]![range]![start] Is Null,Date(),[Forms]! [range]! [start]) And ([calculated dates].calculatedFromDate) =IIf ([Forms]![range]![end] Is Null,[Forms]![range]! [start], [Forms]![range]![end])) AND (([calculated dates].calculatedToDate)=IIf([Forms]![range]! [start] Is Null,Date(),[Forms]![range]![start]) And ([calculated dates].calculatedToDate)=IIf([Forms]![range]! [end] Is Null,[Forms]![range]![start],[Forms]![range]! [end]))); . . . |
#6
|
|||
|
|||
more dates!!!
HI,
In that case, I do not see any other solution than to temporary remove the WHERE clause, and push the whole expression in the SELECT clause. Once that is done, add the criteria: IS NULL under that expression and run the so modified query. At that point, try to see why there are records where the computed expression evaluates to NULL... Unless calculatedFromDate or calculatedToDate is NULL, I fail to see why the computed expression could return NULL. SELECT [calculated dates].calculatedFromDate, [calculated dates].calculatedToDate FROM ... WHERE ((([calculated dates].calculatedFromDate) Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date())) AND (([calculated dates].calculatedToDate) Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date())) ) IS NULL If the problem is not visible, break the computed expression in its four part, as four "SELECT" computed expression: SELECT [calculated dates].calculatedFromDate, Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()), Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()), [calculated dates].calculatedToDate, Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()), Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) FROM ... WHERE Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) IS NULL OR Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) IS NULL OR Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) IS NULL OR Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) IS NULL ( or use the same WHERE clause as previously). Since the NULL has to come from somewhere, it has to come from calculatedFromDate or from calculatedToDate, since the double Nz should definitively supply a not null value, Date() cannot be null. Alternatively, there is no null, and the problem is something else, like having a calculatedFromDate AFTER the calculatedToDate ! or anything we assumed, and that we didn't think about... but with the data in front of you, hopefully, that would be more evident... Vanderghast, Access MVP |
#7
|
|||
|
|||
more dates!!!
Hi,
There is another possibility that should have been mentioned earlier. If your fields have a date AND a time, then, comparing them to Date() implicitly means at 00:00:00. So, for the upper bound, we must have to add 23:59:59 (or 1 full day, for all practical purposes). So, use 1+Date() rather than just Date(), in that case. WHERE ((([calculated dates].calculatedFromDate) Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),1+Date())) AND (([calculated dates].calculatedToDate) Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),1+Date())) ) Hoping it may help, Vanderghast, Access MVP "Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message ... HI, In that case, I do not see any other solution than to temporary remove the WHERE clause, and push the whole expression in the SELECT clause. Once that is done, add the criteria: IS NULL under that expression and run the so modified query. At that point, try to see why there are records where the computed expression evaluates to NULL... Unless calculatedFromDate or calculatedToDate is NULL, I fail to see why the computed expression could return NULL. SELECT [calculated dates].calculatedFromDate, [calculated dates].calculatedToDate FROM ... WHERE ((([calculated dates].calculatedFromDate) Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date())) AND (([calculated dates].calculatedToDate) Between Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) And Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date())) ) IS NULL If the problem is not visible, break the computed expression in its four part, as four "SELECT" computed expression: SELECT [calculated dates].calculatedFromDate, Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()), Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()), [calculated dates].calculatedToDate, Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()), Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) FROM ... WHERE Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) IS NULL OR Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) IS NULL OR Nz(Nz([Forms]![range]![start],[Forms]![range]![end]),Date()) IS NULL OR Nz(Nz([Forms]![range]![end],[Forms]![range]![start]),Date()) IS NULL ( or use the same WHERE clause as previously). Since the NULL has to come from somewhere, it has to come from calculatedFromDate or from calculatedToDate, since the double Nz should definitively supply a not null value, Date() cannot be null. Alternatively, there is no null, and the problem is something else, like having a calculatedFromDate AFTER the calculatedToDate ! or anything we assumed, and that we didn't think about... but with the data in front of you, hopefully, that would be more evident... Vanderghast, Access MVP |
Thread Tools | |
Display Modes | |
|
|