Max of Records Date in Text Format Problem

[Guest]

Hi, I have a Query, which is supposed to bring back the max date (The
greatest Date) of all records from a table.

The table is an Order Details Table relating on the many side to a master
Order Header table.
It is used to get the ETA's for the parts, get the last date relating to
that particular order and return that value to my Orders Header Table in the
Overall ETA. By this VBA: (ValeID is the PK, DepotETA is the ETA of each part
on the Order Details and the OverallETACalculation is the Query))

Private Sub OverallETA_DblClick(Cancel As Integer)

Me!OverallETA = DLookup("[MaxOfDepotETA]", "OverallETACalculation",
"[ValeID] = Forms!Orders!ValeID")

End Sub

The problem I have is this. My DepotETA cannot be formatted as a Date/Time
Field because if there is no ETA I have to put the Text in "Trying To Source"
Therefore has to be formatted as a text field. This results that the Max
lookup looks up the maximum of the first to digits of the date before the "\"
identifier.

Therefore looks at the highest value compared to days.
IE -

dd /mm/yy

07/01/04 is returned instead of 05/01.04 because 07 is higher in value then
05 (I'm using English dates by the way, because I'm in the UK)

However it does return the text to my Order Header "Trying To Source"
instead of a date.

Can anyone suggest a way around this problem, where my query could look at
the whole date rather than the days?


Thanks very much in advance

Andi Lee Davis.

 

[Allen Browne]

The only reliable solution will be to change the text field into a date/time
field. Leave it blank if you are still "trying to source". You can actually
put the text "Trying to source" onto your report in place of Null if you
wish.

If you cannot do that, you will have to store the text value of the date
like the Koreans do as yyyymmdd, so that the most significant parts are
first. Otherwise it will not sort correctly.

 

[Guest]

Thanks Allen,

That should be a better idea. I will resort to that if no one else will be
able to help me.
However thinking about it, it may be using the date as a fraction using the
date seperator as a divide.

02/01/2005 = 2/2005 = 0.0009975

It may be taking the largest value in which case

07/01/2004 = 7/2004 = 0.003493
07/01/2005 = 7/2005 = 0.0034912

Both numbers are larger because 7>2

Is there a way to do it in VB instead of a query?

Thanks

Andi

Thanks

The only reliable solution will be to change the text field into a date/time
field. Leave it blank if you are still "trying to source". You can actually
put the text "Trying to source" onto your report in place of Null if you
wish.

If you cannot do that, you will have to store the text value of the date
like the Koreans do as yyyymmdd, so that the most significant parts are
first. Otherwise it will not sort correctly.

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

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

Hi, I have a Query, which is supposed to bring back the max date (The
greatest Date) of all records from a table.

The table is an Order Details Table relating on the many side to a master
Order Header table.
It is used to get the ETA's for the parts, get the last date relating to
that particular order and return that value to my Orders Header Table in
the
Overall ETA. By this VBA: (ValeID is the PK, DepotETA is the ETA of each
part
on the Order Details and the OverallETACalculation is the Query))

Private Sub OverallETA_DblClick(Cancel As Integer)

Me!OverallETA = DLookup("[MaxOfDepotETA]", "OverallETACalculation",
"[ValeID] = Forms!Orders!ValeID")

End Sub

The problem I have is this. My DepotETA cannot be formatted as a Date/Time
Field because if there is no ETA I have to put the Text in "Trying To
Source"
Therefore has to be formatted as a text field. This results that the Max
lookup looks up the maximum of the first to digits of the date before the
"\"
identifier.

Therefore looks at the highest value compared to days.
IE -

dd /mm/yy

07/01/04 is returned instead of 05/01.04 because 07 is higher in value
then
05 (I'm using English dates by the way, because I'm in the UK)

However it does return the text to my Order Header "Trying To Source"
instead of a date.

Can anyone suggest a way around this problem, where my query could look at
the whole date rather than the days?


Thanks very much in advance

Andi Lee Davis.

 

[Allen Browne]

If the field type is Text, I would expect Access to perform a text
comparion. There are contexts where the value might be treated as a numeric
expression, but I would not expect that on a Text field unless you
introduced Eval().

Not sure what you mean about doing it in VB. Using DMax() within your code
to retrieve a value from the table perhaps? You sure don't want to be
loading all the records into memory and then scanning them all in code in
order to find the largest one.

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

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

Thanks Allen,

That should be a better idea. I will resort to that if no one else will be
able to help me.
However thinking about it, it may be using the date as a fraction using
the
date seperator as a divide.

02/01/2005 = 2/2005 = 0.0009975

It may be taking the largest value in which case

