LIKE and criteria


Is it possible to enter criteria in a LIKE function

Something like:
Where customer LIKE "*[enter criteria]*"

I know this doesn't work...

Any way to get this working

Thx in advance.
Posted On: Monday 22nd of October 2012 05:57:01 AM Total Views:  210
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Passing multiple string criteria into a query

Im using Access 2003. Ive got a bit of code which creates some SQL as follows: Code: sSQL = "TRANSFORM Sum(tbl_Local_Data.Sales) AS Sales " & _ "SELECT tbl_Local_Data.PostingDate " & _ "FROM tbl_Local_Data " & _ "WHERE (((tbl_Local_Data.Store)='" & GetStore() & "') " & _ "AND ((tbl_Local_Data.Product) " & sSearchProducts & ")) " & _ "GROUP BY tbl_Local_Data.PostingDate " & _ "ORDER BY tbl_Local_Data.Product " & _ "PIVOT tbl_Local_Data.Product;" Rather than deal with the SQL in VBA, Id prefer to have a function for sSearchProducts and feed it into the query criteria. The only problem is that the contents of sSearchProducts is: In(Cans, Drinks, Sweets) If I put sSearchPruducts() into the query criteria, no data is returned. If I type In(Cans, Drinks, Sweets) into the criteria the correct results are returned. Ive checked my code and sSearchProducts contains the correct string value. The function is also being run when the query runs. I read online somewhere that you cant use the In function how Ive used it. Is this true Is there another way of doing what Im trying to do
VIEWS ON THIS POST

121

Posted on:

Wednesday 17th October 2012
View Replies!

Lookup expression (or something like it)

I need some help with Access 2007 expressions. I have one table of company cost allocations, and another table that needs to use these calculations to distribute costs. Example - the split could be Company A - 25%, Company B - 25%, Company C - 50%. There are many different combinations of splits to choose from. Total cost (to date) could be $125,000....and I need to take the above percentages and calculate the cost distribution across the participating companies. Is this possible In my case - I will have many different split scenarios which need to be "looked up", percentages passed, and calculations done. Please help.
VIEWS ON THIS POST

91

Posted on:

Wednesday 17th October 2012
View Replies!

Query criteria to show part of record

. I have a search form with a txtbox where I type a LastName. The query displays in a listbox all records with that LastName. I then click on the record a open another form. In this form I have a lot of data. One is the File Numer Its format is 000A, 000b, 000c etc. Each form shows only the file number of the record. Is there a way I can add a textbox or listbox on the same form next to the File Number field, to show the last character (A, B, C) associated to that file number I know there is a Mid function but cannot make it work. Thank you
VIEWS ON THIS POST

94

Posted on:

Wednesday 17th October 2012
View Replies!

Update Query criteria

Hi I am trying to update a field A in Table 1 to 20 if field B in Table 1 begins with the letter C. I have tried many things but it keeps saying 0 fields will be updated. Can someone please provide the right criteria syntax for this
VIEWS ON THIS POST

69

Posted on:

Wednesday 17th October 2012
View Replies!

SSN query criteria

, In my table all SSN data is showing in the format 000/00/0000CC;;_ (13 characters- example 000-00-0000AA). I would like to create a query which returns only part of the SSN field, basically only the first 11 digits. I have created a text box on a search form along with a cmdbutton that opens the form. The query has this criteria: forms!mysearchform!txtSSN Can anyone help me Thank you.
VIEWS ON THIS POST

93

Posted on:

Wednesday 17th October 2012
View Replies!

Part-matching in query criteria

Hi Another problem I'm having.. I have a form with a number of text boxes that are linked to a query. As an example, I type in "Jones" (without the "") in the surname text box and via the query, it will search through a list of names to match the word Jones. To narrow down my search further, I have other text boxes too so I can type in more info as required. It all works very nicely. However, I have found some problems. When I search for a name of "Farncray Ltd" it fails to match the record. I assume because there is a space in the phrase I would like someone to help correct my code or problem so that it is able to part-match the phrase. So if i type "Farncray" - even if this is only one of the words, it will find the record. Likewise, if I'm looking for Dodd Homes Ltd.... and i type in Dodd - it will again find the record. I hope that is clear. The code (if you can call it that!) that I am using in the query is: Like IIf(NZ([Forms]![Frm_Lettersearch]![Text2],"")="","*",[Forms]![Frm_Lettersearch]![Text2]) [Text2] in this case refers to the surname text box. I found this 'code' somewhere on the net, and it works fine.. but i don't really understand it enough to be able to play around with it myself and fix the problem.
VIEWS ON THIS POST

104

Posted on:

Wednesday 17th October 2012
View Replies!

How to set current month as criteria?

Hi! What expession should I use to set the current month as criteria in a simple query IE in the criteria field of the query design view... Thank you for your help
VIEWS ON THIS POST

60

Posted on:

Thursday 18th October 2012
View Replies!

how can I add a criteria field in a form ASAP

Good day . I'm designing an access system in which I have many reports (Primary Key is reportNumber) I need a form with a field in which I can speicify a range of reportNumber to print them. example I need to print reports from 1000 to 2000 please note than I'm a beginner in this so I need detaild steps. any help will be very much appreciated!
VIEWS ON THIS POST

155

Posted on:

Thursday 18th October 2012
View Replies!

Need Query to Sum where both criteria from same field are met

