Currentdb.execute sql update string problem

[JKDeveloper0718]

Hello I been attempting to update a table called tblMTCSBasic that has 3 important fields as follows:

HouseholdId Number
EffectiveDateofAction Text
RentAnnualUpdate Text

The idea is Im grabbing a field from the form called Effective date and Set the Rent Annual Update to one year later than the Effective date. HouseholdId is the unique identifier of the person.

the flatrentdate is a calculation to make the effective date one year later and sets the RentAnnualUpdate. The area of concern is EffectiveDateofAction which has a filter to a date variable hudeff = Me.ActEffectiveDate and the Me.ActEffectiveDate is the form field. Keep in mind that the field in the database is TEXT so the problem is the where clause is not filtering properly because it doesnt recognize that date in whatever format I try.

I have tried many methods and the where cluase is my problem here is the following code:

Public hudeff as Date

....

hudeff = Me.ActEffectiveDate

CurrentDb.Execute ("UPDATE tblMTCSBasic SET RentAnnualUpdate = '" & flatrentdate & "' WHERE EffectiveDateofAction = '" & hudeff & "' And HouseholdId = " & Me.hhid)

Note I put hudeff = Me.ActEffectiveDate to variable because I loose the date upon an action so I need to have it set to variable.

 

[PHV]

CurrentDb.Execute "UPDATE tblMTCSBasic SET RentAnnualUpdate=#" & flatrentdate & "# WHERE EffectiveDateofAction=#" & hudeff & "# And HouseholdId=" & Me.hhid


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886