Update query with parameters

[alanfortune]

I'm trying to run a complete MI suite for the current year, but early next year I need the ability to run it for the previous year too.

I'm already doing this in Excel, but The Management has decreed that as there are no Excel skills in the team (I'd also argue a lack of Access skills too!) that it would be more easily managed with Access queries.

So, Plan A is to have the year held in a table. The queries refer to this and extract the data accordingly (this is the easy part), and there's the ability to change it from Excel when required (this isn't!).

I have managed to get the queries to read from the date table and successfully pull the correct data, and I can pass parameters to Access queries. However, when I build a new update query from scratch it updates the year on the first run, but as soon as I try to rerun it, it updates '0 rows'.

I've searched this forum, Googled everything in sight and even resorted to Access Help, all to no avail.
I should add that I'm a bit of a novice with Access so any help with this would be greatly appreciated.

It's Access 2010 and I've included a few images below to illustrate my dilemma and hopefully aid resolution.

Thanks

Alan

PS. Haven't cracked posting an image yet either, working on it right now....

 

[alanfortune]

I still can't include a picture, but it's something like this;

Table DateParm only contains an ID (primary key: 1) and the field Audit Year.

Update query UpdateDate is based on table DateParm, and has the following in the design view;

Field: Audit Year
Table: DateParm
Update To: [Audit Year]
Criteria: [InputDate]

The SQL looks like this;

UPDATE DateParm SET DateParm.[Audit Year] = [Audit Year]
WHERE (((DateParm.[Audit Year])=[InputDate]));

When I run the code, I get an input box asking for InputDate, but it doesn't go on to put the value in DateParm.Audit Year.

Thanks

Alan

 

[TerryHogarth21]

I still can't include a picture, but it's something like this;

Table DateParm only contains an ID (primary key: 1) and the field Audit Year.

Update query UpdateDate is based on table DateParm, and has the following in the design view;

Field: Audit Year
Table: DateParm
Update To: [Audit Year]
Criteria: [InputDate]

The SQL looks like this;

UPDATE DateParm SET DateParm.[Audit Year] = [Audit Year]
WHERE (((DateParm.[Audit Year])=[InputDate]));

When I run the code, I get an input box asking for InputDate, but it doesn't go on to put the value in DateParm.Audit Year.

Thanks

Alan

This works as intended like the way you have written it, the reason being no updates is because you don't have any data in the field [Audit Year] equaled to what your prompt is. All your prompt did was set the input year to what is equaled to the audit year if the audit year exists. So you are in essence setting 2015 to 2015 if you typed in 2015 in your prompt and the Audit Year has 2015 in the table... Is that really what you want?

 

[strive4peace]

this:

SET DateParm.[Audit Year] = [Audit Year]

is like saying

SET 1=1 ... they are already the same

</pre>

 

[alanfortune]

Terry - this isn't what I'm trying to do.

I'm trying to get an input box to appear. The value entered should then be put into the DateParm.Audit Year field for use in a later query.

Thanks

Alan

 

[alanfortune]

CRACKED IT!

Thanks for your help gentlemen, you got me thinking about the code a bit more.

After 'experimenting' with the code a bit, I ended up with this;

UPDATE DateParm SET DateParm.[Audit Year] = [iDate]
WHERE (((DateParm.TestCase)>5));

TestCase is always set to 10 so the condition is always true (I couldn't get it to execute without a Where clause).

When executed, it now pops up an input box requesting a year and puts that value into Audit Year ready for the next query to use.

Thanks again for your help.

Alan