I have Data in a Field that looks like this
W442643 -30
I need to trim the first 8 Characters off, and add 00 to the beginning of the remaining characters like this
0030
would I use Ltrim as an Expression in a query and if so, how would I write it out?
ReleaseNo: Ltrim ([OrderNo]) 8......???????????
Need a clue here. HELP!!!
I've got an address in one field which i want to seperate by store, address, city and postcode. The problem I have is that the bits I want to seperate out have vairable lenghts so using a left or mid function with a set length does not work
Sample of field
(SOLIHULL) 17 MILL LANE ARCADE TOUCHWOOD e146
I have a field that contains an IP Address. The ranges vary significantly meaning some of the octecs have 3 characters and some have 1 or 2. Example; 100.2.25.250 and the next record may be 1.20.25.251. I need to add a "/32" to the end of the IP Address. How can I automate this for all the records? I know I can create an update query, but I'm not sure how I can do this. However, I will say for most of the records the IP Address is the only thing in the field. Some of the fields have multiple IP Addresses.
View 3 Replies View RelatedI'm using Access to create some web page templates. To Do this I have a memo field called "Description" into which I'm entering text. Example:
This is an obvious description example
What I need to be able to do is put a "<p>" before each sentence and a "</p>" at the end of each sentence. Examle
<p>This is an obvious description example</p>
The information is stored in a form page, and the text have paragraphs.
My client is reporting corrupt characters cropping up in fields. These characters appear as Asian Characters. They only corrupt one record.
This happens very seldom. In fact, it hasn't happend for about 2 months and today it happend again. Their was no change to the database.
Is this a network problem? Network noise corrupting data?
Thanks
Hi
I want to ask something simple but because i am new i am dont know it!!! If you try to make queries in Access, untill how many characters you can put in each query???
Hello,
I am totally new at this database stuff. I have been searching the archives for a couple hours and confused on how to accomplish what I need to do. I saw references to using a module but I have not used that feature yet.
I am using Access 2003. I need to remove/delete " - " space dash space, "-" and "&" from a string in one field (DGName). I'm trying to do this with a make table query
DGName
P1000
P1000 - SMLS
P1000-CA (not a type error)
UD000 - C&B
V-NET
and I want it to come out like this
DGName
P1000
P1000SMLS
P1000CA
UD000CB
V-NET
Thanks
Sean
Hi, Everyone,
I have an excel column that has building location and building name in one column, example 1245 Accounting, how can I copy the 1245 into it's own column, please help, i can do it either in access or excel. Please.
users are using a hand held scan gun to enter data into a field. if the barcode is 12 characters long, I only want the 1st 7 to be written to the table.
I was thinking a validation rule using TRIM or LEN but can't seem to get it to work.
Any ideas?
Thanks
I am trying to write a query to extract the first 3 characters from a character string but have no idea where to start since we usually use Select "field" etc etc
Example, for a Code ACC01, the query result should give ACC
Would appeciated any guidance. Thank!
TK
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.
Hello all. Have a bit of a strange one here (and fairly frustarting). I have a table which includes a field (type memo) which holds text comments.
I have a query which includes this field. For some reason, when the comments field is empty (identified thorugh opening the table), when it is returned through the query is shows two characters, namely '@v'.
Can anyone shed any light on this for me? Thanks for reading :)
Good Morning to all
i would like to kindly ask if there is a pre defined function in the VBA SQL of access in which it will trim a certain field programmatically
for example
Old string = Manila-MSE Building - 30.26f
New String = Manila-MSE Building
Thanks for your help
I am sure there is an easier way fo doing this, but I am looking one at a time!!!
I want to know what is the Max and Min number of characters used in each field of my table.
I am using this code below, the max will be at the top and then scroll to the bottom to find the min, but as can be seen this is doing one field at a time and is very time consumining!
SELECT tbl05to06.Account, Len([Account]) AS MyAccount
FROM tbl05to06
ORDER BY Len([Index Key]) DESC , Len([Centre]) DESC , Len([Account]) DESC;
Hi,
I am trying to use a query to select the first two characters from a field I have created in a form. I would then like to use these 2 letters to filter out results in the query.
For example:
I have a number of sections in a document (1a - TAKE OFF, 1b - LANDING, 1c - STATIC etc.) which include associative chapters (1a.Contents, 1a.Introduction etc.).
From a combo box I select the section (i.e. 1a - TAKE OFF), I then want to filter out all the chapters in the associative section (i.e. 1a.Contents, 1a.Introduction etc) by using the first two characters of this combo box in the query.....
I hope this makes sense!!!
Cheers
Hi, i am looking to replace characters in a field using a query, I can use the replace to replace them all but that aint what I want e.g.
I want this: ~~test~test~~~~~test~~
To become this: test~test~test
I can use replace and get this testtesttest, but I need the ~ placed back between each word.
Any help appreciated, thanks:confused:
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
Is there a attribute for setting the max amount of characters in a textfield, I have thought about adding some code in the on keypress event, to lock the textfield once the required amount of characters has been reached, but I was hoping there would be a more simple way.
View 5 Replies View RelatedI just discovered that the report I am trying to create has too many characters in it. I have approximately 200 fields I am trying to get a sum for. Does anyone know the easiest way for me to get this sum with out typing in each field, ie., (1jbo)+(2jbo)+(3jbo). In Excel, we can just type in =sum(A:C) and it would just add them all up. That's what I need. Is there a way to add up all 200 items in a similar fashion?
View 1 Replies View RelatedI've discovered I have a problem sending text between an Access Database and ASP page.
The database contains Turkish characters (eg, i without a dot) and the output to a web page is fine when run on a server that has Turkey for its international settings. Interestingly, the database shows the correct data regardless of environment... just the written page that screws it up.
However when the same data and pages are used on an "English" server, the Turkish characters are lost and modifyed to their closest Western cousins.
I'm not sure whether this is exactly an Access or an ASP problem... I think it's the latter, but maybe someone reading this forum has som ideas too
Since I need to use this data in a server hosted in the UK, is there any code I can add which will fool the server into reading and writing the Turkish characters correctly?
Obviously, I could set up a function on the page to convert the characters, but that seems like a waste of system resources.
Hi everyone,
I have a DB that has over 180,000 records with zip code as one of the fields.
I would like to know how do you query the 1st 3 characters? For example, if zip is 95127, I want to query and retrieve records for the first 951.
I seen this on another thread, but can't find it. THanks in advance guys.
Hi guys,
I have a question regarding special characters that access doesn't like to see.
When passing a string to be absolved via html, if I set myHtmlBody = "<font color="#000FF"></font> it will throw a syntax error because of the # character.
How can I solve this? Thx for any help.
Hi
I wish to check my phone number fields for non-numeric characters.
If the field contains 0-9 then I want to keep the field otherwise I want to replace the contents with a null.
Can you please help?
Noel
Hi,
Simple question really (if you know your VBA)...
Have a field which contains records of strings of alphabetic characters i.e.
ADGHJLP
BCEFHIJKLMOPST
etc
Need to be able to separate these characters with a '/' so that the field reads
A/D/G/H/J/L/P
B/C/E/F/H/I/J/K/L/M/O/P/S/T
etc
I know it'll be a For..Next loop of some kind but can't seem to get the syntax correct.
Any help appreciated.
cheers,
Alex
Hi:
I have a table with one field for comments. The problem is the limit of the capacity of words, that you can type.
Any sugestions ????
Thanks for your time
I don't know VBA and I am not a programmer.
I have a table with locations identified by latitude and longitude.
I have separate fields for degrees minutes seconds and decimal degrees.
For example, 50 degrees 30 minutes 0 seconds is also stored as
50.5 degrees. I have a radial search query that finds all targets
within a given distance from a location. The three fields input by the
user are latitude, longitude and distance. The problem is the query
only works with decimal degrees. I want the user to be able to
input in a format like "50 30 0". How do I convert a character string
with non-numeric characters to a numeric string?