Date()-1

[Guest]

Access 2003 on an XP SP2 machine, Access 2000 or 2003 format database.

Trying to establish period queries, but using the =Date()-1 (or any other
combo of Date()+ or-) as criteria returns an empty recordset. Only one table
involved.

Sample:
SELECT tblData.Received AS [Time In], tblData!GuestFirstName & " " &
tblData!GuestLastName AS Patron, tblData!GuestStreet1 & " " &
tblData!GuestHomeCity & ", " & tblData!GuestHomeState & " " &
tblData!GuestZip AS Address, tblData!GuestPhone AS Phone, tblData.GuestEmail
AS EMail, "Visited the " & tblData!Restaurant & " store and had a " &
tblData!Category & " about " & tblData!Subcategory & "." AS Verbiage,
tblData.Comments
FROM tblData
WHERE (((tblData.Received)=Date()-1))
ORDER BY tblData.Received;

Shaved my head, so when I start pulling hair it will be eyebrows.

 

[Allen Browne]

Is there any chance that the Received field may contain a time component as
well as a date? Typically this happens if its Default Value is Now() instead
of Date().

If so, try:
WHERE (tblData.Received >= Date() - 1) And (tblData.Received < Date())

If you open tblData in design view, is Received a date/time field? Or is it
Text?

If that doesn't solve the problem, perhaps there is a problem with
references:
http://allenbrowne.com/ser-38.html

I doubt it will end up being the data type not recognised:
http://allenbrowne.com/ser-45.html

 

[Guest]

Allen:
Thanks for your prompt reply. Good info in thos articles.

My date field doesn't contain a time component, and uses Date() as the
default value. Received is a date\time field. All the references are in
place, and the proper versions. Wasn't the data type not recognized issue.

I encounter the same problem on by my desktop and laptop, each Access 2003,
XP SP2.

Just for grins, I created a form with a textbox control on it, set its
default value to Date()-1, -7, +10, etc., and that works correctly. So it
appears Access recognized the Date() function and utilizes it properly.

Any other ideas where to look?

One eyebrow down and one to go!
--
John Cello
John Cello Consulting
Helping organizations hire, develop, and retain their best employees.
www.johncelloconsulting.com

Is there any chance that the Received field may contain a time component as
well as a date? Typically this happens if its Default Value is Now() instead
of Date().

If so, try:
WHERE (tblData.Received >= Date() - 1) And (tblData.Received < Date())

If you open tblData in design view, is Received a date/time field? Or is it
Text?

If that doesn't solve the problem, perhaps there is a problem with
references:
http://allenbrowne.com/ser-38.html

I doubt it will end up being the data type not recognised:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Access 2003 on an XP SP2 machine, Access 2000 or 2003 format database.

Trying to establish period queries, but using the =Date()-1 (or any other
combo of Date()+ or-) as criteria returns an empty recordset. Only one
table
involved.

Sample:
SELECT tblData.Received AS [Time In], tblData!GuestFirstName & " " &
tblData!GuestLastName AS Patron, tblData!GuestStreet1 & " " &
tblData!GuestHomeCity & ", " & tblData!GuestHomeState & " " &
tblData!GuestZip AS Address, tblData!GuestPhone AS Phone,
tblData.GuestEmail
AS EMail, "Visited the " & tblData!Restaurant & " store and had a " &
tblData!Category & " about " & tblData!Subcategory & "." AS Verbiage,
tblData.Comments
FROM tblData
WHERE (((tblData.Received)=Date()-1))
ORDER BY tblData.Received;

Shaved my head, so when I start pulling hair it will be eyebrows.

--
John Cello
John Cello Consulting
Helping organizations hire, develop, and retain their best employees.
www.johncelloconsulting.com

 

[Allen Browne]

Try DateAdd() instead of just -1.

If there are other things in the query too, it might be sandbox mode:
http://office.microsoft.com/assista...3&CTT=8&Origin=EC011081751033&Product=acc2003

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:
Thanks for your prompt reply. Good info in thos articles.

My date field doesn't contain a time component, and uses Date() as the
default value. Received is a date\time field. All the references are in
place, and the proper versions. Wasn't the data type not recognized issue.

I encounter the same problem on by my desktop and laptop, each Access
2003,
XP SP2.

