How can I create a multiple field search box that use logical operators in an option box? - 21045

Hi,
I need assistance in creating a multiple field search box, i have a
screen shot but i couldn't find a way to include it in this message.
I have been battling with this problem for weeks.
I'm trying to use ms access 2003 to create a search box similar to
one i saw in foxpro.  It searches all the fields in only one table so
each
table has a search box for it.  Here is a description for the search
box for the community table which is one of the tables in the
database.
It has a drop down list that selects all the fields in the table such
as area code for the community, community code, and community
description.  Beside it is another dropdown list box for operator
which you can use to select operators such as plus, equals to (=),
greater than, less than, is null, between, in.
beside this is a box that display value.
Below all of these form fileds is an option box with options for and,
or.
and beneath this is another row just like the one before the options
box.
Below theses are three buttons for search, all, cancel. What's really
hard for me to figure out is the option box that uses AND, OR to
combine
the two diffrent search groups.
I'd be glad to send a screen shot to anyone who requires it to assist
me.
Thank you

0
suleyman
7/9/2007 4:11:06 PM
access.forms 6864 articles. 2 followers. Follow

2 Replies
654 Views

Similar Articles

[PageSpeed] 19

suleyman.mutuwa@gmail.com wrote in
news:1183997466.481906.174500@i13g2000prf.googlegroups.com: 

> Hi,
> I need assistance in creating a multiple field search box, i
> have a screen shot but i couldn't find a way to include it in
> this message. I have been battling with this problem for
> weeks. I'm trying to use ms access 2003 to create a search box
> similar to one i saw in foxpro.  It searches all the fields in
> only one table so each
> table has a search box for it.  Here is a description for the
> search box for the community table which is one of the tables
> in the database.
> It has a drop down list that selects all the fields in the
> table such as area code for the community, community code, and
> community description.  Beside it is another dropdown list box
> for operator which you can use to select operators such as
> plus, equals to (=), greater than, less than, is null,
> between, in. beside this is a box that display value.
> Below all of these form fileds is an option box with options
> for and, or.
> and beneath this is another row just like the one before the
> options box.
> Below theses are three buttons for search, all, cancel. What's
> really hard for me to figure out is the option box that uses
> AND, OR to combine
> the two diffrent search groups.
> I'd be glad to send a screen shot to anyone who requires it to
> assist me.
> Thank you
> 
The option group returns a number assigned to each option in the 
group.

Build the filter string for the first row, 


