Remove Random Characters

Dec 12, 2007

I'm new to this so here goes...I've got an Access query that I'm trying to remove the first 5 characters from the 10 character name in each record. The name is composed of both letters and numbers. The first 5 characters vary but it is always the first 5 that need to go.

Thanks in advance!

View Replies


ADVERTISEMENT

Remove Last 3 Characters From Field Data

May 22, 2007

I trying to figure out how to remove the last 3 Characters from my data

Such as

MA-D-97
MA-09
UJS-KK-OL-20

Like the data to look like

MA-D
MA
UJS-KK-OL

I Have This so far

Right([TYPEFY],3)

that selects the last three now is there a remove function I can use??

Remove(Right([TYPEFY],3) ???

Need help thanks

View 4 Replies View Related

How Can I Remove The Last Alpha Characters In A Query?

Aug 16, 2007

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!

View 5 Replies View Related

Queries :: How To Remove First 7 Characters In A Field

Dec 24, 2013

explain me the Query Expression to remove the first 7 characters in a column, to rum a Query.Eg - I have entry like - 4/8/11-Pipe...I need to return the result like "Pipe".How to build the query.

View 5 Replies View Related

Reports :: Remove Characters Before 1st Space

Mar 11, 2013

What forumla could I use in a report field to remove all characters before the first space in a text field e.g.

32A Bank Street --> Bank Street
19 Alder Street --> Alder Street

I've tried =Right([JLADD1],Len([JLADD1])-2) but this obviously doesn't work for the first example above.

View 5 Replies View Related

Modules & VBA :: Remove Special Characters

Feb 25, 2015

I have a Memo field that is used to be a description of a document. I don't know what the user will put in (could be anything), but it is eventually passed in a string to a query, etc.I discovered during testing that if an apostrophe is entered (Ex: This document explains how to deal with Joe's pleasant disposition.), it breaks my code. I then realized that all reserved words and characters would cause this problem.

I can get around this one just by using Replace(str, "'", "''"). However, I know there are others that will cause problems, and I don't want to end up with a string of replace statements just to fix them.

View 2 Replies View Related

Modules & VBA :: Remove Special Characters From File Name (when Saving)

Feb 20, 2014

I use the below code as part of a database which creates documents and then saves them using data from the form fields.

Every now and again one of the form fields contains a "special" character such as /,@,& and this prevents the file from saving.

Is there a simple addition to the below code to remove special characters from the file name if there is one present?

It causes all sorts of issues as many people don't realise that there has been a problem until it is too late.

Code:
objWord.ActiveDocument.SaveAs2 FileName:="C:UsersPublic" & Forms![Front Page]![Site 2 Name] _
& " " & Forms![Front Page]![Combo79] & " " & "O2" & ".doc"
objWord.ActiveDocument.Close

View 9 Replies View Related

Queries :: Make Table Query - Remove Characters

Jun 26, 2013

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 Related

Can Access Remove Nonprinting Characters From Imported Data?

Jul 27, 2014

I copy some information from a website and then paste/values into Excel. I noticed that some non-printing characters are copied to. Suppose I were to import this Excel spreadsheet into an Access table. Now I need to clean up/remove those non-printing characters. What is the best way to do this in Access?

I suppose I could set up a calculated column with =SUBSTITUTE([InvHours],char(160),"") as the formula, where InvHours is the field name containing the non-printing characters. But if I did this, then I would still retain the original field with the non-printing characters which would increase the size of the database. I don't think I could delete it because then the calculated column wouldn't work. So I would rather not pursue this option if possible.

Would it be possible to somehow run a macro that could automatically be triggered as soon as data is imported, and that macro would clip out the non-printing characters without having to copy anything to blank columns?

Or does Access have some built-in feature that could do this?

View 3 Replies View Related

Rnd Not Very Random

Aug 10, 2005

I have a small unbound form which has a txt box with code something like =Rnd((20-1+1)*Rnd()+1)) to generate random numbers. When the db and form are first opened the field always contains the same number. Using F9 to refresh will generate another number but it is always the same too.

If the form is closed then reopened (db is not closed) the field will happily generate a random number each time the form is opened or if F9 is used.

I have tried Recalc in numerous events but each time it failed to work. It's late at night here so I'm hoping the morning will bring a clearer outlook???

Has anyone got any suggestions? :confused:

View 10 Replies View Related

Is Random Really Random?

Jan 5, 2005

Was playing around, and when using the
Code:Me.txtNum1.Value = Int((51 - 1 + 1) * Rnd + 1)
format, I noticed that, with 6 differnet text boxes, that after opening the database, the "random" numbers repeat in the same order. That is, with 6 randomly generated numbers, the first set is always the same and the second the same and so on.