Just for grins, I created a form with a textbox control on it, set its
default value to Date()-1, -7, +10, etc., and that works correctly. So it
appears Access recognized the Date() function and utilizes it properly.

Any other ideas where to look?

One eyebrow down and one to go!
--
John Cello
John Cello Consulting
Helping organizations hire, develop, and retain their best employees.
www.johncelloconsulting.com

Is there any chance that the Received field may contain a time component
as
well as a date? Typically this happens if its Default Value is Now()
instead
of Date().

If so, try:
WHERE (tblData.Received >= Date() - 1) And (tblData.Received <
Date())

If you open tblData in design view, is Received a date/time field? Or is
it
Text?

If that doesn't solve the problem, perhaps there is a problem with
references:
http://allenbrowne.com/ser-38.html

I doubt it will end up being the data type not recognised:
http://allenbrowne.com/ser-45.html

Access 2003 on an XP SP2 machine, Access 2000 or 2003 format database.

Trying to establish period queries, but using the =Date()-1 (or any
other
combo of Date()+ or-) as criteria returns an empty recordset. Only one
table
involved.

Sample:
SELECT tblData.Received AS [Time In], tblData!GuestFirstName & " " &
tblData!GuestLastName AS Patron, tblData!GuestStreet1 & " " &
tblData!GuestHomeCity & ", " & tblData!GuestHomeState & " " &
tblData!GuestZip AS Address, tblData!GuestPhone AS Phone,
tblData.GuestEmail
AS EMail, "Visited the " & tblData!Restaurant & " store and had a " &
tblData!Category & " about " & tblData!Subcategory & "." AS Verbiage,
tblData.Comments
FROM tblData
WHERE (((tblData.Received)=Date()-1))
ORDER BY tblData.Received;

Shaved my head, so when I start pulling hair it will be eyebrows.

--
John Cello
John Cello Consulting
Helping organizations hire, develop, and retain their best employees.
www.johncelloconsulting.com

 

[Guest]

Query is as in the SQL statement. One table, requesting 2 fields, only
criteria is Date()-1, single table not linked to any others. Not in Sandbox
mode. DateAdd doesn't work either. (Yes, there are records that should be
returned.)

Kind of mystified about the Date()-1 working in a textbox on a form but not
as criteria in a query.
--
John Cello
John Cello Consulting
Helping organizations hire, develop, and retain their best employees.
www.johncelloconsulting.com

Try DateAdd() instead of just -1.

If there are other things in the query too, it might be sandbox mode:
http://office.microsoft.com/assista...3&CTT=8&Origin=EC011081751033&Product=acc2003

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:
Thanks for your prompt reply. Good info in thos articles.

My date field doesn't contain a time component, and uses Date() as the
default value. Received is a date\time field. All the references are in
place, and the proper versions. Wasn't the data type not recognized issue.

I encounter the same problem on by my desktop and laptop, each Access
2003,
XP SP2.

Just for grins, I created a form with a textbox control on it, set its
default value to Date()-1, -7, +10, etc., and that works correctly. So it
appears Access recognized the Date() function and utilizes it properly.

Any other ideas where to look?

One eyebrow down and one to go!
--
John Cello
John Cello Consulting
Helping organizations hire, develop, and retain their best employees.
www.johncelloconsulting.com

Is there any chance that the Received field may contain a time component
as
well as a date? Typically this happens if its Default Value is Now()
instead
of Date().

If so, try:
WHERE (tblData.Received >= Date() - 1) And (tblData.Received <
Date())

If you open tblData in design view, is Received a date/time field? Or is
it
Text?

If that doesn't solve the problem, perhaps there is a problem with
references:
http://allenbrowne.com/ser-38.html

I doubt it will end up being the data type not recognised:
http://allenbrowne.com/ser-45.html

Access 2003 on an XP SP2 machine, Access 2000 or 2003 format database.

Trying to establish period queries, but using the =Date()-1 (or any
other
combo of Date()+ or-) as criteria returns an empty recordset. Only one
table
involved.

Sample:
SELECT tblData.Received AS [Time In], tblData!GuestFirstName & " " &
tblData!GuestLastName AS Patron, tblData!GuestStreet1 & " " &
tblData!GuestHomeCity & ", " & tblData!GuestHomeState & " " &
tblData!GuestZip AS Address, tblData!GuestPhone AS Phone,
tblData.GuestEmail
AS EMail, "Visited the " & tblData!Restaurant & " store and had a " &
tblData!Category & " about " & tblData!Subcategory & "." AS Verbiage,
tblData.Comments
FROM tblData
WHERE (((tblData.Received)=Date()-1))
ORDER BY tblData.Received;