stWhereClause1 = Me.cboFields1 + " " + cboOperator1 + " """ + 
txtValue1 + """"

The + concatenation operators should return nothing if the 
textbox is empty.

Build stWhereClause2 using the equivalent controls in hte next 
row

Now we need to test that each whereclause has been populated, 
If len(StwhereClause1)> 0 and Len(stWhereClause2) > 0 then
  If groupJoinType = 1 then
   StwhereClause = stwhereClause1 " AND " stwhereclause2
  elseif groupJoinType = 2 then
   StwhereClause = stwhereClause1 " OR " stwhereclause2
  end if
elseif len(StwhereClause1)> 0 then
  StwhereClause = stwhereClause1
elseif len(StwhereClause2)> 0 then
  StwhereClause = stwhereClause2
else
  StwhereClause = ""
end if
-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
7/9/2007 4:07:28 PM
On Jul 9, 5:07 pm, Bob Quintal <rquin...@sPAmpatico.ca> wrote:
> suleyman.mut...@gmail.com wrote innews:1183997466.481906.174500@i13g2000prf.googlegroups.com:
>
>
>
>
>
> > Hi,
> > I need assistance in creating a multiple field search box, i
> > have a screen shot but i couldn't find a way to include it in
> > this message. I have been battling with this problem for
> > weeks. I'm trying to use ms access 2003 to create a search box
> > similar to one i saw in foxpro.  It searches all the fields in
> > only one table so each
> > table has a search box for it.  Here is a description for the
> > search box for the community table which is one of the tables
> > in the database.
> > It has a drop down list that selects all the fields in the
> > table such as area code for the community, community code, and
> > community description.  Beside it is another dropdown list box
> > for operator which you can use to select operators such as
> > plus, equals to (=), greater than, less than, is null,
> > between, in. beside this is a box that display value.
> > Below all of these form fileds is an option box with options
> > for and, or.
> > and beneath this is another row just like the one before the
> > options box.
> > Below theses are three buttons for search, all, cancel. What's
> > really hard for me to figure out is the option box that uses
> > AND, OR to combine
> > the two diffrent search groups.
> > I'd be glad to send a screen shot to anyone who requires it to
> > assist me.
> > Thank you
>
> The option group returns a number assigned to each option in the
> group.
>
> Build the filter string for the first row,
>
> stWhereClause1 = Me.cboFields1 + " " + cboOperator1 + " """ +
> txtValue1 + """"
>
> The + concatenation operators should return nothing if the
> textbox is empty.
>
> Build stWhereClause2 using the equivalent controls in hte next
> row
>
> Now we need to test that each whereclause has been populated,
> If len(StwhereClause1)> 0 and Len(stWhereClause2) > 0 then
>   If groupJoinType = 1 then
>    StwhereClause = stwhereClause1 " AND " stwhereclause2
>   elseif groupJoinType = 2 then
>    StwhereClause = stwhereClause1 " OR " stwhereclause2
>   end if
> elseif len(StwhereClause1)> 0 then
>   StwhereClause = stwhereClause1
> elseif len(StwhereClause2)> 0 then
>   StwhereClause = stwhereClause2
> else
>   StwhereClause = ""
> end if
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -
>
> - Show quoted text -
Hey Bob, Hey steve profound thanks for your assistance, i'll give your
solutions a try.

0
pointer
7/12/2007 12:22:40 PM
Reply:

Similar Artilces:

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

option button
Hi I need some help! I have a column with results from a survey. The results are in text but do have a ranking, e.g. the results can be A,B,C,D or E. I now want to create an option button or similar where I can let the user choose to cap the results so e.g. the maximum result is a B and then all A results should show up as B's. Is this possible? Thanks You'll need a helper column to do this. Try something like this: =IF(B3<=C$1,C$1,B3) B3 is the survey result, C1 is the "Cap" value. HTH, Barb Reinhardt "Andreas" wrote: > Hi > > I need som...

Autonumber created.. problems in the future?
I managed to create an autonumber in Microsoft CRM. I did this by making a field "Number"(in the database "New_Number") and I published it on the form. Then I went To the SQL server and I changed the field in the table to Identity Yes, Identity seed 1, Identity Increment 1. I locked the field on the form. It worked! I think that this is not supported by Microsoft. But has anybody got any idea which troubles i could get with this configuration? San ________________________________ Do you know all add-ons for Microsoft CRM? Visit http://www.pimpmycrm.com The biggest dange...

How to create an autonumber field?
hi i need to create an autonumber field to automate account numbering. how can i do this? thanx You can do this using a post callout piece of code so when you update an account this code is called which calls back into the platform and works out the last account number then adds one to it and updates the account record. look on msdn.microsoft.com under crm for examples -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Max" <Max@discussions.microsoft.com> wrote in message news:0ABFF244-EC0A-48EC-9E76-7CA61E6EBC3A@microsoft.com... > hi > > i need ...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

Is anyone an expert with outlook that I can call on the phone?
How do I share calenders between outlook and my MSN Premier account? Assuming you are using the outlook connector, you need to set the msn account to be the default message store then outlook will use the msn calendar as the default. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this...

explanation of codes in Visual Basic when creating User form
Hi, I am trying to create a user form in Visual Basic however I'm trying to teach myself by reading/watching tutorials. (www.contectures.o.ca, etc) A lot of the instructions I am seeing simply give the code rather than explain how to actually write one from scratch. So... I need to know what each 'term' means so I can understand how the codes work. Any help is much appreciated :) One of the first codes is for the Add button Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("PartsData") What d...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

Certain Keys/Characters not recognised when creating a task
I've just attempted to create a task and the edit control for the subject of the task refused to accept the characters c h s t u and v. I was able to switch to other applications such as a command prompt and internet explorer and type the characters quite happily (so there is nothing wrong with the keyboard) but when I switch back to Outlook it will not recognise them. I'm using Outlook2003 as installed with Office 2003 Professional (SP1 and all other updates applied). As a last resort I closed down Outlook and restarted the program which is now accepting the keys/characters. A...

get a result of an sql into a field
Hi there I would like to get a result of an sql execution (ms sql server) into aq filed. example i A1 I have a ID number in A2 I would like to get the result of something like this 'select name from address where id=A1' Does this exist in Excel ? Thanks in advance Ralf Here is the sub i have written for loading an Sql Query into th worksheet. Parameters: Server Name DataBase Name SQL Command Target Sheet name Column to begin from Row to begin from ex: CALL LoadData("MyServer","MyDataBase","Select UserName fro TblNames", "QueryData"...

print multiple pages on one sheet of paper
I am using mailmerge in Publisher to create placecards for a party we are hosting. The final size of the placecards is 1.5" by 1.5" and we have to print 100 final cards. Publisher gives me the option of printing multiple copies of the same page on one sheet of letter sized paper or one page on one sheet of letter sized paper. What I would like to do, however, is print multiple different pages on one sheet of paper. If I cannot find a solution for this, I will need to print 100 separate pages with a 1.5" square box of copy in the center of each sheet. In page setup, sel...

Can I share entities in migration process?
The entities in CRM have only one owning user. If I want=20 who a user see a entity of another user, the entity must=20 be shared with this user.=20 Well, I want migrate entities who must be shared with some=20 users. Exists a way to do it? Thank you for pay attention and sorry my bad english. []'s Vin=EDcius Pitta Lima de Ara=FAjo ...

Can't do adjustment in analytical accounting
I am trying to change an analytical assignmnet through the Edit Analysis. When I try and change it, I receive an error stating "The Code of this Transaction Dimension cannot be adjusted". Has anyone seen this before? I have done similar changes many times, but I don't know why I can't now. Any insight would be helpful. Thanks, KJ Hi KJ What version of Gp are you running? I haveseen this happen in 8 but one of the service packs fixed it. (I think it was SP 4 or 5) Fliehigh "KJ" wrote: > I am trying to change an analytical assignmnet through the Edit >...

Load image in a unbound control from a attachment field in recor
I have a unbound (single not continuious) form with 16 differant records from the same recordset. No problem loading the this data from recordset in VBA. PROBLEM I need to know how to load the unbound controls with Image's from an attachment field in another recordset The normal method of control = Rs!field does not work Please advise -- Thanks Tom dans l'article 78DC5502-3A76-4562-AA20-736446AB1448@microsoft.com, Tom � Tom@discussions.microsoft.com a �crit le 21/01/08 20:28�: > I have a unbound (single not continuious) form with 16 differant records from > the same record...

Does Outlook use the DAV protocol?
I'm an Outlook Express user who wants to switch to Outlook. I received a notice from Microsoft that includes the following: "... as of June 30, 2008, Microsoft is disabling the DAV protocol and you will no longer be able to access your Hotmail Inbox via Outlook Express." Please tell me if this action by Microsoft will affect Outlook in the same manner, or am I free to make the switch. "BudV" <BudVitoff@(NO)att.(SPAM)net> wrote in message news:%230XUDi%23zIHA.2384@TK2MSFTNGP02.phx.gbl... > I'm an Outlook Express user who wants to switch to Outlook...

form fields not saving to table
I have created a database with a form and three of the fields are not saving the information to the table. I have checked the row and control sources and now at a loss and thoughts? As you scroll through existing records is the data of the table displayed? Post the SQL of the query or form source. -- Build a little, test a little. "Kim" wrote: > I have created a database with a form and three of the fields are not saving > the information to the table. I have checked the row and control sources and > now at a loss and thoughts? > > ...

Outlook context menu lost most options
Hi there. I'd dearly love some help with this matter. It's been going on for months now. Has anyone got any idea how I can sort this issue out? "Jonathan E. (NZ)" <s_pam_stop@hotmail.comTAKEOUTTHESECAPS> wrote in message news:<eYICp6NCFHA.3976@tk2msftngp13.phx.gbl>... Hi there, I run outlook 2003. It would seem that the context menu has lost the plot. It looks like outlook has gone into some "mode" other than the one it is usually in by default. I have found that when I right click on any mail folder the context menu has very different options f...

HOWTO: Customizing CRM
Hello, We would like to customize our CRM Opportunity form. We want to include a OpportunityNumber field, which will be auto-generated when the new opportunity is created, similar to auto-numbers for Contracts, Cases, Articles, Quotes, etc. Is it possible and how do we go about it? I've looked at CRM Deployment Manager. It's possible to create a simple custom Attribute for particular object. But I couldn't figure out how to make it an auto-generated number, like SQLServer Identity... Could I just modify OpportunityBase table in MSCRM database? Do I need to change METABASE databas...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

COUNTIF MULTIPLE CRITERIA #2
To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. Thanks will A Try this: =SUMPRODUCT(--(A1:A10="aa"),--(B1:B10="bb"),--(C1:C10="cc")) Adjust range references to suit your situation Does that help? *********** Regards, Ron "will A" wrote: > To count if Col A=aa and Col B=bb and Col C=cc. Must meet all situation. > Thanks > will A =SUMPRODUCT(--(A1:A1000="aa"),--(B1:B1000="bb"),--(C1:c1000="cc")) SUMPRODUCT does not work on a complete column, just a defined range, and all ranges must b...

Outlook 2003 drag and drop option has disappeared. Why?
It seems my option to drag and drop messages from my inbox to folders has disappeard. I'm wondering if there is an on/off toggle somewhere I'm missing. Any ideas? What do you mean it has "disappeared?" What are you doing and what happens when you try? What information store are you using? -- Russ Valentine "Vic" <Vic@discussions.microsoft.com> wrote in message news:38487EA7-641D-4A9E-ADE3-B127E57DD8AA@microsoft.com... > It seems my option to drag and drop messages from my inbox to folders has > disappeard. I'm wondering if there is...

Provide a blanket purchase order option for sales.
MANY OF OUR CUSTOMERS ISSUE BLANKET PURCHASE ORDER, THEN ASK US TO SHIP A PORTION OF THE ORDER AT A TIME. IE, A PURCHASE ORDER FOR 10,000 UNITS AND THEY WANT 1000 SHIPPED EACH MONTH. CURRENTLY WE CAN SHIP THE FIRST 1000 AND LATER SHIP A SECOND 1000 BUT THEN WE MUS CANCELL THE CUSTOMERS PURCHASE ORDER AND RE-ENTER THEIR ORDER FOR 8000, THEN WE CAN DO 2 SHIMENTS AGAIN AND THEN MUST ONCE MORE CANCELL THE BLANKET PURCHASE ORDER AND ENTER A NEW ONE FOR 6,000, ETC, ETC. I UNDERSTAND THAT PREVIOUS VERSIONS DID ALLOW REPEATED BACK ORDERS AND PARTIAL SHIPMENTS. PLEASE RE-DESIGN THIS BLANKET PUR...

how to create a multiple conditional formula
I am trying the find a solution for the following multiple formula (example); IF(A1="K"; B1+(B1*C10);B1) AND IF(A1="N";B1+(B1*C11);B1). So actually two expressions in one formula. I can't find a good solution. Is there anybody who can help me? Thank you in advance. Ad Buijs =B1+B1*IF(A1="K",C10,if(A1="N",C11,0)) "Ad Buijs" wrote: > I am trying the find a solution for the following multiple formula (example); > IF(A1="K"; B1+(B1*C10);B1) AND IF(A1="N";B1+(B1*C11);B1). So actually two > expressions ...

How can I change text to proper text in multiple cells.
I need to change names that are all in caps to proper case in 100 cells. If I click each one individually, it works, but I need to be able to perfomr this funcion automatically on all the cells. One other post said to be sure calc is set to automatic and mine is. Any instruction is most appreciated. Thank you. Insert a helper column to the right of the column with the names. Then use a formula like: =proper(a1) and drag down that column Then select that column edit|copy select the original range Edit|paste special|Values And then delete the helper column. bethye99 wrote: > > I...