Query not returning all values

[Guest]

I need to redefine the date of entries to match a 24 hour schedule that
starts and ends at 06:00 each day instead of 00:00.

I haven't been able to find anything related in Access help files or the
internet. So, I created a query with an IIf() statement that defines
time<06:00 as the previous day ( NewDate: IIf(Hour([time])<6, [Date]-1,
[Date]) ).

This query works just fine unless I try to confine the results to a specific
date using criteria ( [Enter a date], or Between [Start Date] And [End
Date], or even Date()-1 ). Once I define criteria on the field, it only
returns times between 06:00 and 11:59.

Could anyone tell me how to fix this, or if there is a better way to
accomplish this?

 

[Guest]

You should not use a field named 'Date' as it is a reserved word in Access.
But to your problem, in the design view grid of the query add a new field
like this ---
Date_OffSet: DateAdd("h",-6, [Date])
Apply you criteria to this field.

 

[Guest]

OK. I changed the field name to 'Day'. I've taught myself Access recently.
So, I don't really know about accepted conventions per se.

I tried your approach, but now it doesn't return anything when criteria is
added.

One issue that you might need to be aware of is that the date and time
fields are separate. There isn't really a good way to get around that for my
application. So, my field looks like this: Date_OffSet:
DateAdd("h",-6,DateAdd("h",Hour([time]),[Day])).

You should not use a field named 'Date' as it is a reserved word in Access.
But to your problem, in the design view grid of the query add a new field
like this ---
Date_OffSet: DateAdd("h",-6, [Date])
Apply you criteria to this field.

--
KARL DEWEY
Build a little - Test a little

I need to redefine the date of entries to match a 24 hour schedule that
starts and ends at 06:00 each day instead of 00:00.

I haven't been able to find anything related in Access help files or the
internet. So, I created a query with an IIf() statement that defines
time<06:00 as the previous day ( NewDate: IIf(Hour([time])<6, [Date]-1,
[Date]) ).

This query works just fine unless I try to confine the results to a specific
date using criteria ( [Enter a date], or Between [Start Date] And [End
Date], or even Date()-1 ). Once I define criteria on the field, it only
returns times between 06:00 and 11:59.

Could anyone tell me how to fix this, or if there is a better way to
accomplish this?

 

[Guest]

If both fields are DateTime datatype then you need to add them together. By
the way Time is a reserved word also.
Test adding the two fields like this --
My_Date_Time: [Day]+[Time]
See if it displays the correct date and time. If it is, then use this ---
Date_OffSet: DateAdd("h",-6, [Day]+[Time])

--
KARL DEWEY
Build a little - Test a little

OK. I changed the field name to 'Day'. I've taught myself Access recently.
So, I don't really know about accepted conventions per se.

I tried your approach, but now it doesn't return anything when criteria is
added.

One issue that you might need to be aware of is that the date and time
fields are separate. There isn't really a good way to get around that for my
application. So, my field looks like this: Date_OffSet:
DateAdd("h",-6,DateAdd("h",Hour([time]),[Day])).

You should not use a field named 'Date' as it is a reserved word in Access.
But to your problem, in the design view grid of the query add a new field
like this ---
Date_OffSet: DateAdd("h",-6, [Date])
Apply you criteria to this field.

--
KARL DEWEY
Build a little - Test a little

I need to redefine the date of entries to match a 24 hour schedule that
starts and ends at 06:00 each day instead of 00:00.

I haven't been able to find anything related in Access help files or the
internet. So, I created a query with an IIf() statement that defines
time<06:00 as the previous day ( NewDate: IIf(Hour([time])<6, [Date]-1,
[Date]) ).

This query works just fine unless I try to confine the results to a specific
date using criteria ( [Enter a date], or Between [Start Date] And [End
Date], or even Date()-1 ). Once I define criteria on the field, it only
returns times between 06:00 and 11:59.

Could anyone tell me how to fix this, or if there is a better way to
accomplish this?

 

[Guest]

I had a suspicion that "time" would be a reserved word also. So, I've now
changed the fields to "day" and "hour" respectively.

I appreciate your efforts, but unfortunately that still doesn't help. I now
have multiple options for shifting the date, and they all work (the necessary
date is returned) as long as no criteria is used. The problem is that when I
try to query that field based on a criteria (or add a criteria directly to
the field), some or all of the data is missing. The closest I've come was
with my original approach, but close doesn't cut it.

I've changed field names to avoid reserved words. I even created a small
test database from scratch to make sure that I was isolating the issue
completely. It seems that no matter what I do, if I offset the date in any
way and query the offset date, the query fails at least partially.

I can only imagine that I have come across a bug in the software, but I
don't have any way to report that.

I really do appreciate your efforts, but at this point, I'm not sure that
the fault lies with the method.

 

