setting a form by date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a form which is based on a table, and shows one record from this
table at a time. The form shows the records by date (from earliest to
latest). The problem is that there a records in the table that has no date at
all, and therefor are shown first. What I want is that the records with no
date will appear last, and the rest of the records will appear before.
Is there a way doing this?
thank you.
 
Provided that you have no dates on or earlier than 29 December 1899, setting
the Order By property to "YourDateField IS NULL, YourDateField" should do
it.

The expression YourDateField IS NULL will return True (-1) if the field
'YourDateField' is Null. The numeric value -1 equates to the date 29
December 1899, so records in which the expression returns True will be
listed before records with dates later than that.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
I tried setting the Order By property of the form to:
TransportationDate IS NULL, TransportationDate
as you suggested. now when I try to open the form I get a message that askes
me to fill out what is TransportationDate IS NULL, and only after I write
something the form opens...
Is there something I'm doing wrong?
thank you
 
I'm not sure what the problem is, setting the Order By property seems to be
working for me. Another thing you could try, though, would be to base your
form on a query and use the expression in the query instead. For example
(where the name of my table is 'tblTest' and the name of my date field is
'TestDate') ...

SELECT tblTest.*
FROM tblTest
ORDER BY [TestDate] Is Null, tblTest.TestDate;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Thank you, now it words.
The problem now is that it's doing exactly what it was doing before: first I
get the records with no date, and the the records with a date. I want first
to get the records with a date (from earliest to latest), and only then the
records with no date. The last records should be those that have no date.
thanks again.

Brendan Reynolds said:
I'm not sure what the problem is, setting the Order By property seems to be
working for me. Another thing you could try, though, would be to base your
form on a query and use the expression in the query instead. For example
(where the name of my table is 'tblTest' and the name of my date field is
'TestDate') ...

SELECT tblTest.*
FROM tblTest
ORDER BY [TestDate] Is Null, tblTest.TestDate;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


dshemesh said:
I tried setting the Order By property of the form to:
TransportationDate IS NULL, TransportationDate
as you suggested. now when I try to open the form I get a message that
askes
me to fill out what is TransportationDate IS NULL, and only after I write
something the form opens...
Is there something I'm doing wrong?
thank you
Click to expand...
Click to expand...
 
Oops! Sorry, silly mistake on my part. Try changing IS NULL to IS NOT NULL.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


dshemesh said:
Thank you, now it words.
The problem now is that it's doing exactly what it was doing before: first
I
get the records with no date, and the the records with a date. I want
first
to get the records with a date (from earliest to latest), and only then
the
records with no date. The last records should be those that have no date.
thanks again.

Brendan Reynolds said:
I'm not sure what the problem is, setting the Order By property seems to
be
working for me. Another thing you could try, though, would be to base
your
form on a query and use the expression in the query instead. For example
(where the name of my table is 'tblTest' and the name of my date field is
'TestDate') ...

SELECT tblTest.*
FROM tblTest
ORDER BY [TestDate] Is Null, tblTest.TestDate;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


dshemesh said:
I tried setting the Order By property of the form to:
TransportationDate IS NULL, TransportationDate
as you suggested. now when I try to open the form I get a message that
askes
me to fill out what is TransportationDate IS NULL, and only after I
write
something the form opens...
Is there something I'm doing wrong?
thank you

:

Provided that you have no dates on or earlier than 29 December 1899,
setting
the Order By property to "YourDateField IS NULL, YourDateField" should
do
it.

The expression YourDateField IS NULL will return True (-1) if the
field
'YourDateField' is Null. The numeric value -1 equates to the date 29
December 1899, so records in which the expression returns True will be
listed before records with dates later than that.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


Hello,
I have a form which is based on a table, and shows one record from
this
table at a time. The form shows the records by date (from earliest
to
latest). The problem is that there a records in the table that has
no
date
at
all, and therefor are shown first. What I want is that the records
with
no
date will appear last, and the rest of the records will appear
before.
Is there a way doing this?
thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top