Hi,

I am having some problem with the display format of dates.
As I have to compare the months of dates, there's a problem in abstracting the month of the record of dates from the access database. Even if i managed to abstract the month in the record, I have problem displaying it in the correct manner.

When the dates display in the record, I have formatted it in mm/dd/yy. Only when the double digits of the dates are keyed in, the dates are displayed in dd/mm/yy instead. I have formatted it using formatDateTime(), but it doesn't work.
The data type in my access is time/date type and i did not format it in the access.


Anyone can help uniform the display?

Please go through this post. The comment will help you fix this problem.

http://p2p.wrox.com/topic.asp?TOPIC_ID=20834

Madhu

Hi Madhu,

It isn't exactly solving the problem becos I have checked everything from regional settings and formatted wad mat41 did to the dates. But it still can't be solved. I had a function:

Function FormatDateChanged(val)
    if val<>"" then
        FormatDateChanged = right((month(val)+100),2) & " / " & right((day(val)+100),2) & " / " & year(val)
    else
        FormatDateChanged = ""
    end if
end function

But weird thing is the display of the dates do not tally.

I tried having the mmddyyyy function done, but it still doesn't help at all.

The dates range 1-12 still come out in the dd/mm/yyyy, but range 13-31 will come out in mm/dd/yyyy format. Why is this so? I'm getting stressed with the format..had been changing the format of dates alot of times.

Is there anyway to solve this? I'm using access instead of SQL server...

When you insert into database, you form a string like

#MM/DD/YYYY# and then insert / update. You may be omitting the # delimiter.

Then While retrieving, you extract date, month and year parts seperately using functions day, month/monthname and year. Then formulate the string of date from these values.

This will work I am sure. (Please do not forget to use the # delimiter).

Hi madhukp,

I don't exactly know how the # delimiter is used.Can u actually show me what u mean when u say that # is used when form in a string?

OK, here is the sample SQL.

table name : tbl_date_content

fields : dt_record_id primary key, auto number
         sample_date datetime
         other_field varchar(50)

Let dt_entry_date be the variable which holds the date to be inserted into above table. You may use date() function instead of this variable if you want to insert current date.

Let str_sample_string be the variable which holds the content to be stored in other_field column.

The SQL for insertion is

"INSERT INTO tbl_date_content (sample_date, other_field) VALUES(#" & month(dt_entry_date) & "/" & day(dt_entry_date) & "/" & year(dt_entry_date) & "#, '" & trim(replace(str_sample_string, "'", "''")) & "')"

Let i_record_id be the variable which holds the primary key of the record being edited.

The Update SQL is

"UPDATE tbl_date_content SET sample_date=#" & month(dt_entry_date) & "/" & day(dt_entry_date) & "/" & year(dt_entry_date) & "#, other_field='" & trim(replace(str_sample_string, "'", "''")) & "' WHERE dt_record_id=" & i_record_id

While retrieving you can use the query

"SELECT sample_date, other_field FROM tbl_date_content"

Then assign sample_date field to dt_entry_date as below.

dt_entry_date=rst("sample_date")

where rst is the corresponding recordset used to fetch data.

Then display dt_entry_date either by
1) extracting date, month and year parts and joining them in the required format (mm/dd/yyyy or dd/mm/yyyy or yyyy/mm/dd or any other format preferred by client)

2) Set LCID of session. Then use formatdatetime function.

SESSION.LCID = 1036 ' for french
Response.Write(FormatDateTime(dt_entry_date, vbShortDate))

Ha! It's working!
Thanks madhukp!

Please use following format
dd-MM-yyyy
it will display the correct format