Need to express todays date as YYYYMMDD

[dgillz]

I need to express todays date as an 8 digit integer in a YYYYMMDD format in a MS Access query. I have tried Year(Now())*10000+month(Now())*100+day(now()), but I am not getting very far.

Any help appreciated.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[scriverb]

Use the following in a new column:
NewDateFormat: CLng(Format(Date(),"yyyymmdd&quot)

Let me know if this is what you were needing

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[dgillz]

I assume Date() has 3 arguments, month, day and year. How do I get these 3 arguments into this expression without hard coding it? I need this expression to return todays date in a YYYYMMDD format, wothout having to hard ccode the actual values for the date every day.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[r937]

try what bob suggested

Date() gives today's date


rudy

 

[dgillz]

When I use Bob's example, I get the following error when executing the query:

Compile error. in query expression 'CLng(Format(Date(),"yyyymmdd&quot)'

In fact if I simple make the expression date(), I get the a very similar error:


Compile error. in query expression 'Date()'

Ideas?



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[r937]

do you have any small tables handy? like, only a very few rows? try this --

select Now(), Date()
from yourtable

you should get today's date twice, with time and without, for as many rows as there are in the table

if you get an error on that query, i'd call microsoft


rudy

 

[dgillz]

Where so I go to type in these sql-like statements? I am using the create query wizard, and selecting databsae columns, and in an extra column I am putting the expressions in the thread above and getting the errors I have already mentioned.

I have never entered a select statement in my life in MS Access. I didn't even know you could do it. Is my approach the reason I am getting errors?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[scriverb]

You must have a library reference that is missing if you are getting that error. The Date() function must be available to your database for you to have any hope in using or displaying todays date. Check your references to see what is missing.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[dgillz]

What is a reference and how do I check it?

My only knowledge of "references" are those in VB/VBA. I am not writing any VB/VBA code here, I am using the simple query wizard to return a dataset. I have all the data I need except I need that I need the date field expressed in a YYYYMMDD format.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[scriverb]

Open a form in design view. Make a new one if you don't have one, don't worry it is where you will find the references. Click on the Code button on the button bar. Select from the Menu Tool/References. New window will pop up. Look for a reference to a library that is missing. You will have to refresh this referrence.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[MISMonkey]

If all you need to do is alter the way the date is presented in a queries results try a simple cut and swap approach by creating the following field in your query design window..

MyNewDate: Right([MyDate],4) & "/" & Mid([MyDate],4,2) & "/" & Left([MyDate],2)

[MyDate] would be the date you are trying to rearrange (in dd/mm/yyyy format).

Sometimes a simplistic approach works best

 

[barbola]

Some people get way to technical. All I would do is change the Format property for the date column in the query to YYYYMMDD and the date 5/29/2003 will appear as 20030529



Barbola
Curlers do it between the sheets!

http://www.curling.com

 

[scriverb]

barbola:
My origianl post of using:

NewDateFormat: CLng(Format(Date(),"yyyymmdd&quot

does just as you have suggested.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[barbola]

But that was VBA code, and not what the user is looking for. Use of VBA can be avoided in alot of instances by simply using the wizards for us VBA-illiterates.

I'm learning more and more VBA, because I know there is so much more I can do with a database using VBA.

DGillz mentions in his post he is using query wizard and not VBA or "SQL-like statements"





Barbola
Curlers do it between the sheets!

http://www.curling.com

 

[scriverb]

What I posted is the use of ACCESS functions to be used in a query. Just copy what I posted and in the QD Window paste it into a new column. It will return a long integer in the format requested.

Just moving the numbers of the date string around just creates another string in another order. You must use the CLng function create a numeric value.

This new column in the query will be a numeric value that can be used in a numeric expression.

NewDateFormat: CLng(Format(Date(),"yyyymmdd&quot)

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[barbola]

Yep that worked too! I apologize to all you VBA geeks for overreacting hehe.

barb.

Barbola
Curlers do it between the sheets!

http://www.curling.com