[Guest]

We try a little more.
Post a sample of your data and the SQL of your query. To post the SQL open
the query in design view, click on menu VIEW - SQL View, highlight all, copy
and paste in a post.
--
KARL DEWEY
Build a little - Test a little

I had a suspicion that "time" would be a reserved word also. So, I've now
changed the fields to "day" and "hour" respectively.

I appreciate your efforts, but unfortunately that still doesn't help. I now
have multiple options for shifting the date, and they all work (the necessary
date is returned) as long as no criteria is used. The problem is that when I
try to query that field based on a criteria (or add a criteria directly to
the field), some or all of the data is missing. The closest I've come was
with my original approach, but close doesn't cut it.

I've changed field names to avoid reserved words. I even created a small
test database from scratch to make sure that I was isolating the issue
completely. It seems that no matter what I do, if I offset the date in any
way and query the offset date, the query fails at least partially.

I can only imagine that I have come across a bug in the software, but I
don't have any way to report that.

I really do appreciate your efforts, but at this point, I'm not sure that
the fault lies with the method.

If both fields are DateTime datatype then you need to add them together. By
the way Time is a reserved word also.
Test adding the two fields like this --
My_Date_Time: [Day]+[Time]
See if it displays the correct date and time. If it is, then use this ---
Date_OffSet: DateAdd("h",-6, [Day]+[Time])

 

[Guest]

Notes Table
Day Hour Notes
7/2/07 15:06 sd
7/2/07 2:00 sdf
7/3/07 2:00 sdf
7/2/07 8:00 sdfg
7/2/07 9:00 sdfh
7/3/07 4:00 a;skgl
7/2/07 15:07 315
7/3/07 15:08 xvdfb

Notes FullDate Query
SELECT [day]+[hour] AS FullDate, [Notes Table].*
FROM [Notes Table]
ORDER BY [day]+[hour];

Notes Date_OffSet Query #this works
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query];

Notes Date_OffSet Query #with criteria it doesn't
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((DateAdd("h",-6,[FullDate]))=[Enter a date]));

 

[Guest]

Try it this way --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=CVDate([Enter a date]);

If this does not work then back up a little with this --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=#7/22/2007#;
Fill in you actual date criteria instead of 7/22/2007 and check it.
--
KARL DEWEY
Build a little - Test a little

Notes Table
Day Hour Notes
7/2/07 15:06 sd
7/2/07 2:00 sdf
7/3/07 2:00 sdf
7/2/07 8:00 sdfg
7/2/07 9:00 sdfh
7/3/07 4:00 a;skgl
7/2/07 15:07 315
7/3/07 15:08 xvdfb

Notes FullDate Query
SELECT [day]+[hour] AS FullDate, [Notes Table].*
FROM [Notes Table]
ORDER BY [day]+[hour];

Notes Date_OffSet Query #this works
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query];

Notes Date_OffSet Query #with criteria it doesn't
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((DateAdd("h",-6,[FullDate]))=[Enter a date]));

We try a little more.
Post a sample of your data and the SQL of your query. To post the SQL open
the query in design view, click on menu VIEW - SQL View, highlight all, copy
and paste in a post.

 

[Guest]

Once again, no luck. I had already tried an explicit date criteria, but I
tried it again without success. The CVDate() function doesn't seem to
benefit either.

 

[David Cox]

I would think that [enter a date] 1/1 2000 gets resolved as 1/1/2000
00:00:00, and I doubt that matches many real date/times.

My guess is Format ( yada-yada, "mm/dd/yyyy") on boths sides of the
comparison should do it.

Notes Table
Day Hour Notes
7/2/07 15:06 sd
7/2/07 2:00 sdf
7/3/07 2:00 sdf
7/2/07 8:00 sdfg
7/2/07 9:00 sdfh
7/3/07 4:00 a;skgl
7/2/07 15:07 315
7/3/07 15:08 xvdfb

Notes FullDate Query
SELECT [day]+[hour] AS FullDate, [Notes Table].*
FROM [Notes Table]
ORDER BY [day]+[hour];

Notes Date_OffSet Query #this works
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query];

Notes Date_OffSet Query #with criteria it doesn't
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((DateAdd("h",-6,[FullDate]))=[Enter a date]));

We try a little more.
Post a sample of your data and the SQL of your query. To post the SQL
open
the query in design view, click on menu VIEW - SQL View, highlight all,
copy
and paste in a post.

 

[Guest]