Hi I want to run a query or a report that specifies AND as the criteria in one field. I want it to return the count of clients who have purchased pens AND apples. I have two tables: Names = nameid, name Item - itemid, product, nameid Relevant data is: Bob, pens Bob, apples Karen, pens Karen, apples John, grapes John, pens I have a query, showing name plus product. I have a user prompt [Enter product name] which allows the user to enter product name, and see only those items. EG [pens] will return 3. This works. However, I want a query that will give me how many people purchased both pens and apples where the user enters the product name. So, run query, and at first prompt [Enter product 1] and then a second prompt [Enter product 2]. If I enter prompt 1 = pens and prompt 2 = apples, I want to see the number 2 (representing both Bob and Karen since they both bought pens and apples). The wrong code I have is SELECT Names.Name, Products.Product, Sum(Names.NameID) AS Total FROM [Names] INNER JOIN Products ON Names.NameID = Products.NameID GROUP BY Names.Name, Products.Product HAVING (((Products.Product)=[Enter product 1] OR (Products.Product)=[Enter product 2]")); I can make the criteria [Enter product 1] Or [Enter product 2] work, but that is not what I want. I want [Enter product 1] AND [Enter product 2] but nothing is returned. Help anyone
VIEWS ON THIS POST

63

Posted on:

Monday 22nd October 2012
View Replies!

Something seems to be wrong with my OR criteria

Post the query SQL statement for analysis.
VIEWS ON THIS POST

104

Posted on:

Monday 22nd October 2012
View Replies!

Query not pulling criteria from the form!

Hiya, Looking for some help as I am really stuck. I have a form where the user enters data and then when they hit a button an update query is performed based on the record ID. However for some reason the query is not pulling the record id through when the button is pressed. The query runs fine when the button is clicked as Ive tested it and if I have the form open and run the query manually it pulls through the recordID and works perfectly so I don't understand why it's not working. I have noticed however if I run the query when the form is closed it doesn't prompt me for the value\t It's probably something glaringly obvious but I've done this so many times and never had an issue.
VIEWS ON THIS POST

134

Posted on:

Monday 22nd October 2012
View Replies!

Form not passing ALL criteria to query. . .

Again, thought I was so smart - decided to do a Criteria form with check boxes (named Monday, Tuesday, Wednesday). Then I set up a query (that contains checkbox fields) with parameters of [Forms]![Criteria]![Monday] to pick up the value checked on the form. The other fields in the query are just like above: [Forms]![Criteria]![Tuesday] --- [Forms]![Criteria]![Wednesday] The form has a button to run the query. Click the check box, then the button, viola - query(report) runs with correct parameters. The problem lies when I try to do the other checkboxes on the form. Only Monday will pass to the query (because it's the first one Dunno). I thought perhaps the other checkboxes (Tuesday, Wed, etc) don't work because they are 'empty' so I set the default on all the form checkboxes to 'no.' Still doesn't work - only the first checkbox seems to pass to query. Any ideas Hopefully it's something simple!
VIEWS ON THIS POST

167

Posted on:

Monday 22nd October 2012
View Replies!

How to display all data in a range with non-numerical criteria (2)

Here another similar question, The database will show all car's particulars as long its registered date matches the year inside the combo box of the form. For example, a car with registered date of 1999 will be displayed once the user choose 1999 in the combox box. The problem now is, what if the combo box got non-numerical values like "
VIEWS ON THIS POST

73

Posted on:

Monday 22nd October 2012
View Replies!

How to create a search form with multiple criteria?

Dear members, this is my first post, I need some help with an access project. GOAL: develop an access DB with information which is easily searchable. CONTENT: people with criteria Name, hair color, availability, sex, skin color, travel possibilities (zipcode) ex. John, grey, 24h a day, male, black, 1230/1234/5432 NEEDED: form in which a user can check the criteria for which he/she is looking in a person RESULT: list with people who match the selected criteria EXAMPLE: I am looking for all male persons who are available 24h a day in the zipcodes 1230 and 5432. This search query will result in John. How can this be buikd in Access, do you have examples I can download or give me some ideas. Is this a complex project
VIEWS ON THIS POST

97

Posted on:

Monday 22nd October 2012
View Replies!

Kinda like recent post Macro Updates table

ok i have this query. Code: UPDATE [Claims Header] SET [Claims Header].status = 'I' WHERE ((([Claims Header].Claim_ID)=[claim id])); WHERE ((([Claims Header].Claim_ID)=[claim id])); i want to have a button that runs this query but how do i pass over the claimid from my form also when it's done running the query i want it to run a report. so i was trying to make a macro, but how do i tell the macro to find the claim id from the form
VIEWS ON THIS POST

77

Posted on:

Monday 22nd October 2012
View Replies!

Mutiple criteria in DLookup

I'm back yet again with a new and intriguing set of problems. I am building a fancy report and Come to find out you cannot use SQL to return one simple number in a text box because even though you know you will write the statement to return only one answer everytime, access doesnt understand that so we can do a domain aggregate thing with DLookup. OK fine I have to do alot of them but still OK, here is my issue I can get DLookup to give me what I want if i type into each control source the criteria that I want to return. I want to lookup a number from a recordset but there are two things that make the number unique date and strain. strain is easy to cough up in the right place because i will hard code that into the control source dlookup, the part that is killing me is the date I can get a dlookup to give me accurate results based on multiple criteria, but how can i get that to include the date for which I am running this report. I do enter the date parameter each time i open the report. Here's what i got so far =DLookUp("csQuantity","SpawnScheduleCulSpawn","css train='AC-PA'" & " AND week = #4-29-07#") That returns the answer i want, but i have 50 of these text boxes in one report so i cant go through and write the date in each one. how can i make the part following " AND week =" dynamic so that it runs the date that i entered as the parameter for the report
VIEWS ON THIS POST

33

Posted on:

Monday 22nd October 2012
View Replies!