473,321 Members | 1,667 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Calculated Date Value in a Field not showing up in the Table and Date Ranged Report

Cyberdyne
627 Expert 512MB
Here is the problem,

I have a form with a field named Occurence with a Short Date Value, once entered it subsequently appears in 3 fields SOL1 which adds one year, SOL2 which adds 2 years and SOL6M which adds 6 month to the date entered in the Occurence field, this is done with the following Controls:

=DateAdd("yyyy",1,[Occurence])
=DateAdd("yyyy",2,[Occurence])
=DateAdd("m",6,[Occurence])


The problem is that the data in the SOL1, SOL2, and SOL6M does not show up in the table and therefore my Date Range Report does not show anything except #Error for SOL1, SOL2, and SOL6M fields

How can I fix this so that I can run a Date Ranged Report with the values for SOL1,2,6M showing up?

Any help or suggestion would be much appreciated.
Sep 21 '06 #1
10 3891
PEB
1,418 Expert 1GB
Hi,

It seems that you want to save those values in a table doesn't it?

So in After Update event of the field [Occurence] you need to type the following:
Expand|Select|Wrap|Line Numbers
  1. [SOL1]=DateAdd("yyyy",1,[Occurence])
  2. [SOL2]=DateAdd("yyyy",2,[Occurence])
  3. [SOL6M]=DateAdd("m",6,[Occurence])
  4.  
  5.  
And the values should be saved...

But be carefull... Usually this isn't the best if u don't use often this data to store it in the database...

In your Queries you type in the field row
[SOL1]:DateAdd("yyyy",1,[Occurence])
[SOL2]:DateAdd("yyyy",2,[Occurence])

and you have your fields...

:)
Here is the problem,

I have a form with a field named Occurence with a Short Date Value, once entered it subsequently appears in 3 fields SOL1 which adds one year, SOL2 which adds 2 years and SOL6M which adds 6 month to the date entered in the Occurence field, this is done with the following Controls:

=DateAdd("yyyy",1,[Occurence])
=DateAdd("yyyy",2,[Occurence])
=DateAdd("m",6,[Occurence])


The problem is that the data in the SOL1, SOL2, and SOL6M does not show up in the table and therefore my Date Range Report does not show anything except #Error for SOL1, SOL2, and SOL6M fields

How can I fix this so that I can run a Date Ranged Report with the values for SOL1,2,6M showing up?

Any help or suggestion would be much appreciated.
Sep 22 '06 #2
Cyberdyne
627 Expert 512MB
Hi,

I tried entering the data after updating Occurence Field and here is what I get...


Microsoft Office Access can't find the macro '[SOL1]=DateAdd("yyyy",1,[Occurence]).'

The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.


I left SOL1, SOL2 and SOL6M in default control of SOL1, SOL2 and SOL6M respectively and added the [SOL1]=DateAdd("yyyy",1,[Occurence]) to Occurence After Update field.

Any suggestions?
Sep 22 '06 #3
PEB
1,418 Expert 1GB
Ok :)

You need to choose Event procedure

And then in the white window that appears you put that in ! :)
Sep 23 '06 #4
Cyberdyne
627 Expert 512MB
I get a Run-time error '438': Object doesn't support this property or method ...


here is the code....


Private Sub The_date_of_occurence_AfterUpdate()
[SOL1] = DateAdd("yyyy", 1, [Occurence])
[SOL2] = DateAdd("yyyy", 2, [Occurence])
[SOL6M] = DateAdd("m", 6, [Occurence])
End Sub
Sep 25 '06 #5
PEB
1,418 Expert 1GB
Hi,

Are you sure that the names of your CONTROLS IN YOUR FORM ARE:
[SOL1]
[SOL2]
[SOL6M]
[Occurence]
?

Am Sure No!
Because
[Occurence] isn't [Occurence] but [The_date_of_occurence]

So in this case change the code
Expand|Select|Wrap|Line Numbers
  1. Me![SOL1] = DateAdd("yyyy", 1, Me![The_date_of_occurence])
  2. Me![SOL2] = DateAdd("yyyy", 2, Me![The_date_of_occurence])
  3. Me![SOL6M] = DateAdd("m", 6, Me![The_date_of_occurence])
  4.  
  5.  
