Page 1 of 4 1234 LastLast
Results 1 to 15 of 57
  1. #1
    karanvemuri is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    56

    Query help


    hey guys m a newbie learnin access, i created a form through which the agents enter the data manually into the DB. how can i create a query where i can get retrive the data using the DATE field? thanks a lot in advance

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Go to the 'Create' Tab on top.
    Click Query Design.
    Select the Table that has all your data.
    Dbl-Click the fields you want to see [including your Date field].
    In the Criteria row of the Date field of the Query - put in the date you want to see.
    Then Run the Query.

    Let me know if you have more questions.
    Last edited by Robeen; 09-13-2011 at 01:54 PM. Reason: Spelling mistake.

  3. #3
    karanvemuri is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    56
    Thanks Robeen for getting back on this. I created a search combo box on the form,where agents search the data. is there a way that i can create a pop up box where all the search results can be displayed once the search criteria is given??

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Hi Karan,

    Can you explain with a little more detail exactly what you want to do?

    For example, what is being displayed in the Combo Box?

    When an Item is selected from the Combo Box, what results do you want to see?

    Is all your data in ONE Table or do you have many tables?

    Is your Form based on a Table or on a Query?

    If you put some more details in here, it will be easier to help you.


  5. #5
    karanvemuri is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    56
    hey robeen, i will try to make this clear for you

    Form is based on a table.

    Table has 6 to 7 columns namely OrderNum,Case ID,Date,Action Taken,Status etc

    Agents enter Data by the form,so i used control wizard to have 3 command buttons namely Add new record, Save, Find record

    So when i Click on Find record and enter the search criteria the results are shown in the form columns itself..instead i want a pop up box where the results can be displayed....i hope i atleast made a few things clear?

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    There is a pretty simple way to do what you want to do.

    1. Create a new Form with all the fields that you want to have displayed when you do your search. Name it 'DetailForm'.
    2. Put an Exit button on the Form so you can close it once you have viewed your results.
    3. I still don't know exactly what value you will be using to do your search but let's say that you want to open your DetailForm with the OrderNum that is on your original Form.
    4. Put a new Command Button on your original Form.
    5. Click Cancel when you get the Command Button Wizard.
    6. Select the new command button & Open 'Prpoerty Sheet'.
    7. Click the Event Tab.
    8. Click the Ellipsis [...] to the right of 'On Click'.
    9. Select Code Builder.
    10. Type in this:
    Code:
     
    DoCmd.OpenForm "DetailForm", , , "OrderNum = " & Me.OrderNum
    I am assuming that both your Forms have a field called OrderNum.

    Now - Run your original Form and go to any record on that Form & click your new Command Button. It should open the DetailForm with the Same OrderNum that is on the original Form.

    Let me know if this helps.

  7. #7
    karanvemuri is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    56
    hi Robeen,

    This works pretty gud, but the problem that i have is, if an agent works on an Order today, he will follow up the order for a couple of days,everyday he makes a follow up for the order with new comments. So is there a way i can make a pop up box where the agent can search and retrive the Orders that he worked on yesterday?

  8. #8
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    First of all - it seems like you might be displaying more than one Order that was worked on the previous day - so the DetailsForm will have to have a Default View [Properties -> Format] of Datasheet - or Continuous Form.

    Instead of the Code I gave you before, try this:

    Code:
     
    DoCmd.OpenForm "DetailForm", , , "DateFieldInDetailForm = #" & Me.DateFieldInOriginalForm & "#"
    On the Form from which you will be opening the DetailForm, you should have a date field [in the code above, I have called that field 'DateFieldInOriginalForm'.

    Or - if you will ALWAYS want to see Orders worked on the previous day you can have something like this:

    Code:
     
    Dim Yesterday As Date
    Yesterday = Date - 1
     
    DoCmd.OpenForm "DetailForm", , , "DateFieldInDetailForm = #" & Yesterday & "#"
    This code will pull up all orders that were worked on the previous day.

  9. #9
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    P.S. I said:
    so the DetailsForm will have to have a Default View [Properties -> Format] of Datasheet - or Continuous Form.
    That is not true - you don't have to have one of those Form types if you don't want.

  10. #10
    karanvemuri is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    56
    hey robeen,

    In the Orginial Form i already have a Date Field, IS tht the one u were referring to as DatefieldinOriginal Form?

  11. #11
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Yes. That is what I was referring to - since I don't know the Name of the Date Field on your Original Form.
    Last edited by Robeen; 09-15-2011 at 08:35 AM. Reason: Typo.

  12. #12
    karanvemuri is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    56
    That worked really well, since 3 r 4 agents will manually update the DB everyday, if one agent runs a search in the detail form to have a look at his previous day worked orders, the form gives all the orders which were worked by all the 4 agents, is there a way we can filter this by the agents name r somethin like tht?

  13. #13
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I thought about that earlier.
    You will have to pass the AgentID to the DetailForm when you open it.
    Do you have your AgentID on the Original Form?

  14. #14
    karanvemuri is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Sep 2011
    Posts
    56
    Since there are only 4 agents, i didnt use a Agent ID, Can we filter by agent's name or should i add a AgentID to the form?

  15. #15
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    You don't have to have AgentID.

    If the Agent's Name is on the OriginalForm - and on the DetailForm - you can use the AgentName.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums