#Type! Error in Form (1 Viewer)

[Mahendra]

Hello,

I have created a demo form where user enters the start date and the end date. The access should automatically display the month name in month column based on the date in end date column.

For the control source in month column I have used

=MonthName(Month([EndDate]))

It works fine. The month column automatically displaying the month name but it's displaying #Type! in new column even though it's empty.

How to get rid of this #Type! error from my form?

 

[CJ_London]

is EndDate null?

try populating the controlsource with enddate and formatting the control as mmmm

 

[Mahendra]

The MonthName, Quarters and Year are automatically calculated based on the End Date..

Kindly have a look into the attached screen shot. You will have an idea for the issue.

Thanks

 

[Mahendra]

Thanks CJ_London,

It worked to some extent after making the control source to enddate and formatted as mmmm as stated. When I click on the monthname then the data in the respective column is changing to same as enddate and not showing the month name if the data is selected.

Previously when I edit the end date then the monthname, year and quarters changed automatically but now the year and quarters are changing only after when I click refresh.

 

[vbaInet]

=IIF([NewRecord], Null, MonthName(Month([EndDate])))

... like CJ suspected, the Null (or new record) is causing the issue.

 

[CJ_London]

you can also use the format for quarter (q) and year (yyyy)

with regards updating, agree this won't happen until endate is updated in the table

 

[CJ_London]

agree with vbaInet, not sure why you need a monthname function however

=IIF([NewRecord], Null, =format([EndDate],"mmmm"))
=IIF([NewRecord], Null, =format([EndDate],"q"))
=IIF([NewRecord], Null, =format([EndDate],"yyyy"))

 

[Mahendra]

Thanks for the query vbaInet.. I tried it but it didn't worked.. Still am able to see #type!

CJ_London, previously I used the queries of

=Year([EndDate])
=Format([EndDate];"q"". Quartal""")

It used to update before it has been updated in the main table.

Any suggestions from your end to get rid off this

 

[vbaInet]

Upload a cut-down version of your db that includes only the form and it's related query and or table.

 

[CJ_London]

try

=IIF(isnull([EndDate]), Null, format([EndDate],"mmmm"))

 

[Mahendra]

Thanks for your support CJ_London and vbaInet..

Detailed Explanation

I created a table with ID, Start Date, End Date, MonthName, Year and Quarter (Have a look into Table.JPG for main table)

and then I created a form with ID, Start Date, End Date, MonthName, Year and Quarter. I linked the control sources of ID to ID from main table, StartDate to StartDate from main table, EndDate to EndDate from main table.

The control sources of Monthname, Year and Quarter have been set to
=MonthName(Month([EndDate]))
=Year([EndDate])
=Format([EndDate];"q"". Quartal""")

(For the property sheet and the form please have a look into the attached images Propertysheet and formbackground)

By using my above control souces, whenever the user enters the start date and enddate in the form then the form automatically displays the monthname, year and quarter. It worked well.

2 Issues which causing severe pain in my ass for past few days..

Issue 1: There is a #Type! in form in a new row as shown in the image form. The main form looks like this with the values of monthname, quarter and year.

Issue 2: The ID, StartDate, EndDate values are being propagated into the main table but not the values of month, quarter and year (I know it is due to the control sources connection). How to over come this and how can I propagate the values of month, quarter and year into the main table.

NOTE: That's the reason why you see empty fields in month, quarter and year main table (table.jpg) and values in form.jpg

Dear CJ_London,

I used your queries in the control sources and it's terminating me by saying the syntax errors. Am fixing those errors.

Kindly assist me in my 2 issues please.

 

[vbaInet]

Upload as requested and we'll take a look.

Upload a cut-down version of your db that includes only the form and its related query and or table.

 

[Mahendra]

Attached the sample copy...... Kindly have a look into it.

 

[CJ_London]

I don't think you are reading all your posts - see post #10

 

[Mahendra]

I don't think you are reading all your posts - see post #10

I have replied to your posts in #post11

Sorry for combining all in a single post

 

[Mahendra]

The issue with the using of iiF enddate in month is the updation time.

It's not updating until the form has been refreshed. (I agree with your previous posts that the data should be first saved in the main table and then it will refresh in form)

 

[vbaInet]

* Don't save calculated values in the MonthName, YearName and QuarterName fields.
* Don't use names that are reserved for Access, i.e. MonthName. So the name of the control should not be MonthName neither should the field use that name too.
* Remove the MonthName() and Month() functions and use the Format property of the control by entering "mmmm" without the quotes.

 

[Mahendra]

* Don't save calculated values in the MonthName, YearName and QuarterName fields.
* Don't use names that are reserved for Access, i.e. MonthName. So the name of the control should not be MonthName neither should the field use that name too.
* Remove the MonthName() and Month() functions and use the Format property of the control by entering "mmmm" without the quotes.

If I use this am facing the issues of updating times. The form needs to be refreshed before seeing the changes.

Could you check my demo database attached in my previous post #13.

 

[vbaInet]

My suggestions are based on the db you uploaded.

 

[Mahendra]

could you let me know how the values from the form can be stored to the main table.

The Month, quarter and year values are in the form but not in the main table.