Query any part of the field Results

I have created a form to record any stock borrowed from another department. The item_desc record is a combo box. How can I search the description field in the combo box for a word in any part of the field? eg look for "survac" ('cause you don't know that it is a type of drain & stock list has it as Drain - Survac - complete unit). When I created a macro to find a word, it looked only at previous records for the department, not the combo box. I think it needs some code, but I don't know VB or SQL well enough.
HELP! (I have created a temporary solution by going to a form which runs a query with the parameter Like * & [Enter word you are searching for] & *. But then once you have found the exact description, you have to close the form & find the same description in the combo box - a bit messy!)

[This message has been edited by FionaC (edited 03-20-2000).]

I would to make a prompt on my query for any part of the record. For example if I type 123 into the prompt I will receive the records for 123AQEF, GHY1230, VGD123H etc. In another database software I use I have a "Contains" feature of the query that enables me to do this. Is there any way to do it in Access?? I've tried searching and I cannot find it.

Thanks in advance for any help on this matter.

(The field is Alphanumeric and I am running Office 2010)

I have a table that I need to identify the records in that have specific text in one of the fields, the field also contains other data. i.e. the field (accessdescription) can contain any combination of the following text (Bridge, Report, Email). and I want to list only the records that have email in this field, noting that the field usually contains at least two of the possible entries.

Any pointers in the right direction would be greatly appreciated.



Hi guys, bit of a tricky one here and I can't seem to find satisfactory answers anywhere so hoping you can help.

I have a form which contains 7 parameters. 4 combo boxes and 3 text fields. The user selects entries on any of the combo boxes or types text in any of the text fields. They do not have to fill in all 7, it could be any combination of the 7.

Under this form is a sub form which will display results according to which fields are set above but how do I code my query to accept any of the above?

My main form is called Orders_Queried and my sub form is called Order_Query_Results

The 7 fields are:


The first part of my query is:

	SELECT Orders.OrderNumber, Orders.[Customer Name], Orders.[Order Date], Order_Details.[Item Number], Order_Details.[Item
Type], Order_Details.Description, Order_Details.Type_Colour_Size, Order_Details.Quantity, Order_Details.Price,
Order_Details.Denomination, Order_Details.Department, Orders.Complete
FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber

Now obviously my query needs to be set to show results where fields are equal to the combo box entries, but for the txt fields then the results need to be like. But how do I code this so it will display the results no matter which combination of parameters are set.

I have a question about if and how you would separate out certain parts of fields. I don't think I can explain this well so I'll say what I am trying to do.

I have a huge list of machines that have sold via auction at various amounts. I'm trying to compile an average sale price so as we go forward with more auctions so we can know what fair prices are. The problem is that the machine descriptions are vary slightly. A certain type of machine, for example a 2653A, will have different descriptions based on the features of that machine.

Is it possible to create some sort of query that will only look at the numeric portion of the description, such as that 2653A, so that it will find all machines with just that value and disregard the other junk in the descripton so that I can use all of the machines in my list to create a better average. Is it possible to sort information based on just a part of the field? Thank you for any help in advance.

quick question: in a criteria i need to set a code than even the slightest amount of letters return a search. for example in the "Description" column:

"Dell Inspirion D5000"

Lets say a user writes 'pirion' in the criteria. All the records with those letters will return a search. I tried 'like "*" but doesn't work.

Hi guys,

Hate to ask a question twice in the same day but...

I'd like to know if its possible to run a query on a table with a "/" as part of a field name. I'd like to place the query in the properties/rowsource portion of a combo box. I've tried brackets and a bunch of other stuff but I haven't had any luck. The field name is set in stone.


Hi All,

Normally I would do this in VBA, but I'm intrigued as to whether I can do it in a query.

I'm building an advanced search for my DB and the user can select many option:

1) What field to search in
2) Whether to search the entire field, or Any part of the field
3) Whether to search all records, or just 'Live' Records.

To do this I'm trying to use the iif statement. But can't get the LIKE operator to work inside the iif.


IIf([srchCriteria1]=1,Like '*' & [name] & '*',[name])

This works fine if srchCriteria1 isn't '1' and the full name is found. But can't get it to search any part of field.

Any help greatly appreciated.

Given a table field that is a hyperlink type.
I need an Update Query to set all records of that table so that the Displayed Value part of the hyperlink field (not the Address part) is set to a particular value.
Any ideas how?

Hi All,

I am looking for case sensitive query for MS Access. For whole string, I can use StrComp(). But, I am looking for "Startswith" and "Any part of the field means Contains". It must be case sensitive. I know in SQL but I don't any function for Access. Please share your knowledge if any buddy knows about it.

Thanks & Regards,
Manoj Srivastava

This might be a tricky one, or not.

I have a table with an autonumber Primary Key. Then I have a form that simply shows the records in this table. One of the field in this table is a text field which I like to have automatically generated. The field value will start with an "S" followed by "0000" and then the value of the autonumber primary key field.
So if the primary key for the record is 1, then the text field would be "S00001".

I could have done this if I used the form in Data Entry mode. But I don't. I enter data into this table using an unbound form, and using the following:

Set rst = CurrentDb.OpenRecordset("TableName")

With rst

!FieldName = Me![ControlName]
'and so on

End With
Set rst = Nothing

Using this method, update query, or any other method other than using the bound form with DataEnty mode; how can I have the value of this text field generated as explained above?

I know this is an easy one for you geniuses out there.


I have various users inputting data into excel sheets which are then imported into a database. I need to prevent duplicates and for this I use a UQFld:[DateFld]&[TimeFld]&[Booking]&[OtherRef]etc
at the point of importing if there are any of the fields missing the data row is not imported. Once all fields are complete the append query will upload the data as long as the UQfld is not already there. This works fine except where a users PC is not in the same regional format as the Date portion is local etc i.e 1/Aug/2011 will be 01/08/2112 on some but 8/1/2012 or 2012/08/01 etc
this makes my UQFld not unique, and can allow duplicate uploads
I wish to format the date field part to a standard just for this purpose. I am looking for advise as to the best way
ie Format([DateFld],"dd/mm/yyyy") but would a better method be?


I am using MS Query to return data into Excel. I know Access but am quite new to MS Query and keep finding vast differences between the two!! I've looked on the help but it doesn't seem to be of any!

I have two date fields which are formatted as dd/mm/yyyy hh:mm - and I need to take one from the other to pick out negatives as errors, but I need to convert the date/time fields to date only, because one of the fields has all the times unentered, (ie, they're all set to 00:00:00). For example, where:

Date of Onset Date of Referral
09/06/2010 10:00 23/06/2010 00:00 OK because Onset < Referral
28/07/2010 07:30 26/07/2010 00:00 Not OK because Onset > Referral
26/06/2010 10:00 26/06/2010 00:00 This should be OK because they are effectively the same date but because there's a time in the Onset Date field it causes an error. This is why I need to convert it.

In Excel we got around this by using the INT function.


	=IF(OR(H4="",G4=""),"",IF(INT(H4)-INT(G4) External Query and refining my query in MS Query. 

I hope this all makes sense and would be extremely grateful for any help!

I'm using Excel 2003.

Thank you!


Access 2003 syntax

Can anyone help with the following problem concerning Operator syntax please. Access Help and Alison Balti's Mastering Access 2003 do not enlighten.

I have a field in a query for MemberType in a membership database.
Member Types are designated in a single field by single characters separated by a space.
A member may have more than one member type (e.g. Gold member G and Founder member F).
I can use the Access Like Operator to set criteria e.g:
Like [Enter Member Type] & "*"
to open a window, then enter the single character, e.g. G, to display the selection of Gold members, but this only displays the members where G is the first character in the field. If the character is in the field is not the first character the member is not displayed.

I believe I need to deploy the In Operator to display the Member Type wnen the relevant character isin any part of the field. I have tried all sorts of syntax e.g. In [Enter Member Type] & "*", but get messages such as In operator needs parentheses. But if I add parentheses - (In) - I get a message Too many parentheses.
And many other variations on the same theme.

Can anybody put me right please?

Richard Tetley

Hello All

Has anyone ever encoutered a problem where when running a query to return a dataset which includes a memo field, the query only returns part of the info contained in the memo field?

Do queries limit the return of memo field data to 255 characters? I have run other queries on other memo fields in the past, and do not recall ever having this problem.

FYI, there is no indexing, criteria or joins/relationships on the memo field - whilst returning other fields, I purley wanna return whatever is contained in the field.

Any ideas?



I need help with following: in query I made I have one short date-type field called "CurrentDate". On my PC, default short date format is like this "MM/dd/yyyy".

In my query I have one more field which looks like this "ExtractDate: Left([CurrentDate],2)". This is how it looks like:

Task of this field is to "extract" first wo values of field "CurrentDate". So for example if value of field "CurrentDate" is "07.02.2011" then field "ExtractDate" will show value "07" and that works very good but recently I had to change short format date on my PC to this one "dd/MM/yyyy" so my field "ExtractDate" is now showing value "02".

Im not sure how to write function in query which will "extract" month from this type of date format "dd/MM/yyyy". How to single out value "02" from this type of date "07.02.2011"??

Any help is appreciated and many thanks in advance for prompt replys!


Hi, I use the software "Parish Soft" and their reports and queries are written but I am able to edit them. The address field contains both house number and street name and I want to sort by the street name only. I don't want to change the structure of the table but Is there a function that I can use to sort by the fifth value in the address column using SQL? I have used other programming languages but I'm not that familar with access and can't find the syntex. Thanks for any help. ConnieL1954

I need to query a text field where the last 1 or 6 positions of the field always consists of a number. I want to be able to query that number. Now normally I would expect a seperate field in the table for that number. Alas, the manufacturer of the program has decided to store it in this way:

"some odd text 123456"

Now I am building a custum report not available in the program. Any ideas as to how I could query the number?

Ok, I have been trying to figure this out all day and can't seem to place my finger on what I'm doin wrong. I am using Access 2000 (unfortunately the only thing available on my work computer) I have a simple database that has a Table containing fields such as Last Name, First Name, etc. I have made a Form called FormSearch that I placed some text boxed on called txtFirst and txtLast. I made a Query and added the appropriate fields that I want to query. in the criteria portion I have placed this: Like Like "*" & [Forms]![FormSearch]![txtLast] & "*".

When I run the query a "Enter Parameter Value" box appears instead of using the data inputed in the text box on the FormSearch form. When i do use the "Enter Parameter Value" box to search a last name or any part of the last name it does however work. If left blank it shows all records as expected.

Once this didn't I would try using the "Enter Parameter Value" box rather than using text boxes. To do this though I created two separate queries, called QueryLast and QueryFirst,to run the search. I figured I would give the user a choice at which one to run by putting two different buttons on the form, called cmdLast and cmdFirst. When trying to run the already working queries from a button, this was the result:

In the command button wizard I selected Miscellaneous, then run query. I selected the appropriate query. When I try to run the query from the button I get this message:
"The expression On Click you entered as the event property produced the following error: A problem occured while Microsoft Office Access was communicating with the OLE or ActiveX Control

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Proceedure]
*There may have been an error evaluating the function, event, or macro."

I would much rather be able to run a single search query from multiple text boxes on my form as I was trying to at the beginning. This allows the user the easiest searching method, although If I cold get the multiple button/ multiple query method I was trying to work I would also resort to that as well. I have seen a tutorial online showing the same criteria in the query that I used, in each search field, and changing the names of the respective text box in each one and it always worked just fine. I have followed everything to the "T" and I can't seem to figure out what is wrong.

Please Help!

Thank you!

I want to show only a part of a data that is stored in a field that I am quering.

I can do it in Excel with the MID function ( located under the Text section of the Formula in Excel)

But I did try it out like I do it in Excel, but doesn't work...

Any Help


Not finding an answer? Try a Google search.