DateDiff command - no negative numbers

[Guest]

Hi.
I have used the DateDiff command in a query to show the difference between
requested delivery date and shipment date. I now have some cases where the
deliveries were not late, giving me a negative number in the datediff column.
I want to use my query's criteria to allow only positive numbers to show (ie.
deliveries which were not late). I have tried >0 in criteria field of the
datediff column but this gives me an error.
Any suggestions?
Thanks

 

[Allen Browne]

DateDiff() returns a Long, so >0 should be acceptable criteria.

What error message are you seeing? Does it indicate that Access is
misunderstanding the data type of the calculated field? This is quite
common, as discussed in this link:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If that is the issue, you can typecast the result. Your calculated field
will look something like this:
CLng(Nz(DateDiff("d", [Date1], [Date2]),0))
and it should then accept the criteria.

 

[Guest]

Hello

Sorry for the long delay, have got tied up with other things! I dont get an
error message, I get negative numbers in the calculated column when a
delivery was not late i.e.
Requested Delivery Date Shipment Date Late by (days)

09/06/2004 08/06/2004 -1
I am using the following code:
Late by (days): DateDiff("d",[requested delivery date],[shipment date])

I dont want the negative numbers to be displayed. What should I do?

Thanks

DateDiff() returns a Long, so >0 should be acceptable criteria.

What error message are you seeing? Does it indicate that Access is
misunderstanding the data type of the calculated field? This is quite
common, as discussed in this link:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If that is the issue, you can typecast the result. Your calculated field
will look something like this:
CLng(Nz(DateDiff("d", [Date1], [Date2]),0))
and it should then accept the criteria.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have used the DateDiff command in a query to show the difference between
requested delivery date and shipment date. I now have some cases where the
deliveries were not late, giving me a negative number in the datediff
column.
I want to use my query's criteria to allow only positive numbers to show
(ie.
deliveries which were not late). I have tried >0 in criteria field of the
datediff column but this gives me an error.
Any suggestions?
Thanks

 

[Allen Browne]

You will have to put the Criteria back in. You said you got an error with
the criteria: Is that an error message, or does it just show #Error in the
column?

You did not include the typecast as suggested.

Do you have your fields reversed here?
Late by (days): CLng(DateDiff("d",[shipment date], [requested delivery
date]))

If you are only interested in the records where the [shipment date] was
after the [requested delivery date], you could just type:

[requested delivery date]

in the Criteria row under the [shipment date] field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello

Sorry for the long delay, have got tied up with other things! I dont get
an
error message, I get negative numbers in the calculated column when a
delivery was not late i.e.
Requested Delivery Date Shipment Date Late by (days)

09/06/2004 08/06/2004 -1
I am using the following code:
Late by (days): DateDiff("d",[requested delivery date],[shipment date])

I dont want the negative numbers to be displayed. What should I do?

Thanks

DateDiff() returns a Long, so >0 should be acceptable criteria.

What error message are you seeing? Does it indicate that Access is
misunderstanding the data type of the calculated field? This is quite
common, as discussed in this link:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If that is the issue, you can typecast the result. Your calculated field
will look something like this:
CLng(Nz(DateDiff("d", [Date1], [Date2]),0))
and it should then accept the criteria.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have used the DateDiff command in a query to show the difference
between
requested delivery date and shipment date. I now have some cases where
the
deliveries were not late, giving me a negative number in the datediff
column.
I want to use my query's criteria to allow only positive numbers to
show
(ie.
deliveries which were not late). I have tried >0 in criteria field of
the
datediff column but this gives me an error.
Any suggestions?
Thanks

 

[Guest]

Hi
Had a re-look at the query and the criteria, error is given (my mind is in
too many places today). Get a data type mismatch in expression error. Tried
your code, still get the same error when using >0 as criteria. Not sure what
else to try.....

Hello

Sorry for the long delay, have got tied up with other things! I dont get an
error message, I get negative numbers in the calculated column when a
delivery was not late i.e.
Requested Delivery Date Shipment Date Late by (days)

09/06/2004 08/06/2004 -1
I am using the following code:
Late by (days): DateDiff("d",[requested delivery date],[shipment date])

I dont want the negative numbers to be displayed. What should I do?

Thanks

DateDiff() returns a Long, so >0 should be acceptable criteria.

What error message are you seeing? Does it indicate that Access is
misunderstanding the data type of the calculated field? This is quite
common, as discussed in this link:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If that is the issue, you can typecast the result. Your calculated field
will look something like this:
CLng(Nz(DateDiff("d", [Date1], [Date2]),0))
and it should then accept the criteria.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have used the DateDiff command in a query to show the difference between
requested delivery date and shipment date. I now have some cases where the
deliveries were not late, giving me a negative number in the datediff
column.
I want to use my query's criteria to allow only positive numbers to show
(ie.
deliveries which were not late). I have tried >0 in criteria field of the
datediff column but this gives me an error.
Any suggestions?
Thanks

 

[Guest]

Hello Allen

This is the expression as copied from the query.
Expr1: CLng(Nz(DateDiff("d",[requested delivery date],[shipment date],0)))

