Deleting data from selected records

[jcbhydro]

Good Morning,
My Membership Database includes 3 fields in the main Table which I need to clear prior to the new subscription year, but on a selective basis.
The fields are R/N for (Renewal or New), a subscription date and an amount field (Currency).

At the due date I need to clear all 3 fields except where N/R=N AND date >01/01/2014, when data in all 3 fields is to be retained.

I have tried several SQL codings to achieve this but have been unsuccessful so far.

Any suggestions to solve this problem would be most welcome.

jcbhydro

 

[Minty]

Create a select query that pulls in the field data you need, then change it to an update query, updating those fields to either zero or null values as required.

 

[jcbhydro]

Thanks Minty,

I shall try again based on your suggestion.
It seems so logical, now that you have suggested it.

jcbhydro

 

[jcbhydro]

Minty,

I have followed your advice and produced the following SQL which works beautifullyl

UPDATE [Mail List] SET [Mail List].[Renew/New] = "R", [Mail List].[£ Recd] = 0, [Mail List].SubDate = Null
WHERE ((([Mail List].[Renew/New])="R")) OR ((([Mail List].[Renew/New])="N") AND (([Mail List].SubDate)<#1/1/2014#));

I have 2 supplementary questions relating to this query;

a) Is it possible make the date criteria valid for any year, thereby obviating the necessity to modify the SQL code each year?

b) Is there a means of flashing up a Warning Message to alert the operator to consequences of running the Query and to provide an OK/Cancel facility. This is a feature that I could usefully add to a number of the queries I have built.

Many thanks,

jcbhydro

 

[Minty]

UPDATE [Mail List] SET [Mail List].[Renew/New] = "R", [Mail List].[£ Recd] = 0, [Mail List].SubDate = Null
WHERE ((([Mail List].[Renew/New])="R")) OR ((([Mail List].[Renew/New])="N") AND (([Mail List].SubDate)<#1/1/2014#));

I have 2 supplementary questions relating to this query;

a) Is it possible make the date criteria valid for any year, thereby obviating the necessity to modify the SQL code each year?

Yes - If you always run it up to the 1/1 of the current year then Year([Maillist.SubDAte]) < Year(Date())

b) Is there a means of flashing up a Warning Message to alert the operator to consequences of running the Query and to provide an OK/Cancel facility. This is a feature that I could usefully add to a number of the queries I have built.

There are many ways to achieve this. In the simplest way make a form with a button for each query you want to run. Then attach some code to the on click event for the button that uses a msgbox to alert the user what is happening and if they want to continue. Air code;

Dim LResponse As Integer

LResponse = MsgBox("Do you wish to continue?", vbYesNo, "Continue")

If LResponse = vbYes Then
   {...statements to run your query here , 
...I would probably also turn warning off , no point asking them again
...docmd.openquery "your queryname"
... turn warnings back on 
...}

Else
   {...other code here if required...}

End If

You could get a lot more sophisticated and make a list of queries to run and have date selectors that feed into the queries from the form as well if appropriate, but that is something you will gain a lot more from if you look around and pick up from these forums.

 

[jcbhydro]

Many thanks for you additional assistance.

The item b) will require some considerable thought for someone with my limited capablities.

The date problem seemed more straightforward. I substituted [Mail List].Subdate for my specific date of 01/01/2014, but it didn;t work. The query ran, but didn't produce the same result. Does Year(DATE()) at this time equate to 01/01/2014? For me it produced a date in 1905.
I can't seem to find a definition of YEAR (DATE()) in the Access Helpfiles.

jcbhydro

 

[spikepl]

check the time functions http://www.techonthenet.com/access/functions/