Why is it not really random?

View 1 Replies View Related

Random Numbers

Aug 30, 2005

Hi,

Can you please help me out--give me some guidance-if you have knowledge about random numbers. Say, for instance, I have 2,000 records and I want to randomly create a final list with 800 of these records, how do I go about it? Thanks.

View 2 Replies View Related

Random Deletions

Oct 4, 2005

Hi, I have a DB that is basically a big electronic form. Each table is a section of the form and the main central table stores the ID's from the other tables. Most of the relationships are one to one but without referential integrity because sometimes sections need to be skipped.

It's been growing fairly rapidly lately as bit's keep getting added but now a weird deletion problem has started appearing. Every once in a while when a user goes to complete the form (right at the end) it suddenly dissapears. The records in the related "section" tables are still there but the main table storing all the ID's loses the record.

There are NO cascade update/delete's on any of the relationships, also no-error message appears it just goes. I think there is a problem in actually saving the record because I moved a DoCmd.RunCommand acSaveRec command from the complete button to a slightly earlier process and the point at which the record dissapeared also moved.

Does anyone know what may be causing it? It only happens to about 1 in 30 forms and there is no obvious link between the problem and
a) the machine it happens on
b) the person using it
c) the data entered into the form

If it happens the same user can input exactly the same data in exactly the same way and it saves it fine.

It really is random!

People are starting to get really frustrated. I was tring to "sell" the idea to a new department as well and had 15 people sitting in front of a projector when it happened to me. I was mortified :eek:

Any ideas would be greatly appreciated.

thanks,

Tom

View 4 Replies View Related

Random Error

Feb 11, 2008

Hi,

everything was going fine until a random error appeared and now wont go away.

here is my code

MainFormPay()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = " Payment Numbers" ' name of form t open
ANumber = [LastOfVerificationID].Value ' set value of latest Verify number

stLinkCriteria = "[ContractorID]=" & me.ContractorID ' filter to contractorID
DoCmd.OpenForm stDocName, , , stLinkCriteria
Docmd.GoToRecord, , acNewRec

Forms![Payment Numbers]![VerificationID].Value = ANumber

end sub

this was working fine but now i get the following error message:

Run-time error '40036'
Method 'Item' of object 'Forms' failed

when i debug, it highlights the line
Forms![Payment Numbers]![VerificationID].Value = ANumber

the purpose is to get the LastOfVerificationID value from form 1 and then open Payment Numbers, create a new payment number, set verificationID to the new value. this was working fine and without even woring on the forms code, this error has arisen.

how can i fix it?


Nigel

View 3 Replies View Related

Random Autonumber

Jul 27, 2007

With Access 2003 I have a tbl_Orders with the field OrderID set as Autonumber>Long Integer>Random. This produces long strings of numbers both positive and minus. Is it possible to set a certain format such as #### and no less than 0 but no more than 9,999?

View 3 Replies View Related

Textboxes (random)

May 1, 2006

hi everyone,

i have a query which returns n number of rows. my problem is that i need to display the values in text box which is created automatically according to the number of rows returned in the same page(form).

for example if my query returns 1 row then 1 text box must be automatically created and then the value must be displayed and if my query returns 3 rows then 3 text boxes must be created and then the corresponding three values must be displayed.(on the same page).

is this possible?


aravind.s

View 3 Replies View Related

Random Selection Sample

Jan 18, 2006

I'm looking for a way to generate a random list of 25 case numbers from a table which contains 500case numbers. Can someone tell me how to do this.

View 1 Replies View Related

Creating A Random Number

Jul 8, 2006

Quick question, how do I have access generate a random number between 1 and 8? I want it to generate either a 1,2,3,4,5,6,7, or 8. (no zero, no nine)

Expr1: Fix(Rnd(8-1)*10*Rnd()) is what I have been using, but this always generates the number 2? I do want the decimals to be rounded to the nearest integer, thus I have Fix.

If someone could please help me, it would be greatly appreciated! Thanks in advance.

View 3 Replies View Related

Fill Table Random

Apr 7, 2008

Hello,

I have a very complex issue.

First of all there is a table called "tblParTeam" wich contain the teamname and ID of teams partissepating in a certain Tournament. The tournemant data is kept in the table tblTournament.

Each tournament contains a couple of variables: "Number of teams per tournament", "Number of Rounds" wich reside in the tblTournament.

