Calculated date field - no date format

[Bibi]

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA

 

[Duane Hookom]

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

 

[Bibi]

Duane
Thank you for the prompt response. It did not work.... :-(
A criteria of Date()+whatever number does work - that is, it gives expected
results but when a date is entered as a criteria - - nothing. Any other
thoughts?
TIA

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA

 

[Duane Hookom]

Does the Date Available column display left or right-aligned in the datasheet
view? If it is right aligned, it should accept a true date in the criteria.
Are you using a format of m/d/y?

You might simplify the expression a little with
Date Available: CDate(IIf([Max Lease].[Lease Ends]+4>Date(),[Max
Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP

Duane
Thank you for the prompt response. It did not work.... :-(
A criteria of Date()+whatever number does work - that is, it gives expected
results but when a date is entered as a criteria - - nothing. Any other
thoughts?
TIA

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA

 

[Bibi]

Duane,
Again, thank you but I am still not able to obtain consistent results for a
date range query - the result shows the correct month but for any year, not
just the year entered in the range....it does now sort by true date - it is
right aligned - I can only assume that there is a problem with the query
itself but we have tested it and have had no problems up to now. It is
formatted as a short date.
Any other suggestions?
Thank you.
Bibi

Does the Date Available column display left or right-aligned in the datasheet
view? If it is right aligned, it should accept a true date in the criteria.
Are you using a format of m/d/y?

You might simplify the expression a little with
Date Available: CDate(IIf([Max Lease].[Lease Ends]+4>Date(),[Max
Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP

Duane
Thank you for the prompt response. It did not work.... :-(
A criteria of Date()+whatever number does work - that is, it gives expected
results but when a date is entered as a criteria - - nothing. Any other
thoughts?
TIA

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA

 

[Duane Hookom]

Please provide more information like your full sql and any parameters.
--
Duane Hookom
Microsoft Access MVP

Duane,
Again, thank you but I am still not able to obtain consistent results for a
date range query - the result shows the correct month but for any year, not
just the year entered in the range....it does now sort by true date - it is
right aligned - I can only assume that there is a problem with the query
itself but we have tested it and have had no problems up to now. It is
formatted as a short date.
Any other suggestions?
Thank you.
Bibi

Does the Date Available column display left or right-aligned in the datasheet
view? If it is right aligned, it should accept a true date in the criteria.
Are you using a format of m/d/y?

You might simplify the expression a little with
Date Available: CDate(IIf([Max Lease].[Lease Ends]+4>Date(),[Max
Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP

Duane
Thank you for the prompt response. It did not work.... :-(
A criteria of Date()+whatever number does work - that is, it gives expected
results but when a date is entered as a criteria - - nothing. Any other
thoughts?
TIA

:

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA

 

[Bibi]

Duane
There's an awful lot going on in the query - I don't write code and am self
taught so to get the results we needed the process was a bit convoluted - I
have pared it down considerably but the results are still, unfortunately, the
same. That said, here it is:

SELECT CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())) AS [Date Availalbe], [Max Lease].[Lease Begins], IIf([Max
Lease].[Lease Begins]>Date()-1,[Max Lease].[Lease Begins]," ") AS [Future
Lease Begins], IIf([Max Lease].[Lease Begins]>Date(),[Max Lease].[Lease
Ends]," ") AS [Future Lease Ends], [PN All status report as of today with
percentages].[Property ID], [PN All status report as of today with
percentages].[Currently Available], [PN All status report as of today with
percentages].[Lease Begins], [PN All status report as of today with
percentages].[Lease Ends], [PN All status report as of today with
percentages].Count, [Max Lease].Number
FROM ([PN All status report as of today with percentages] LEFT JOIN [Max
Lease] ON [PN All status report as of today with percentages].[Property ID] =
[Max Lease].[Property Id]) LEFT JOIN Tenants ON [Max Lease].[Tenant A] =
Tenants.[Tenant ID]
WHERE (((CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date()))) Between [start date] And [end date]))
ORDER BY CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())), [PN All status report as of today with percentages].[Lease
Ends];

All help appreciated.
Bibi

Please provide more information like your full sql and any parameters.
--
Duane Hookom
Microsoft Access MVP

Duane,
Again, thank you but I am still not able to obtain consistent results for a
date range query - the result shows the correct month but for any year, not
just the year entered in the range....it does now sort by true date - it is
right aligned - I can only assume that there is a problem with the query
itself but we have tested it and have had no problems up to now. It is
formatted as a short date.
Any other suggestions?
Thank you.
Bibi

Does the Date Available column display left or right-aligned in the datasheet
view? If it is right aligned, it should accept a true date in the criteria.
Are you using a format of m/d/y?

You might simplify the expression a little with
Date Available: CDate(IIf([Max Lease].[Lease Ends]+4>Date(),[Max
Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:


Duane
Thank you for the prompt response. It did not work.... :-(
A criteria of Date()+whatever number does work - that is, it gives expected
results but when a date is entered as a criteria - - nothing. Any other
thoughts?
TIA

:

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA

 

[Duane Hookom]

First, I would change the following so it would return just one data type:
IIf([Max Lease].[Lease Begins]>Date()-1,[Max Lease].[Lease Begins]," ")
I would replace the " " with Null.

Then, what are [Start Date] and [End Date]? Are these parameter prompts? If
they are prompts, I would change them to reference controls on a form. Also,
you should specify the data type of the parameters.

--
Duane Hookom
Microsoft Access MVP

Duane
There's an awful lot going on in the query - I don't write code and am self
taught so to get the results we needed the process was a bit convoluted - I
have pared it down considerably but the results are still, unfortunately, the
same. That said, here it is:

SELECT CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())) AS [Date Availalbe], [Max Lease].[Lease Begins], IIf([Max
Lease].[Lease Begins]>Date()-1,[Max Lease].[Lease Begins]," ") AS [Future
Lease Begins], IIf([Max Lease].[Lease Begins]>Date(),[Max Lease].[Lease
Ends]," ") AS [Future Lease Ends], [PN All status report as of today with
percentages].[Property ID], [PN All status report as of today with
percentages].[Currently Available], [PN All status report as of today with
percentages].[Lease Begins], [PN All status report as of today with
percentages].[Lease Ends], [PN All status report as of today with
percentages].Count, [Max Lease].Number
FROM ([PN All status report as of today with percentages] LEFT JOIN [Max
Lease] ON [PN All status report as of today with percentages].[Property ID] =
[Max Lease].[Property Id]) LEFT JOIN Tenants ON [Max Lease].[Tenant A] =
Tenants.[Tenant ID]
WHERE (((CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date()))) Between [start date] And [end date]))
ORDER BY CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())), [PN All status report as of today with percentages].[Lease
Ends];

All help appreciated.
Bibi

Please provide more information like your full sql and any parameters.
--
Duane Hookom
Microsoft Access MVP

Duane,
Again, thank you but I am still not able to obtain consistent results for a
date range query - the result shows the correct month but for any year, not
just the year entered in the range....it does now sort by true date - it is
right aligned - I can only assume that there is a problem with the query
itself but we have tested it and have had no problems up to now. It is
formatted as a short date.
Any other suggestions?
Thank you.
Bibi

:

Does the Date Available column display left or right-aligned in the datasheet
view? If it is right aligned, it should accept a true date in the criteria.
Are you using a format of m/d/y?

You might simplify the expression a little with
Date Available: CDate(IIf([Max Lease].[Lease Ends]+4>Date(),[Max
Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:


Duane
Thank you for the prompt response. It did not work.... :-(
A criteria of Date()+whatever number does work - that is, it gives expected
results but when a date is entered as a criteria - - nothing. Any other
thoughts?
TIA

:

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA

 

[Bibi]

Duane
Thank you.
I made the change to null in the appropriate fields. [Start Date] and [End
Date] are the beginning and end dates of the target range required by the
user. They are parameter prompts. I can not change them to reference
controls on a form because I do not know how. I have tried many times to get
that technique down but just can't. This is the first time I have encounted
a problem of this type where the date format is not recognized. I am able to
export the query result to excel and then import back into access and the
data type is then date and a range query will work. Unfortunately I do not
know how to create a macro that would perform that action without user
intervention...so...I plan to just give the user the option of dates within
one , two or three months.
Thank you for your help.
Bibi

First, I would change the following so it would return just one data type:
IIf([Max Lease].[Lease Begins]>Date()-1,[Max Lease].[Lease Begins]," ")
I would replace the " " with Null.

Then, what are [Start Date] and [End Date]? Are these parameter prompts? If
they are prompts, I would change them to reference controls on a form. Also,
you should specify the data type of the parameters.

--
Duane Hookom
Microsoft Access MVP

Duane
There's an awful lot going on in the query - I don't write code and am self
taught so to get the results we needed the process was a bit convoluted - I
have pared it down considerably but the results are still, unfortunately, the
same. That said, here it is:

SELECT CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())) AS [Date Availalbe], [Max Lease].[Lease Begins], IIf([Max
Lease].[Lease Begins]>Date()-1,[Max Lease].[Lease Begins]," ") AS [Future
Lease Begins], IIf([Max Lease].[Lease Begins]>Date(),[Max Lease].[Lease
Ends]," ") AS [Future Lease Ends], [PN All status report as of today with
percentages].[Property ID], [PN All status report as of today with
percentages].[Currently Available], [PN All status report as of today with
percentages].[Lease Begins], [PN All status report as of today with
percentages].[Lease Ends], [PN All status report as of today with
percentages].Count, [Max Lease].Number
FROM ([PN All status report as of today with percentages] LEFT JOIN [Max
Lease] ON [PN All status report as of today with percentages].[Property ID] =
[Max Lease].[Property Id]) LEFT JOIN Tenants ON [Max Lease].[Tenant A] =
Tenants.[Tenant ID]
WHERE (((CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date()))) Between [start date] And [end date]))
ORDER BY CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())), [PN All status report as of today with percentages].[Lease
Ends];

All help appreciated.
Bibi

Please provide more information like your full sql and any parameters.
--
Duane Hookom
Microsoft Access MVP


:

Duane,
Again, thank you but I am still not able to obtain consistent results for a
date range query - the result shows the correct month but for any year, not
just the year entered in the range....it does now sort by true date - it is
right aligned - I can only assume that there is a problem with the query
itself but we have tested it and have had no problems up to now. It is
formatted as a short date.
Any other suggestions?
Thank you.
Bibi

:

Does the Date Available column display left or right-aligned in the datasheet
view? If it is right aligned, it should accept a true date in the criteria.
Are you using a format of m/d/y?

You might simplify the expression a little with
Date Available: CDate(IIf([Max Lease].[Lease Ends]+4>Date(),[Max
Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:


Duane
Thank you for the prompt response. It did not work.... :-(
A criteria of Date()+whatever number does work - that is, it gives expected
results but when a date is entered as a criteria - - nothing. Any other
thoughts?
TIA

:

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA

 

[Duane Hookom]

Allen Browne has information on how to set up a form for criteria at
http://www.allenbrowne.com/casu-08.html.

My other recommendation "Also, you should specify the data type of the
parameters" can be accomplished by viewing the Query->Parameters in design
view of your query and entering:
[Start Date] Date/Time
[End Date] Date/Time

--
Duane Hookom
Microsoft Access MVP

Duane
Thank you.
I made the change to null in the appropriate fields. [Start Date] and [End
Date] are the beginning and end dates of the target range required by the
user. They are parameter prompts. I can not change them to reference
controls on a form because I do not know how. I have tried many times to get
that technique down but just can't. This is the first time I have encounted
a problem of this type where the date format is not recognized. I am able to
export the query result to excel and then import back into access and the
data type is then date and a range query will work. Unfortunately I do not
know how to create a macro that would perform that action without user
intervention...so...I plan to just give the user the option of dates within
one , two or three months.
Thank you for your help.
Bibi

First, I would change the following so it would return just one data type:
IIf([Max Lease].[Lease Begins]>Date()-1,[Max Lease].[Lease Begins]," ")
I would replace the " " with Null.

Then, what are [Start Date] and [End Date]? Are these parameter prompts? If
they are prompts, I would change them to reference controls on a form. Also,
you should specify the data type of the parameters.

--
Duane Hookom
Microsoft Access MVP

Duane
There's an awful lot going on in the query - I don't write code and am self
taught so to get the results we needed the process was a bit convoluted - I
have pared it down considerably but the results are still, unfortunately, the
same. That said, here it is:

SELECT CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())) AS [Date Availalbe], [Max Lease].[Lease Begins], IIf([Max
Lease].[Lease Begins]>Date()-1,[Max Lease].[Lease Begins]," ") AS [Future
Lease Begins], IIf([Max Lease].[Lease Begins]>Date(),[Max Lease].[Lease
Ends]," ") AS [Future Lease Ends], [PN All status report as of today with
percentages].[Property ID], [PN All status report as of today with
percentages].[Currently Available], [PN All status report as of today with
percentages].[Lease Begins], [PN All status report as of today with
percentages].[Lease Ends], [PN All status report as of today with
percentages].Count, [Max Lease].Number
FROM ([PN All status report as of today with percentages] LEFT JOIN [Max
Lease] ON [PN All status report as of today with percentages].[Property ID] =
[Max Lease].[Property Id]) LEFT JOIN Tenants ON [Max Lease].[Tenant A] =
Tenants.[Tenant ID]
WHERE (((CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date()))) Between [start date] And [end date]))
ORDER BY CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())), [PN All status report as of today with percentages].[Lease
Ends];

All help appreciated.
Bibi


:

Please provide more information like your full sql and any parameters.
--
Duane Hookom
Microsoft Access MVP


:

Duane,
Again, thank you but I am still not able to obtain consistent results for a
date range query - the result shows the correct month but for any year, not
just the year entered in the range....it does now sort by true date - it is
right aligned - I can only assume that there is a problem with the query
itself but we have tested it and have had no problems up to now. It is
formatted as a short date.
Any other suggestions?
Thank you.
Bibi

:

Does the Date Available column display left or right-aligned in the datasheet
view? If it is right aligned, it should accept a true date in the criteria.
Are you using a format of m/d/y?

You might simplify the expression a little with
Date Available: CDate(IIf([Max Lease].[Lease Ends]+4>Date(),[Max
Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:


Duane
Thank you for the prompt response. It did not work.... :-(
A criteria of Date()+whatever number does work - that is, it gives expected
results but when a date is entered as a criteria - - nothing. Any other
thoughts?
TIA

:

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA

 

[Bibi]

Duane
Thank you so much - I was at my wit's end. Setting the parameter type in
design view did the trick! I did not know the mechanics on how to do that.
I will also check the form for criteria reference - I know I need to learn
how.
Again, thank you.
Bibi

Allen Browne has information on how to set up a form for criteria at
http://www.allenbrowne.com/casu-08.html.

My other recommendation "Also, you should specify the data type of the
parameters" can be accomplished by viewing the Query->Parameters in design
view of your query and entering:
[Start Date] Date/Time
[End Date] Date/Time

--
Duane Hookom
Microsoft Access MVP

Duane
Thank you.
I made the change to null in the appropriate fields. [Start Date] and [End
Date] are the beginning and end dates of the target range required by the
user. They are parameter prompts. I can not change them to reference
controls on a form because I do not know how. I have tried many times to get
that technique down but just can't. This is the first time I have encounted
a problem of this type where the date format is not recognized. I am able to
export the query result to excel and then import back into access and the
data type is then date and a range query will work. Unfortunately I do not
know how to create a macro that would perform that action without user
intervention...so...I plan to just give the user the option of dates within
one , two or three months.
Thank you for your help.
Bibi

First, I would change the following so it would return just one data type:
IIf([Max Lease].[Lease Begins]>Date()-1,[Max Lease].[Lease Begins]," ")
I would replace the " " with Null.

Then, what are [Start Date] and [End Date]? Are these parameter prompts? If
they are prompts, I would change them to reference controls on a form. Also,
you should specify the data type of the parameters.

--
Duane Hookom
Microsoft Access MVP


:

Duane
There's an awful lot going on in the query - I don't write code and am self
taught so to get the results we needed the process was a bit convoluted - I
have pared it down considerably but the results are still, unfortunately, the
same. That said, here it is:

SELECT CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())) AS [Date Availalbe], [Max Lease].[Lease Begins], IIf([Max
Lease].[Lease Begins]>Date()-1,[Max Lease].[Lease Begins]," ") AS [Future
Lease Begins], IIf([Max Lease].[Lease Begins]>Date(),[Max Lease].[Lease
Ends]," ") AS [Future Lease Ends], [PN All status report as of today with
percentages].[Property ID], [PN All status report as of today with
percentages].[Currently Available], [PN All status report as of today with
percentages].[Lease Begins], [PN All status report as of today with
percentages].[Lease Ends], [PN All status report as of today with
percentages].Count, [Max Lease].Number
FROM ([PN All status report as of today with percentages] LEFT JOIN [Max
Lease] ON [PN All status report as of today with percentages].[Property ID] =
[Max Lease].[Property Id]) LEFT JOIN Tenants ON [Max Lease].[Tenant A] =
Tenants.[Tenant ID]
WHERE (((CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date()))) Between [start date] And [end date]))
ORDER BY CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())), [PN All status report as of today with percentages].[Lease
Ends];

