RE: [MS_AccessPros] Expression in query question

 

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: "Graham Mandeno" <graham@mandeno.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___