A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Max of Records Date in Text Format Problem



 
 
Thread Tools Display Modes
  #1  
Old February 1st, 2005, 02:55 AM
Andi Lee Davis
external usenet poster
 
Posts: n/a
Default Max of Records Date in Text Format Problem

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.




Ads
  #2  
Old February 1st, 2005, 03:37 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andi Lee Davis" wrote in 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.



  #3  
Old February 1st, 2005, 09:53 AM
Andi Lee Davis
external usenet poster
 
Posts: n/a
Default

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 72

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

Thanks

Andi

Thanks

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andi Lee Davis" wrote in 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.




  #4  
Old February 1st, 2005, 11:08 AM
Allen Browne
external usenet poster
 
Posts: n/a
Default

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andi Lee Davis" wrote in message
...
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 72

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

Thanks

Andi

Thanks

"Allen Browne" wrote:

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.


"Andi Lee Davis" wrote in
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.



  #5  
Old February 1st, 2005, 12:45 PM
Andi Lee Davis
external usenet poster
 
Posts: n/a
Default

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

"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andi Lee Davis" wrote in message
...
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 72

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

Thanks

Andi

Thanks

"Allen Browne" wrote:

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.


"Andi Lee Davis" wrote in
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.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Outline Renee Hendershott Page Layout 2 December 25th, 2004 03:49 PM
Concatenatd fields in a query for a searching form Marc Running & Setting Up Queries 8 October 19th, 2004 08:49 PM
change date format to text confused Worksheet Functions 1 September 30th, 2004 03:50 AM
Format problem : "Text" & Number; Number -> Standard format. How? BerHav Setting Up & Running Reports 0 August 19th, 2004 06:31 PM
Any way of forcing Plain Text Format universally? Astra General Discussions 0 June 4th, 2004 09:34 AM


All times are GMT +1. The time now is 03:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 OfficeFrustration.
The comments are property of their posters.