Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a control's value in an SQL statement (Access 97 / SR-2)

    In a production tracking database that I maintain, I have 13 tables that represent different departments. Once a day we append time card data (estimated) into the corresponding departments table. We can then run reports that give us our unit costs and other pertinent business information.

    Because of the time consuming nature (due to my lack of access knowledge) we have never done updates to the time card data when the actuals are completed. Consequently we are always going to be a little bit off on our unit cost calculations.

    I have come up with an SQL statement that I can run that will simplify the process of deleting the estimated time card data and then we can append the actual data and re-run the reports. I am including the SQL statement I am using to do the updates.

    <pre> DoCmd.RunSQL "DELETE Embossing.*, Embossing.Date, Embossing.[Machine#] " & _
    "FROM Embossing " & _
    "WHERE (((Embossing.Date)= <font color=red> date from forms control </font color=red> ) " & _
    "AND ((Embossing.[Machine#])=99));", 0
    </pre>


    Could someone help me figure out a way to use a date in a form control inside as a variable inside my SQL statement.

    The way my process would work is, the user opens a form, enters the date of the data they are going to delete, and then click on a command button that fires a VB function that contains the above referenced SQL statement.

    My problem is getting the SQL statement to recognize the date value in the textbox control.

    The forms name is "frmRptDates46", the date control is "box1" and the command button is "button1".

    Any help is greatly appreciated.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using a control's value in an SQL statement (Access 97 / SR-2)

    Just reference the box1 date like:
    Forms!frmRptDates46!box1

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a control's value in an SQL statement (Access 97 / SR-2)

    What a simple solution. Thank you for pointing it out to me. It worked like a charm.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using a control's value in an SQL statement (Access 97 / SR-2)

    That's ok, but a question I have is why do you have a table per department ? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a control's value in an SQL statement (Access 97 / SR-2)

    couple of reasons. 1) the things being tracked are different for each department, and more importantly 2) when I set this up several years ago, I didn't know any better. I was an access beginner who was tasked with the project. Now I don't have the time to do it better.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Using a control's value in an SQL statement (Access 97 / SR-2)

    Fair enough !!
    I had to ask.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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