David Cox had the answer. Must strip the time from the adjusted date.
SELECT CVDate(Int(DateAdd("h",-6,[FullDate]))) AS Date_OffSet, [Notes
FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((CVDate(Int(DateAdd("h",-6,[FullDate]))))=#7/2/2007#));

--
KARL DEWEY
Build a little - Test a little

Once again, no luck. I had already tried an explicit date criteria, but I
tried it again without success. The CVDate() function doesn't seem to
benefit either.

Try it this way --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=CVDate([Enter a date]);

If this does not work then back up a little with this --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=#7/22/2007#;
Fill in you actual date criteria instead of 7/22/2007 and check it.

 

[Guest]

That was actually my first guess as well. I thought that I might need to
explicitly define the format, but that doesn't help either.

 

[Guest]

KARL,

PMFJI

In Access I only found CDATE(), not CVDate(). In my test, CDATE() didn't
strip off the time portion.

I did get just the date portion when I used used the DateValue() function:

SELECT [Notes FullDate Query].*, DateValue([fulldate]) AS Expr1
FROM [Notes FullDate Query]
WHERE (((DateValue([fulldate]))=#7/2/2007#));


BTW handsclark, Day and Hour are also reserved words in Access. (You could
use dteDay and dteHour)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

David Cox had the answer. Must strip the time from the adjusted date.
SELECT CVDate(Int(DateAdd("h",-6,[FullDate]))) AS Date_OffSet, [Notes
FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((CVDate(Int(DateAdd("h",-6,[FullDate]))))=#7/2/2007#));

--
KARL DEWEY
Build a little - Test a little

Once again, no luck. I had already tried an explicit date criteria, but I
tried it again without success. The CVDate() function doesn't seem to
benefit either.

Try it this way --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=CVDate([Enter a date]);

If this does not work then back up a little with this --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=#7/22/2007#;
Fill in you actual date criteria instead of 7/22/2007 and check it.

 

[Guest]

I am very happy to report that both solutions work (CVDate(Int()) and
DateValue()). Even though I had tried to limit the result by changing the
format, apparently the time had to be stripped explicitly.

Thank you for your help.

KARL,

PMFJI

In Access I only found CDATE(), not CVDate(). In my test, CDATE() didn't
strip off the time portion.

I did get just the date portion when I used used the DateValue() function:

SELECT [Notes FullDate Query].*, DateValue([fulldate]) AS Expr1
FROM [Notes FullDate Query]
WHERE (((DateValue([fulldate]))=#7/2/2007#));


BTW handsclark, Day and Hour are also reserved words in Access. (You could
use dteDay and dteHour)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

David Cox had the answer. Must strip the time from the adjusted date.
SELECT CVDate(Int(DateAdd("h",-6,[FullDate]))) AS Date_OffSet, [Notes
FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((CVDate(Int(DateAdd("h",-6,[FullDate]))))=#7/2/2007#));

--
KARL DEWEY
Build a little - Test a little

Once again, no luck. I had already tried an explicit date criteria, but I
tried it again without success. The CVDate() function doesn't seem to
benefit either.

:

Try it this way --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=CVDate([Enter a date]);

If this does not work then back up a little with this --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=#7/22/2007#;
Fill in you actual date criteria instead of 7/22/2007 and check it.

 

[Guest]

Oops, I missed the Int() function. Victim of cut and type - instead of cut
and paste

PBKAC....

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

KARL,

PMFJI

In Access I only found CDATE(), not CVDate(). In my test, CDATE() didn't
strip off the time portion.

I did get just the date portion when I used used the DateValue() function:

SELECT [Notes FullDate Query].*, DateValue([fulldate]) AS Expr1
FROM [Notes FullDate Query]
WHERE (((DateValue([fulldate]))=#7/2/2007#));


BTW handsclark, Day and Hour are also reserved words in Access. (You could
use dteDay and dteHour)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

David Cox had the answer. Must strip the time from the adjusted date.
SELECT CVDate(Int(DateAdd("h",-6,[FullDate]))) AS Date_OffSet, [Notes
FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((CVDate(Int(DateAdd("h",-6,[FullDate]))))=#7/2/2007#));

--
KARL DEWEY
Build a little - Test a little

Once again, no luck. I had already tried an explicit date criteria, but I
tried it again without success. The CVDate() function doesn't seem to
benefit either.

:

Try it this way --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=CVDate([Enter a date]);

If this does not work then back up a little with this --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=#7/22/2007#;
Fill in you actual date criteria instead of 7/22/2007 and check it.

 

[David Cox]

I thought the same about Hour and Day but:-

http://office.microsoft.com/en-us/access/HA100306431033.aspx

did not agree with me.

KARL,

PMFJI

In Access I only found CDATE(), not CVDate(). In my test, CDATE() didn't
strip off the time portion.

I did get just the date portion when I used used the DateValue() function:

SELECT [Notes FullDate Query].*, DateValue([fulldate]) AS Expr1
FROM [Notes FullDate Query]
WHERE (((DateValue([fulldate]))=#7/2/2007#));


BTW handsclark, Day and Hour are also reserved words in Access. (You could
use dteDay and dteHour)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

David Cox had the answer. Must strip the time from the adjusted date.
SELECT CVDate(Int(DateAdd("h",-6,[FullDate]))) AS Date_OffSet, [Notes
FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((CVDate(Int(DateAdd("h",-6,[FullDate]))))=#7/2/2007#));

--
KARL DEWEY
Build a little - Test a little

Once again, no luck. I had already tried an explicit date criteria,
but I
tried it again without success. The CVDate() function doesn't seem to
benefit either.

:

Try it this way --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate
Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=CVDate([Enter a date]);

If this does not work then back up a little with this --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate
Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=#7/22/2007#;
Fill in you actual date criteria instead of 7/22/2007 and check it.

 

[Guest]

I found these lists:

http://support.microsoft.com/kb/321266/EN-US/

http://msdn2.microsoft.com/en-us/library/bb208875.aspx


I always add a prefix so it doesn't really matter for me..... a habit from
looong ago


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I thought the same about Hour and Day but:-

http://office.microsoft.com/en-us/access/HA100306431033.aspx

did not agree with me.

KARL,

PMFJI

In Access I only found CDATE(), not CVDate(). In my test, CDATE() didn't
strip off the time portion.

I did get just the date portion when I used used the DateValue() function:

SELECT [Notes FullDate Query].*, DateValue([fulldate]) AS Expr1
FROM [Notes FullDate Query]
WHERE (((DateValue([fulldate]))=#7/2/2007#));


BTW handsclark, Day and Hour are also reserved words in Access. (You could
use dteDay and dteHour)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

David Cox had the answer. Must strip the time from the adjusted date.
SELECT CVDate(Int(DateAdd("h",-6,[FullDate]))) AS Date_OffSet, [Notes
FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((CVDate(Int(DateAdd("h",-6,[FullDate]))))=#7/2/2007#));

--
KARL DEWEY
Build a little - Test a little


:

Once again, no luck. I had already tried an explicit date criteria,
but I
tried it again without success. The CVDate() function doesn't seem to
benefit either.

:

Try it this way --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate
Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=CVDate([Enter a date]);

If this does not work then back up a little with this --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate
Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=#7/22/2007#;
Fill in you actual date criteria instead of 7/22/2007 and check it.

 

[David Cox]

Very confusing - I found other versions of the list I posted that also did
not give the full picture.

Thanks for that list, even though it contained some words I had never
encountered before. More reading to do ... :-<

David F. Cox

I found these lists:

http://support.microsoft.com/kb/321266/EN-US/

http://msdn2.microsoft.com/en-us/library/bb208875.aspx


I always add a prefix so it doesn't really matter for me..... a habit from
looong ago


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I thought the same about Hour and Day but:-

http://office.microsoft.com/en-us/access/HA100306431033.aspx

did not agree with me.

KARL,

PMFJI

In Access I only found CDATE(), not CVDate(). In my test, CDATE()
didn't
strip off the time portion.

I did get just the date portion when I used used the DateValue()
function:

SELECT [Notes FullDate Query].*, DateValue([fulldate]) AS Expr1
FROM [Notes FullDate Query]
WHERE (((DateValue([fulldate]))=#7/2/2007#));


BTW handsclark, Day and Hour are also reserved words in Access. (You
could
use dteDay and dteHour)


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

David Cox had the answer. Must strip the time from the adjusted date.
SELECT CVDate(Int(DateAdd("h",-6,[FullDate]))) AS Date_OffSet, [Notes
FullDate Query].*
FROM [Notes FullDate Query]
WHERE (((CVDate(Int(DateAdd("h",-6,[FullDate]))))=#7/2/2007#));

--
KARL DEWEY
Build a little - Test a little


:

Once again, no luck. I had already tried an explicit date criteria,
but I
tried it again without success. The CVDate() function doesn't seem
to
benefit either.

:

Try it this way --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate
Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=CVDate([Enter a date]);

If this does not work then back up a little with this --
SELECT DateAdd("h",-6,[FullDate]) AS Date_OffSet, [Notes FullDate
Query].*
FROM [Notes FullDate Query]
WHERE DateAdd("h",-6,[FullDate])=#7/22/2007#;
Fill in you actual date criteria instead of 7/22/2007 and check
it.

 

[Marshall Barton]

This list is pretty comprehensive:
http://allenbrowne.com/AppIssueBadWord.html

 

[Guest]

That's the one I was searching for...!!! I'm suffering from CRS disease

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

This list is pretty comprehensive:
http://allenbrowne.com/AppIssueBadWord.html
--
Marsh
MVP [MS Access]

Very confusing - I found other versions of the list I posted that also did
not give the full picture.

Thanks for that list, even though it contained some words I had never
encountered before. More reading to do ... :-<