Query Date Problem

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

Secret Squirrel

I have the following query trying to calculate a date. I'm trying to say if
the current date is greater than the "8MA" date in my query then it should
equal "1" and if not then it should equal "0". But they are all coming out as
1 even when they shouldn't be. Am I missing something here?

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.StartDate, GetBusinessDay(DateAdd("m",8,[StartDate]),1) AS 8MA,
IIf(Date()>[8MA],1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
 
Secret said:
I have the following query trying to calculate a date. I'm trying to
say if the current date is greater than the "8MA" date in my query
then it should equal "1" and if not then it should equal "0". But
they are all coming out as 1 even when they shouldn't be. Am I
missing something here?

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.StartDate, GetBusinessDay(DateAdd("m",8,[StartDate]),1)
AS 8MA, IIf(Date()>[8MA],1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...

I don't think you can directly reference another field in your query if that
field is derived. Try...

SELECT tblEmployees.ID,
tblEmployees.LastName,
tblEmployees.FirstName,
tblEmployees.StartDate,
GetBusinessDay(DateAdd("m",8,[StartDate]),1) AS 8MA,
IIf(Date()> GetBusinessDay(DateAdd("m",8,[StartDate]),1),1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
 
Thank you Rick! That worked. I wasn't aware that you couldn't reference a
field like that.

Rick Brandt said:
Secret said:
I have the following query trying to calculate a date. I'm trying to
say if the current date is greater than the "8MA" date in my query
then it should equal "1" and if not then it should equal "0". But
they are all coming out as 1 even when they shouldn't be. Am I
missing something here?

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.StartDate, GetBusinessDay(DateAdd("m",8,[StartDate]),1)
AS 8MA, IIf(Date()>[8MA],1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...

I don't think you can directly reference another field in your query if that
field is derived. Try...

SELECT tblEmployees.ID,
tblEmployees.LastName,
tblEmployees.FirstName,
tblEmployees.StartDate,
GetBusinessDay(DateAdd("m",8,[StartDate]),1) AS 8MA,
IIf(Date()> GetBusinessDay(DateAdd("m",8,[StartDate]),1),1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...
 
Rick said:
Secret said:
I have the following query trying to calculate a date. I'm trying to
say if the current date is greater than the "8MA" date in my query
then it should equal "1" and if not then it should equal "0". But
they are all coming out as 1 even when they shouldn't be. Am I
missing something here?

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.StartDate, GetBusinessDay(DateAdd("m",8,[StartDate]),1)
AS 8MA, IIf(Date()>[8MA],1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...


I don't think you can directly reference another field in your query if that
field is derived. Try...

SELECT tblEmployees.ID,
tblEmployees.LastName,
tblEmployees.FirstName,
tblEmployees.StartDate,
GetBusinessDay(DateAdd("m",8,[StartDate]),1) AS 8MA,
IIf(Date()> GetBusinessDay(DateAdd("m",8,[StartDate]),1),1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...

I reference a "field" like that all the time in A97 through A2K3 (but
not in, say, the WHERE section) so I decided to test SS's example. I
created a tblEmployees with SS's fields and tried to run the original
query after creating a public function in a module called
GetBusinessDay() that returns a date. It worked perfectly. I even got
both 1's and 0's. Either SS is using A2K7 and somehow it has changed
the way Access queries work or something else I don't see is going on.
I guess I'm the one who should be saying, "Am I missing something here?"

James A. Fortune
(e-mail address removed)
 
James said:
Rick said:
Secret said:
I have the following query trying to calculate a date. I'm trying
to
say if the current date is greater than the "8MA" date in my query
then it should equal "1" and if not then it should equal "0". But
they are all coming out as 1 even when they shouldn't be. Am I
missing something here?

SELECT tblEmployees.ID, tblEmployees.LastName,
tblEmployees.FirstName,
tblEmployees.StartDate,
GetBusinessDay(DateAdd("m",8,[StartDate]),1)
AS 8MA, IIf(Date()>[8MA],1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...


I don't think you can directly reference another field in your
query if that field is derived. Try...

SELECT tblEmployees.ID,
tblEmployees.LastName,
tblEmployees.FirstName,
tblEmployees.StartDate,
GetBusinessDay(DateAdd("m",8,[StartDate]),1) AS 8MA,
IIf(Date()>
GetBusinessDay(DateAdd("m",8,[StartDate]),1),1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...

I reference a "field" like that all the time in A97 through A2K3 (but
not in, say, the WHERE section) so I decided to test SS's example. I
created a tblEmployees with SS's fields and tried to run the original
query after creating a public function in a module called
GetBusinessDay() that returns a date. It worked perfectly. I even
got both 1's and 0's. Either SS is using A2K7 and somehow it has
changed the way Access queries work or something else I don't see is
going on. I guess I'm the one who should be saying, "Am I missing
something here?"

James A. Fortune
(e-mail address removed)
Click to expand...

I use it also in a2k7 with no problem, I tried just now something
along the lines of the original post, both in mdb and accdb with no
problem.

I think it is supposed to work like that - as long as the alias is
defined prior to it's usage - i e earlier in the SELECT clause, one
should be able to refer to it later in the SELECT clause.

But, since (if I understand correct), the SELECT clause is among the
last, if not the last, clause to be evaluaeted by the Jet engine,
the alias cannot be referred to in other clauses.

Fortunately, one can use ordinal position in the ORDER BY clause.
 
James,
I'm using A2003. There really isn't more to my query other than what you
read. So...why would it work for you using my original design? I guess I'm
confused now.

James A. Fortune said:
Rick said:
Secret said:
I have the following query trying to calculate a date. I'm trying to
say if the current date is greater than the "8MA" date in my query
then it should equal "1" and if not then it should equal "0". But
they are all coming out as 1 even when they shouldn't be. Am I
missing something here?

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.StartDate, GetBusinessDay(DateAdd("m",8,[StartDate]),1)
AS 8MA, IIf(Date()>[8MA],1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...


I don't think you can directly reference another field in your query if that
field is derived. Try...

SELECT tblEmployees.ID,
tblEmployees.LastName,
tblEmployees.FirstName,
tblEmployees.StartDate,
GetBusinessDay(DateAdd("m",8,[StartDate]),1) AS 8MA,
IIf(Date()> GetBusinessDay(DateAdd("m",8,[StartDate]),1),1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...

I reference a "field" like that all the time in A97 through A2K3 (but
not in, say, the WHERE section) so I decided to test SS's example. I
created a tblEmployees with SS's fields and tried to run the original
query after creating a public function in a module called
GetBusinessDay() that returns a date. It worked perfectly. I even got
both 1's and 0's. Either SS is using A2K7 and somehow it has changed
the way Access queries work or something else I don't see is going on.
I guess I'm the one who should be saying, "Am I missing something here?"

James A. Fortune
(e-mail address removed)
Click to expand...
 
RoyVidar said:
James said:
Rick said:
Secret Squirrel wrote:

I have the following query trying to calculate a date. I'm trying to
say if the current date is greater than the "8MA" date in my query
then it should equal "1" and if not then it should equal "0". But
they are all coming out as 1 even when they shouldn't be. Am I
missing something here?

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.StartDate, GetBusinessDay(DateAdd("m",8,[StartDate]),1)
AS 8MA, IIf(Date()>[8MA],1,0) AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;



I don't think you can directly reference another field in your query
if that field is derived. Try...

SELECT tblEmployees.ID,
tblEmployees.LastName,
tblEmployees.FirstName,
tblEmployees.StartDate,
GetBusinessDay(DateAdd("m",8,[StartDate]),1) AS 8MA,
IIf(Date()> GetBusinessDay(DateAdd("m",8,[StartDate]),1),1,0)
AS 8M
FROM tblEmployees
ORDER BY tblEmployees.StartDate;
Click to expand...

I reference a "field" like that all the time in A97 through A2K3 (but
not in, say, the WHERE section) so I decided to test SS's example. I
created a tblEmployees with SS's fields and tried to run the original
query after creating a public function in a module called
GetBusinessDay() that returns a date. It worked perfectly. I even
got both 1's and 0's. Either SS is using A2K7 and somehow it has
changed the way Access queries work or something else I don't see is
going on. I guess I'm the one who should be saying, "Am I missing
something here?"

James A. Fortune
(e-mail address removed)
Click to expand...


I use it also in a2k7 with no problem, I tried just now something
along the lines of the original post, both in mdb and accdb with no
problem.
Click to expand...

That's good to know. Thanks.
I think it is supposed to work like that - as long as the alias is
defined prior to it's usage - i e earlier in the SELECT clause, one
should be able to refer to it later in the SELECT clause.
Click to expand...

I just tried another example and the order in the SELECT part didn't
seem to matter, although I like to order them that way (i.e., define
before use) just to keep things organized.
But, since (if I understand correct), the SELECT clause is among the
last, if not the last, clause to be evaluaeted by the Jet engine,
the alias cannot be referred to in other clauses.
Click to expand...

I don't know enough about JET's inner workings to know if that theory is
correct or not -- but it sounds good :-).

James A. Fortune
(e-mail address removed)
 
Secret said:
James,
I'm using A2003. There really isn't more to my query other than what you
read. So...why would it work for you using my original design? I guess I'm
confused now.
Click to expand...

I admit that I'm also confused now about why your original design didn't
work. Maybe I shouldn't have said anything :-).

James A. Fortune
(e-mail address removed)
 
As long as it works in the end is all that matters I guess. Weird that it
worked for you and not me. That's still very interesting to me.
 
Back
Top