Automating Expiration Date (and a few other things)

[Spitfire]

Good morning to everyone.
I am hoping that you will be able to help me with a table that I am working on. I have almost no experience with access, so I am useless when it comes to this.

I have attached a condensed version of the database that I am working on (removed everything except the one table that I am working on) so that you can see and edit what I am working on.

The requirements are simply this.
-If "Status" = 'new inquiry' and has not been update in 6 months, automatically changes to 'inactive' and "probability of order" automatically changes to 0%

-"Initial Inquiry Date" is set to the date the Inquiry was added to the table (but user has ability to change it manually)

-*bonus* If ship quarter has passed todays quarter then change "Status" to 'inactive'.

I hope I have attached enough for everyone to be able to help.
Thank you very much in advance, I really appreciate it.

 

[Spitfire]

Well I figured out the 2nd one down. It was rather simple having trouble with the 1st and 3rd still tho.

 

[Rabbie]

Use this update query for the first one

UPDATE Inquires SET Inquires.Status = "Inactive", Inquires.[Probability of Order] = 0
WHERE (((Inquires.Status) Like "New inquiry") AND ((DateAdd("m",6,[Last Updated]))<Now()));

 

[Spitfire]

Use this update query for the first one

UPDATE Inquires SET Inquires.Status = "Inactive", Inquires.[Probability of Order] = 0
WHERE (((Inquires.Status) Like "New inquiry") AND ((DateAdd("m",6,[Last Updated]))<Now()));

How would I do that? And would It be an automated process? Sorry like I said I have basically 0 experience with Access.

 

[Rabbie]

Create a Macro from the macros page. Set the Action to RunSQL. It will open a window where you paste in the SQL I gave you earlier

You can run the Macro from a button on a form. Just put the macro in the Onclick event for the button or use the button wizard.

 

[Spitfire]

Thanks alot, I got it working and it works the way I want almost. When i put it in the full version, It is unable to locate the table inquiries. It may be because there are a number of other tables, or maybe because there is also a Form called inquiries, I'm not entirely sure. How would I tweak this?

 

[Rabbie]

In the SQL I gave you it is spelt "Inquires" and in your last post it is spelt "inquiries" so you may need to change the name in the query to match the table.

 

[Spitfire]

Good catch, so I got it running now, however have 2 questions. First of all,

WHERE (((Inquires.Status) Like "New inquiry")

how would i change this section so instead of

Like "New inquiry"

it does all of them (because it can be "active", "new inquiry", or "order")

and secondly, it also has to do the exact same thing to a form called inquires as well. (when I click on display in datasheet within the from it takes me to a datasheet which looks identical to the form).

Should I attach a version so you can see what I mean?

I really really appreciate all the help btw.

 

[Spitfire]

here is the uploaded file
http://files-upload.com/422681/Inquiries.mdb.html
(just a bit to big to be zipped)

but if you check under forms you can see the form that I am talking about, and I was wondering if you can essentially do the exact same thing to that form?

 

[Rabbie]

Try this for the first bit

WHERE ((((Inquires.Status) Like "New inquiry") or (Inquires.Status Like "active") or (Inquires.Status Like "order") )

You may need to check the brackets are correct. I may have lost count.

For the form you would need to add something similar to the source query for the form.

 

[Spitfire]

Access won't allow a query string of more than 254 characters, the one above is 265...any why around this problem?

 

[Rabbie]

Access won't allow a query string of more than 254 characters, the one above is 265...any why around this problem?

Are you sure? I looked in Access Help - specifications Query

Attribute Maximum
Number of enforced relationships 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 gigabyte
Sort limit 255 characters in one or more fields
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255
Number of ANDs in a WHERE or HAVING clause 99
Number of characters in an SQL statement approximately 64,000

 

[Spitfire]

Help gave me this

Setting
The RunSQL action has the following arguments.

Action argument Description
SQL Statement The SQL statement for the action query or data-definition query you want to run. The maximum length of this statement is 255 characters. This is a required argument.
Use Transaction Select Yes to include this query in a transaction. Select No if you don't want to use a transaction. The default is Yes. Prior to Access 97, Access always included the query in a transaction when you ran this action by starting with a BeginTrans method, executing the SQL statement, and then concluding with a CommitTrans method. If you select No for this argument, the query may run faster.

but it also said

If you need to type an SQL statement longer than 255 characters, use the RunSQL method of the DoCmd object in Visual Basic instead. You can type SQL statements of up to 32,768 characters in Visual Basic.

but i'm not exactly sure what the DoCmd object in Visual Basic is?

 

[Rabbie]

docmd.runsql("Sql string")

 

[Spitfire]

docmd.runsql("Sql string")

Where do I enter that?

 

[Rabbie]

Where do I enter that?

In the event handler that you want to trigger running this query like the Click event for a button.

 

[Rabbie]

Had another thought which you might prefer. Open the query design page in SQL mode and save it with a sensible name. Then you can create a Macro using OpenQuery action and put the query name in the box.

 

[Spitfire]

I was actually able to tweak it so that I don't have to do the docmd thing so it looks like this now

UPDATE Inquiries
SET Status = "Inactive", [Probability of Order] = 0
WHERE Status Like "New inquiry" or Status Like "active" or Status Like "order"
AND DateAdd("m",6,[Last Updated]<Now());

I'm not sure if the last line however

AND DateAdd("m",6,[Last Updated]<Now());

is working properly. When I run the code, it simply changes all of the active/new inquiry/order to inactive. Even the ones that were updated less than 6 months ago. So it would indicate that this line is the problem. Any advice?

 

[Rabbie]

Try this

UPDATE Inquiries
SET Status = "Inactive", [Probability of Order] = 0
WHERE ((Status Like "New inquiry" or Status Like "active" or Status Like "order")
AND (DateAdd("m",6,[Last Updated]<Now()));

Without the brackets it was selecting on the Statuses without looking at the date

 

[Spitfire]

Ah, making stupid mistakes like that makes it pretty obvious that programming isn't exactly my forte. I'm taking a quick lunch break and ill be sure to check that out when I get back. Thank you very much, and the help is really appreciated.