Shaved my head, so when I start pulling hair it will be eyebrows.

--
John Cello
John Cello Consulting
Helping organizations hire, develop, and retain their best employees.
www.johncelloconsulting.com

 

[Allen Browne]

Perhaps you can ask Access to inform you about what's going on, e.g.:
SELECT Date() - 1 AS Yesterday, ...

If that works, notice whether Access left-aligns the result (like text) or
right-aligns it (like a date).

Some experimenting like that, and perhaps with TypeName() might give some
clues.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Query is as in the SQL statement. One table, requesting 2 fields, only
criteria is Date()-1, single table not linked to any others. Not in
Sandbox
mode. DateAdd doesn't work either. (Yes, there are records that should be
returned.)

Kind of mystified about the Date()-1 working in a textbox on a form but
not
as criteria in a query.
--
John Cello
John Cello Consulting
Helping organizations hire, develop, and retain their best employees.
www.johncelloconsulting.com

Try DateAdd() instead of just -1.

If there are other things in the query too, it might be sandbox mode:
http://office.microsoft.com/assista...3&CTT=8&Origin=EC011081751033&Product=acc2003

Allen:
Thanks for your prompt reply. Good info in thos articles.

My date field doesn't contain a time component, and uses Date() as the
default value. Received is a date\time field. All the references are in
place, and the proper versions. Wasn't the data type not recognized
issue.

I encounter the same problem on by my desktop and laptop, each Access
2003,
XP SP2.

Just for grins, I created a form with a textbox control on it, set its
default value to Date()-1, -7, +10, etc., and that works correctly. So
it
appears Access recognized the Date() function and utilizes it properly.

Any other ideas where to look?

One eyebrow down and one to go!
--
John Cello
John Cello Consulting
Helping organizations hire, develop, and retain their best employees.
www.johncelloconsulting.com


:

Is there any chance that the Received field may contain a time
component
as
well as a date? Typically this happens if its Default Value is Now()
instead
of Date().

If so, try:
WHERE (tblData.Received >= Date() - 1) And (tblData.Received <
Date())

If you open tblData in design view, is Received a date/time field? Or
is
it
Text?

If that doesn't solve the problem, perhaps there is a problem with
references:
http://allenbrowne.com/ser-38.html

I doubt it will end up being the data type not recognised:
http://allenbrowne.com/ser-45.html

message
Access 2003 on an XP SP2 machine, Access 2000 or 2003 format
database.

Trying to establish period queries, but using the =Date()-1 (or any
other
combo of Date()+ or-) as criteria returns an empty recordset. Only
one
table
involved.

Sample:
SELECT tblData.Received AS [Time In], tblData!GuestFirstName & " " &
tblData!GuestLastName AS Patron, tblData!GuestStreet1 & " " &
tblData!GuestHomeCity & ", " & tblData!GuestHomeState & " " &
tblData!GuestZip AS Address, tblData!GuestPhone AS Phone,
tblData.GuestEmail
AS EMail, "Visited the " & tblData!Restaurant & " store and had a "
&
tblData!Category & " about " & tblData!Subcategory & "." AS
Verbiage,
tblData.Comments
FROM tblData
WHERE (((tblData.Received)=Date()-1))
ORDER BY tblData.Received;

Shaved my head, so when I start pulling hair it will be eyebrows.

 

[Gary Walter]

You don't say if you at least tried Allen's

WHERE (tblData.Received >= Date() - 1) And (tblData.Received < Date())

nor if return records w/o WHERE clause

just to be sure.....

 

[Gary Walter]

You don't say if you at least tried Allen's

WHERE (tblData.Received >= Date() - 1) And (tblData.Received < Date())

nor if return records w/o WHERE clause

just to be sure.....

nor if tried date literal and got records

WHERE tblData.Received = #1/28/2007#

 

[Guest]

Here's what I did to finally get this working:
Deleted the query
Compacted the database on exit
Unregistered the references in Access and regsvr32
Rebooted
Registered the references in Access and regsvr32
Rebooted
Recreated the query

Success

Thanks for all of your help. I'd have never thought about the references.