Hello,
I am not sure if this is possible, but I am running an update query to add text in a field if a certain criteria is met. However, if the field I am adding to already contains what I am trying to add, i don't want it to add it again. Everything works fine except the IIF statement in my update field. Here is what I have:
If the field contains the text V3, I want it to be left alone and continue to show what is already in the field, if not, i want it to add V3 to the end.
I've got a query that does exactly what this (http://www.access-programmers.co.uk/forums/showthread.php?t=89564&highlight=null+records) chap got his to do.
However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:
[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null
However when I try the logical extension:
[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null
this doesn't work, and nor does:
[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null
WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! :eek: thanks and sorry for being so dumb!
Hi, I'm making a query which is used to generate a report. Anyways, I've ran into a problem. The query is based on some controls in a form, but what I would like to do is that if a control was null then a field in the query should be assumed to be a wildcard for the criteria, otherwise the data in the control should be used in the criteria of the query. Does anyone have any ideas if this could be done?
I have a sales invoice table that contains parts that start with *, for example *SPR362.
I need to select only those parts that start with the *, as this is the wildcard in queries how do i select those products that start with it without it being recognised as the wildcard and selecting all records?
In SQL in MS Access a '?' '*' '%' are all used as a wildcards.
I need to be able to retrieve all messsages which contain a '?' within the message. i.e. If a message contains the following characters : why?
I want to retrieve the message as a '?' is part of the message.
When I write the code :
SELECT User.User, Message.Message FROM Message INNER JOIN [User] ON Message.[Message ID]=User.[Message ID] WHERE Message Like '*?*';
Access takes the '?' as a wildcard and retrieves all the messages in the database. Is it possible to get round this and use '?' as a specific character.. not a wildcard?
I have built a search form to feed information to a query. The form uses combo boxes tied to table values, and all have wild cards built into them so if the user leaves the combo box they get all the records. I also have to text boxes representing start date and end date. I would like to allow the user to leave these blank and get all there as well. I have been looking through my one Access book, as well as searched all over the internet, but I cant seem to find the way to do this. My filter criteria for the text based combo boxes are like this:
Code: Like "*" & [Forms]![ReportDesignF]![Company] & "*"
My filter criteria for the Start and End Dates looks like this:
Code: Between [Forms]![ReportDesignF]![StartDate] And [Forms]![ReportDesignF]![EndDate]+#11:59:59 PM#
In this case of the user leaves the date values blank, the query returns nothing. I would like to return all dates if that is the case. I am assuming it is my lack of knowledge of wild cards and how they work with date values.
I'm using an Excel spreadsheet that is importing external data from an access database in which I've got a field where the name of a person checking materials out is entered. it is currently set up, and I cannot change it, as a free form field. So folks enter information in a variety of ways.
For example, Larry Martin might be entered as "Larry Martin" or "Martin, Larry" or "larry.martin@somewhere.com". I'm trying to run a query that would look in that field for any entry with the string I enter, such as "Martin."
I've tried setting the criteria like this:
Like "*" & [Which Last Name] & "*"
However, when I try and run the query I get a message telling me the system is expecting two parameters.
Does anyone have any idea what I'm doing wrong? I've been banging my head against this for awhile now and am thoroughly stumped! All assistance, as usual, is greatly appreciated!
How do I design a query to return a result in a wildcard format? So that I could enter a part of a name, and it returns all the names that include that part of name?
I am new to Access 2007. I have a list of companies of are constantly updated,deleted, and created which are listed on a table. I have a form with an unbound text box with a sumbit button that opens a new form with the information of the name of the company in the search box. Unfornately, if the name of the company is not perfect, the record is not found and the form opens a new record. I want search box tyo autill, or "wildcard" when I begin to input a name of a company. I tried a combo box but my boss does not like it. He wants a wildcard search.
i'm tryin to create one that if i search for like let say "brisbane" it would show the results of the complete spelling, but let say if i was to just type "b" or "bris" it would show u a list of "B" towns or matching words of "bris".
I have a field in a table that has the following data in it:
W-01-2005 W-02-2005 W-03-2005 W-04-2005 etc..
I created a query against this table and put a pop-up on this field so they can enter the search criteria needed, or leave blank for all.
I'd like to modify this pop-up code to ignore the W- and -2005. When the pop-up appears and asks them what week they want, I'd like for them just to enter 01 or 02 or 03 etc... not W-01, W-02 etc..
I am running a query that links to a "wildcard" form so that the user can basically run a query filtered on any field they want.
For some reason when I try to use wildcards along with Criteria in my query the query will not return any results. I know the link to the textbox is right because if I take out the wildcard and put an exact word from the table I get a result.
I have tried using many different combinations inclusive of the following:
Like [Forms]![Refurb-WildcardReport]![RefurbWildcard-Name] Like "[Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]" "[Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]" [Forms]![Refurb-WildcardReport]![RefurbWildcard-Name]
Any help would be appreciated, as always thank you ahead of time.
I have several queries looking up products from a table. I use a simple form to access the query and in the criteria of the field I use [Forms]![EnterCroft]![ProductName] to filter by name. (EnterCroft is the name of the form).
It works fine but doesn't like the asterisk (*) for all products.
I also want to use wildcard before and after key words in the product names, eg *cup* for anything with CUP in it's name.
ok in access 2003 i have tried everything to get the Like "*oak*" to work but to all fail it replaces "Like" with "ALike" i have tried to find some info on this but have not it seams like something easy i wanted to do.
SELECT MainData.ModelName FROM MainData WHERE (((MainData.ModelName) ALike "*oak*"));
some reason it wont find all data with "oak" in it just returns empty
this is like query 101 but for some reason it has stumped me, along with one other problem but i am still researching that one.
This is making me mad now :mad: i want a query to search for what a user inputs into the query. but for example if i seach in a field wheres there is one or more words in it. eg. I want to search my table for a address (60 donnington road etc) so in the user input box i type "donnington" but i get no results even though it should find "60 Donnington Road"
code i used. [Please enter address] now where does the wild card go?!?!
Hey everyone, I've been busy creating a switchboard based on database, and I came across this issue. When I put a search through records command button, it seems that I can't use the wildcard characters, such as *,%,_ in order to find multiple records matching my criteria.
Am I supposed to "enable" them from somewhere or am I doing something wrong.
I get a response saying it cannot find the file and it shows the * in the path it cannot find. How can I make it find the right file?There are more files in the folder and the (ZZ131008) defines that course, I'd like to reuse the code for the other courses too.
I have a combobox with 2 columns where I manually set the following criteria:
"E";"English" "F";"French" "EF";"Bilingual"
In my employee table under the Languages field I have E,F or EF
I wanted to display in a sub-form a list of employees who speak a specific language but I can't use a wildcard in the combo-box properties. Basically when someone is looking for an English speaking employee they should get both E and EF employees not only E.
I tried doing "E*";"English" but it doesn't display anything.
I have a query that prompts with a parameter box for the field Contributor_ID. Contributor_ID is a numeric field & Key and I use it to allow the user to print a report based on which Contributor_ID value the user enters. This works fine. However, I would like the user to have the option to enter * and then have the report include ALL the contributor IDs. I have done this before on a text field without a problem, however is this not possible on a numeric field as it is not working? I'm trying not to have another version for the ALL option.
If this is not possible, are there any alternatives?
Just been searching around, found a few posts of uppercase questions, none that quite fit with mine, anyway,
Basically, the data I am working on produces names as such "JoeBloggs" "FredPerry"
Anyway, Im going to use a query to seperate these out into first name and last name, but, I can't seem to find a way to specify a capital letter as a wild card. So far:
CapPos: InStr(2,[calllogs]![CalledNumber],"A")
Then I am going to use the mid function to seperate the two, depending on the position of the first capital letter.
But I just need to replace "A" with something that will find any capital letter.
I have a SQL that enables the user to search the form from different combo boxes to narrow down the data. I can get this to work but it only finds the fields that exactly match their input data. I want them to be able to type something in the combo box and then it pull up anything with that value in that field, whether it is 1839B or B1839 it should find both if I type in 1839
Here is my sql statements
SELECT DISTINCT [WeldingSpecification].[Spec], [WeldingSpecification].[Steel Type], [WeldingSpecification].[Group11], [WeldingSpecification].[Group143], [WeldingSpecification].[Substitute1] FROM WeldingSpecification WHERE ((([spec] & "") Like IIf([Forms]![frmSearchCriteriaMain]![spec] Is Null,"*", [Forms]![frmSearchCriteriaMain]![Spec] & "*")) And (([SteelType] & "") Like IIf([Forms]![frmSearchCriteriaMain]![SteelType] Is Null,"*",[Forms]![frmSearchCriteriaMain]![SteelType] & "*")) And (([Group11] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Group11] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Group11] & "*")) And (([Group143] & "") Like IIf([Forms]![frmSearchCriteriaMain]![Group143] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Group143] & "*")) And (([Substitute1] & "")([spec] Like "*" & Forms![frmSearchCriteriaMain]!Spec & "*" & "") Like IIf([Forms]![frmSearchCriteriaMain]![Substitute1] Is Null,"*",[Forms]![frmSearchCriteriaMain]![Substitute1] & "*"))
Any help is appreciated, please ask if you do not understand fully
I am trying to use a Switch expression as a column heading in a crosstab query to generate statistics for how much work is done per client (Requester). Each requester is referred to by acronym, so it may look like "DOD/OUSD/FTT/ATA/B", but I want that column to include everything that starts with DOD, instead of listing each instance separately. This is my expression so far:
When I run the query, the column "ATAP" shows up perfectly with all the correct info, but it's as if I never entered "DOD*". Nothing appears, and since I didn't add anything in the Switch function for what to do with other requesters, the second column shows up as <>.
Do wildcard *'s not work in Switch functions? I don't get any errors, but this is definitely not the information I'm looking for.
Is there any other way to include all Requesters that start with DOD in one column?
This is my SQl if that's helpful: TRANSFORM Sum([Q and D Database].[Word Count]) AS [SumOfWord Count] SELECT [Q and D Database].[Source/Target] FROM [Q and D Database] WHERE ((([Q and D Database].[Out Date]) Between #1/1/2006# And #12/31/2006#)) GROUP BY [Q and D Database].[Source/Target] ORDER BY [Q and D Database].[Source/Target], Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP") PIVOT Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP");
