Using Between for DateField storing Text date

But Peter,

Deleteing the records will not be possible for me as it consists of the old bookings data.

Jigs :(
 
You have a promlem then. Can you go back and convert the old data to UK format? Access can handle various formats but you can't mix MM/DD and DD/MM as there is no way to tell what date 6/9/2005 is meant to be D/M? M/D?

Peter
 
When I tried to Convert whole field in DataType with Format DD/MM/YYYY, then it is mashing the format and does not help me. I tried. Peter Can u try this and help me please.

Jigs :o
 
I can do you a function that will convert US text dates in to UK text dates. Can you tell where one starts and the other finishes? in the test data you sent me they looked to be all mixed up together.

This function will do the conversion, save it in a module and save the module with a differnt name to the function.

Function DateSwap(strIn As String) As String
' takes a date in text in M(M)/D(D)/YYYY and outputs Text DD/MM/YYYY
Dim aVar
aVar = Split(strIn, "/")
DateSwap = Right("00" & aVar(1), 2) & "/" & Right("00" & aVar(0), 2) & "/" & aVar(2)
End Function

If you know which record the formats change on then you could use an update query like this to alter your data

UPDATE bookings SET bookings.arrival_date = dateswap([arrival_date])
WHERE (((bookings.id)<300));

where the 300 would be the first ID of UK dates.
Another posibility would be to tempoarily add an extra column to the table to flag the date as US/UK and use that as a filter.

Make sure that you do any testing on copied data first!!


Peter
 
Peter, all the DateSerial manipulation could be avoided by using CDate(). This works fine on text versions of dates in UK format and converts them to Date/Time in UK format.

Jigs, you have a field called DATE. This is bad news since DATE is a reserved word in Access. Change it to something else, otherwise it will eventually cause you an error.
 
Yep :o I even used it in part of one of the answers :rolleyes:

Still would have had a problem with the first 500 or so dates that have been mashed. Looking at them it looks supiciusly as if they were originaly UK dates that got processed into US but only got changed where they would not fit the US format.

I hope that you have the original data available still Jigs!

Peter
 
I have original data available but that original data also consists of all the mash of date formats.

I will not be able to delete any of the records in original data. The only thing I could do is change the data formats of all the data manually to dd/mm/yyyy.

Is there any other way ? if yes, please suggest so that the solution given by PETER can be implemented. :rolleyes:

Thanks

Jigs
 
In your database, it seems records with ID#<=640 are of US d/m/yyyy format, whereas ID#>640 are of UK dd/mm/yyyy format.

Based on this observation/assumption, I have added a query in your database. When the query is run on a system using the UK dd/mm/yyyy format, it will return the UK dates as follows:-

Code:
id	arrival_date	AssumedFormat	[b]UKDates[/b]
  1	6/20/2004	US Format	20/06/2004
..................................................
635	5/15/2005	US Format	15/05/2005
636	3/3/2005	US Format	03/03/2005
637	4/3/2005	US Format	03/04/2005
638	4/4/2005	US Format	04/04/2005
639	5/4/2005	US Format	04/05/2005
640	5/3/2005	US Format	03/05/2005

645	05/6/2005	UK Format	05/06/2005
646	07/03/2005	UK Format	07/03/2005
647	07/03/2005	UK Format	07/03/2005
648	08/03/2005	UK Format	08/03/2005
649	03/08/2005	UK Format	03/08/2005
650	03/08/2005	UK Format	03/08/2005
651	03/11/2005	UK Format	03/11/2005
652	14/03/2005	UK Format	14/03/2005
653	03/20/2005	UK Format	20/03/2005
654	15/03/2005	UK Format	15/03/2005
..................................................
869	17/06/2005	UK Format	17/06/2005
870	13/06/2005	UK Format	13/06/2005
871	13/06/2005	UK Format	13/06/2005

Note: As I used CDate() in the query for ID#>640, the query runs correctly only on systems using the UK date format.
.
 

Attachments

I suspect that the date were mashed using something like Cdate in US format. If you look at the runs of dates they appear to be OK except where they would not fit the format (month over 12) then they were changed. Look at the sequence from 78 to 171 if we take that to be in UK format then you have a nice daily incrementing list until 172 when the date pattern reverse itself.
I would try using Cdate on the whole column to reverse the original mashing.

Peter
 
Then the date specification might have been switched more than once in the past.

In that case, I would add a field in the table to manually flag either the UK dates or the US dates, depending on whichever are fewer.

And then base the query expression on this flagged field instead of the [ID] field.


Using CDate() on the whole column on a UK system would erroneously convert any US dates where the first two numbers are less than 13 (e.g. 3/11/2005 i.e. March 11) to UK dates (i.e. 3 November.)
.
 
Last edited:
That was the point, I think that has already happened so doing it again should undo it.

the other question of course is does that part of the historical data matter? if not then just set to a format that wont upset access and forget it :)

Peter
 
Yes Peter , you r true. Before I was handed over the project, the previous programmer had change the dataformat more then once and thats why it is creating problem ...

But your suggestion is good to change the dataformat. The data which is in the demo database are data which is on my local server and which is 1/5 of the original. So to change the date format of the original database would be extremely difficult for me.

I will try with the additional flag field for data format type.

Jigs :)
 
I would try using Jon's query on a copy of your full data. You will need to adjust the crieria (ID#>640) to suit your full data and look over the dates produced to see if it looks right. I would also try it with the filter set to ID#>0 so that it applies the CDate() function to all data. This will let you compare the affect of both methods on your data.

