+ 7 Working Days Date Function

[RuralGuy]

Compact and Repair the db 1st and then zip it up. use the Post Reply button and scroll down to the "Manage Attachments" area to attach a file. It will need to be < 394KB to post as a zip.

 

[RuralGuy]

If the zip is too big you can use .rar and simply rename the file .zip and let us know. We can rename it back before decompressing.

 

[rntkid]

Okay, I have pared the Db down to the bare bones. You will see two queries with similar names. One is titled "qrySIRCompliance"; the other is "qrySIRComplianceTest." The first one is the one in use now. The test one has the "PlusWorkdays" module as a field called, "2DayTest." The field called "WorkdayRecd" is the original lookup field that is joined to the "tblDueDates" table. I created a date/time field that has the same dates in it as the "WorkdayRecd" field called "WeekdayRec'd" to use for the purpose of testing the module. I left the original field in the test query for comparison purposes. I hope all this makes sense...sorry if it sounds too convoluted.

I sure hope you can find the problem.

Many thanks for all your help!

 

[RuralGuy]

The field name [HolDate] in the [tblHolidays] table actually has 4 trailing spaces in the name! Get rid of the trailing spaces and it works.

 

[rntkid]

OMG, it works!!! I have tried so many modules over the last year and changed the name of the holiday table date field so many times that I never even noticed that there were spaces left over. I can't believe that itty bitty brilliant module of yours works like a charm. I could kiss you! Thank you a million times and then some!!

Forever grateful,
rntkid

 

[RuralGuy]

Just glad we finally got it working. Have fun.

 

[rntkid]

Sorry to bother you again, but I'm not quite there yet. When I went back into the real database and tried to update all the queries that I deleted to get the Db small enough to post it as a ZIP file, I ran into a problem. There are a number of fields in my queries where the criteria includes the due date fields that have now been replaced by the module. So when I put the name of the new module field in the criteria, i.e. >[30DayDue] and run the query, I get an "Enter Parameter Value" dialog box that says "30DayDue." It worked before when I was using the separate Due Dates table, which was joined to the query but I can't figure out how to make it work with the module. You've been so patient...I hope you can bear with me a little more.

Thanks for your help...

 

[RuralGuy]

I'm not sure I understand. What is the complete Field definition?

 

[gemma-the-husky]

probably youve changed a field that used to be

"fieldname " with trailing spaces and is now

"fieldname" without spaces

--------you've fixed the code, but queries that previously included the field WITH the spaces now wont find that field - hence the enter parameter when you try to use those queries

unfortunately theres no inherent tool for renaming field names that sweeps up the use of the field name in your queries, and in any form controls, although i think there are third party tools to do this

 

[rntkid]

It's working now...

I was able to figure it out. I needed to reference the name of the module itself rather than the name of the field expression in the query criteria. So instead of >[ModuleFieldName], it is >PlusWorkdays([WorkdayRecd],30).

Thanks so much for replying...
rntkid

 

[rntkid]

Okay, one last thing. When I create a new record, the three due date fields in the form that use the PlusWorkdays module fields created in the query as the control source show "#ERROR in them until I fill in the [WorkdayRecd] field. Is there any way to suppress the #ERROR?

 

[RuralGuy]

You can use IIF( IsError([YourField]) , "", [YourField]) in the ControlSource of the control.

 

[rntkid]

I tried putting IIf(IsError([2DayDue]), " ", [2DayDue]) but the field stays blank after I update the [WorkdayRecd] field. Any idea what I'm doing wrong?

 

[RuralGuy]

In the query or on your form?

 

[rntkid]

On the form...

 

[RuralGuy]

Put your calculations in the query and they will update as you make changes.

 

[rntkid]

Sorry to be dense, but now I'm lost. The record source for the form is the query that has the date/time field "WorkdayRecd". The three due date fields in the query are based on that field using your PlusWorkdays module. They are:
2DayDue: PlusWorkdays([WorkdayRecd],2)
27DayDue: PlusWorkdays([WorkdayRecd],27)
30DayDue: PlusWorkdays([WorkdayRecd],30)
These are the Control Source for the three due date fields in the form. By "calculations," did you mean the due date fields?
When you posted, "You can use IIF( IsError([YourField]) , "", [YourField]) in the ControlSource of the control," was that not supposed to be in the Control Source of each due date field of the form?

 

[RuralGuy]

2DayDue: IIF(IsDate([WorkdayRecd]),"",PlusWorkdays([WorkdayRecd],2))
27DayDue: IIF(IsDate([WorkdayRecd]),"",PlusWorkdays([WorkdayRecd],27))
30DayDue: IIF(IsDate([WorkdayRecd]),"",PlusWorkdays([WorkdayRecd],30))

 

[rntkid]

I copied and pasted your due date expressions into the query. After running the query, it produces blank due date fields in all the query records. And on the form, the due date field is blank until I add a new record...then I get the #ERROR in the due date field. When I fill in the WorkdayRecd field, then the due date field is blank again...

Could it be that I need to have a "Before Update" event in the due date field of the form where it would say that if the WorkdayRecd field is missing, then the due date field is not visible and then becomes visible after the WorkdayRecd field is updated?

 

[RuralGuy]

Setting the visibility property of the calculated fields is a good idea if it acceptable for you UI. It should be done in the Current event of the form as well as the AfterUpdate event of the WorkdayRecd control.