I have an access database and table name called student. The table includes field name such as identification_id and registration_no. The value in identification_id is 3434-34-213 and registration_no are 234/45. Can i query the field name so i can get both value without the sign - and / so the output will be 343434213 for identification_id and 23445 for registration_no?
View RepliesIs there any way I can remove specific information from all the records in a field using a query?
For example- how could I write a query that would remove the "NME" from NME ACTIVE; FENCE CO INC? I know you could add text to a field by including & "text".
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delte the records which are already null .
i tried delete query but it did not work
Please help me
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delete the records which are already null .
i tried delete query but it did not work . This is in microsoft Access.
Please help me
Hi,
I have the following set up on a form to pull together a query (by form) and resulting report:
Publication
Sector
Product
Region
Each publication can have multiple sectors/products/regions. The resulting query therefore duplicates the publication, for example:
Publication 1 Sector 1 Product 1 Region 1
Publication 1 sector 2 product 1 region 1
publication 1 sector 2 product 2 region 1
etc etc...
The report ONLY has publication on it, but as it is based upon the query utilising the 4 factors above, the resulting report is:
Pub 1
Pub 1
Pub 1
Pub 2
etc
I want the report to only list the publications once - i have tried "hide duplicates" from the form field properties, however this hides the text but leaves a big gap on the resulting report when generated where the duplicate would be if it were not hidden.
There must be a simpler way to acheive this than getting another query to create a table based on the first query which (the table) only includes the publication name, and is filtered to remove duplicates through a primary key...
Can anyone help!?
cheers
amx
I have this query:
INSERT INTO 1_2_06 ( Issue_ID, Assigned_To, ListType )
SELECT Issue_ID, Assigned_To, 'QPT'
FROM QPTActions
WHERE QPTActions.Entry_Date<=DateValue('1/2/2006') And QPTActions.Close_Date>DateValue('1/2/2006');
The Assigned_To values are a Lastname, Firstname format as they come out of the first database. But I need the comma stripped before they get entered into the INSERT database. Is it possible to do this on the fly?
I have field called [LOCATION] that varies in length. I need to remove any alpha characters at the end, if there are any. Listed are some examples.
[LOCATION] [NEEDED]
PA 109S 55W 2N 1A PA 109S 55W 2N 1
WC 155A WC 155
PB4 68N 1UNH PB4 68N 1
B 3E 1P B 3E 1
XB1 203S 14W 2N 6PU XB1 203S 14W 2N 6
PB1 6 PB1 6
Thanks!
Hi,
I'm trying to run an update query on a field called "DisplayURL" to remove all of the spaces that the field contains. Some have 1 space, some 5 and some have no spaces.
I'm using Access 2000 and the replace function ( Replace("DisplayURL"," ","") or Replace([DisplayURL]," ",""). However it produces an error stating that there is an "Undefined function "Replace" in expression".
I've searched online and found that Access 2000 doesn't have this function, but its easy to create a "wrapper" to make it work.
I've tried various examples online with no luck. Does anyone know what "module" I'd have to create to get this working, and how to "call" the function as I do not understand how to do this?
Thanks in advance for your help, I've been having a nightmare with this problem.
Thanks again.
Dean
Hello friends,
I have slitted my database and given each user a copy of the front end so tha I can work on the master. I have notice that each time I sent them an updated copy of the new file once they replace the old one in their C drive, when they run a push button which makes run certain append queries, I have noticed that the message comes up although I have removed it from the Options settings. I was wandering if there was a way I can exclude this via code. THank you.
I have a query which contains about 19 fields in it and some of the fields contain parameters. What I'm trying to do is to run the query using the parameters I've set, but at the same time remove fields that are empty (therefore only fields that are populated will be shown). I've tried using the 'is not null' parameter but it seems to interfere with the other parameters that I've set and as a result the query doesn't show any information. Also the field containing the 'is not null' parameter is still being shown. I'm not really sure what else to try!! Any suggestions would be most appreciated!!
View 14 Replies View RelatedHere is the SQL from my Query
SELECT PreStatus.Operator, PreStatus.Mode, Sum(PreStatus.CountOfMode) AS SumOfCountOfMode, IIf([SumOfCountOfMode]>=3,"YES","NO") AS [Current], PreStatus.Rate, PreStatus.Last, PreStatus.First, PreStatus.DET
FROM PreStatus
GROUP BY PreStatus.Operator, PreStatus.Mode, IIf([SumOfCountOfMode]>=3,"YES","NO"), PreStatus.Rate, PreStatus.Last, PreStatus.First, PreStatus.DET
HAVING (((PreStatus.Rate) Like "*" & [Forms]![Status]![Rate] & "*") AND ((PreStatus.Last) Like "*" & [Forms]![Status]![Last] & "*") AND ((PreStatus.First) Like "*" & [Forms]![Status]![First] & "*") AND ((PreStatus.DET) Like "*" & [Forms]![Status]![DET] & "*"))
ORDER BY PreStatus.Operator;
When I run Query a dialog box request for SumOfCountOfMode appears. Is there anyway I can turn this MsgBox off?This Database is used for the user to find out if their personel are current in said data. The user will be running this query through a Form and will get their results via a report if they put anything in this MsgBox it could possibly skew the data they are looking for.
I have a count and store data INTO a table named USTATE
When I run the query it works within 30 seconds. but when i add INTO USTATE then it takes 4-8 minutes to complete it and asks for if i want to delete the existing.
I have this small database, I would like to have your support to setup this query "QryResults" in order to remove the duplicate records, I can't find a way to get shown only true records, for some reason I'm getting duplicate rows and fake values, the query is calculating operations from two different queries and a table.
View 3 Replies View RelatedI am needing to compare some information in a way I cant with the normal Unmatched Query.
I have 2 tables with the fields:
| PHONE1 | PHONE2 | FIRST | LAST |
What I need to do is have Table 2 check against Table 1 to see if any of the 'PHONE1' records match 'PHONE1' or 'PHONE2,' and if they do have them remove just the information from PHONE1, not the entire record.
Then I would have to repeat the process to do this with PHONE2. Having it check against 'PHONE1' and 'PHONE2' in Table1.
If both PHONE1 and PHONE2 have a match, the entire record would be deleted. If not, it would keep whichever did not match.
I am currently having to do this manually, because if I just run an Unmatched Query it gets rid of the whole record regardless of if PHONE2 is a good number or not.
If any more information is needed, let me know. Like I said, Im relatively new to working in Access and dont know much.
All of the fields are 'TEXT' type.
Access 2003 on Windows XP.
I am attempting to filter the data in query design such that it will not return anyone in the database has there name present more than one time.
For example, in the image below under the Owner_Name field there is "Abbot, Mark C Etux Wanda C". This name appears twice in the database. My criteria is the name can only appear once, therefore this record would not be returned after the query. In query design I would like it to return only name records that appear once in the entire database if there is a way to structure that.
I need to be able to run a number of queries where some of the queries are make-table queries. What I don't want is to have is all the messages that come up during the creation of the table ie deleting existing files and showing the number of records created to place in the file. Is there a way to block these messages?
View 4 Replies View RelatedWhat I have a a form that my lab supervisor would use. That person selects the records that are to be modified and assigns work, completes work or otherwise updates the status of the records. One of the options is to mark a record as not having a sample here if it has already been marked as having been here. So essentially, I want to modify the record to change the sample arrival date and sample number field (which is a foreign key field) to null or empty. I have created a delete query that deletes the corresponding record just fine.I just am having difficulty updating the two fields mentioned above. When the supervisor selects the record the primary key for that record is also picked up so it is easy to know exactly what record to adjust. The query returns the information to be updated just fine, it just doesn't do that. Here is the sql of the query. As I said, I don't care if the fields are empty or null. Also the fields aren't required.
Code:
UPDATE TestRequestTable SET TestRequestTable.SampleLocation = "", TestRequestTable.SampleArrivalDate = ""
WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![LabScheduleForm]![TRNumberCombo]));
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 have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
View 1 Replies View RelatedI'm having difficulty with the syntax in this query to remove duplicate data for the field "StocktransID".
Code:
SELECT DISTINCT tblStockTrans.StockTransID, tblItem.Brand, tblItem.Category, tblItem.SubCategory,
tblItem.Model, tblItem.Description, IIf(TransTypeID=3,Quantity*-1,Quantity) AS Qty,
tblTransaction.TranstypeID, tblItem.ItemID, tblTransaction.TransactionID, tblItem.ItemType,
tblItem.Origin, tblOption.ParentID
[code]...
I have a link to a "csv" file. I then run a query to exclude some rows.
Then I need to export the query result as a text file with quotes (as text qualifier) and semicolon (as delimiter). This is not a problem. The problem is, that I only need the text qualifer for the data in row 2+ and not the headings.
The data export should look like this:
ID;Contract;Date
"1";"4700001360";"20150618"
How can I specify this export layout in the export wizard?
I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
View 1 Replies View RelatedI have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.
View 1 Replies View RelatedI have a query that displays this
Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleC TravelerD TravelerC TravelerE
TripTItleC TravelerE TravelerC TravelerD
TripTitleD TravelerF N/A N/A
I want to filter Column1 to have no duplicates so it looks like this
Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleD TravelerF N/A N/A
Is this possible?
i removed the DIsTINCT in my query to move some field to be updateable on the form. Once I did that my detail section of my form was empty ..why and how do I fix this problem.
View 1 Replies View RelatedHi i have created a plotchart and at the bottom of the chart it is displayed as SUM of SUM of apr 05 etc for all the months is there anyway i can remove the sum of before each month??
thanks :)