Peter
 
I had tried criteria (ID#>640) for the date format which are different then what needed and applied Jon's query but still it is not giving me the appropriare result. Peter Can u please try this for me and attach the query

Thanks Peter,

Jigs
 
did you try with ID#>0 as I suggested as this is all I would have been doing

Peter
 
Yes, I did tried with ID#>0 but with this query all the missalaneous formats of dates gets mixed up and doesn't corelate with the desired result

I will try with other criteria and check

Jigs
 
I did tried with ID#>0 but with this query all the missalaneous formats of dates gets mixed up and doesn't corelate with the desired result
Changing ID#>640 to ID#>0 in Jon K's query was the same as assuming that the records in the table were all UK text dates, which of course would not work.

In his second post, Jon has given an approach that works.

^
 
The problem is that if the date field is "text" oriented, you should not compare against the date fields anyway.

Use CDate to convert a text-oriented date field into a date that is linear with respect to time flow. THEN you can meaningfully use "Between {a} and {b}" as an operator to select dates.

Doesn't matter WHERE you get the dates from for the comparison, but if they are entered as text, you should also convert THESE items (i.e. {a} and {b}) with CDate.

Your ARRIVAL DATE field must be compared using CDate([ARRIVAL DATE]) if you are going to use "#date-string#" as the comparator.

Using DateSerial is wrong because that is an Excel subroutine. The pound-sign syntax is Access date syntax. Believe it or don't, Access and Excel have slightly different standards for dates, so you are doing an apples to oranges comparison.
 
Last edited:
EMP did you look at Jigs data?

The point I was trying to make was that they were not really US dates but UK that had been mashed by conversion.

Code:
id	arrival_date	Assume US	Assume Mashed
76	10/23/2004	23-Oct-04	23-Oct-04
77	10/30/2004	30-Oct-04	30-Oct-04
81	5/11/2004	11-May-04	05-Nov-04
94	6/11/2004	11-Jun-04	06-Nov-04
105	7/11/2004	11-Jul-04	07-Nov-04
106	8/11/2004	11-Aug-04	08-Nov-04
115	9/11/2004	11-Sep-04	09-Nov-04
116	10/11/2004	11-Oct-04	10-Nov-04
171	12/11/2004	11-Dec-04	12-Nov-04
172	11/13/2004	13-Nov-04	13-Nov-04
193	12/20/2004	20-Dec-04	20-Dec-04
194	11/20/2004	20-Nov-04	20-Nov-04
216	1/12/2004	12-Jan-04	01-Dec-04
217	12/1/2004	01-Dec-04	12-Jan-04
223	1/12/2004	12-Jan-04	01-Dec-04
225	12/1/2004	01-Dec-04	12-Jan-04
229	11/16/2004	16-Nov-04	16-Nov-04
230	12/1/2004	01-Dec-04	12-Jan-04
231	11/16/2004	16-Nov-04	16-Nov-04
232	12/2/2004	02-Dec-04	12-Feb-04

I have removed duplicate dates, if you look at the sequences which column looks most reasonable?

the set of dates that go 1/12/04 and 12/01/04 in the original look suspiciously as if they have been enterd bothways though, but could just be coincidence :D

Peter
 
Originally Posted by Jon K
Then the date specification might have been switched more than once in the past.

In that case, I would add a field in the table to manually flag either the UK dates or the US dates, depending on whichever are fewer.

And then base the query expression on this flagged field instead of the [ID] field.


Using CDate() on the whole column on a UK system would erroneously convert any US dates where the first two numbers are less than 13 (e.g. 3/11/2005 i.e. March 11) to UK dates (i.e. 3 November.)
.

Peter,

I put the records that you selected in a table.

Then following Jon K's approach, I manually flagged only those records where "the first two numbers are less than 13" and which I determined to be US text dates that needed conversion (i.e. the four records ID# 217, 225, 230 and 232 out of the twenty.)

I modified Jon K's query, basing it on the flagged field instead of the ID field. Now when I ran the query on a system that used the UK date format of dd/mm/yyyy, I got the dates all come out correct in UK format.

Code:
id	arrival_date	[b]USDate		[color=red]UKDates[/color]		Converted
			ThatNeeds
			Conversion[/b]
 76	10/23/2004	 0		23/10/2004	
 77	10/30/2004	 0		30/10/2004	
 81	5/11/2004	 0		05/11/2004	
 94	6/11/2004	 0		06/11/2004	
105	7/11/2004	 0		07/11/2004	
106	8/11/2004	 0		08/11/2004	
115	9/11/2004	 0		09/11/2004	
116	10/11/2004	 0		10/11/2004	
171	12/11/2004	 0		12/11/2004	
172	11/13/2004	 0		13/11/2004	
193	12/20/2004	 0		20/12/2004	
194	11/20/2004	 0		20/11/2004	
216	1/12/2004	 0		01/12/2004	
217	12/1/2004	-1		01/12/2004	Converted
223	1/12/2004	 0		01/12/2004	
225	12/1/2004	-1		01/12/2004	Converted
229	11/16/2004	 0		16/11/2004	
230	12/1/2004	-1		01/12/2004	Converted
231	11/16/2004	 0		16/11/2004	
232	12/2/2004	-1		02/12/2004	Converted

I don't see anything wrong with Jon K's approach. Since the records that need to be converted are manually picked, the final results should be correct, unless of course you have picked some wrong records.

^
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom