add a value to a date

[P Lally]

I have a table linked to an ODBC 2005 SQL database. One of the values in the
table is a date stored in the table as "Date/Time". When I try to create a
query in MS Access to add an integer to this date value I receive the
following message: "ODBC call failed" - [Microsoft\ODBC SQL Server
Driver][SQL Server]Operand and data typedatetime is invalid for sum operator
(#8117). How can I work around this?

 

[KARL DEWEY]

It appears you use 'Sum' to do the addition. Sum is to add the field of all
records in a data set.
Use + (Plus) like this --
New_Date: [SomeDateField] + 1

 

[P Lally]

This is how my expression looks like:

DaysToPay: [dbo_ARInvoiceHistory]![PaidDate]+30

It appears you use 'Sum' to do the addition. Sum is to add the field of all
records in a data set.
Use + (Plus) like this --
New_Date: [SomeDateField] + 1

--
Build a little, test a little.

I have a table linked to an ODBC 2005 SQL database. One of the values in the
table is a date stored in the table as "Date/Time". When I try to create a
query in MS Access to add an integer to this date value I receive the
following message: "ODBC call failed" - [Microsoft\ODBC SQL Server
Driver][SQL Server]Operand and data typedatetime is invalid for sum operator
(#8117). How can I work around this?

 

[Marshall Barton]

I have a table linked to an ODBC 2005 SQL database. One of the values in the
table is a date stored in the table as "Date/Time". When I try to create a
query in MS Access to add an integer to this date value I receive the
following message: "ODBC call failed" - [Microsoft\ODBC SQL Server
Driver][SQL Server]Operand and data typedatetime is invalid for sum operator
(#8117). How can I work around this?

I have no idea how SQL Server deals with dates, but the
error implies that adding an integer to a date is not
allowed.

While Access/Jet is able to deal with that, you should add
days to a date using:

DaysToPay: DateAdd("d", 30, dbo_ARInvoiceHistory.PaidDate)

I would expect ODBC to process the same calculation.

 

[plally]

This is getting a little closer to what I'm looking for. How can I make the
number of days (in your example, 30) a variable?

I have a table linked to an ODBC 2005 SQL database. One of the values in the
table is a date stored in the table as "Date/Time". When I try to create a
query in MS Access to add an integer to this date value I receive the
following message: "ODBC call failed" - [Microsoft\ODBC SQL Server
Driver][SQL Server]Operand and data typedatetime is invalid for sum operator
(#8117). How can I work around this?

I have no idea how SQL Server deals with dates, but the
error implies that adding an integer to a date is not
allowed.

While Access/Jet is able to deal with that, you should add
days to a date using:

DaysToPay: DateAdd("d", 30, dbo_ARInvoiceHistory.PaidDate)

I would expect ODBC to process the same calculation.

 

[plally]

As an alternate, what function would I use in Access to convert text
formattted as a date to a numerical value, add the variable, and then convert
the result back to a date?

I have a table linked to an ODBC 2005 SQL database. One of the values in the
table is a date stored in the table as "Date/Time". When I try to create a
query in MS Access to add an integer to this date value I receive the
following message: "ODBC call failed" - [Microsoft\ODBC SQL Server
Driver][SQL Server]Operand and data typedatetime is invalid for sum operator
(#8117). How can I work around this?

I have no idea how SQL Server deals with dates, but the
error implies that adding an integer to a date is not
allowed.

While Access/Jet is able to deal with that, you should add
days to a date using:

DaysToPay: DateAdd("d", 30, dbo_ARInvoiceHistory.PaidDate)

I would expect ODBC to process the same calculation.

 

[John Spencer]

Use CDate or DateValue to convert a date string to an actual date. (No Nulls
allowed). Once you have a valid date you can use CLng to convert that to a
number, then add a number of days. Then convert the result back to a date
using CDate or DateValue.

Or just use the DateAdd function. It will convert a valid date string to a
variable of the type DATE. And DateAdd will handle NULLS.

DateAdd("d",1,"09/30/2008")
will give you a DATE that has the value of
1 October 2008


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

As an alternate, what function would I use in Access to convert text
formattted as a date to a numerical value, add the variable, and then convert
the result back to a date?

I have a table linked to an ODBC 2005 SQL database. One of the values in the
table is a date stored in the table as "Date/Time". When I try to create a
query in MS Access to add an integer to this date value I receive the
following message: "ODBC call failed" - [Microsoft\ODBC SQL Server
Driver][SQL Server]Operand and data typedatetime is invalid for sum operator
(#8117). How can I work around this?

I have no idea how SQL Server deals with dates, but the
error implies that adding an integer to a date is not
allowed.

While Access/Jet is able to deal with that, you should add
days to a date using:

DaysToPay: DateAdd("d", 30, dbo_ARInvoiceHistory.PaidDate)

I would expect ODBC to process the same calculation.

 

[Marshall Barton]

I guess the error was caused by the date really being a Text
string that looks like a date. The DateAdd function will
try to convert the text to a real date value.

You can paramerterize the 30 the same way you would do it
for any other query parameter. Where is the number of days
specified? You could use a popup prompt:
DaysToPay: DateAdd("d", [Enter number of days],
dbo_ARInvoiceHistory.PaidDate)

But, that is a really crude way to do things. Better to
provide a form text box for users to enter the number of
days. In this case, the parameter would be like:
DaysToPay: DateAdd("d", Forms!theform.thetextbox,
dbo_ARInvoiceHistory.PaidDate)

There may or may not be other ways to do all this when
dealing with SQL Server??
--
Marsh
MVP [MS Access]

This is getting a little closer to what I'm looking for. How can I make the
number of days (in your example, 30) a variable?

I have a table linked to an ODBC 2005 SQL database. One of the values in the
table is a date stored in the table as "Date/Time". When I try to create a
query in MS Access to add an integer to this date value I receive the
following message: "ODBC call failed" - [Microsoft\ODBC SQL Server
Driver][SQL Server]Operand and data typedatetime is invalid for sum operator
(#8117). How can I work around this?

I have no idea how SQL Server deals with dates, but the
error implies that adding an integer to a date is not
allowed.

While Access/Jet is able to deal with that, you should add
days to a date using:

DaysToPay: DateAdd("d", 30, dbo_ARInvoiceHistory.PaidDate)

I would expect ODBC to process the same calculation.

 

[plally]

thanks for all of your valuable tips.

Pauline L Lally

Use CDate or DateValue to convert a date string to an actual date. (No Nulls
allowed). Once you have a valid date you can use CLng to convert that to a
number, then add a number of days. Then convert the result back to a date
using CDate or DateValue.

Or just use the DateAdd function. It will convert a valid date string to a
variable of the type DATE. And DateAdd will handle NULLS.

DateAdd("d",1,"09/30/2008")
will give you a DATE that has the value of
1 October 2008


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

As an alternate, what function would I use in Access to convert text
formattted as a date to a numerical value, add the variable, and then convert
the result back to a date?

I have a table linked to an ODBC 2005 SQL database. One of the values in the
table is a date stored in the table as "Date/Time". When I try to create a
query in MS Access to add an integer to this date value I receive the
following message: "ODBC call failed" - [Microsoft\ODBC SQL Server
Driver][SQL Server]Operand and data typedatetime is invalid for sum operator
(#8117). How can I work around this?

I have no idea how SQL Server deals with dates, but the
error implies that adding an integer to a date is not
allowed.

While Access/Jet is able to deal with that, you should add
days to a date using:

DaysToPay: DateAdd("d", 30, dbo_ARInvoiceHistory.PaidDate)

I would expect ODBC to process the same calculation.