Date subtraction

[NSAMSA]

I have a field in a query for [Consume Date]. I've formatted this field as the following:

ConsumptionDate: Format([Consume Date], "mm/dd/yyyy")

When you build Date()-1 you get yesterday's date. However, when I build [ConsumptionDate]-1, I get #error. Why can I not subtract 1 from this date, and how can this be remedied?

Thank you in advance for any help

 

[arnelgp]

it is formatted as text. use [consume date]-1

 

[Minty]

As soon as you format() a date it becomes a string. You need to do the -1 to the original date field

NewField: [Consume Date] -1

 

[NSAMSA]

I'm getting #Error when I try to subtract 1 from either the Consume Date or the Consumption Date. Is there a way to work around the string issue? I'm drawing this data from an ODBC connection and the field giving the date and time also gives me the #Error.

 

[isladogs]

What do the dates look like when imported?
Or are you saying there is an error in the imported data?

 

[NSAMSA]

Here's an example of an imported date

2017-10-24 08:59:01 -04:00

 

[Minty]

That looks like a UTC string. If the table is linked and you open the ODBC data table it will tell you what data type it is.

If it is a string you may need to use DateAdd("d",-1,CDate([Consume Date]))

Cdate() converts a string to a date

 

[NSAMSA]

CDate() was the perfect solution. Thank you!

 

[Minty]

You are welcome. Glad to have helped.