expression too complex

[jalge2]

Hi all, my problem is, I have an update Query that updates information in a table. The problem is, I'm getting an error saying that the expression is too complex. I think the part that is not working correctly is

"990-"+Format(Date(),"yyyymmdd&quot

That is in the Update To; field in my query. I was curious if this was correct, or what the problem was.

 

[lespaul]

What does the rest of your query look like?

Leslie

 

[jalge2]

In the Update to field the rest is:

No / Yes / "Freight" / Date() / "Standard Register"

They are all going to the Temp PO Index Table, and the fields in that table are

PO Number
Order Complete (checkbox inside field)
Waiting on Invoice (checkbox inside field)
Shipping Info
Order Date
Vendor

The PO Number is the format above "990-"+Format(Date(),"yyyymmdd&quot

and the Order Date is the Date()

 

[lespaul]

What is your ACTUAL sql statement?

Leslie

 

[jalge2]

UPDATE [Temp PO Index] SET [Temp PO Index].[PO Number] = "990-"+Format(Date(),"yyyymmdd&quot, [Temp PO Index].[Order Complete] = No, [Temp PO Index].[Waiting on Invoice] = Yes, [Temp PO Index].[Shipping Info] = "Freight", [Temp PO Index].[Order Date] = Date(), [Temp PO Index].Vendor = "Standard Register";

 

[lespaul]

Are you really setting all the records in this table to these values or is there a WHERE clause?

Leslie

 

[jalge2]

yeah, all records.

 

[lespaul]

Ok, it's not the:

"990-"+Format(Date(),"yyyymmdd&quot

I just tried that in my database and it updated just fine. The two fields that you are setting to 'Yes' and 'No' are these boolean fields or string fields? Does the table show these fields as Yes and No or as -1 and 0?

Have you tried the update with each piece separately to see exactly where it's failing? I would run each of the following queries separately to see:

UPDATE [Temp PO Index] SET [Temp PO Index].[PO Number] = "990-"+Format(Date(),"yyyymmdd&quot

UPDATE [Temp PO Index] SET [Temp PO Index].[Order Complete] = No

UPDATE [Temp PO Index] SET [Temp PO Index].[Waiting on Invoice] = Yes

UPDATE [Temp PO Index] SET [Temp PO Index].[Shipping Info] = "Freight"

UPDATE [Temp PO Index] SET [Temp PO Index].[Order Date] = Date()

UPDATE [Temp PO Index] SET [Temp PO Index].Vendor = "Standard Register"

Is this a one time only thing or something you'll be doing regularly?

Leslie

 

[jalge2]

The two fields are checkboxes. The Waiting on Invoice is checked, the Order Complete is not.

This is also something that we do regularly, and until about a week ago, this was working fine. That's why I'm so confused about it.

 

[lespaul]

Have you tried each one separately and figured out which part it's failing on?


Leslie

 

[jalge2]

The "990" and the Date() are erroring out. Am I missing a control of some sort?

 

[lespaul]

Is the PO Number field a text field? I didn't have any problems with that statement at all.

leslie

 

[r937]

just a shot in the dark, but would & instead of + for string concatenation be the answer?

rudy

 

[jalge2]

No, that doesn't do the trick. The thing I don't understand is, this worked a week ago, and now it is saying that it's too complex. This isn't a database that we created, it's a database that's been around for 2 years. Is there a possibility of the PC missing something?

 

[lespaul]

Have you tried repairing and compacting the database? Has anything changed in the last two weeks?

Leslie

 

[jalge2]

Funny thing about that, the only thing that has changed is, the girl who usually runs this database is out, and so I've been put in place to take over this database. whoops.

 

[AlienRay]

I don't know if this will help, but I have databases that get the "too complex" error if records are missing certain data. If, for example, it needs to evaluate the distance between StartTime and EndTime, but EndTime is null for one record, the query will fail.

Try setting criteria on each of your query fields of "Is Not Null" and see if anything changes.