Today's Date in Update Query

[Irwin109]

Ok, yet again a problem with Access!
These queries have been working until yesterday (which is the mainly
baffling part).
Using Access 2003 and Windows XP

Ok here goes:

I have a query in my database and I want it so an update query will check so
if the [End Date] is before today then [Status] will change to "Terminated".
It was working as:

End Date
Criteria: <=Date()

Status
Update To: "Terminated"

and until recently this worked however now it won't... Any suggestions?

 

[Allen Browne]

That should work, assuming that when you open your table in design view, you
see the fields that data types like this:
End Date Date/Time
Status Text

Is it generating an error message? Date() is a VBA function, so perhaps
there is a problem with VBA library references:
http://allenbrowne.com/ser-38.html

You might also try a compact/repair (in case there's a bad index.)

If the status should always be Terminated once the date has arrived, it
would be much better to drop the Status field from your table completely.
Then create a query, and type an expression like this into the Field row:
Status: IIf([End Date] <= Date(), "Terminated", "Current")
Use the query wherever you would have used your table. It updates itself
automatically, without needing an Update query, and can't go wrong.

 

[Irwin109]

The field types are correct (text and Date/Time)

The original error was "Operation must use updatable query"

Tried compact/repair but everything is fine.

"> If the status should always be Terminated once the date has arrived, it

would be much better to drop the Status field from your table completely.
Then create a query, and type an expression like this into the Field row:
Status: IIf([End Date] <= Date(), "Terminated", "Current")
Use the query wherever you would have used your table. It updates itself
automatically, without needing an Update query, and can't go wrong."

Tried that and it came up with "Query must have at least one destination
field."

Looking at the rest of my Database I think there is some permission error,
I'm going as Admin yet it won't let me enter data as it normally does... More
fun!

 

[Allen Browne]

Now we know what the error is, here's a list of things to check:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The field types are correct (text and Date/Time)

The original error was "Operation must use updatable query"

Tried compact/repair but everything is fine.

"> If the status should always be Terminated once the date has arrived, it

would be much better to drop the Status field from your table completely.
Then create a query, and type an expression like this into the Field row:
Status: IIf([End Date] <= Date(), "Terminated", "Current")
Use the query wherever you would have used your table. It updates itself
automatically, without needing an Update query, and can't go wrong."

Tried that and it came up with "Query must have at least one destination
field."

Looking at the rest of my Database I think there is some permission error,
I'm going as Admin yet it won't let me enter data as it normally does...
More
fun!

 

[Irwin109]

Cheers for the assistance but it's still all ok. I really can't figure this
one out. If I don't get it by friday I'll just load the backup and go from
there =/

Now we know what the error is, here's a list of things to check:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The field types are correct (text and Date/Time)

The original error was "Operation must use updatable query"

Tried compact/repair but everything is fine.

"> If the status should always be Terminated once the date has arrived, it

would be much better to drop the Status field from your table completely.
Then create a query, and type an expression like this into the Field row:
Status: IIf([End Date] <= Date(), "Terminated", "Current")
Use the query wherever you would have used your table. It updates itself
automatically, without needing an Update query, and can't go wrong."

Tried that and it came up with "Query must have at least one destination
field."

Looking at the rest of my Database I think there is some permission error,
I'm going as Admin yet it won't let me enter data as it normally does...
More
fun!

 

[Allen Browne]

You say that it used to work and now it suddenly doesn't.

One possible reason is that there used to be a relationship which is no
longer present. This can happen if Access finds a duplicate in the primary
key when you do a compact/repair. Rather than delete the data, it delete's
the primary key index, and so the relationship that depends on that index.
Consequently, a query with a JOIN between the 2 tables is will no longer be
updatable.

If this did happen, your database will contain a new table named "Compact
Error" or something along those lines.

This doesn't happen often, but it would be worth checking for.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cheers for the assistance but it's still all ok. I really can't figure
this
one out. If I don't get it by friday I'll just load the backup and go from
there =/

Now we know what the error is, here's a list of things to check:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

The field types are correct (text and Date/Time)

The original error was "Operation must use updatable query"

Tried compact/repair but everything is fine.

"> If the status should always be Terminated once the date has arrived,
it
would be much better to drop the Status field from your table
completely.
Then create a query, and type an expression like this into the Field
row:
Status: IIf([End Date] <= Date(), "Terminated", "Current")
Use the query wherever you would have used your table. It updates
itself
automatically, without needing an Update query, and can't go wrong."
Tried that and it came up with "Query must have at least one
destination
field."

Looking at the rest of my Database I think there is some permission
error,
I'm going as Admin yet it won't let me enter data as it normally
does...
More
fun!

 

[Irwin109]

I have found the problem! (At last) When I have changed the source of the
linked tables from C:\Blah Blah To \\Network\Blah Blah it's disallowed access
(even though it is the same file, just a different file path)...

You say that it used to work and now it suddenly doesn't.

One possible reason is that there used to be a relationship which is no
longer present. This can happen if Access finds a duplicate in the primary
key when you do a compact/repair. Rather than delete the data, it delete's
the primary key index, and so the relationship that depends on that index.
Consequently, a query with a JOIN between the 2 tables is will no longer be
updatable.

If this did happen, your database will contain a new table named "Compact
Error" or something along those lines.

This doesn't happen often, but it would be worth checking for.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Cheers for the assistance but it's still all ok. I really can't figure
this
one out. If I don't get it by friday I'll just load the backup and go from
there =/

Now we know what the error is, here's a list of things to check:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

The field types are correct (text and Date/Time)

The original error was "Operation must use updatable query"

Tried compact/repair but everything is fine.

"> If the status should always be Terminated once the date has arrived,
it
would be much better to drop the Status field from your table
completely.
Then create a query, and type an expression like this into the Field
row:
Status: IIf([End Date] <= Date(), "Terminated", "Current")
Use the query wherever you would have used your table. It updates
itself
automatically, without needing an Update query, and can't go wrong."
Tried that and it came up with "Query must have at least one
destination
field."

Looking at the rest of my Database I think there is some permission
error,
I'm going as Admin yet it won't let me enter data as it normally
does...
More
fun!

 

[Irwin109]

Let me word that a little better!

MS Access 2003
Windows XP

I have found the problem! (At last) When I have changed the source of the
linked tables from
C:\Documents and Settings\USER NAME\My Documents\FOLDER WHICH THE DB IS KEPT
to
\\NETWORK FOLDER\FOLDER IN WHICH THE DB IS KEPT

it seems to have lost the permissions, yet does not mention it opening in
read only, the Security/Workgroup file still works as the Login Prompt still
appears.

Any more assistance?! Thanks in advance

 

[Irwin109]

ZOMG Fixed! Finally!

 

[Allen Browne]

Good news. And thanks for posting the solution for others to benefit from
when they search.

 

[Irwin109]

Yeah. That was a pain, it was the relationship to the Back End, I just
removed all the linked tables and put them back in... Seemed to work fine!!!

However after another problem (which I fixed) I've got a new one!

You seem the best person to ask:

http://www.microsoft.com/communitie...abe2-3bc70e8bebb1&lang=en&cr=US&sloc=&m=1&p=1