If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need to create a search based on a specific field
Okay so I am really new at all of this and I will try to explain as best as I
can as I would appreciate the return of information in the same matter (dummy terms). On my form I need to create a text box that allows someone to do a search based on one of my fields in my table. I actually need 2 searches...one for each. I have a memo column and a account number column in my table. I would like to put on my form a selection so they can type in a part of or the entire account number and have it retrieve the information and all the rest of the associated information with it based again on my table data. I would also like the next search to be able to have them enter either a partial name, or even an address and have it return anything in the table that meets that criteria....because there will be information held in the memo section that will need to be searched in case of a name being put in there as well. I hope I make sense...it is hard when I know what I want and can see it in my head but can't figure out how to make it happen. Thanks again. |
#2
|
|||
|
|||
Need to create a search based on a specific field
The best way to approach this is to have a 'Find' screen in front of your
main screen. On this screen you enter search criteria and from this you build the record source for your main screen. I do this in lots of applications and it works very well. However, quite a lot of VBA coding is needed. You would need to build a record source with something like SELECT ... FROM MyTable WHERE AccountNumber LIKE '*123*' AND MyMemoField LIKE '*Find This*' -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Julie" wrote: Okay so I am really new at all of this and I will try to explain as best as I can as I would appreciate the return of information in the same matter (dummy terms). On my form I need to create a text box that allows someone to do a search based on one of my fields in my table. I actually need 2 searches...one for each. I have a memo column and a account number column in my table. I would like to put on my form a selection so they can type in a part of or the entire account number and have it retrieve the information and all the rest of the associated information with it based again on my table data. I would also like the next search to be able to have them enter either a partial name, or even an address and have it return anything in the table that meets that criteria....because there will be information held in the memo section that will need to be searched in case of a name being put in there as well. I hope I make sense...it is hard when I know what I want and can see it in my head but can't figure out how to make it happen. Thanks again. |
#3
|
|||
|
|||
Need to create a search based on a specific field
Well since I don't know code and as I said previously I need it explained in
"dummy language" I don't think that this would be a suitable solution for me but thanks...Any other suggestions? "Dorian" wrote: The best way to approach this is to have a 'Find' screen in front of your main screen. On this screen you enter search criteria and from this you build the record source for your main screen. I do this in lots of applications and it works very well. However, quite a lot of VBA coding is needed. You would need to build a record source with something like SELECT ... FROM MyTable WHERE AccountNumber LIKE '*123*' AND MyMemoField LIKE '*Find This*' -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Julie" wrote: Okay so I am really new at all of this and I will try to explain as best as I can as I would appreciate the return of information in the same matter (dummy terms). On my form I need to create a text box that allows someone to do a search based on one of my fields in my table. I actually need 2 searches...one for each. I have a memo column and a account number column in my table. I would like to put on my form a selection so they can type in a part of or the entire account number and have it retrieve the information and all the rest of the associated information with it based again on my table data. I would also like the next search to be able to have them enter either a partial name, or even an address and have it return anything in the table that meets that criteria....because there will be information held in the memo section that will need to be searched in case of a name being put in there as well. I hope I make sense...it is hard when I know what I want and can see it in my head but can't figure out how to make it happen. Thanks again. |
#4
|
|||
|
|||
Need to create a search based on a specific field
Julie wrote:
Well since I don't know code and as I said previously I need it explained in "dummy language" I don't think that this would be a suitable solution for me but thanks...Any other suggestions? "Dorian" wrote: The best way to approach this is to have a 'Find' screen in front of your main screen. On this screen you enter search criteria and from this you build the record source for your main screen. I do this in lots of applications and it works very well. However, quite a lot of VBA coding is needed. You would need to build a record source with something like SELECT ... FROM MyTable WHERE AccountNumber LIKE '*123*' AND MyMemoField LIKE '*Find This*' -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "Julie" wrote: Okay so I am really new at all of this and I will try to explain as best as I can as I would appreciate the return of information in the same matter (dummy terms). On my form I need to create a text box that allows someone to do a search based on one of my fields in my table. I actually need 2 searches...one for each. I have a memo column and a account number column in my table. I would like to put on my form a selection so they can type in a part of or the entire account number and have it retrieve the information and all the rest of the associated information with it based again on my table data. I would also like the next search to be able to have them enter either a partial name, or even an address and have it return anything in the table that meets that criteria....because there will be information held in the memo section that will need to be searched in case of a name being put in there as well. I hope I make sense...it is hard when I know what I want and can see it in my head but can't figure out how to make it happen. Thanks again. Ok. VBA and everything that goes with it is a bit daunting if you're new. Two suggestions: 1) Use a parameter query. Build a query which returns all the fields you want. Then put "Like", followed by a string in square brackets, in the "Criteria" row of the query builder under the field you want to match. If your entry is: Like ["Enter characters"] .... then Access will prompt you for the value to replace "Enter characters" when the query runs. Use asterisks (*) to match any characters at that point, so you might want them at the beginning and end of what you type into the prompt, or it will only match the whole field. I've not used parameter queries that much but I suspect they may be harder to use with numeric fields (where the number is _stored_ as a number and not a string), in which case you'd need to replace the relevant field in your query with a function which converts the contents to a string, so that it can match the numeric text characters you type at the prompt. Instead of having MyNumericField at the top of a column in the query builder you change it to read: MyNameForIt: Cstr([MyNumericField]) You may also need to use the NZ function to deal with null values: MyNameForIt: Cstr(nz([MyNumericField],"")) .... which passes on a null string ("") where there is nothing in MyNumericField. Sounds daunting, but if you fool around with these things it'll make sense and you'll be much encouraged. Experiment with a plain text field and a parameter query first, and then worry about numeric fields if you have to. You can build a simple form on top of a parameter query, and the prompt will still "fire", and limit what you see in the form. 2) Use "filter by form". See Help about this. You can create a simple form, then click the filter-by-form button and type a matching string (remember to use asterisks where appropriate or you'll only get exact matches) in one or more fields, the click the "apply filter" button. Voila! No programming - it's built into Access. You (and your users?) just need to know how to use it. Try both. You'll use them again and again. Phil, London |
Thread Tools | |
Display Modes | |
|
|