Library not Referenced...

[New_Guy]

Hey, just wanted to see what you all thought before i totally rebuilt this form. Here is a little info on the problem i am having. I am a new to access so i will try to provide enough information on what is going on.

I have a form that has two date fields and i need to know the difference in days between the two dates. No problem so far, the DateDiff function can easily handle this job... However i named one of my text boxes datediff before i realized the function itself was called datediff. I then went back to the table and changed the name of the text box to DD. Now when i try to code a button to calculate the difference in days this is the error i get.

Microsoft Access cannot find the field datediff referred in your expression.

I then click debug and then click definition and here is what it tells me.

Cannot jump to datediff because it is in the library C:\NameOfDatabase.MDB which is not currently referenced.

The funny thing is i can get the DateDiff function to work on any other Form in my database. Any suggestions?

Thanks

 

[Dennisk]

After you changed the name in the table did you reconnect the form's text box to the renamed column AND changed the name of the textbox to the renamed column?

 

[New_Guy]

I didn't change the name of the table. i changed the name of the text box within the table. I then went back into my form, deleted the code and text box associated with the datediff name and added the text box DD. I then recoded my button to look like this.

Dim d1, d2, d3 As Variant

d1 = Date
d2 = DateIssued
d3 = datediff("d", [d2], [d1])

DD = d3

This exact same code works on another form within this database and also will work on any new forms i create. Notice how datediff is all lower case and the function should look like this DateDiff. When i type in DateDiff it changes it to all lower case.

I have been doing a lot of reading on references but mine do not say MISSING REFERENCE like other posts say it should.

 

[boblarson]

I didn't change the name of the table. i changed the name of the text box within the table.

What are you talking about? Text boxes do not exist within a table. Text boxes exist on forms and reports.

 

[New_Guy]

Ok, i went into the table and the field name datediff which happens to have the display control of a "text box" was renamed to DD.

 

[boblarson]

The table has field names, no text boxes. In the lookup tab, you can choose which TYPE a lookup is (stay away from using lookups on a table) selected, but there is no text box, period.

So, you should have renamed the field and then the text box on the form should be changed to be bound to that new name. Contrary to Dennisk's advice to name the text box the same name as the field (not a good idea as it can cause problems anyway), you should name it something like txtDD.

Notice how datediff is all lower case and the function should look like this DateDiff. When i type in DateDiff it changes it to all lower case.

You should do a search within all of your project to find all instances of datediff as I think you probably have some place where you have written something like
Dim datediff As ...
And that is why it keeps reverting to that capitalization, instead of the way it should show up.

 

[New_Guy]

The field was named datediff and had the data type of a number. On the form it was a text box - sorry for the misunderstanding. I use the combo box feature all the time to link one table to a field in another table and that works great for me. Maybe its not a good programming method but thats another post altogether.

I have searched my entire database several times for datediff before i even posted here. The only thing it picks up is the code in my button.

 

[boblarson]

If you can post your database after compacting (Tools > Database Tools > Compact and Repair) and zipping with WinZip (or something like it), maybe we can find it for you. It needs to be 393Kb or under to post here. If it winds up to be too big after compacting AND zipping, then I can PM you my email address for you to email it to me and I can take a look.

 

[New_Guy]

Hey Bob,

I can probably remake the entire form and just copy/paste the buttons and code faster then i can find a fix. I really just thought this would be an easy fix, like re-linking a library to this form. It seems as if i did some kind of function override on this form.

I will post my database if the results will be a big help to others if not it looks like some strange bug that i can get around. The zipped Database is 0.99MB's so i can email it to you if you want to see it.

Thanks for your help.

 

[boblarson]

I'll PM you my email address so you can send it. It might be good if we can determine your problem anyway so we can help you avoid it in the future.

 

[boblarson]

Okay, you have several things going on that you should change immediately or they're going to cause you a world of hurt.

1. Do NOT name fields DATE. Date is a reserved word and you should not use it. You can use RADate or something like that, but don't use DATE.

2. Do NOT use special characters in your field names or object names. Your tables that have the special characters of * and & in them should be renamed and those characters removed. They have meaning within Access and you are just going to confuse Access by using them and just like using reserved words, using special characters (especially those with meaning) will again cause you a world of pain and suffering.

3. Something is corrupt in your database and that's causing the problem with the DateDiff function. After renaming all of your tables, forms, queries, fields to get rid of the special characters and rename the date fields, and then importing into the new blank database, everything works. I also changed your click event to:

Dim d1, d2 As Date
Dim d3 As Long

d1 = Date
d2 = Me.DateIssued

d3 = DateDiff("d", d1, d2)
Me.DD = d3

 

[New_Guy]

Will do all of that ASAP. Thank you very much for your time and effort. Its great to be able to get help from the pros.