Hello Jim
The reason your second one works is that you are using "Between" instead of ">=".
Your expression using ">=" will never work with "And", no matter what dates you supply – if it does return any records, they won't be the ones you expect. The reason is that "And" performs two different functions in a WHERE clause, depending on context. Usually, it is a boolean operator which assumes the expressions on either side are boolean (True/False) values. Dates are meaningless as boolean values (are they True or False?), so it is also meaningless to put them together with "And" to see is they are both True. The height of nonsense occurs when you take your date field and ask, "Is it greater than this meaningless thing I have created?" Probably, SQ should really give you a "Type mismatch" error for attempting to use such an expression, but instead it valiantly soldiers on and does its best with the parameters it's given.
"And" takes on a whole new meaning when it is in the form "Between expression-1 And expression-2". In this form, it is shorthand for:
([fieldname] >= expression-1) And ([fieldname] <= expression-2)
That is why your expression works with "Between", but not with ">=".
Try this:
Between (Date()-30) And (Date()-Weekday(Date())+6)
This will give you all records where EffDt is in the range from 30 days ago to the Friday of the current week.
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, 27 September 2017 07:12
To: luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Expression in query question
This works. I guess I will use this instead
Between Date()-30 And Date()+6
Jim Wagner
On Tuesday, September 26, 2017 10:48:43 AM, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hello all,
I have the following expression in a query on Effdt.
>=(Date()-30) And (Date()-Weekday(Date())+6)
It stopped working. I get no records. I remove the criteria and I get records. then I use the between 30 days back and 6 days from todays date and I get records. So I am not sure why it would not work. Is this the correct syntax?
Thank You for your help.
Jim Wagner
Posted by: Jim Wagner <luvmymelody@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar