Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2002
    Posts
    33

    Unanswered: pulling data from query into table

    I have field DUEDATE in a table ASP. I have a query QryASP. Query expression is :
    DUEDATE : IIf(date()-ASP!DUEDATE=7, date(), ASP!DUEDATE)

    How can I pull this DUEDATE data from QryASP to ASP table

    any help is appreciable.

    thanks

  2. #2
    Join Date
    Oct 2002
    Posts
    33
    URGENT!!!!!

    Please HELP me

  3. #3
    Join Date
    Nov 2002
    Posts
    9
    Not sure if this is what you are looking for, but here's sql you can use to update the ASP table

    UPDATE ASP SET ASP.DueDate = IIf(Date()-[ASP].[DUEDATE]=7,Date(),[ASP].[DUEDATE]);

    You can run this by invoking one of the following from a code module:

    save the sql as a query, then
    docmd.openquery "Name of your query as string", acViewNormal

    OR

    docmd.runSQL "UPDATE ASP SET ASP.DueDate = IIf(Date()-[ASP].[DUEDATE]=7,Date(),[ASP].[DUEDATE]);"

    OR

    CurrentDB.Execute "UPDATE ASP SET ASP.DueDate = IIf(Date()-[ASP].[DUEDATE]=7,Date(),[ASP].[DUEDATE]);"

    OR

    CurrentDB.Execute "Name of your query as a string"

  4. #4
    Join Date
    Oct 2002
    Posts
    33
    i am really new in VBA. i am not sure that where i should write these sql statements or code. could you pls tell me where do i have to write these sql statements or code. AFTERUPDATE of DUEDATE?

    thanks

  5. #5
    Join Date
    Nov 2002
    Posts
    9
    It would help to know how you are using this. Are you calling the update from a form? If so the OnClick event of a button would work fine. If it is part of a larger routine (that is you are running a whole lot of things besides this update) then you would have to insert it in an appropriate place in the over all routine - where it makes sense.

    I can answer your question better if you can fill me in on where, how and why you need to use this update in your app.

    Ed

  6. #6
    Join Date
    Oct 2002
    Posts
    33
    hi Ed,

    thanks for reply. let me tell u about my database. i want to make a database about student tution fee payment system. they have to pay weekly. so, if tomorrow (11/18/02) is the duedate then duedate will be changed (11/25/02). so duedate will be changed automatically every week and certain tution fee will be added to students account automatically either somebody open the form or not. i dont know i did make sense to u or not. let me know if u need more information.

  7. #7
    Join Date
    Nov 2002
    Posts
    9
    You could update the due date each time the application is opened. Do you have a 'Splash' screen or maybe a Main Menu in your application?

    If so you could run any automatic updates when that form is opened. To do this include your update query as part of the OnOpen event of the splash or menu form.

    This will only help, of course, if the application is closed down and reopened on a regular basis (if all due dates for all student are the same, eg - each Monday, then the app would only need to be run once a week to ensure the dates are updated. If students have different due dates from each other, then the app should be opened daily (or there could be a button to run the update manually) to catch all possible updates). The down-side of this method is that the routine will run each time the application is opened and, depending on the size of the database, it maybe annoying to have to wait for the updates to run. However, I don't think that this update should impact (time-wise) things too much. This is probably the most straight forward way of doing it. I hope this makes sense.

    It would also be possible to create a scheduler function, based on a timer event, to check to see if any dates need to be updated. This would be paticularly useful if the application remains open, but it also requires a little more involved coding.

    Let me know if this will work for you. If not, we can always try again )

    Ed

  8. #8
    Join Date
    Oct 2002
    Posts
    33
    hi Ed

    thanks for detailed instruction. it helped me a lot. yes i am almost done with my app. i created a update button to update once in a week manually.

    THANKS FOR HELP

    Anwar

  9. #9
    Join Date
    Nov 2002
    Posts
    9

    Thumbs up

    Cool, man, glad to be of help

    Ed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •