Query help

[karanvemuri]

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

[Robeen]

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.

[karanvemuri]

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??

[Robeen]

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.

[karanvemuri]

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?

[Robeen]

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:

 
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.

[karanvemuri]

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?

[Robeen]

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:

 
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:

 
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.

[Robeen]

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.

[karanvemuri]

hey robeen,

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

[Robeen]

Yes. That is what I was referring to - since I don't know the Name of the Date Field on your Original Form.

[karanvemuri]

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?

[Robeen]

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?

[karanvemuri]

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?

[Robeen]

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.