SO check those controls also and replace their names!

[SOL1]
[SOL2]
[SOL6M]

:)

Best regards!


I get a Run-time error '438': Object doesn't support this property or method ...


here is the code....


Private Sub The_date_of_occurence_AfterUpdate()
[SOL1] = DateAdd("yyyy", 1, [Occurence])
[SOL2] = DateAdd("yyyy", 2, [Occurence])
[SOL6M] = DateAdd("m", 6, [Occurence])
End Sub
Sep 26 '06 #6
Cyberdyne
627 Expert 512MB
same run time error again, I double checked the controls and they are indeed

SOL1
SOL2
SOL6M

I also change the Table [Occurence] to [The_date_of_occurence]


After the run time error I debug and it points to ...


Private Sub The_date_of_occurence_AfterUpdate()
Me![SOL1] = DateAdd("yyyy", 1, Me![The_date_of_occurence])
Me![SOL2] = DateAdd("yyyy", 2, Me![The_date_of_occurence])
Me![SOL6M] = DateAdd("m", 6, Me![The_date_of_occurence])
End Sub
Sep 26 '06 #7
PEB
1,418 Expert 1GB
So

Me![SOL1] isn't a label and it's a text box?

And If yeah, so
type stop before this statement and instaed "Me![SOL1]=" write Msgbox

When the code stops on the stop expression press F8 to continue

If there is a msgbox that appears with the right value so there is something wrong with Me![SOL1]=

If not we continue to debug together the next part of expression!

:)
Sep 26 '06 #8
Cyberdyne
627 Expert 512MB
Cool, I sent you the database zipped to your e-mail so you can see what I am talking about. Let's debug.
Sep 26 '06 #9
PEB
1,418 Expert 1GB
Does it works?

:)
Sep 27 '06 #10
Cyberdyne
627 Expert 512MB
Does it work?

Yes it does!

Great Work, here is the right code....


Private Sub The_date_of_occurence_AfterUpdate()
Me![SOL 1] = DateAdd("yyyy", 1, Me![The_date_of_occurence])
Me![SOL 2] = DateAdd("yyyy", 2, Me![The_date_of_occurence])
Me![SOL 6M] = DateAdd("m", 6, Me![The_date_of_occurence])
End Sub

User specified date reports are now working perfect! All SOL data is showing up in the table as well =)

Thanks PEB and great Job!
Sep 29 '06 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Jack | last post by:
Hi, I have a asp input form of a financial report. Here most of the fields are text boxes where the user need to input the values corresponding to an item. However, there are few items which are...
1
by: douh | last post by:
I know that this is not the way, however I need to save certian calculated values so that they do not change over time, ie. new tax rates etc. This is for an invoice header and invoice subform. I...
1
by: Norbert Lieckfeldt | last post by:
MS Access 2002 here. I am just trying to set up a simple database for a friend who's an optician. Basically, all clients with address details, date of last eyetest and a drop-down combo box to...
1
by: shobhit_shanker | last post by:
Here are the relevant "givens" to my problem... Form: frmLaunchRpt - Text Box: txtAsOfDate - Check Box: chkLogEval - Command Button: cmdLaunchRpt Report: rptEvaluation - Text Box:...
2
by: John | last post by:
I am using Access 2000. One table in my database has a field called RankName. Values inlcude: Officer, Sergeant, Lieutenant. I need create a report that groups these three RankNames into two...
2
by: Ryker | last post by:
I have a Purchase Order where I have a calculated field called Price that is calculated by multiplying Qty * Sales Price. I have 10 of these fields...Price 1 - Price 10 (for each line of the PO). ...
3
by: kelley.l.turner | last post by:
Hi all, I am very new to MS Access so please bear with me! I have created a simple calculated field in my data entry form, yet when I view my data table or try to generate a report based on...
2
by: jcf378 | last post by:
hi all. I have a form which contains a calculated control ("days") that outputs the # of days between two dates (DateDiff command between the fields and ). However, when I click "Filter by...
3
by: Ciara9 | last post by:
I am having problems trying to update a field in a database using a field in a form. I currently have two fields, Today and Tomorrow in a table named Date. The Today field automatically defaults to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.