Here is the thing. I also have a form based on the tblTournament. Each tournament had a number of partisipating teams (stored in the variable "Number of teams per tournament"). Then I have a table called tblTournamentDetail wich containt the Tournament_ID, TeamPro_ID (looks in the table "tblParTeam"), TeamContra (also looks in "tblParTeam").

When I pusch a button on my form, wich is based on the tblTournament, tblParTeams and tblTournamentDetail, I would like the X (Number) of particepating teams to be filled in randomly in the tblTournamentDetail.

Lets say I have 10 teams I want 5 to be filled in randomly in the TeamPro_ID and 5 in the TeamContra_ID.
How do I go about this?

Thanks in advance.

View 12 Replies View Related

How To Select X Random Records

Jun 20, 2005

I need to select 200 random customers from my table, how can I do that?

Table: tblCustomers
PK: CustID

View 1 Replies View Related

Random Auto Number

Feb 11, 2006

How can I make random auto numbers 8 digits in length (including leading zeros) and always positive?

Thank you

View 2 Replies View Related

Random Duplicate Records

Feb 20, 2006

Hi Guys,

I hope someone can help with this. I have a table, "Blasthole Submission" which is populated by input in a form, using the code below:


Const MyTable As String = "Blasthole Submission"
Const MyField As String = "Sample Name"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
For intCounter = Me.txtStartValue To Me.txtEndValue
rs.AddNew
rs.Fields(MyField) = "TP" & intCounter
rs.Fields("Submission #") = Me.SubNum
rs.Fields("Sample Type") = "Blasthole"
rs.Fields("XRF") = "True"
rs.Fields("LOI") = "True"
rs.Update
Next intCounter
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing


What I am hoping to do is to place a random duplicate in the table, called, for example TP111152 DUP, approximately every 50th record. Is there any easy way of doing this?

Thanks in advance for your help!

View 11 Replies View Related

Random Selection Of Data

Dec 18, 2006

Hi all

I would like to find out if there is a program where you can randomly select data out of a table?

I sit with a table with 17000 SKU's(codes and data) and need to randomly select items out of it for Stock Count's:confused:

Thanx:rolleyes:

View 6 Replies View Related

Generating Random Numbers

Apr 26, 2007

Hi Guys,

In my table design view I've set my primary key to auto number and the 'New Values' to random. The problem is, sometimes the numbers generated are too long or negitive. Is there any way I can specify the limit of the numbers to be generated and keep them positive numbers?

View 3 Replies View Related

Counter Become Random After Importing

Jan 21, 2008

Hello all

this is my first post, so i hope i'm in the right bullettin.

I need help with this problem:

I have to work on this mdb that has been imported from Access 2000 to Access 2003. The guys did it some months ago, and of course they deleted the old .mdb and there are no backups.
However, the db still works, except that the ID of some tables are now "random id" than "incremental id", and since they used it for some months, now they are full of new records with random IDs.
Since they use THAT id for internal use, you understand that is not very easy to communicate stuff like "please send me document # -3189175187" and so they asked me to get it back to incremental, i tried from the table menu to change the dropdown, as i thought it was that easy. Bad luck, access now complains that the data aren't now contiguous (might be different error, it's a localized version, so i don't know how it is the exact error message, sorry), and i'm not really sure on what to do to avoid problems.

I've explained the best way i could, so if you need more info just ask, thanks everyone in advance!

View 1 Replies View Related

Can 2 Random Queries Work?

Sep 27, 2005

Hi all,

I am not real sure whether or not this can be done with a query, but here goes.
I have a query randomly selecting the top 6 from a selected group.

SELECT TOP 6 tblConsortium.ContactID, tblConsortium.RandomID, patients.LastName & ", " & Patients.Firstname AS [Employee Name], Contacts.Company AS [Company Name]
FROM patients INNER JOIN (Contacts INNER JOIN tblConsortium ON Contacts.ContactID=tblConsortium.ContactID) ON patients.PatientID=tblConsortium.PatientID
WHERE (((tblConsortium.ContactID) In (SELECT ContactID FROM tblConsortium GROUP BY ContactID HAVING Count(*) <25))) And (((tblConsortium.PatientID)=Randomizer())=0)
ORDER BY Rnd(IsNull(tblConsortium.patientID)*0+1);

What I would like to do now is: The randomly selected Employees need to have a randomly selected TEST. There are only 3-Test to choose from. I have placed the TESTS in a separate table and entered 20 of TEST1, 4 of TEST2, and 1 of TEST3. I then set a query randomly shuffling these items. I need these TESTS to randomly be assigned to the randomly selected Employees above.

I would greatly appreciate any thoughts or help…
Thanks Enviva

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved