Date Formatting

[smil3y]

I have reviewed this topic and cannot find anything relative to my problem.

My database is Access and I am using cR 8.5. My database is created by a VB6 application and one of the entries in the table is a date field (incDate). This field is stored as a string intentionally (ie Jan 2003, Feb 2003 etc).

I use this date field in both a crosstab report and a chart and currently I have created a formula @SortDate which is CDATE({DB.incDate}). This seems to work but puts the date in the format 1/01/2003, 1/02/2003 etc.

I then tried TOTEXT(CDATE({DB.incDate}),"mmm yyyy&quot - very strange results. My date ends up something like MMM 2003 etc.

I then used CR8.5 with my report in the .rpt format and simply used CDATE(etc) to get the date then used format field to put it in the correct date format (ie Jan 2003) etc. This all worked and I then tried to do the same with my report in VB. Although the report designer from within VB is almost identical it does not allow me to format the formula driven date field of my crosstab as a date.

Looks like I have no option but to use a formula only - can someone please explain why my TOTEXT does not work. OR is there a better way to do it.

 

[lbass]

Case matters. Try:

TOTEXT(CDATE({DB.incDate}),"MMM yyyy&quot

-LB

 

[Madawc]

To expand on what lbass said, Totext uses M for Months and m for minutes, since it allows for both date and time. So m is minutes and mm is minutes with leading zeros. mmm is meaningless, and so was taken to be a literal that you wanted included.

Madawc Williams
East Anglia, Great Britain

 

[Reebo99]

The following list is referenced in the Crystal Reports help file, as mentioned before, this is case sensitive:

d
day of month without leading zero for single digits

dd
day of month with leading zero for single digits.

ddd
day of week as a three letter abbreviation

dddd
full name of day of week

M
month without leading zero for single digit

MM
month with leading zero for single digit

MMM
month as three letter abbreviation.

MMMM
full name of month

yy
last two digits of year

yyyy
full four digits of year

h
hours without leading zeros for single digits (12 hour)

hh
hours without leading zeros for single digits (12 hour).

H
hours without leading zeros for single digits (24 hour)

HH
hours with leading zeros for single digits (24 hour)

m
minutes without leading zeros for single digits

mm
minutes with leading zeros for single digits

s
seconds without leading zeros for single digits

ss
seconds with leading zeros for single digits

t, tt
single character or multiple character a.m./p.m. string



Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.