Date/Text field in Report

[PPCO]

I have an order schedule report that is sorted and grouped by ship date. I
want any orders dated before today to show up grouped under a “Backorderâ€
heading and anything else to show up grouped under its ship date. Is there a
way to write an IF statement in the control source box of the ship date field
to make that happen?
I tried this: IIF ([shipdate]<date(), “Backorderedâ€, [shipdate]). It says
there’s a syntax error.
Thanks!

 

[Arvin Meyer [MVP]]

Your problem is that you wrote it in the ship date field. Add a new column
(called an alias column) something like:

Status: IIF ([shipdate]<date(), "Backordered", [shipdate])

 

[Klatuu]

Arvin,
Woudld it not be necessary to convert the date to a string to use for
sorting and grouping when you have a string value to include in the field?

Even if the date were coersed into a string automatically, the sorting would
be not be truely by date (assuming crossing of year boundries) and
"Backordered" would sort to the bottom. To put "Backordered" at the top and
sorty by date, I would make the calculated field like this:
Status: IIF ([shipdate]<date(), " ", Format([shipdate],"yyyymmdd")
The values in the calculated field don't have to be displayed anywere, so
dummying it up like that has no effect on what the user sees, but does
control the sorting like you want it.
--
Dave Hargis, Microsoft Access MVP

Your problem is that you wrote it in the ship date field. Add a new column
(called an alias column) something like:

Status: IIF ([shipdate]<date(), "Backordered", [shipdate])

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an order schedule report that is sorted and grouped by ship date. I
want any orders dated before today to show up grouped under a "Backorder"
heading and anything else to show up grouped under its ship date. Is there
a
way to write an IF statement in the control source box of the ship date
field
to make that happen?
I tried this: IIF ([shipdate]<date(), "Backordered", [shipdate]). It says
there's a syntax error.
Thanks!

 

[Arvin Meyer [MVP]]

Aircode:

Select [shipdate], IIf([shipdate]<Date(),"Backordered",[shipdate]) As Status
From MyTable
Order By IIf([shipdate]<Date(),"Backordered",[shipdate]) Desc;

Should put Backordered to the top and the other dates in descending order
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,
Woudld it not be necessary to convert the date to a string to use for
sorting and grouping when you have a string value to include in the field?

Even if the date were coersed into a string automatically, the sorting
would
be not be truely by date (assuming crossing of year boundries) and
"Backordered" would sort to the bottom. To put "Backordered" at the top
and
sorty by date, I would make the calculated field like this:
Status: IIF ([shipdate]<date(), " ", Format([shipdate],"yyyymmdd")
The values in the calculated field don't have to be displayed anywere, so
dummying it up like that has no effect on what the user sees, but does
control the sorting like you want it.
--
Dave Hargis, Microsoft Access MVP

Your problem is that you wrote it in the ship date field. Add a new
column
(called an alias column) something like:

Status: IIF ([shipdate]<date(), "Backordered", [shipdate])

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an order schedule report that is sorted and grouped by ship date.
I
want any orders dated before today to show up grouped under a
"Backorder"
heading and anything else to show up grouped under its ship date. Is
there
a
way to write an IF statement in the control source box of the ship date
field
to make that happen?
I tried this: IIF ([shipdate]<date(), "Backordered", [shipdate]). It
says
there's a syntax error.
Thanks!

 

[Klatuu]

Very clever.
--
Dave Hargis, Microsoft Access MVP

Aircode:

Select [shipdate], IIf([shipdate]<Date(),"Backordered",[shipdate]) As Status
From MyTable
Order By IIf([shipdate]<Date(),"Backordered",[shipdate]) Desc;

Should put Backordered to the top and the other dates in descending order
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,
Woudld it not be necessary to convert the date to a string to use for
sorting and grouping when you have a string value to include in the field?

Even if the date were coersed into a string automatically, the sorting
would
be not be truely by date (assuming crossing of year boundries) and
"Backordered" would sort to the bottom. To put "Backordered" at the top
and
sorty by date, I would make the calculated field like this:
Status: IIF ([shipdate]<date(), " ", Format([shipdate],"yyyymmdd")
The values in the calculated field don't have to be displayed anywere, so
dummying it up like that has no effect on what the user sees, but does
control the sorting like you want it.
--
Dave Hargis, Microsoft Access MVP

Your problem is that you wrote it in the ship date field. Add a new
column
(called an alias column) something like:

Status: IIF ([shipdate]<date(), "Backordered", [shipdate])

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an order schedule report that is sorted and grouped by ship date.
I
want any orders dated before today to show up grouped under a
"Backorder"
heading and anything else to show up grouped under its ship date. Is
there
a
way to write an IF statement in the control source box of the ship date
field
to make that happen?
I tried this: IIF ([shipdate]<date(), "Backordered", [shipdate]). It
says
there's a syntax error.
Thanks!

 

[PPCO]

Almost there--is it possible to put Backordered at the top, and then sort
dates in ascending order?

Very clever.
--
Dave Hargis, Microsoft Access MVP

Aircode:

Select [shipdate], IIf([shipdate]<Date(),"Backordered",[shipdate]) As Status
From MyTable
Order By IIf([shipdate]<Date(),"Backordered",[shipdate]) Desc;

Should put Backordered to the top and the other dates in descending order
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,
Woudld it not be necessary to convert the date to a string to use for
sorting and grouping when you have a string value to include in the field?

Even if the date were coersed into a string automatically, the sorting
would
be not be truely by date (assuming crossing of year boundries) and
"Backordered" would sort to the bottom. To put "Backordered" at the top
and
sorty by date, I would make the calculated field like this:
Status: IIF ([shipdate]<date(), " ", Format([shipdate],"yyyymmdd")
The values in the calculated field don't have to be displayed anywere, so
dummying it up like that has no effect on what the user sees, but does
control the sorting like you want it.
--
Dave Hargis, Microsoft Access MVP


:

Your problem is that you wrote it in the ship date field. Add a new
column
(called an alias column) something like:

Status: IIF ([shipdate]<date(), "Backordered", [shipdate])

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an order schedule report that is sorted and grouped by ship date.
I
want any orders dated before today to show up grouped under a
"Backorder"
heading and anything else to show up grouped under its ship date. Is
there
a
way to write an IF statement in the control source box of the ship date
field
to make that happen?
I tried this: IIF ([shipdate]<date(), "Backordered", [shipdate]). It
says
there's a syntax error.
Thanks!

 

[Klatuu]

You could modify Arvin's original query a bit:

Select [shipdate], IIf([shipdate]<Date(),#1/1/1900#,[shipdate]) As Status
From MyTable
Order By IIf([shipdate]<Date(),"Backordered",[shipdate]);

That would give every shipdate less than the current date a date value of
January 1, 1900. That will cause it to sort to the top with all other dates
in ascending order after it.


--
Dave Hargis, Microsoft Access MVP

Almost there--is it possible to put Backordered at the top, and then sort
dates in ascending order?

Very clever.
--
Dave Hargis, Microsoft Access MVP

Aircode:

Select [shipdate], IIf([shipdate]<Date(),"Backordered",[shipdate]) As Status
From MyTable
Order By IIf([shipdate]<Date(),"Backordered",[shipdate]) Desc;

Should put Backordered to the top and the other dates in descending order
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,
Woudld it not be necessary to convert the date to a string to use for
sorting and grouping when you have a string value to include in the field?

Even if the date were coersed into a string automatically, the sorting
would
be not be truely by date (assuming crossing of year boundries) and
"Backordered" would sort to the bottom. To put "Backordered" at the top
and
sorty by date, I would make the calculated field like this:
Status: IIF ([shipdate]<date(), " ", Format([shipdate],"yyyymmdd")
The values in the calculated field don't have to be displayed anywere, so
dummying it up like that has no effect on what the user sees, but does
control the sorting like you want it.
--
Dave Hargis, Microsoft Access MVP


:

Your problem is that you wrote it in the ship date field. Add a new
column
(called an alias column) something like:

Status: IIF ([shipdate]<date(), "Backordered", [shipdate])

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an order schedule report that is sorted and grouped by ship date.
I
want any orders dated before today to show up grouped under a
"Backorder"
heading and anything else to show up grouped under its ship date. Is
there
a
way to write an IF statement in the control source box of the ship date
field
to make that happen?
I tried this: IIF ([shipdate]<date(), "Backordered", [shipdate]). It
says
there's a syntax error.
Thanks!

 

[Arvin Meyer [MVP]]

Custom sorting is possible in Access by writing a VBA function in a standard
module and calling it in a query, then sorting on the returned value. It
works great and you can build almost anything you want. It does slow down
the query because the function has to run for each line returned. Usually
it's smarter to build 2 queries, the first to limit the records returned,
and the second run the function on the results of the first. Be aware that
if you every upsize the back-end to another DBMS, you'll still need to run a
Jet DBMS as the front-end.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Almost there--is it possible to put Backordered at the top, and then sort
dates in ascending order?

Very clever.
--
Dave Hargis, Microsoft Access MVP

Aircode:

Select [shipdate], IIf([shipdate]<Date(),"Backordered",[shipdate]) As
Status
From MyTable
Order By IIf([shipdate]<Date(),"Backordered",[shipdate]) Desc;

Should put Backordered to the top and the other dates in descending
order
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,
Woudld it not be necessary to convert the date to a string to use for
sorting and grouping when you have a string value to include in the
field?

Even if the date were coersed into a string automatically, the
sorting
would
be not be truely by date (assuming crossing of year boundries) and
"Backordered" would sort to the bottom. To put "Backordered" at the
top
and
sorty by date, I would make the calculated field like this:
Status: IIF ([shipdate]<date(), " ", Format([shipdate],"yyyymmdd")
The values in the calculated field don't have to be displayed
anywere, so
dummying it up like that has no effect on what the user sees, but
does
control the sorting like you want it.
--
Dave Hargis, Microsoft Access MVP


:

Your problem is that you wrote it in the ship date field. Add a new
column
(called an alias column) something like:

Status: IIF ([shipdate]<date(), "Backordered", [shipdate])

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have an order schedule report that is sorted and grouped by ship
date.
I
want any orders dated before today to show up grouped under a
"Backorder"
heading and anything else to show up grouped under its ship date.
Is
there
a
way to write an IF statement in the control source box of the ship
date
field
to make that happen?
I tried this: IIF ([shipdate]<date(), "Backordered", [shipdate]).
It
says
there's a syntax error.
Thanks!