Date format issues

[budedwards]

Hopefully someone here can help



I recently purchased an electronic product from China

Now the product comes with a hand held device that donwloads the activity of the product

You then plug it into the PC via the com port and using a piece of software you can see the "audit trail"

Now from what i can work out it is using a MS Access database

My problems are thus

1) the database was password protected(i managed to work around this)
2) The major problem is the date format is yyyy/mm/dd and i need it to be dd/mm/yyyy

Of course i can alter Windows XP regional settings but this effects every other piece of software

I am happy to send anyone the software/database

Any help appreciated greatly

Bud

[alansidman]

Access actually stores dates in a serial format, ie. 40982 which equates to today March 14, 2012. So all you really need to do is change how it is formatted in your query or report to get it to look how you want.

[budedwards]

Hi Alan,

I printed off some info from the net but i must confess it is jibberish to me

I have read the piece about setting up custom date formats but alas i end up poking around the database and messing it up

I will persevere and i feel better now i know its possible

[budedwards]

Could anyone recommend someone who could look at this problem for me?

For a reasonable cost?

[alansidman]

Perhaps you can upload your database with some sample data in it. Make sure that it is not confidential material. Run a compact and repair before you upload to keep the size down.

[budedwards]

Perhaps you can upload your database with some sample data in it. Make sure that it is not confidential material. Run a compact and repair before you upload to keep the size down.

I'm happy to do that today, there is nothing confidential as the product is only being tested at the moment.

I shall upload the database and hopefully if possible someone can figure it out

[budedwards]

Here is the file attached

If anyone is able to look at it i will post the password in a private message to them

I didn't put the password on the document that is how it arrived

Basically my problem is the date format

The report prints out the date in yyyy/mm/dd

As you will all know the uk regional settings are dd/mm/yyyy and i want to know if the DB can be changed to match this??

[orange]

I opened your database.
There are no reports, so I can't tell you why something appears as it does.

I did look at your Table called "RECORD".

I created a query using

SELECT RECORD.Record_id
, RECORD.Field1
, RECORD.ddate
, RECORD.ttime
, RECORD.open_type
, Format([ddate],"dd/mm/yyyy") AS myFormatDDMMYYY
, Format([ddate],"yyyy/dd/mm") AS myFormatYYYYddmm
, Format([ddate],"yyyy/mmm/dd") AS FormatYYYYMMMDD
FROM RECORD;

to show you various options of the Format function. The output (partial output) is in the attached jpg.

My best guess is that the report you talk about has simply formatted the [ddate] field to produce a specific output.
If you can find the record source for the report in question, I think you'll find a query with some version of the Format function.

I'm not sure what the little purple triangle is at the lower right-- but it's in the table as well.

[pbaldy]

FYI, I changed the title of this thread from the meaningless "I do not understand MS Access at all" to "Date format issues".

[budedwards]

I opened your database.
There are no reports, so I can't tell you why something appears as it does.

I did look at your Table called "RECORD".

I created a query using

SELECT RECORD.Record_id
, RECORD.Field1
, RECORD.ddate
, RECORD.ttime
, RECORD.open_type
, Format([ddate],"dd/mm/yyyy") AS myFormatDDMMYYY
, Format([ddate],"yyyy/dd/mm") AS myFormatYYYYddmm
, Format([ddate],"yyyy/mmm/dd") AS FormatYYYYMMMDD
FROM RECORD;

to show you various options of the Format function. The output (partial output) is in the attached jpg.

My best guess is that the report you talk about has simply formatted the [ddate] field to produce a specific output.
If you can find the record source for the report in question, I think you'll find a query with some version of the Format function.

I'm not sure what the little purple triangle is at the lower right-- but it's in the table as well.

I'm not sure if your saying it is do-able or not?

[budedwards]

FYI, I changed the title of this thread from the meaningless "I do not understand MS Access at all" to "Date format issues".

Lets hope it helps!

[orange]

I'm not sure if your saying it is do-able or not?

I'm not sure what you are asking now.
From your sample data base, I showed you how to use the Format function to change the presentation of the field [ddate].

I specifically used a query against your table "RECORD".

In your earlier post you said the report was giving an incorrect/unacceptable presentation of Date. But there was no report in the database you provided.

Do some research on Report record source. You can try going to the report, go to design,check properties, check data and look at the record source.
Copy it and post it here for comments.

It would seem that simply adjusting the Report's record source (to format the date field the way you want it), would solve the issue.

If I have misunderstood, please clarify.

[budedwards]

I'm not sure what you are asking now.
From your sample data base, I showed you how to use the Format function to change the presentation of the field [ddate].

I specifically used a query against your table "RECORD".

In your earlier post you said the report was giving an incorrect/unacceptable presentation of Date. But there was no report in the database you provided.

Do some research on Report record source. You can try going to the report, go to design,check properties, check data and look at the record source.
Copy it and post it here for comments.

If I have misunderstood, please clarify.

Sorry Orange i don't understand how to do that?

You have the same database that i uploaded

i may not of clearly stated my problem

Is it possible to alter the database so the report comes out in the correct format without changing the regional settings of windows?

[orange]

There is NO REPORT in your database!! so I can not show you exactly what to do.

Yes it is possible to change the record source of a report to output dates in a specific format without changing the regional settings of windows

[budedwards]

There is NO REPORT in your database!! so I can not show you exactly what to do.

Yes it is possible to change the record source of a report to output dates in a specific format without changing the regional settings of windows

Ok thanks for your assistance