07/01/2004 = 7/2004 = 0.003493
07/01/2005 = 7/2005 = 0.0034912

Both numbers are larger because 7>2

Is there a way to do it in VB instead of a query?

Thanks

Andi

Thanks

The only reliable solution will be to change the text field into a
date/time
field. Leave it blank if you are still "trying to source". You can
actually
put the text "Trying to source" onto your report in place of Null if you
wish.

If you cannot do that, you will have to store the text value of the date
like the Koreans do as yyyymmdd, so that the most significant parts are
first. Otherwise it will not sort correctly.


message

Hi, I have a Query, which is supposed to bring back the max date (The
greatest Date) of all records from a table.

The table is an Order Details Table relating on the many side to a
master
Order Header table.
It is used to get the ETA's for the parts, get the last date relating
to
that particular order and return that value to my Orders Header Table
in
the
Overall ETA. By this VBA: (ValeID is the PK, DepotETA is the ETA of
each
part
on the Order Details and the OverallETACalculation is the Query))

Private Sub OverallETA_DblClick(Cancel As Integer)

Me!OverallETA = DLookup("[MaxOfDepotETA]", "OverallETACalculation",
"[ValeID] = Forms!Orders!ValeID")

End Sub

The problem I have is this. My DepotETA cannot be formatted as a
Date/Time
Field because if there is no ETA I have to put the Text in "Trying To
Source"
Therefore has to be formatted as a text field. This results that the
Max
lookup looks up the maximum of the first to digits of the date before
the
"\"
identifier.

Therefore looks at the highest value compared to days.
IE -

dd /mm/yy

07/01/04 is returned instead of 05/01.04 because 07 is higher in value
then
05 (I'm using English dates by the way, because I'm in the UK)

However it does return the text to my Order Header "Trying To Source"
instead of a date.

Can anyone suggest a way around this problem, where my query could look
at
the whole date rather than the days?


Thanks very much in advance

Andi Lee Davis.

 

[Guest]

Ok I guess it would be easier to re-format the Field as a Date Time and Put
the Null Value to the report to Text "Trying To Source"

Thanks for all your help.

Andi

If the field type is Text, I would expect Access to perform a text
comparion. There are contexts where the value might be treated as a numeric
expression, but I would not expect that on a Text field unless you
introduced Eval().

Not sure what you mean about doing it in VB. Using DMax() within your code
to retrieve a value from the table perhaps? You sure don't want to be
loading all the records into memory and then scanning them all in code in
order to find the largest one.

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

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

Thanks Allen,

That should be a better idea. I will resort to that if no one else will be
able to help me.
However thinking about it, it may be using the date as a fraction using
the
date seperator as a divide.

02/01/2005 = 2/2005 = 0.0009975

It may be taking the largest value in which case

07/01/2004 = 7/2004 = 0.003493
07/01/2005 = 7/2005 = 0.0034912

Both numbers are larger because 7>2

Is there a way to do it in VB instead of a query?

Thanks

Andi

Thanks

The only reliable solution will be to change the text field into a
date/time
field. Leave it blank if you are still "trying to source". You can
actually
put the text "Trying to source" onto your report in place of Null if you
wish.

If you cannot do that, you will have to store the text value of the date
like the Koreans do as yyyymmdd, so that the most significant parts are
first. Otherwise it will not sort correctly.


message
Hi, I have a Query, which is supposed to bring back the max date (The
greatest Date) of all records from a table.

The table is an Order Details Table relating on the many side to a
master
Order Header table.
It is used to get the ETA's for the parts, get the last date relating
to
that particular order and return that value to my Orders Header Table
in
the
Overall ETA. By this VBA: (ValeID is the PK, DepotETA is the ETA of
each
part
on the Order Details and the OverallETACalculation is the Query))

Private Sub OverallETA_DblClick(Cancel As Integer)

Me!OverallETA = DLookup("[MaxOfDepotETA]", "OverallETACalculation",
"[ValeID] = Forms!Orders!ValeID")

End Sub

The problem I have is this. My DepotETA cannot be formatted as a
Date/Time
Field because if there is no ETA I have to put the Text in "Trying To
Source"
Therefore has to be formatted as a text field. This results that the
Max
lookup looks up the maximum of the first to digits of the date before
the
"\"
identifier.

Therefore looks at the highest value compared to days.
IE -

dd /mm/yy

07/01/04 is returned instead of 05/01.04 because 07 is higher in value
then
05 (I'm using English dates by the way, because I'm in the UK)

However it does return the text to my Order Header "Trying To Source"
instead of a date.

Can anyone suggest a way around this problem, where my query could look
at
the whole date rather than the days?


Thanks very much in advance

Andi Lee Davis.