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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Outline | Renee Hendershott | Page Layout | 2 | December 25th, 2004 02: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 |