MS Access 2010 - Setting the date the way I want.

Hi Experts,

I have this date on the SQL Server:
2008-11-11 00:00:00.000

On the Report of MS Access 2010 I get this:

mm/dd/yyyy

I want it to be:
yyyymmdd

Please help and thanks
Amour22015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can use this...

select convert(varchar(12),@field,112)

Open in new window


replace your field with the actual field name..

Saurabh....
0
Amour22015Author Commented:
I am new to all this.

I get this value from a Stored Procedure.
So you mean to change it on the SQL Server "SP".

Or do that in MS Access like:
=convert(varchar(12), Name,112

Thanks
0
Nick67Commented:
Two choices.
In the query that powers the report, you can format the field fed to the control.
Let's say the field is presently MyDate
Change the field to
MyFormattedDate:Format([MyDate],"yyyymmdd") and bind the control to the new field name MyFormattedDate

or

In the properties of the control, change the Format property.
Try giving it
"yyyymmdd" and see if it will play nice
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Nick67Commented:
I get this value from a Stored Procedure.
 So you mean to change it on the SQL Server "SP".


That is a third option if a sproc is in play.
You can change the sproc to return a format value.

BUT

What Saurabh has suggested will change your DateTime data to string data.
Access will no longer treat it as a date for sorting or calculating purposes.
0
Amour22015Author Commented:
Ok,

First let me say this is a "Text Box"

It is mentioned:
MyFormattedDate:Format([MyDate],"yyyymmdd") and bind the control to the new field name MyFormattedDate

can you explain in more detail? is MyFormatDate  in the Property Sheet of Access?

I tried:
In the properties of the control, change the Format property.
 Try giving it
 "yyyymmdd" and see if it will play nice
So I placed
"yyyymmdd" in the format section of the Property Sheet.

and all it did was give me:
yyyymmdd
in the field NOT the actual date

Please help and thanks
0
Nick67Commented:
Try without the quotes.
Format properties are finicky, which is why I gave them as a second choice.
Look below, though.
format textbox with date format
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
That worked thanks for your help
0
Nick67Commented:
MyFormattedDate:Format([MyDate],"yyyymmdd") and bind the control to the new field name MyFormattedDate

 can you explain in more detail? is MyFormatDate  in the Property Sheet of Access?


Queries are fun things, in and of themselves.
They take data from tables and other queries and pass it on to other objects.
You do NOT have to pass out the data strictly as the table gives it.
You can transform it in the query.

Let's say you have Table1
Table1 has fields A and B
In a query, you can transform the data to be passed out.
You could create
MyConcat:A&B
and it would pass out A and B concatenated together as a field named MyConcat.

Similarly with you Date field (whatever it is named -- I used MyDate as an example)
Format() is a built-in Access function.
Look it up in the help
Here, we will use it to transform your field (if it was named myDate) to the output you'd desire
Format(MyDate,"yyyymmddd")
Now, we are doing this in a query.
Every field that does not come directly from a table or other query requires a name (alias)
Or Access will assign it an ugly one like Expr01
So we'll give it one, MyFormattedDate
The name ( more properly the alias) is the part before the colon in
MyFormattedDate:Format(MyDate,"yyyymmddd")
Run the query and you will see a column called MyFormattedDate that has your MyDate data in the format you want to see.

Clear enough?
0
Amour22015Author Commented:
Ok, so you are mentioning using/creating another query to get the date from my "SP" and format that date using the new query.  That is clear, Thanks
0
Nick67Commented:
In the beginning you did not mention that the data was coming from a sproc.
I have tables linked to SQL Server.
For those, you add tables to a query and can manipulate the data before the query outputs it.
The underlying SQL is stuff like

"Select ThisColumn, ThatColumn, Format([MyDate],"yyyymmdd") as MyFormattedDate from SomeTable"
This is quite amenable to manipulation.

A pass-through to sproc is a different monster altogether.
All it has for SQL is

Exec uspWhateverSprocIsOfInterest
And this is not amenable to manipulation in the same way at all!

But if that pass-through query was named, say,  qryMySproc, you could create another query, add qryMySproc as a source table and then manipulate it in the same fashion as a linked table.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.