Question about query

I want to query a text field in my database which inputs data of the form xxx-xxxx-aa where "x" can be any alphabet and "a" can be any number.(eg "ora-frm-81").I have a form where user inputs the above text field and clicks the "go" button to run it.I am planning to use the command button wizard to connect the query to the button and to get the output..but does not know how to query a text field like above.Basically I want to search the table for the above textfield and pullout all the records which corresponds to that.Can anyone please help me with this query?Thanks much.

Typically, set the criteria of your query to the form that has the textbox that the user will type the search data in. Then use the command button to open the query. To set the criteria open the query in design view and place your curser in the criteria row directly below the field you want to query on. Right click and select Build from the available commands. In the Build wizard, in the left pane select your form, in the center pane select the control used for your input box and then click okay to add the criteria to the query. Save and close the query. Now open the form and type in what you want to search for, then open the query with your command button.

lwells

Hi Thankyou so much for your reply.I was attempting to do as you had suggested and running into other problems.I have a input form through which user enters data into the table.When I reopen the form to add new record ,the last entered record remains and any newdata entered overwrites previous records.Can you pls.tell me how to fix it.ie, each time I open the input form it should show blank fields.I am not able to move further because all my previous records get overwritten.Please help.Thanks

I tried to set criteria by doing rightclick-build.In the left pane I selected form1(name of my form).I am not clear about what you meant by "control used for your inputbox".There were options like label4 and text4.When I chose text4 and ran the query through the "go" button I got all the records in the table.Using label4 too I got the same.Where am I making mistake?Please help.Also suggest a way to get blankform when opening.Thanks a million

I reread my earlier post and failed to mention that you need to click the Paste before you click Okay in the Build wizard. Basically if the form is Form1 and Text4 is the control that the user will type in, then the criteria for the query will appear like this:

[Forms]![Form1]![Text4]

If that is the correct name of your form and control, then you can just copy and paste that into the criteria of your query under the field you want to query on.

To open a form for new records only, then in the properties of the form locate the property Data Entry under the All tab and set it to Yes. Now when the form opens it will always be at a new record.

lwells

It worked.I can't thankyou enough.I have one more question though.When I clcik my "go" button which is linked to the query right now I get output in the form of datasheet view of the query.Is it possible to get this output in the form of a report?How do I do this if it is possible.thanku.

Just design your report based on this query and then open the report rather than the query. If you go to the report wizard and select this query as the record source it will pretty much build a report for you. Using the wizard you can select from a few 'canned' templates/designs or just design your own.

lwells

Thanks much again.I was able to design my own report.I waned to open the DB in formview.I did tools-startup-and slected the form to be displayed whenver thedatabse opens.However I came across a project done by someone else where the database opens in the formview and inorder to open in the regularview we have to press shift and doubleclick.How can I do that?I very much appreciate all your answers.