UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Default Value Calculation - No Results, Access 2016    
 
   
MonteyBurns
post Jan 14 2019, 07:24 PM
Post#1



Posts: 64
Joined: 11-June 18



This has got to be one of the most frustrating problems I've had so far. This has been going on for awhile and keep coming back to it.
Please keep in mind that I have done the all of the following along with repeating the following and doing a Compact & Repair after each step.

I have a table with a StartDate, EndDate and SV_Month. My intention is to have the SV_Month as the default value of 5 months after the start date but I keep getting a variety of errors.

I am entering the following in the table for the SV_Month Default Value:

=DateAdd("m",5,"StartDate")

The Error I receive when I try to save the table:

Error 'Type mismatch' in the default value.
Both fields are setup the exact same. (Date/Time, no format, no input mask, etc.)

I enter the formula with square brackets around the StartDate:

=DateAdd("m",5,[StartDate])

The error I receive when I try to save the table:

The database engine does not recognize either the field 'StartDate' in a validation expression, or the default value in the table 'TBL_Intern'.

Then I thought, shouldn't I be using #'s around the date field I want referenced?
So the formula changed to:

=DateAdd("m",5,#StartDate#)

I receive an error right away when I navigate to another field:

The expression you entered has an invalid date value.
So I thought I will use a variation which is:

=DateAdd("m",5,#[StartDate]#)

Results: I received the same error right away.


I thought if the table isn't working, why not go to the form. So in the form control for SV_Month, I entered the formula

=DateAdd("m",5,"StartDate")

Although I receive no error, there are no "default values" displayed at all. The cells remain blank.

I tried the same formula using [StartDate] and exactly the same. No results displayed.

Any and all help is appreciated.

Cheers

Montey



Go to the top of the page
 
orange999
post Jan 14 2019, 08:10 PM
Post#2



Posts: 1,889
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


What data type is SV_Month? What does this field represent?

Why a default value?
Consider a query or a sub to do the DateAdd("m",5,StartDate) when needed?????
This post has been edited by orange999: Jan 14 2019, 08:27 PM

--------------------
Good luck with your project!
Go to the top of the page
 
RJD
post Jan 14 2019, 08:59 PM
Post#3


UtterAccess VIP
Posts: 9,368
Joined: 25-October 10
From: Gulf South USA


Hi Montey: I agree with Orange - that a better approach is most likely to create/modify the value in a form, rather than using a default value in the table. Using the After Update event procedure (Sub, as Orange pointed out), you can check if a valid Start Date has been entered, then, if so, add 5 months to that date and update the SV_Month value (I am assuming that SV_Month is a date, although we do not know what SV is and what the type is).

You should be able to do this pretty easily - but let us know if you have any difficulties, and perhaps attach a db for us to work with.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MonteyBurns
post Jan 14 2019, 09:42 PM
Post#4



Posts: 64
Joined: 11-June 18



The users do site visits that occur 5 months after the start date. I know you can do this with a query and have the information represented by a report but they like the idea that the form contains the SV_Month and the information is attached to the form with all the other relevant is right there. I did try to do it through the form already but I didn't use an event trigger.

Thank you all for the information.

I will try this at work tomorrow and let you know how I make out.

Cheers.

Montey
Go to the top of the page
 
MonteyBurns
post Jan 17 2019, 09:04 AM
Post#5



Posts: 64
Joined: 11-June 18



G'day;

I thought I would just post an update for this problem.
I have still been unable to get it to work. I don't get any errors there is just no results to show.
Both fields are date/time data type and the only thing I can deduce is that it has something to do with the format of the date.
The start date originally had a "dd-mmm-yyyy' format. I removed anything and everything that referenced that format and changed it to be entered in as "dd/mm/yyyy".

The SV_Month was to display "mmm-yyyy" (JAN-2019) so my guess is either have to add another field to and either use a Datepart or Format it as "mmm-yyyy" in another cell.

Either way this is getting to be a riddiculous amount of time to do something that shouldn't be that difficult.
The more I read about using date/time and errors, there can be so many different reasons that make using it pretty sketchy. I found that some issues come down to the Regional Settings on the individual Computer. If you develop a database and an individual's Computer Date and Time settings cause errors, it might be best to stay away from it in this situation.

Thanks for all your advice.

Montey
Go to the top of the page
 
RJD
post Jan 17 2019, 12:35 PM
Post#6


UtterAccess VIP
Posts: 9,368
Joined: 25-October 10
From: Gulf South USA


Hi Montey: We still haven't really seen what you are doing now (no db to look at), so I made a small demo to do what I think you are trying to do. It uses the After Update event in StartDate to insert a date (StartDate plus 5 months) into SV_Month. It first checks to make sure that StartDate is a valid date. And the SV_Month display is mmm-yyyy.

Take a look at the existing records in the demo, then try changing a StartDate and then add a new record and enter a StartDate.

This will not fix existing records without a SV_Month date, but you can do that with an update query that checks whether a record has a null SV_Month.

I sometimes overlook something involving European dates vs US dates, so take a hard look at that to make sure it works correctly.

HTH
Joe
Attached File(s)
Attached File  DefaultValueCalculation.zip ( 21.78K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Jan 17 2019, 01:35 PM
Post#7



Posts: 5,740
Joined: 11-November 10
From: SoCal, USA


QUOTE
My intention is to have the SV_Month as the default value of 5 months after the start date

well, the intended value of field SV_Month is a calculated value. that flies in the face of normalization best practice: don't store calculated values, store raw data and calculate derived values on the fly.

as others have pointed out, if you want to display the calculated value in a form or report, you can add a calculated field to the underlying query. or, you can add an unbound textbox control to the object and set its' ControlSource to the calculation.

or, if you really want to see the calculated value in the table (though your user should never see table data directly) then add a calculated field in the table. the value itself is not stored for each record; the calculation is stored. to my understanding, it's basically the same as adding a calculated field in a query.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
RJD
post Jan 17 2019, 03:17 PM
Post#8


UtterAccess VIP
Posts: 9,368
Joined: 25-October 10
From: Gulf South USA


Hi tina:

QUOTE
well, the intended value of field SV_Month is a calculated value. that flies in the face of normalization best practice: don't store calculated values, store raw data and calculate derived values on the fly.

I guess I interpreted this a bit differently. While the default value of the Site Visit (SV_Month) is 5 months after the StartDate, that is just the default. I interpreted this to mean that the user could then adjust that date if desired or if circumstances dictated - thus the saved value.

Of course, that may be an incorrect interpretation, and your question is well founded. We need to hear from the OP about whether the user is allowed to adjust the SV_Month.

Meanwhile, in case you are interested, an A2003 copy of my demo is attached. You could have knocked this out as well, quickly, but I thought I would show my interpretation in actual usage.

Regards and wavehi.gif
Joe
Attached File(s)
Attached File  DefaultValueCalculation_A2003.zip ( 20.67K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Jan 17 2019, 08:53 PM
Post#9



Posts: 5,740
Joined: 11-November 10
From: SoCal, USA


hi Joe! you're right, hon, good point, so we'll see what the op's actual intentions are. and thanks for the A2003 db! :) i'll download it when i get home; i don't have A2003 here at work. tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
MonteyBurns
post Jan 17 2019, 10:46 PM
Post#10



Posts: 64
Joined: 11-June 18



Good evening everyone;

Thank you all for the replies.
Thank you for the Database examples. You did hit that bang on.
I do realize that this calculation goes against Normalization but for some reason the End Users want to see this field in the main form.
This purpose of this database is to monitor interns. As interns get placed into jobs, part of the requirement from the users of this database is to provide an on site visit to make sure the intern is doing what they are supposed in the discipline they are seeking experience in. It's more or less a method of quality control. One to ensure that the interns get good experience and two, that employers are getting an opportunity to see if a person is a good fit within their company.

My original intent was to have a report to display the files that are nearing they Site Visit month and they could do what they need to do with this information. However, when I first presented my design, the end users wanted the SV_Month showing on the form and asked for it to be calculated. I said sure it shouldn't be a problem. But you know the rest of the story.

I sense is the End Users use this SV_Month field as a trigger so that when they actually see it, it reminds them to get the necessary information to do the Site Visits and get them ready. I think this is because they have been using an excel spreadsheet to do this for so long, they are just used to seeing that field. I will still implement the report for them and then they might have a better understanding on how a the database could automate this type of information.

Thank you again for all the replies and I will test this out tomorrow.

I'm just curious, I have changed the format and things so much, could this have caused some form of corruption that would have caused this calculation to not work?

Cheers laugh.gif
This post has been edited by MonteyBurns: Jan 17 2019, 10:50 PM
Go to the top of the page
 
RJD
post Jan 18 2019, 07:30 AM
Post#11


UtterAccess VIP
Posts: 9,368
Joined: 25-October 10
From: Gulf South USA


QUOTE
...the end users wanted the SV_Month showing on the form and asked for it to be calculated.

Actually, if the only requirement is to display the result of the StartDate + 5 months, and this result will never be adjusted to another date, then storing the SV_Month, as was discussed above by others, is not a good idea. This value can always be calculated in a query or even on a form or report without it being present in the table. Storing the date only makes sense if a user can adjust that "default" date to something other than StartDate + 5 months (accelerate or delay the site visit). Storing a default SV_Month (if it is always + 5 months) can, and often does, lead to errors if the StartDate can be changed without the SV_Date being automatically updated. My solution assumed that you might adjust the SV_Month to something other than the default. If that is not true, then my solution is not the best approach.

QUOTE
I'm just curious, I have changed the format and things so much, could this have caused some form of corruption that would have caused this calculation to not work?

Possibly impossible to know. We would have to have your actual db to see how things are set up and explore/test the details.

Good luck with your project. And do re-visit the store/not store issue with SV_Month, and whether it can be adjusted by the user.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Jan 18 2019, 10:20 AM
Post#12


UA Admin
Posts: 34,536
Joined: 20-June 02
From: Newcastle, WA


FWIW, I've been working on a set of Videos on Date stuff.

I'd appreciate feedback on whether you see anything there that might contribute to this discussion on DISPLAYING dates using date logic. I intend to expand the line up, and this problem (displaying a calculated date) might make a good topic to add.

Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MonteyBurns
post Jan 18 2019, 01:54 PM
Post#13



Posts: 64
Joined: 11-June 18



Hi Grover;

I will for sure look through this information this afternoon and will post a reply.

As for the database, I should have explained a little more but the reply was getting rather lengthy.
The end users with this database like to see the SV_Month as a trigger a reminder to them. Also, it would be calculated as a "default value" because in 80% of the cases the site visit will be around the 6 month mark.

This value may change and/or it may increase as some jobs may have more risks, therefore, there would be more than one site visit.
So it's quite possible for an Intern to have up to 4 site visits but every intern will have at least one.

Hope this helps clear it up.

Thanks for the advice again.

Montey
Go to the top of the page
 
RJD
post Jan 18 2019, 02:38 PM
Post#14


UtterAccess VIP
Posts: 9,368
Joined: 25-October 10
From: Gulf South USA


QUOTE
The end users with this database like to see the SV_Month as a trigger a reminder to them. Also, it would be calculated as a "default value" because in 80% of the cases the site visit will be around the 6 month mark.

Okay, my understanding from this statement is that the user can change the SV_Month if required. A definitive statement like "The user may change the SV_Month as required" would put this to rest, and clear the way to use the demo I posted - setting a default using the After Update event in the StartDate control and saving the SV_Month as a field in the table.

And you said "6 month mark" above whereas you indicated 5 months in previous posts... If that default delay might change occasionally, you might consider storing the delay value (say, in a table) and referencing that value in the After Update procedure. Storing that value will allow you to more easily change it rather than modifying code, possibly in multiple places.

Correct?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MonteyBurns
post Jan 18 2019, 07:09 PM
Post#15



Posts: 64
Joined: 11-June 18



BINGO !!!

That is how this started. It was supposed to be a "default value". In my interpretation a "Default Value" indicates it could change.
The difference in using "5 Months" and "6 Months" is that the Site Visit is supposed to be completed by at least the "6 month" period. I talked to a user today about it and they indicated they use the SV_Month to get everything ready for the actual Site Visit. They have to assign the file to someone, they have to provide the details on the internship, etc. So the SV_Month is more like a warning, like you need to get these files ready for a site visit.

QUOTE
And you said "6 month mark" above whereas you indicated 5 months in previous posts... If that default delay might change occasionally, you might consider storing the delay value (say, in a table) and referencing that value in the After Update procedure. Storing that value will allow you to more easily change it rather than modifying code, possibly in multiple places.


I did try to think of a way to do something like this, but it got too confusing. Essentially the users who will be looking after this would have their own discretion at determining how many site visits there should be for an intern. There should be no more than 4, however, there have been incidents where someone had made a complaint and therefore, increased the number of Site Visits. So what I have done was create a button on the main form called Site Visits and this opens a site visit form where the user can enter the details of a completed site visit no matter how many they have had.

I hope this makes sense now.

Thanks

Montey
Go to the top of the page
 
RJD
post Jan 18 2019, 07:36 PM
Post#16


UtterAccess VIP
Posts: 9,368
Joined: 25-October 10
From: Gulf South USA


Hi Montey: Since the user can adjust the SV_Month month-by-month, just for fun I added buttons next to the SV_Month to increment/decrement the month, to save having to enter a complete date ... in case you are interested ....

HTH
Joe
Attached File(s)
Attached File  DefaultValueCalculation_Rev1.zip ( 23.16K )Number of downloads: 6
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MonteyBurns
post Jan 18 2019, 08:06 PM
Post#17



Posts: 64
Joined: 11-June 18



GPG;

Good video and I like watching these kinds of videos because you learn different ways to look and work with data. For example, concatenating the "0" to make it work. WHO WOULD HAVE THOUGHT, Genius....

With respect to displaying the date and inline with my issue. I would have liked to see or hear your opinion on why some things wouldn't work or what your preference would be with using the DatePart; Date format on the form/report control (wouldn't that have worked or did that relate back to the date and time settings on your computer?)

This may be getting beyond the scope of what you were seeking for input from viewing the video, but if Access gives you so many locations (at least 4, table, forms, reports, queries) where you can set the format for the Date, why is it that at times when you are looking for a specific Date format it's not coming out as expected?

Where could this be going wrong? - Even a quick blurb of where to check for things.

What source supersedes all other formats? For example; if you supply a Date format in the table dd-MMM-yyyy, does this format get propagated where ever the data is being used? If the table has dd-MMM-yyyy shouldn't that format continue into a report?

What is the best practice for date and times? Do you leave the date and time format alone in the table and do the formatting on the control in the form or report?

In my situation, I had the calculations right, I used VBA and macros but still I received No Results, so my question is... When you receive no results (when there is data), why don't see anything? Is it a formatting issue or could it be a data mismatch?

Finally if it's a data mismatch? Where do you check besides the table to determine if the data type changed? Can data type change? If you identify it in the table as Date/Time, don't queries, forms and reports keep that data type or do you have declare it each and every time you use the data? Where else do you declare it if you have to?

These are just somethings that came to mind and again, sorry if it's outside of the scope of what you were looking for. I hope you don't feel I'm bombarding you and by all means, you can tell me I am way out to lunch on some things. I probably am. laugh.gif To me, writing VBA is backwards for the most part. If you want check to see if a field is null, the proper way is...

If IsNull(FieldName) where I think of it as. If FieldName(IsNull)

I find that the hardest thing to get through. I am still very inexperienced with access and when thinking about the Date Format and issues I have had, as a complete newbie these are the things that come to mind.

Again sorry for bombarding you, I hope it helps.

Montey
Go to the top of the page
 
MonteyBurns
post Jan 18 2019, 09:05 PM
Post#18



Posts: 64
Joined: 11-June 18



RJD;

Thanks ... that is actually a great idea.
The only difference that stands out is used the

If IsDate(Me!dteStartDate)

I didn't use this function. But it makes more sense to use it.

Thanks notworthy.gif

Montey
Go to the top of the page
 
MonteyBurns
post Jan 21 2019, 09:37 PM
Post#19



Posts: 64
Joined: 11-June 18



Problem Solved.
I feel like an idiot.

The data in the StartDate was pre-populated. Therefore, no matter what event (On Change, After Update) the calculation never took place. It wasn't until snooping through and trying to figure things out that I Changed a Start Date. Tabbing over to the next control triggered the event and the calculation is working now.

Thanks to all that responded and provided help.

Montey
Go to the top of the page
 
RJD
post Jan 21 2019, 09:58 PM
Post#20


UtterAccess VIP
Posts: 9,368
Joined: 25-October 10
From: Gulf South USA


Glad you got that solved, Montey. Sounds like you had a solution all along, just missed the pre-populated StartDates.

If you still have the issue with the pre-populated StartDates not filling the SV_Month, you might consider an Update query, checking for Null SV_Month values and updating with your formula if found - unless you already have that fixed.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th February 2019 - 12:50 PM