Solved

need to convert yyyymmdd back to yyyy/mm/dd or mm/dd/yyyy without getting '#Error'

Posted on 2004-10-13
14
383 Views
Last Modified: 2012-08-13

I converted some dates to yyyymmdd for some calculations and now I need to convert the results back to normal dates.

1. Date ex. - '10/1/2004'
2. ChangeDateTo8: Format([Date],"yyyymmdd") ex. '20041001'
3. ChangeBackToSeperatedlDate: Format([ChangeDateTo8],"yyyy/mm/dd") ex. '#Error'

How can I convert back without an error?
0
Comment
Question by:pixellight
  • 7
  • 6
14 Comments
 
LVL 12

Expert Comment

by:pique_tech
ID: 12300988
You need to use the same "original" source in 3 as you did in 2.  Formatting never changes the value of a date, only its appearance.
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12301002
for that matter, you can just do any date calculation on the original field.  Changing its formatting is not required for date calculations.
0
 
LVL 84
ID: 12301017
Where are you calling this? Dates are stored in Access tables as numeric values; the Format functions exist so that you can present them in a more readable format for endusers (or so that you can ensure that queries return the correct records, etc). You use Format functions as needed; formatting doesn't change the way dates are stored in the table (they are still stored as numeric values).

Can you post more of your code? Are you trying to store this in a table?
0
 

Author Comment

by:pixellight
ID: 12301200
it's in a query. Tried to simplify it above but here are the details:

Intro DatePrep: DateAdd("ww",-[Intro (Live CPL - weeks)],[Live CPL Date2])

Intro Date: Format([Intro DatePrep],"yyyymmdd")  - because I couldn't get all my compares (not shown) to work in other formats

Made Intro: IIf([MinConcentraDate]<[Intro Date],"100%","0%")

What I need next is to be able (in this quesry or new one) is to filter the query down to a particular month. What % made intro and what didn't for that month. Need help isolating one months worth of results. '>20040831 and <20041001' (for the month of september) as the critiria in Intro Date: Format([Intro DatePrep],"yyyymmdd") doesn't work and neither does >08/31/2004 and <10/01/2004 (asks for 'Live CPL Date2')
0
 

Author Comment

by:pixellight
ID: 12301229
and neither does >08/31/2004 and <10/01/2004 (asks for 'Live CPL Date2') used in the criteria for Intro DatePrep: DateAdd("ww",-[Intro (Live CPL - weeks)],[Live CPL Date2])
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12301319
How about
WHERE [Intro DatePrep] Between #9/1/2004# And #10/1/2004#

(change the dates to suit)

If you do this in Query Designer, then create a new column with just the [Intro DatePrep] as its value and put Between #9/1/2004# And #10/1/2004# in as the criteria.  You can uncheck the Show checkbox.
0
 

Author Comment

by:pixellight
ID: 12301407
the above gives me a 'Enter Parameter Value' -> 'Intro DatePrep'
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 12

Expert Comment

by:pique_tech
ID: 12301433
What is the name of the field in your table which contains the date on which you want to filter?
0
 

Author Comment

by:pixellight
ID: 12301448
Intro DatePrep: DateAdd("ww",-[Intro (Live CPL - weeks)],[Live CPL Date2])
0
 

Author Comment

by:pixellight
ID: 12301488
I'm looking for the month that is calcualated from 2 other fields [Live CPL Date2] ....a date and [Intro (Live CPL - weeks)]....a table of different numbers used with [Live CPL Date2] to calculate Intro date
0
 

Author Comment

by:pixellight
ID: 12302029
This is my first time using ee. Do I need to accept comments to continue the dialog?
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12302168
No, sorry, my "real life" interefered for a little while with my commenting here   ; )

Let me look at this a bit longer and I'll try to answer a little later in the evening (I'm in the central US, it's the middle of the afternoon here).
0
 

Author Comment

by:pixellight
ID: 12302190
thank you.
0
 
LVL 12

Accepted Solution

by:
pique_tech earned 500 total points
ID: 12303689
I think that you should be able to add the criteria
     Between #9/1/2004# And #10/1/2004#
in the criteria box under the column you defined as
     Intro DatePrep: DateAdd("ww",-[Intro (Live CPL - weeks)],[Live CPL Date2])

That should cause Access to only return the records where the date value you've calculated above is between the two specified dates.

Comment back if not.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now