When I add the criteria as >0, the query returns results and then brings up
the "datatype mismatch in criteria expression" error, the columns then all
contain #Name?
I am interested in all records where [shipment date] is greater than
[requested delivery date] and the number of days the shipment was late by, so
the suggestion to add > [requested delivery date] to the shipment column
criteria will not meet the requirement, but thanks for the suggestion.

You will have to put the Criteria back in. You said you got an error with
the criteria: Is that an error message, or does it just show #Error in the
column?

You did not include the typecast as suggested.

Do you have your fields reversed here?
Late by (days): CLng(DateDiff("d",[shipment date], [requested delivery
date]))

If you are only interested in the records where the [shipment date] was
after the [requested delivery date], you could just type:

[requested delivery date]

in the Criteria row under the [shipment date] field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello

Sorry for the long delay, have got tied up with other things! I dont get
an
error message, I get negative numbers in the calculated column when a
delivery was not late i.e.
Requested Delivery Date Shipment Date Late by (days)

09/06/2004 08/06/2004 -1
I am using the following code:
Late by (days): DateDiff("d",[requested delivery date],[shipment date])

I dont want the negative numbers to be displayed. What should I do?

Thanks

DateDiff() returns a Long, so >0 should be acceptable criteria.

What error message are you seeing? Does it indicate that Access is
misunderstanding the data type of the calculated field? This is quite
common, as discussed in this link:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If that is the issue, you can typecast the result. Your calculated field
will look something like this:
CLng(Nz(DateDiff("d", [Date1], [Date2]),0))
and it should then accept the criteria.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I have used the DateDiff command in a query to show the difference
between
requested delivery date and shipment date. I now have some cases where
the
deliveries were not late, giving me a negative number in the datediff
column.
I want to use my query's criteria to allow only positive numbers to
show
(ie.
deliveries which were not late). I have tried >0 in criteria field of
the
datediff column but this gives me an error.
Any suggestions?
Thanks

 

[Allen Browne]

Okay, what are [requested delivery date] and [shipment date]?
Are they fields in a table, or calculated query fields?
If table fields, what data type are they? Date/Time?

Does the query work successfully with the field:
Expr1: CLng(Nz(DateDiff("d",[requested delivery date],[shipment
date],0)))
and only show errors once you add the criteria?

If so, could you use the

[requested delivery date]

on the [shipment date] field, and then use the calculated field above
without criteria to give you the number of days?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is the expression as copied from the query.
Expr1: CLng(Nz(DateDiff("d",[requested delivery date],[shipment date],0)))

When I add the criteria as >0, the query returns results and then brings
up
the "datatype mismatch in criteria expression" error, the columns then all
contain #Name?
I am interested in all records where [shipment date] is greater than
[requested delivery date] and the number of days the shipment was late by,
so
the suggestion to add > [requested delivery date] to the shipment column
criteria will not meet the requirement, but thanks for the suggestion.

You will have to put the Criteria back in. You said you got an error with
the criteria: Is that an error message, or does it just show #Error in
the
column?

You did not include the typecast as suggested.

Do you have your fields reversed here?
Late by (days): CLng(DateDiff("d",[shipment date], [requested delivery
date]))

If you are only interested in the records where the [shipment date] was
after the [requested delivery date], you could just type:

[requested delivery date]

in the Criteria row under the [shipment date] field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello

Sorry for the long delay, have got tied up with other things! I dont
get
an
error message, I get negative numbers in the calculated column when a
delivery was not late i.e.
Requested Delivery Date Shipment Date Late by (days)

09/06/2004 08/06/2004 -1
I am using the following code:
Late by (days): DateDiff("d",[requested delivery date],[shipment date])

I dont want the negative numbers to be displayed. What should I do?

Thanks



:

DateDiff() returns a Long, so >0 should be acceptable criteria.

What error message are you seeing? Does it indicate that Access is
misunderstanding the data type of the calculated field? This is quite
common, as discussed in this link:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If that is the issue, you can typecast the result. Your calculated
field
will look something like this:
CLng(Nz(DateDiff("d", [Date1], [Date2]),0))
and it should then accept the criteria.


I have used the DateDiff command in a query to show the difference
between
requested delivery date and shipment date. I now have some cases
where
the
deliveries were not late, giving me a negative number in the
datediff
column.
I want to use my query's criteria to allow only positive numbers to
show
(ie.
deliveries which were not late). I have tried >0 in criteria field
of
the
datediff column but this gives me an error.
Any suggestions?
Thanks

 

[Guest]

Hello

The errors only came up once the criteria was added. Columns are in a query
with base table datatype being text, due to text such as "2 off by 10/06/05".
I have added this data to a comments column and made the datatype Date/Time,
then tried your suggestion and it all works fine.

Thanks you so much and have a great day.

Okay, what are [requested delivery date] and [shipment date]?
Are they fields in a table, or calculated query fields?
If table fields, what data type are they? Date/Time?

Does the query work successfully with the field:
Expr1: CLng(Nz(DateDiff("d",[requested delivery date],[shipment
date],0)))
and only show errors once you add the criteria?

If so, could you use the

[requested delivery date]

