Date subtraction (1 Viewer)

NSAMSA · Nov 17, 2017

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 · Nov 17, 2017

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

Minty · Nov 17, 2017

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

Code:

NewField: [Consume Date] -1

NSAMSA · Nov 17, 2017

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 · Nov 17, 2017

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

NSAMSA · Nov 17, 2017

Here's an example of an imported date

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

Minty · Nov 17, 2017

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 · Nov 17, 2017

CDate() was the perfect solution. Thank you!

Minty · Nov 17, 2017

You are welcome. Glad to have helped.

Date subtraction (1 Viewer)

NSAMSA

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Minty

AWF VIP

NSAMSA

Registered User.

isladogs

MVP / VIP

NSAMSA

Registered User.

Minty

AWF VIP

NSAMSA

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread