Dateadd on query value

[Annette Massie]

I am trying to insert a record into a table via code and one of the
values to add I would like as a dateadd calculation on a value from a
query.

My code looks like this:
Set db = CurrentDb() ' if the table is in the same database
Set rsAdd = db.OpenRecordset("tblClientTreatment")
With rsAdd
.AddNew
!ClientID = Me.ClientID
!TreatmentPlanComplete = Me.TxPlanCompleted
!TreatmentPlanReviewed = Me.TxPlanReviewed
!SignedByClient = Me.TxClientSigned
!NextReview = DateAdd("d", Me.txtNumberOfDays,
qryListClientTreatmentLast.LastReview)
.Update
.Close
End With

It is erroring out at the dateadd statement. Is there a different way
to do this?

[Rich P]

Greetings,

The reason you are getting your error is because

qryListClientTreatmentLast.LastReview

does not represent a date value. It is just a field in a query. You
have to extract the date value from that field.

I am assuming that qryListClientTreatmentLast.LastReview is a query
specifically for retrieving the LastReview date. Like it only has one
column and one row. Well, you can use the DLookUp function to do the
same thing as your query:

Dim d1 As Date
d1 = DLookup("LastReview", "tblClientTreatment", "some criteria would go
here")

Or, if there is no criteria and it is just the Max date, you can do
this:

d1 = DMax("LastReview", "tblClientTreatment")

where d1 contains the DateValue of LastReview. Now you can say
...
!NextReview = DateAdd("d", Me.txtNumberOfDays,
d1)
...

DLookup and DMax are in the Access Help Files. There are also examples
of the usage.

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[fredg]

On 6 Oct 2004 06:28:35 -0700, Annette Massie wrote:

I am trying to insert a record into a table via code and one of the
values to add I would like as a dateadd calculation on a value from a
query.

My code looks like this:
Set db = CurrentDb() ' if the table is in the same database
Set rsAdd = db.OpenRecordset("tblClientTreatment")
With rsAdd
.AddNew
!ClientID = Me.ClientID
!TreatmentPlanComplete = Me.TxPlanCompleted
!TreatmentPlanReviewed = Me.TxPlanReviewed
!SignedByClient = Me.TxClientSigned
!NextReview = DateAdd("d", Me.txtNumberOfDays,
qryListClientTreatmentLast.LastReview)
.Update
.Close
End With

It is erroring out at the dateadd statement. Is there a different way
to do this?

A couple of things to consider.
1) You are trying to store a calculated field (NextReview) in your
table, which is a No No in Access.
Fields which are of a derived nature should not be stored.
When your NextReview date is needed, use a DateAdd() expression, based
upon the [LastReview] to return the Next Review date.

2) If you are doing this code in an Access Module, the Me Keyword
cannot be used, as the module is not part of any form/report object.
If you arte doing this code in a form's code window, then your use of
Me! is OK.

3) What is "qryListClientTreatmentLast.LastReview"?
If that is a field returned by a query, you need to use DLookUp() to
return the value. DLookUp(), without any additional criteria, will
return the first record's value it comes to.

If the qryListClientTreatmentLast query has only one record, then you
can use (directly in a form or report control control source):

= DateAdd("d",txtNumberOfDays,
DLookUp("[LastReview]","qryListClientTreatmentLast"))

Note: Me is a VBA keyword, not an Access keyword, so you cannot use
Me! in a control's control source.

If the query returns more than one record you will have to add
criteria to the DLookUp to get the correct value. Based upon the
information you posted, the criteria MAY be something like this:

= DateAdd("d",txtNumberOfDays,
DLookUp("[LastReview]","qryListClientTreatmentLast","[ClientID] = " &
[ClientID]))
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Pieter Linden]

an******@co.saint-croix.wi.us (Annette Massie) wrote in message news:<c6**************************@posting.google. com>...

I am trying to insert a record into a table via code and one of the
values to add I would like as a dateadd calculation on a value from a
query.

My code looks like this:
Set db = CurrentDb() ' if the table is in the same database
Set rsAdd = db.OpenRecordset("tblClientTreatment")
With rsAdd
.AddNew
!ClientID = Me.ClientID
!TreatmentPlanComplete = Me.TxPlanCompleted
!TreatmentPlanReviewed = Me.TxPlanReviewed
!SignedByClient = Me.TxClientSigned
!NextReview = DateAdd("d", Me.txtNumberOfDays,
qryListClientTreatmentLast.LastReview)
.Update
.Close
End With

It is erroring out at the dateadd statement. Is there a different way
to do this?
Umm... where do you get the value from the query? Are you somehow
assured of only a single row/value being returned when you run it?
Normally, you'd either use a domain function (DSum, DCount,
DLookup...) to return a value, and then use that, or use a function to
return a single value.

so your code *might* (VERY provisional!) look something like this:

!NextReview = DateAdd("d", Me.txtNumberOfDays, qryListClientTreatmentLast.LastReview)

Function GetLastTreatment(strCustID As String) As Date
....open a parameterized query to return a single value..

GetLastTreatment=rs.Fields("TreatmentDate").Value
End Function

and then you could use that function in your code...

!NextReview = DateAdd("d", Me.txtNumberOfDays,
GetLastTreatment("cust0001"))

Hope I haven't completely lost you...