I am trying to replace a # character and all characters after in a text field.
U set up a Select query and used the following expression:
Expr1: Replace([Part Number],"[#]*","")
It doesn't change any of the records. Is this possible to do in Access 2007?
I am exporting an Access table as an xml file and need to be able to identify and replace '&', '(' and ')' as they are not accepted in xml.
Does anyone know how a query can be built which will find these characters in fields and update to ' and' or just remove?
Thanks
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.
How can I do this?
Thank you very much.
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.
Any help?
Thanx in advance
Howdy,
I am trying to clean up one field of a large database. Currently this field has many records that are listed as "XXXX, Inc.". I am trying to find a way to change all of those to "XXXX Inc" in the entire database.
I saw raskews code snippet on changing multiple characters in a string, but I have to admit I have only use VBA in Excel before and having a hard time with the basics.
Does anyone have some code or ideas on how to do this or something similar?
:confused:
Thanks,
Will
I have a data base,one of the field contain Data like "ZZZ-DEFS#UUH1234567".
There should always be 19 characters in this field including #.I want to design a query which can sort out entries less than and more than 19 characters, so that wrong entries can be corrected .
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.
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?
View 1 Replies View RelatedIs it possible to query a field for special characters in a string? I want to find any records that have characters other than A-Z, 0-9
View 9 Replies View RelatedI have a query that is linked to a data table. The values entered in the data table are fine and display as expected. When I run the query, the results of one of the columns show Chinese like characters instead of the actual value. But, when I filter that column by choosing 'does not contain...' the actual cell contents display. I have 2 other identical queries reading from the same table and they do not display funky.
View 3 Replies View Relatedmemo fields chopping the content when appending to another table memo field.I have a query that takes in a couple of tables and then appends the content to one larger table.In each of the smaller tables there is a memo filed that when I check it has the entire type content in above 255 without a problem.
When the query runs and appends it to the larger table the content in the memo filed is chopped down.Both tables fields are set to memo I have double checked this.I have seen some comments about unique values in the query properties but these are set to NO as advised but it still does this.
I have a current file with GBP 12.00. I wish to remove the GBP in a make table query when ran to print off.
View 2 Replies View RelatedI am having a query, where a few date records are null because of no entry or record, what a want that the field with no date entry to be come up with zero "0" .
View 1 Replies View RelatedI need to create a query that combines two columns (lets say Column A and Column B for example) however the problem is that whatever non-null values that are in Column B must replace any value in Column A. If Column B has a value that is null then Column A's value is shown.
I have an example of what I'm working with (access file) and what result I want (excel file).
I need to import an excel sheet containing updated tickets to my access ticket database. They have unique IDs in the form of a "Ticket ID" field.
I could just use an append query to add the new tickets to my database, but there are some changes on the excel side to tickets that already exist in access as well. What would be the easiest way to facilitate this import and overwrite all tickets with matching Ticket IDs to the new ones contained in the excel file?
I already have the excel file imported into it's own table, so it's a matter of updating my main table off of this temporary table containing the excel tickets. The field names are the same across both tables, it's just a matter of updating the info contained in them.
This will need to be repeated for multiple different excel files, so the easier the better!
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.
Hi
I am looking for ways to sort date columns.
Something like find all in one particular month, or year.
I know i have done this before to sort data into monthly queries,
but now i have forgotten
>> something like ="#*/7/*#" to sort all date on the 7th month.
By the way I cannot seem to get access to format dates in the
dd/mm/yy format, does anyone know if there is an option for this.
cheers
hi. i are replacing queries in my tables to get the database ready to convert to SQLServer.
Am having a problem replacing a query that is brings in a table for a drop down and inserting the selection in the record.
during creation, the record shows a date, id, task, hours.
the datatable needs date, id, task_code, task_name, task_job, hours.
On "task" the drop-down allows for selection of several choices that are 3 columns wide. i can get the columns to show but only the first column currently goes in my new table. the table's columns that the selection comes from is "task_code,task_name, task_job". All columns are needed as different areas use the same task code but use different jobs or names.
I was told to use a insert into statement, however am having problems with it.
have as follows:
SQLStatement = "SELECT [Task_code], [Job_name], [Task_name] FROM tablea; "
rec.Open SQLStatement, , adOpenDynamic
job = rec(1)
task = rec(2)
rec.Close
SQLStatement = " Insert into [tableb]([Job_name],[Task_name]) " & _
" values (" & job & ", " & task & " ) "
rec2.Open SQLStatement, , adOpenDynamic
db.Execute (SQLStatement)
any point in the right direction would be greatly appreciated.
thanks,
slh
In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.
The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.
I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".
I tried using the criteria:
Code:
Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"
Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.
Code:
IIf(IsNull([Forms]![SearchJobs]![SearchAddressLine1]),Null,[Forms]![SearchJobs]![SearchAddressLine1] & "*")
The full sql of my query is:
Code:
SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsJobStatus, Jobs.JobsEnquiryDate, Jobs.JobsTakenBy, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, ClientDetails.ClientDetailsAddressLine1, ClientDetails.ClientDetailsAddressLine2, ClientDetails.ClientDetailsAddressLine3, ClientDetails.ClientClientPostCode, Jobs.JobsClientJobNumber, Jobs.JobsAlternativeClientJobNumber, Jobs.JobsClientContact,
[Code] ....
I'm building a query using wildcard "like". See the attached file. My question is that why doesn't the query "result" return "aaaaa" for "aaa" is contained within "aaaaa"?I'm basically limiting my records to those found in tbl_site based on a wildcard "like".
View 12 Replies View RelatedI am trying to get a wildcard search to work with a form.
I have a query, in which the criteria is:
Like "*" & [Enter a word] & "*"
That works fine. I enter a word, and I get the few records in which the word appears.But if I try to replace [Enter a word] with a word entered on a control on a form, it doesn't work - I get all the records. This is my code:
Like "*" & [Forms]![Myform]![Mycontrol]&"*"
What am I doing wrong?
The following is a simplified version of a puzzle I'm faced with, but if I can solve the simplified version I'll be able to solve the real thing.
Take the following column:
MyField
1-2A-B
YZ-A6
53-75-XC
How within a query can I replace the hyphens with spaces but only where they appear between two numbers? So this is the result I'm after:
MyField
1 2A-B
YZ-A6
53 75-XC
(note that the letters and numbers could be any values in any positions.)
Can I use the append query to update a table by replacing all existing information with updated information while at the same time adding all data that isn't currently in the table?
Right now every time I run the Append Query it just adds the same information as a new row instead of replacing the existing row with the updated information.
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:
IIf(Temp_Vendor_Trips.[Reco Classification]="*" & "V3" & "*",Temp_Vendor_Trips.[Reco Classification],Temp_Vendor_Trips.[Reco Classification] & " " & "V3")
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.
Thanks in advance for the help!
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?
Cheers,
Ben