DateTime field

[Shri]

Hi,

I have a table that has DateTime Field called "Time_end" in the format
06/20/2007 12:28:30 PM.
In a query I am trying to extract only DatePart and I am using below syntax.

PullDate: DateValue([time_end])

I am trying to run a report off off the above query for the start date
11/01/08 and end date 11/10/08. The query is returning wrong result. The
query is returning Nov'07 data and Nov'08 data. I am not sure why the query
is including 07 data when I am trying to pull the report for only 10 days.

I have tried using below syntax too. But couldn't resolve the issue.

PullDate: Format([time_end], "mm/dd/yyyy")

I would appreciate any help on this issue.

Thanks.

 

[John Spencer]

I would try

PullDate: IIF(IsDate(Time_End),DateValue(Time_End),Null)
Criteria: BETWEEN #11/1/2008# and #11/10/2008#

Actually I would do the following which is likely to be much faster.
Field: Time_End
Criteria: >= #11/1/2008# and <#11/11/2008#

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Shri]

I tried with PullDate: IIF(IsDate(Time_End),DateValue(Time_End),Null)
and it works.

I am not sure the difference between PullDate: DateValue([time_end]) and the
above syntax. If you don't mind can you please explain why this didn't work.

Thank you so much.

I would try

PullDate: IIF(IsDate(Time_End),DateValue(Time_End),Null)
Criteria: BETWEEN #11/1/2008# and #11/10/2008#

Actually I would do the following which is likely to be much faster.
Field: Time_End
Criteria: >= #11/1/2008# and <#11/11/2008#

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have a table that has DateTime Field called "Time_end" in the format
06/20/2007 12:28:30 PM.
In a query I am trying to extract only DatePart and I am using below syntax.

PullDate: DateValue([time_end])

I am trying to run a report off off the above query for the start date
11/01/08 and end date 11/10/08. The query is returning wrong result. The
query is returning Nov'07 data and Nov'08 data. I am not sure why the query
is including 07 data when I am trying to pull the report for only 10 days.

I have tried using below syntax too. But couldn't resolve the issue.

PullDate: Format([time_end], "mm/dd/yyyy")

I would appreciate any help on this issue.

Thanks.

 

[John Spencer]

ISDate looks at the variable and determines whether or not it can be
interpreted as a date. Null can't be date and if you use DateValue on a null
you will get an error - 94 Invalid use of Null.

As I said, I would try the second method. It handles nulls with no problem,
but if you have a working solution and it is fast enough then go with what is
working for you.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I tried with PullDate: IIF(IsDate(Time_End),DateValue(Time_End),Null)
and it works.

I am not sure the difference between PullDate: DateValue([time_end]) and the
above syntax. If you don't mind can you please explain why this didn't work.

Thank you so much.

I would try

PullDate: IIF(IsDate(Time_End),DateValue(Time_End),Null)
Criteria: BETWEEN #11/1/2008# and #11/10/2008#

Actually I would do the following which is likely to be much faster.
Field: Time_End
Criteria: >= #11/1/2008# and <#11/11/2008#

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have a table that has DateTime Field called "Time_end" in the format
06/20/2007 12:28:30 PM.
In a query I am trying to extract only DatePart and I am using below syntax.

PullDate: DateValue([time_end])

I am trying to run a report off off the above query for the start date
11/01/08 and end date 11/10/08. The query is returning wrong result. The
query is returning Nov'07 data and Nov'08 data. I am not sure why the query
is including 07 data when I am trying to pull the report for only 10 days.

I have tried using below syntax too. But couldn't resolve the issue.

PullDate: Format([time_end], "mm/dd/yyyy")

I would appreciate any help on this issue.

Thanks.