I have a field Trans date with different formats, some are DD/MM/YYYY and 
some are DD/MM/YYYY/HH/MM/SS. I want to use the Between begin date and End 
date. How do I convert all the date to the format DD/MM/YYYY/HH/MM/SS
Please help.
pon

Formatting has nothing to do with how the data are stored.  It only presents 
the data in the specified format.

It is possible some of your records may have date and time and others only 
time.  It should not be that way, but it is.

If you are trying to compare on date only when there may be time in the 
field, you can format the values in the query so you get a corrent comparison.
-- 
Dave Hargis, Microsoft Access MVP


"pon" wrote:

> I have a field Trans date with different formats, some are DD/MM/YYYY and 
> some are DD/MM/YYYY/HH/MM/SS. I want to use the Between begin date and End 
> date. How do I convert all the date to the format DD/MM/YYYY/HH/MM/SS
> Please help.
> pon

You don't have if the date are date_time. By default, those without time 
will be at midnight (00:00:00)

If the values are strings, better to have your default regional setting to 
dd/mm/yyyy  (In the USA, it would probably be  mm/dd/yyyy ). If you cannot 
change the regional setting (because it has to run on a PC which is not 
yours), you can use DateSerial:

DateSerial( Mid(yourString, 7, 4),  Mid(yourString, 4, 2), Mid(yourString, 
1, 2) )  + TimeValue( CDate( yourString)  )


to convert the data into a date_time value.



Vanderghast, Access MVP


"pon" <pon@discussions.microsoft.com> wrote in message 
news:472DD389-E305-4A76-865A-23D02136D5BC@microsoft.com...
>I have a field Trans date with different formats, some are DD/MM/YYYY and
> some are DD/MM/YYYY/HH/MM/SS. I want to use the Between begin date and End
> date. How do I convert all the date to the format DD/MM/YYYY/HH/MM/SS
> Please help.
> pon 

I created a expression
TRANS_DATE: Format([FGBTRNH_TRANS_DATE],"mm/dd/yyyy hh:nn:ss")
which worked.
Thank you
pon

"Michel Walsh" wrote:

> You don't have if the date are date_time. By default, those without time 
> will be at midnight (00:00:00)
> 
> If the values are strings, better to have your default regional setting to 
> dd/mm/yyyy  (In the USA, it would probably be  mm/dd/yyyy ). If you cannot 
> change the regional setting (because it has to run on a PC which is not 
> yours), you can use DateSerial:
> 
> DateSerial( Mid(yourString, 7, 4),  Mid(yourString, 4, 2), Mid(yourString, 
> 1, 2) )  + TimeValue( CDate( yourString)  )
> 
> 
> to convert the data into a date_time value.
> 
> 
> 
> Vanderghast, Access MVP
> 
> 
> "pon" <pon@discussions.microsoft.com> wrote in message 
> news:472DD389-E305-4A76-865A-23D02136D5BC@microsoft.com...
> >I have a field Trans date with different formats, some are DD/MM/YYYY and
> > some are DD/MM/YYYY/HH/MM/SS. I want to use the Between begin date and End
> > date. How do I convert all the date to the format DD/MM/YYYY/HH/MM/SS
> > Please help.
> > pon 
> 
> 
> 

You are aware that you have a string and that it is highly dependant on the 
regional setting as to get the result right, right?

In fact, it is more complex than that, but just for illustration of the 
possible problems (that you seem to not be fully aware), try the simple line 
of code in the Immediate debug window:

?   Format( CDate("31/12/2007"), "long date"),  Format(CDate("12/31/2007"), 
"long date")
                    Monday, December 31, 2007 
Monday, December 31, 2007



See, you get the same date, but the first one was typed as dd/mm/yyyy and 
the second as "mm/dd/yyyy".

The conclusion is to AVOID strings representation of date, if you don't 
control very well the regional setting. Definitively "10/11/12" can be in 
October (in USA), or in November (in England) ; the 10th of November 2012, 
or, with ISO, the 12th of November 2010. String, as a date, is far, far, 
from being 'safe'.


But sure, it is your data, not mine, after all.


Vanderghast, Access MVP



"pon" <pon@discussions.microsoft.com> wrote in message 
news:F3A2B9A1-B477-410A-AEC9-21E1CCE03F09@microsoft.com...
>I created a expression
> TRANS_DATE: Format([FGBTRNH_TRANS_DATE],"mm/dd/yyyy hh:nn:ss")
> which worked.
> Thank you
> pon
>
> "Michel Walsh" wrote:
>
>> You don't have if the date are date_time. By default, those without time
>> will be at midnight (00:00:00)
>>
>> If the values are strings, better to have your default regional setting 
>> to
>> dd/mm/yyyy  (In the USA, it would probably be  mm/dd/yyyy ). If you 
>> cannot
>> change the regional setting (because it has to run on a PC which is not
>> yours), you can use DateSerial:
>>
>> DateSerial( Mid(yourString, 7, 4),  Mid(yourString, 4, 2), 
>> Mid(yourString,
>> 1, 2) )  + TimeValue( CDate( yourString)  )
>>
>>
>> to convert the data into a date_time value.
>>
>>
>>
>> Vanderghast, Access MVP
>>
>>
>> "pon" <pon@discussions.microsoft.com> wrote in message
>> news:472DD389-E305-4A76-865A-23D02136D5BC@microsoft.com...
>> >I have a field Trans date with different formats, some are DD/MM/YYYY 
>> >and
>> > some are DD/MM/YYYY/HH/MM/SS. I want to use the Between begin date and 
>> > End
>> > date. How do I convert all the date to the format DD/MM/YYYY/HH/MM/SS
>> > Please help.
>> > pon
>>
>>
>>