Type mismatch for year() and month()

Hi,
   On my access application, I am writing an Event procedure in VBA to get no. of working days. But i have found some type mismatch error when i use year(now()) or month(now()). With this i have noticed that Year() , month() and Day()  function is not working only for that editor.  what could be wrong here?

I am expecting an early response.

regards,
Nabeel.
nabeelkpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jobrienctCommented:
post your code pls.

John
0
klyonCommented:
nabeelkp,

You will get these kinds of crazy errors, if you have problems with your References.  When in the VBA Editor, go to Tools -> References, and check to see if you have any References that say "MISSING:" in front of them.

Kevin
0
Alan WarrenApplications DeveloperCommented:
Hi nabeelkp

They all return an integer, what have you declared your variables as?

Alan :)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jobrienctCommented:
If you do, uncheck the references and then recheck them and see if that helps.

John
0
Alan WarrenApplications DeveloperCommented:
Dim iYr as integer
Dim iMth as integer
Dim iDay as Integer

iYr = Year(Now)
Imth = Month(Now)
iDay = Day(Now)

debug.Print iYr, iMth, iDay

0
nabeelkpAuthor Commented:
Hi,
  There is no missing links in "reference" , funny thing is that Day() function is working , but year() and month() it even didn't recognize as a function only in this project.
If i create a test database and try this functions , it works just fine.
I want to fix this one on my running project.


0
jobrienctCommented:
create a new database and import all your objects - sounds like corruption perhaps. If that works then just rename the original database and then name the new database with the original filename.

John
0
klyonCommented:
jobrienct is onto something...  A good, old-fashioned "Compact and Repair" might straighten things out too.  But, before you do it, be sure to back up your database, just in case.
0
jobrienctCommented:
I find its easier to just move it all out of the offending database usually, start clean. Sometimes it saves a step. If thine object offend thee... cut it off.

John
0
nabeelkpAuthor Commented:
Hi,
  I have created a new MS database and import all objects and it still shows the same. For the same project, some of forms VBA give me  Year() and month() working.

Nabeel.
0
jobrienctCommented:
ok so corruption is not the issue. so can you please post your vba code thats not working ?

John
0
nabeelkpAuthor Commented:
Hi,
   I am sure that my code is correct as it is working in a separate form i have tried by creating a dummy access db.  
   EndDate = Day(DateSerial(Year(Now()), Month(Now()) + 1, 0))
This will actually return no. of days in a month. But it shows me type mismatch error only for that particular form vba editor  , because year() and Month doesn't recognize as a function for that current editor.

Nabeel.
0
jobrienctCommented:
so you have the exact same code in another form and its working... odd, something has to be amiss with the form object, but if you imported this form into a new db, then the import actually should have brought over only the uncompiled code of the form and so i cant imagine it was corrupted in the new db.

is there anything on the form, or in a global module,  that might create a naming conflict - no fields, form controls named with internal keywords?

are you able to post the database up to a server where we might get a look at it?

John
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jobrienctCommented:
My next suggestion would be to recreate the form, adding that code as soon as possible, and then testing that  its working after each control you add until something you put on the form screws it up.

John
0
nabeelkpAuthor Commented:
Hi John,
   Thank you for your help,  I have a query fields named as month and year which makes conflict with date functions. I renamed the query fields , now my problem is solved.

regards,
Nabeel.
0
jobrienctCommented:
glad we could help you out,

Next window please ;-)

John



0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.