Strange Problem with Run Time Error

[JeffGMACI]

I'm having a really strange problem and wonder if anyone can help me. Here's my table structure:

tblTV
TvAgentNumber (PK long integer)
TVStartWeek (short form date)

tblNewBus
AgentNumber (PK long integer...related to tblTV)
01-Jan-03 (Weeks with long integer data)
08-Jan-03
....

What I'm trying to do is use the DateAdd function to manipulate the TVStartWeek and then reference this output to a quantity in the weekly fields of the other table. Here is the VB I have constructed:

Function DateChange(StartDate As Variant, AdAgentNumber As Variant)
Mth1 = "[tblNewBus].[" & CStr(Format(DateAdd("d", -7, StartDate), "dd-mmm-yy&quot) & "]"
Mth1Sls = DSum(Mth1, "tblNewBus", "[tblNewBus].[AgentNumber]=" & AdAgentNumber)
DateChange = (Mth1Sls)/2

When I do this, I am getting an error message that says:

Runtime error 2001: You cancelled the previous operation.

I cannot for the life of me figure out what is wrong with my VB. When I take out the Mth1Sls line, and make DateChange=Mth1, it outputs the right [tblNewBus].[date] reading for all my fields in the query, but it breaks down in the Mth1Sls part. Does anyone have any ideas on how to fix this? I'm really at my wits end.

Thank you,
jeff

 

[substitute]

I'm guessing here, but the problem might be related to records wih null fields. The only time I got that error was due to a null field, and I just did an Internet search and came up with a FAQ response on a commercial site which said much the same.

Check if your "tblnewbus" has any nulls.

By the way, I've used similar code to construct field names in my own projects. I don't like it. You have to be really careful. What if there isn't a field named with a date 7 days before the one passed to the function? Are you sure all the field names returned in your test version were valid?

Looks to me as if your data structure is all wrong - how many weeks are you going to put in the table? Use a table with a PK of agent number and week and put the data in a third field - that way you don't have to predefine the weeks and your VBA code could be done as a simple query.

Also, you don't need cstr() because format() returns a string anyway

Good luck