old query doesn't like new fields

[jethomas930]

Hey!

Here's a good one for you!

I’ve added several fields to a straight forward Access dB with a single data table. Once the new fields are added, Access decided it doesn’t like a formula in an unrelated date field and I get an error message that says "the expression you entered has an invalid date value" when I go to save it.

Query runs fine without the new fields, there is no validation rule on the field, even it if where it's an original formula not something I did, so that's a none issue. The only changes made to the query were adding the new fields (text, hyperlink, number) and in a few cases there was an update to a value for another [text] field. But in all cases there is no relationship between the problem date field (or its formula) with the fields that were added. It's something totally unrelated but I have not been able to trace it -- and I bet it's something either well hidden or hiding in plain sight.

The thing is it's NOT a newly written formula that could contain a typo or missing syntax, the formula was already there, I'd own that if it were. AND it happens in each and every query, not just one. These are a queries that already work and it still work -- just without the new fields (which were created with default settings).

I'll be revisiting this error in a few days, so I thought I'd reach out to see if anyone has encountered the same issue, or for had any ideas/suggestions about my puzzle.

Here's the original formula and its error message:
>=#Sun Jan 01 2012# And <Date()+30
also this one
>="Fri Jan 01, 2010" (for a different query)
"the expression you entered has an invalid date value"

In my original attempts to fix I tried several things including using quotes instead of the pound sign, changing the date format, using the builder to recreate the expression, manually entering the date and Access always lets me save the query and the formula syntax "is always" the same as the original. BUT, when I run it, Access tells me my expression is typed wrong or my date value is invalid.

Here is an example of one of my correction attempts. I can save the query with this formula, whether typed or created with the expression builder, but in all cases the following error message is what I get when I run the new query:
>="Sun Jan 01 2012" And <Date()+30
"this expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Thanks in advance for the help and fresh eyes!

 

[WayneRyan]

J,

The "And" is causing you trouble:

>=#Sun Jan 01 2012# And <Date()+30

Change to:

Between CDate("1/1/2012") And Date() + 30

Or

Between CDate("1/1/2012") And DateAdd("d", Date(), 30)

Wayne

 

[jethomas930]

Cool, I'll be trying that shortly. There are two other formulas that return the same error, one doesn't have an "AND". They are:

>(date()-730) and <date()+730
and
>="Fri Jan 01, 2010"

Any suggestions!

Thnx a mil, I really appreciate it.

 

[pr2-eugin]

Same principal applies.. However the DateAdd function's argument is a bit messed up in Wayne's post.. It should be..

Between DateAdd("d", [B]-730, Date()[/B]) And DateAdd("d", [B]730, Date()[/B])
>= CDate("1/1/2010")

 

[jethomas930]

Thnx, so much, I really appreciate it -- didn't realize how rusty I am.

And all your formula's work!

 

[WayneRyan]

Thanks Paul,

Too much on the Server side for me.

Wayne

 

[pr2-eugin]

Thanks Paul,

Too much on the Server side for me.

Wayne

No problem Wayne.. Thought I might jump in while you were away..