All help appreciated.
Bibi


:

Please provide more information like your full sql and any parameters.
--
Duane Hookom
Microsoft Access MVP


:

Duane,
Again, thank you but I am still not able to obtain consistent results for a
date range query - the result shows the correct month but for any year, not
just the year entered in the range....it does now sort by true date - it is
right aligned - I can only assume that there is a problem with the query
itself but we have tested it and have had no problems up to now. It is
formatted as a short date.
Any other suggestions?
Thank you.
Bibi

:

Does the Date Available column display left or right-aligned in the datasheet
view? If it is right aligned, it should accept a true date in the criteria.
Are you using a format of m/d/y?

You might simplify the expression a little with
Date Available: CDate(IIf([Max Lease].[Lease Ends]+4>Date(),[Max
Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:


Duane
Thank you for the prompt response. It did not work.... :-(
A criteria of Date()+whatever number does work - that is, it gives expected
results but when a date is entered as a criteria - - nothing. Any other
thoughts?
TIA

:

The IIf() function returns a variant data type. Try wrap your expression in
CDate()
Date Available: CDate(IIf([Max Lease].[Lease Ends]+5>Date()-1,
[Max Lease].[Lease ends]+5,Date()))

--
Duane Hookom
Microsoft Access MVP


:

I have a calculated field that calculates a date. It does not seem to retain
the date property. It sorts as text rather than a date – I have been able to
set the field property format to short date. It will sort by date if run
from the query screen. I need to be able to select a range of dates. Between
is not giving me the required results.


This is the field:
Date Available: IIf([Max Lease].[Lease Ends]+5>Date()-1,[Max Lease].[Lease
ends]+5,Date())


and the results are as expected. But I can not obtain range data no matter
what I try (I can't write code.) Please help!

TIA