on the [shipment date] field, and then use the calculated field above
without criteria to give you the number of days?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is the expression as copied from the query.
Expr1: CLng(Nz(DateDiff("d",[requested delivery date],[shipment date],0)))

When I add the criteria as >0, the query returns results and then brings
up
the "datatype mismatch in criteria expression" error, the columns then all
contain #Name?
I am interested in all records where [shipment date] is greater than
[requested delivery date] and the number of days the shipment was late by,
so
the suggestion to add > [requested delivery date] to the shipment column
criteria will not meet the requirement, but thanks for the suggestion.

You will have to put the Criteria back in. You said you got an error with
the criteria: Is that an error message, or does it just show #Error in
the
column?

You did not include the typecast as suggested.

Do you have your fields reversed here?
Late by (days): CLng(DateDiff("d",[shipment date], [requested delivery
date]))

If you are only interested in the records where the [shipment date] was
after the [requested delivery date], you could just type:
[requested delivery date]
in the Criteria row under the [shipment date] field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello

Sorry for the long delay, have got tied up with other things! I dont
get
an
error message, I get negative numbers in the calculated column when a
delivery was not late i.e.
Requested Delivery Date Shipment Date Late by (days)

09/06/2004 08/06/2004 -1
I am using the following code:
Late by (days): DateDiff("d",[requested delivery date],[shipment date])

I dont want the negative numbers to be displayed. What should I do?

Thanks



:

DateDiff() returns a Long, so >0 should be acceptable criteria.

What error message are you seeing? Does it indicate that Access is
misunderstanding the data type of the calculated field? This is quite
common, as discussed in this link:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If that is the issue, you can typecast the result. Your calculated
field
will look something like this:
CLng(Nz(DateDiff("d", [Date1], [Date2]),0))
and it should then accept the criteria.


I have used the DateDiff command in a query to show the difference
between
requested delivery date and shipment date. I now have some cases
where
the
deliveries were not late, giving me a negative number in the
datediff
column.
I want to use my query's criteria to allow only positive numbers to
show
(ie.
deliveries which were not late). I have tried >0 in criteria field
of
the
datediff column but this gives me an error.
Any suggestions?
Thanks

 

[Allen Browne]

Okay, you have it working, and that's good.

Turns out the problems were caused by the fields being Text instead of
Date/Time. CVDate() might have sorted it out for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello

The errors only came up once the criteria was added. Columns are in a
query
with base table datatype being text, due to text such as "2 off by
10/06/05".
I have added this data to a comments column and made the datatype
Date/Time,
then tried your suggestion and it all works fine.

Thanks you so much and have a great day.

Okay, what are [requested delivery date] and [shipment date]?
Are they fields in a table, or calculated query fields?
If table fields, what data type are they? Date/Time?

Does the query work successfully with the field:
Expr1: CLng(Nz(DateDiff("d",[requested delivery date],[shipment
date],0)))
and only show errors once you add the criteria?

If so, could you use the

[requested delivery date]

on the [shipment date] field, and then use the calculated field above
without criteria to give you the number of days?

This is the expression as copied from the query.
Expr1: CLng(Nz(DateDiff("d",[requested delivery date],[shipment
date],0)))

When I add the criteria as >0, the query returns results and then
brings
up
the "datatype mismatch in criteria expression" error, the columns then
all
contain #Name?
I am interested in all records where [shipment date] is greater than
[requested delivery date] and the number of days the shipment was late
by,
so
the suggestion to add > [requested delivery date] to the shipment
column
criteria will not meet the requirement, but thanks for the suggestion.

:

You will have to put the Criteria back in. You said you got an error
with
the criteria: Is that an error message, or does it just show #Error in
the
column?

You did not include the typecast as suggested.

Do you have your fields reversed here?
Late by (days): CLng(DateDiff("d",[shipment date], [requested delivery
date]))

If you are only interested in the records where the [shipment date]
was
after the [requested delivery date], you could just type:
[requested delivery date]
in the Criteria row under the [shipment date] field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello

Sorry for the long delay, have got tied up with other things! I
dont
get
an
error message, I get negative numbers in the calculated column when
a
delivery was not late i.e.
Requested Delivery Date Shipment Date Late by (days)

09/06/2004 08/06/2004 -1
I am using the following code:
Late by (days): DateDiff("d",[requested delivery date],[shipment
date])

I dont want the negative numbers to be displayed. What should I do?

Thanks



:

DateDiff() returns a Long, so >0 should be acceptable criteria.

What error message are you seeing? Does it indicate that Access is
misunderstanding the data type of the calculated field? This is
quite
common, as discussed in this link:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If that is the issue, you can typecast the result. Your calculated
field
will look something like this:
CLng(Nz(DateDiff("d", [Date1], [Date2]),0))
and it should then accept the criteria.


I have used the DateDiff command in a query to show the
difference
between
requested delivery date and shipment date. I now have some cases
where
the
deliveries were not late, giving me a negative number in the
datediff
column.
I want to use my query's criteria to allow only positive numbers
to
show
(ie.
deliveries which were not late). I have tried >0 in criteria
field
of
the
datediff column but this gives me an error.
Any suggestions?
Thanks