Add number of days to date

  • Thread starter Thread starter rbeach
  • Start date Start date
R

rbeach

I have the below formula and receive a type mismatch when this is run in the
query:

ExpDate: DateAdd("d",[PPE].[ExpDays],[PPEReceived].[DateReceived])

The table "PPE" has a field named "ExpDays" which is a number of days till
expiration.

The Table "PPEReceived" has a field "DateReceived" with a short date for the
date the item was received into inventory.

I need to add the "Expdays" from the "PPE" table to the "DateReceived" from
the "PPEReceived" table to get the actual "ExpDate" (Expiration Date).

Please let me know what formula to use.
 
rbeach said:
I have the below formula and receive a type mismatch when this is run in
the
query:

ExpDate: DateAdd("d",[PPE].[ExpDays],[PPEReceived].[DateReceived])

The table "PPE" has a field named "ExpDays" which is a number of days till
expiration.

The Table "PPEReceived" has a field "DateReceived" with a short date for
the
date the item was received into inventory.

I need to add the "Expdays" from the "PPE" table to the "DateReceived"
from
the "PPEReceived" table to get the actual "ExpDate" (Expiration Date).

Please let me know what formula to use.
Click to expand...

Are the two tables related in the query?

DateReceived has to be a Date field and ExpDays has to be a numeric field.

I'd just use

ExpDate: [PPEReceived].[DateReceived] +[PPE].[ExpDays]

Tom Lake
 
The formula is correct but the error message says that either ExpDays is not
a number (maybe a text field) or DateReceived is not a date (text that looks
like a date) or both.
When you say "PPEReceived" has a field "DateReceived" with a short date I
seem to think you have a text field. Dates are stored as a decimal number
but are formated to appear as some date display.
You can change text to number like this -- CInt([ExpDays])
And text to date -- CVDate([DateReceived]) provided it is in a format that
Access will recognize as a date otherwise you would have to parse it for use
in a different conversion function.
 
Karl,

The Exp Days is set up in the table as a number.
The DateReceived is set up in the table as a Date/Time - Short Date.

I should not need to reformat either of these feilds in the query.

Any other suggestions?
--
Rick


KARL DEWEY said:
The formula is correct but the error message says that either ExpDays is not
a number (maybe a text field) or DateReceived is not a date (text that looks
like a date) or both.
When you say "PPEReceived" has a field "DateReceived" with a short date I
seem to think you have a text field. Dates are stored as a decimal number
but are formated to appear as some date display.
You can change text to number like this -- CInt([ExpDays])
And text to date -- CVDate([DateReceived]) provided it is in a format that
Access will recognize as a date otherwise you would have to parse it for use
in a different conversion function.

--
Build a little, test a little.


rbeach said:
I have the below formula and receive a type mismatch when this is run in the
query:

ExpDate: DateAdd("d",[PPE].[ExpDays],[PPEReceived].[DateReceived])

The table "PPE" has a field named "ExpDays" which is a number of days till
expiration.

The Table "PPEReceived" has a field "DateReceived" with a short date for the
date the item was received into inventory.

I need to add the "Expdays" from the "PPE" table to the "DateReceived" from
the "PPEReceived" table to get the actual "ExpDate" (Expiration Date).

Please let me know what formula to use.
Click to expand...
Click to expand...
 
Simplicity at it's best. That worked.

Thanks,
--
Rick


Tom Lake said:
rbeach said:
I have the below formula and receive a type mismatch when this is run in
the
query:

ExpDate: DateAdd("d",[PPE].[ExpDays],[PPEReceived].[DateReceived])

The table "PPE" has a field named "ExpDays" which is a number of days till
expiration.

The Table "PPEReceived" has a field "DateReceived" with a short date for
the
date the item was received into inventory.

I need to add the "Expdays" from the "PPE" table to the "DateReceived"
from
the "PPEReceived" table to get the actual "ExpDate" (Expiration Date).

Please let me know what formula to use.
Click to expand...

Are the two tables related in the query?

DateReceived has to be a Date field and ExpDays has to be a numeric field.

I'd just use

ExpDate: [PPEReceived].[DateReceived] +[PPE].[ExpDays]

Tom Lake
Click to expand...
 
Back
Top