General :: Sum Of Digits In A String
Jun 1, 2015make sum of digits from a string, something like if i have txt1 = "1234567890" to make txt2 = "1+2+3+4+ ...+0".
View Replies
ADVERTISEMENT
General :: Update Query To Replace Letters And Digits
Feb 27, 2015We have a field of 7m records of varying length, some of which are numbers, some just letters, some alphanumeric, and most which include a dash, space or some sort of punctuation mark.
We need to replace all letters with "L", then replace all digits with "@".
We can replace characters individually using this update query:
Replace([Ref Digit Or Letter],"A","L")
But would like to use wildcard searches to resolve this, something like (these don't work!)...
Replace([Ref Digit Or Letter],"A-Z","L")
Replace([Ref Digit Or Letter],"LIKE [*A-Z*]","L")
General :: Unbound Lookup Textbox Limits Number Of Digits
Nov 7, 2013I have a form with an unbound textbox. I want to be able to enter a sequence of digits and spaces (e.g., 02 950 4187); however, when I paste this into the box, I always have to delete the two spaces until the number is 9 digits long. Then it will accept the number and go look for the prize. How do I get the textbox to accept the number I paste in?
View 5 Replies View RelatedGeneral :: Derive Month In Digits From Date / Time Stamp Field - IIF Statement?
Dec 18, 2012I'm trying to derive the month in digits from a date/time stamp field. Any month between 10 and 12, I need the first 2 digits but anything <=9 is only 1 digit.
MONTH: IIf(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2)="10" Or "11" Or "12",
(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2),(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],1))))
IIF/left statement not returning false argument
General :: Error In Connection String
May 21, 2014I faced this error :
Run-time error '-2147467259(80004005)
The database has been placed in a state by user 'Admin' on machine "topleveldomain' that prevents it from being opened or locked.
in vba code :
I write such as :
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:My ProjectBEdatabase1.accdb;"
I have used the ms access 2013.
I also have split this database such as instruction by people but nothing effect.
General :: Count Amount Of String?
Jun 23, 2014In form (datasheet) I have a three columns "RightColors", "LeftColors" and "AmountOfColors"
I want to do something like this:
If I fill 'RightColors' "red;green;blue;", and fill 'LeftColors' "orange;" then in 'AmountOfColors' column should be "4". Sometimes I can fill only 'RighColors' or 'LeftColors;
I think I should add code in after update event. Is this possible to build code which can count colors using ";"? I need loop for this, right?
General :: How To Get Last Character Of The String In Query
Nov 5, 2012How to get the last character of the string in query?
I have a table name PlateNo and I want to get the last character for registration purposes.
Ex. ZMD-123 - I want to get the number three(3) and if its possible every time the user types the last digit in criteria it will show all plate number ending in the entered number.
General :: String To Return Textbox Sum
Jun 15, 2015I have a textbox on a subform and I want to get the sum in a string as follows.
Code:
Dim s1 As String
s1 = Sum(Forms!CountItem!CountItemLastCount.Form!ThisCount)
MsgBox s1
When I use the above I get an error message saying - Sub or function not defined and it highlights the sum part of the equation.
I have been using the sum criteria in a textbox but if the user doesn't tab of the box then it doesn't see it as being updated.
I have tried me.dirty and everything else that usually works like send keys tab event, requery form and controls, a left mouse click but nothing is working, therefore I thought that code to actually update the textbox may work.
General :: Removing Comma From String
Jun 19, 2015I would like to remove the comma, but only one that is at the end of my string(there is a space after the comma, should be also removed).How should I do that in vba?
View 2 Replies View RelatedGeneral :: String Fields - Add Next Condition To Query
Mar 17, 2015I have a query which working fine:
Code:
strSQL = "Select [nazwa], [id_wycena_pre], [Format_(X)], [Format_(Y)], [kolory p], [kolory t], [nazwa_id], [iloscstron], [numerarkusza], [naklad_pracy], [Kolory P], [Kolory T] from tblGoraZleceniaNowaWyceny where [id_wycena_pre]=" & Forms!frmWycenyObszarRoboczy!ID_wycena_pre & " and [nazwa]='" & "środek" & "' or [nazwa]='" & "okładka" & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)...
But now I would like to add next condition to this query
Code:
where [kolory p] is not null or [kolory t] is not null
- these fields are string
I don't know how to write this condition and add to my exiting strSQL string
General :: Which Function In Access Return Last Value In A String
Aug 14, 2013Which function in access return the the last value in a string.
Period 1 Period 2 Period 3
10 20 30
I need the function to return 30.The reason behind this I have different periods for categories and my formula I'm using needs the ending value.
General :: Specifying Connection String In MS Access Forms
May 9, 2013I have a question about connection strings. Basically I have an access front end and an access back end. It works fine for now, but when it gets rolled out, I don't want to have to manually relink the tables every time. Is it possible to hard-code the database connection strings for all the tables used in the solution as the paths won't change once deployed?
View 9 Replies View RelatedGeneral :: Extract Text And Numeric From A String
Feb 12, 2013"Att Mr/Mrs Vezi Your DEFAULTED account has been handed over to XXX. Payment DUE IMMEDIATELY Ref 1315519509. Tel 0009377500"
I need to extract the word "Ref " plus the number that follows it from the above string. The problem is that the word "Ref " is not always in the same place and the number following it is not always the same length. (ie it could look like "Ref 123456 " or "Ref 1234567 ") The only commonality between the records is that the number will always follow the word "Ref ".
General :: Extract Postcode From String - No Other Filtering
Dec 31, 2014I have tried to find this on the forum but it seems all the answers are to do with manipulating the strings based on what the postcode is whereas I just want to extract the postcode as a separate string as simply as possible.
Basically, I have a text field called Add5 which has the last line of the address including the postcode.
Example: "Northampton NN1 7PQ"
I am trying to end up with two strings like this
Add5 = "Northampton"
Postcode = "NN1 7PQ"
I only need to do this in one place, I don't think it needs a module.
General :: Evaluate String To Make Expression
Jan 11, 2013This returns the correct value Debug.Print [myTable]![myField]
Why does this result in an error? Debug.Print Eval("[myTable]![myField]")
General :: Populate A Text Box Based On String
Sep 12, 2014I have a select query that holds anywhere between 1 to a couple hundred records, I need to take the Name field and populate a textbox which after verification I write to a text file that I later use in a powershell script. So far have tried using VBA to create a variable and populate the textbox based on the string but it only provides me the first item, I've tried dmax and this gives me a single result last Item, Dlookup only gives me the first item, changed it to a listbox which populates the box correctly but only writes the selected item in to my text file.
View 7 Replies View RelatedGeneral :: String Function To Detect First Alphabetical Character?
Jul 18, 2014I have a string where alphabetical and numerical characters are mixed up. I need a string function where I can find the index of the first non-numerical character. I tried with the mid function but could not get it to work.
View 13 Replies View RelatedGeneral :: Convert Text String To Date Format
Apr 10, 2013I am connecting acces to oracle servers via ODBC .
In the table there is a date time stamp - format data type text:
20130225060621
I would like to convert so that it is recognised as a date so I can calculate against e.g. Now() or another standard date format.
this is what I am currently using (which is OK) BUT I need to calculate against the time also.
Current Check Point Date: CDate(Mid([DZ_ZPKT_AK],7,2) & "/" & Mid([DZ_ZPKT_AK],5,2) & "/" & Mid([DZ_ZPKT_AK],1,4))
I spoke to an IT guy who works in TOAD program and he gave me this SQL:
select to_date(dz_zpkt_ak,'DD/MM/YYYY HH24:MI:SS')from orders where ordnr='4411310';
this is the format I would like but cant make this work.
General :: How To Define Variable In Form As String Which Can Work With Any Event
Jul 16, 2013I have a form (Datasheet). I need to define some variable in form as string, which can work with any event. E.G in column "A"on event after update, in column "B" on event after update...
I will try to explain with a simple example : form (datasheet) columns: "A", "B"
on event after update in coulmn "A", I could have some like this:
(variable what I need to define) = 3
on event after update in coulmn "B", I could have some like this:
If (variable what I need to define) = 3 then
msgbox "ok"
end if
Now I try to work around the problem and use another column "c" to keep the value from after update A - but I know that, it's bad solution -Right?
General :: Listbox - Use Columns Data As Where Clause Of SQL String To Populate Another Form
Oct 5, 2012I have a listbox that is populated with data from a table. I would like to use one of the columns data as the where clause of a sql string that will populate another form, how do I get the selected items column data that I need into a variable? This is in Access 2010 vba.
View 9 Replies View RelatedGeneral :: String Manipulation On A Table - Text Is Gibberish In Group By Query
Jun 22, 2015While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.
I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"
Is presented in Group by as: "砅5"
I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.
File Corruption? anything to do about it?
Modules & VBA :: Separate Numbers And String From Alpha-numeric String
Jun 7, 2013MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
Last 4 Digits Of SSN
Oct 12, 2004I have a database that uses the SSN as the primary key, but only the last 4 digits of the SSN need to be shown on the reports that are generated. I can't figure out how to use the Left function to extract only the last 4 digits. For example if a SSN were: 123-45-6789 I would want the field to read ***-**-6789, or just 6789 whichever is easiest. I would appreciate any help that could be given.
View 3 Replies View RelatedLast 4 Digits Of Ssn
Feb 13, 2008I need to pull only the last 4 digits of the SSN. How do I do that in my query?
View 1 Replies View RelatedCompare Digits
Mar 8, 2007Hi there!
I'm bulding this database where the user will have to insert a full phone number. What I would like to know is? if is there any way where Access can pick the first 5 digits of the number to compare with information already imputed in. by example if the user input the number 01942789789, access pick the number 01942 to look for a match? I know I could do with two diferents fields one for the code number and other for the number, but my boss only want one field. Is there anyone that could help me
Thanks
www.joaobeca.co.uk
Sort By First 2 Digits Only
Jan 19, 2006Open the database – then open the Report: rep_Billing
I have a query that is doing some sort and count functions. The column [TYPE] is sorted then counted. Instead of sorting by the full name can the query sort based off of the first 2 characters?
Example:
On the report the first company is: AMERICAN POSTCARDS
First it separates the colors, which is good.
Then separates the [TYPE] then counts the total amount that color was that type.
However I do not want the full type to be separated out, I want it based on the first 2 characters so that the “N-“ will all stay together as a group.
Final count should say: AMERICAN POSTCARDS
GREEN RA 3
RED N- 14
RED PR 16
RED RA 12
BLUE NC 1
I know I can get this info from the report, but it has to be done at the query level because it is going to be exported into another system at that point. Thanks.
Let me know if I need to make this clearer. Thank you all.
Travis