Run query depending on date

[papasmurfuo9]

Hi

I would like a query to run, depending on a date

If the date in [Txt_Date_Account_Opened] is between todays date, and within the last 30 days
do CurrentDb.Execute ("Qry_Append_Units")

else a msg box stating "account is not yet open"

thanks in advance

 

[pr2-eugin]

Use the Form current method.

Private Sub Form_Current()
    If Me.Txt_Date_Account_Opened >= Date() And Me.Txt_Date_Account_Opened <= Date() + 30 Then
        CurrentDb.Execute ("Qry_Append_Units")
    Else
        MsgBox "Account is not yet open"
    End If
End Sub

 

[papasmurfuo9]

Hi
Thanks for the reply,
i have to have it on Private Sub Btn_Update_Click()
tested the above but doesnt seem to work? please advise
thank you

 

[pr2-eugin]

What is the date on the record for which you are trying to do this?

Or

Are you trying to run a Update/Append Query on a table for all records whose date is between Date and Date+30? If it is latter, then we need to resort to a different method.

 

[papasmurfuo9]

Hi
the date is not specific, there is a form with a drop down with 100's of a accounts to select
every account has a date, if the date is within the last 30 days then the user can click update and the query needs to run, if its older then that paticular query needs to be ignored.
hope this makes sense
thank you

 

[pr2-eugin]

Not really ! how about adding a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[papasmurfuo9]

thanks
i cant upload from work, will have to when i get home
thank you

 

[pr2-eugin]

Okay ! Say if you have 100 records on the ComboBox, then you select 1 and click the button. Where does it get this date? The comboBox can only list one item; although it can have several columns. So explain a little bit more in detail how it is set up, so we can guide you correctly.

 

[papasmurfuo9]

the combo box has account numbers, when they select one, it populates all the other fields on the form, one being Txt_Date_Account_Opened which is linked to Fld_Date_Account_Opened on the table

combo box is called Txt_Store_Number_Selector

cheers

 

[pr2-eugin]

So where is this button placed? Is this a MasterForm, SubForm setup? Have you tried code walk through?

 

[papasmurfuo9]

Hi
its a master form,
the button is on there, once pressed if the date is within 30 dates, that query apends it to another table in the data base

thanks

 

[pr2-eugin]

Okay I am just going to wait until your DB upload, as it is not making much sense (atleast to me). Sorry !

 

[papasmurfuo9]

ok thank you