Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

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


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
pixellight
Asked:
pixellight
  • 7
  • 6
1 Solution
 
pique_techCommented:
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
 
pique_techCommented:
for that matter, you can just do any date calculation on the original field.  Changing its formatting is not required for date calculations.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
pixellightAuthor Commented:
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
 
pixellightAuthor Commented:
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
 
pique_techCommented:
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
 
pixellightAuthor Commented:
the above gives me a 'Enter Parameter Value' -> 'Intro DatePrep'
0
 
pique_techCommented:
What is the name of the field in your table which contains the date on which you want to filter?
0
 
pixellightAuthor Commented:
Intro DatePrep: DateAdd("ww",-[Intro (Live CPL - weeks)],[Live CPL Date2])
0
 
pixellightAuthor Commented:
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
 
pixellightAuthor Commented:
This is my first time using ee. Do I need to accept comments to continue the dialog?
0
 
pique_techCommented:
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
 
pixellightAuthor Commented:
thank you.
0
 
pique_techCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now