Any idea why a working query now produces "Compile Error"

[SherryT]

I have a query (in Access 2003) that has worked for 3 months but now gives me
a "compile error" with no real help on what the problem is. Below is the text
it refers to however, I have tried to parse it into smaller pieces (i.e.
mid$(....)) and still get the "compile error" msg.

"DateValue(Mid$(Exceptions!FormPrepDate,3,3) & " " &
Right$(Exceptions!FormPrepDate,2) & ", " & Left$(Exceptions!FormPrepDate,2))"

I have run Compact & Repair and have installed all Office updates. The table
has just under 36000 records. Could that be part of the problem?

Sherry

 

[Gary Walter]

I have a query (in Access 2003) that has worked for 3 months but now gives
me
a "compile error" with no real help on what the problem is. Below is the
text
it refers to however, I have tried to parse it into smaller pieces (i.e.
mid$(....)) and still get the "compile error" msg.

"DateValue(Mid$(Exceptions!FormPrepDate,3,3) & " " &
Right$(Exceptions!FormPrepDate,2) & ", " &
Left$(Exceptions!FormPrepDate,2))"

I have run Compact & Repair and have installed all Office updates. The
table
has just under 36000 records. Could that be part of the problem?

Hi Sherry,

Right off the top, I don't know...
but you might check:

1) I assume it is a typo that you are
using a *comma* in the second separation?
2) I don't know why you are using the "bang"
character (!) if Exceptions is a table and
FormPrepDate is a field in the table
3) It was working, now it doesn't.
I know this is simplistic, but....
SOMETHING CHANGED

- have you checked your References

in Immediate window, does following work

?Mid$("12345", 3, 1)

if not, check your references

- your data has changed

when you say you broke "into smaller pieces,"
do you mean you deleted the calculated field
and made 3 new fields

f1: Mid$(Exceptions.FormPrepDate & "BBBBBB",3,3)
f2: Right$("BB" & Exceptions.FormPrepDate ,2)
f3: Left$(Exceptions.FormPrepDate & "BB",2)

and checked your data? Any "B's" or anomolies?

Above are just guesses, but remove comma
then check your References would be my
best guess.

good luck,

gary

 

[SherryT]

I agree that it makes sense if something worked and now it doesn't... what
changed? The only thing that changed was I imported about 4000 more records
into the table which is why I was wondering if the size of the table might be
a problem.

In an effort to see if I could isolate the issue, I tried breaking the query
into individual pieces. In other words, I tried
Mid$(Exceptions!FormPrepDate,3,3) and still rec'd a compile error. I also
tried DateValue("Dec 31, 2005") and got a compile error. It is almost like my
program is corrupted or am I missing something in the query itself?

 

[Gary Walter]

Hi Sherry,

Did you check your References?

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html