General :: How To Compare Strings
Aug 23, 2013How to compare 2 strings in Access in order to determine one is biggest or highest than the other??
View Replies
ADVERTISEMENT
Compare Strings In SQL
Jul 10, 2007Hello,
I would like to put a restriction on the SQL statement such that if one particular field contains the character "MO", then we take in the record, else we ignore them. May I know how could this be done in the "WHERE" part of the SQL statement? I do not think SQL would read things like Instr(). Thanks!
Regards,
Anyi
Update Several Fields And Compare Strings
Aug 8, 2007Have devloped a database to log all support issues for an application I work with.
I periodically update the database by importing from another database
for example my main table tbl_issues has fields IR_NO, DESCRIPTION and STATUS
I then import data from tbl_import which also has these fields. The following query works fine:
UPDATE tbl_support
SET tbl_support.STATUS = tbl_import.STATUS
WHERE tbl_support.STATUS <> tbl_import.STATUS
AND tbl_support.IR_NO = tbl_import.IR_NO
However I also want to do this at the same time:
UPDATE tbl_support
SET tbl_support.DESCRIPTION = tbl_import.DESCRIPTION
WHERE tbl_support.DESCRIPTION <> tbl_import.DESCRIPTION
AND tbl_support.IR_NO = tbl_import.IR_NO
How do I update both fields at the same time?
General :: Converting Strings To Dates?
Sep 18, 2014I am trying to manipulate some Ebay data, which returns a string like "1d 00h 29m"
Is there a function I can use to convert this string to relevant hours in total?
Strings Within Strings Using Expression Builder
Jun 6, 2007Hello
I have two tables with names of people in different forms.
table1 : [name] can be either in the form of :
- firstname & firstname surname
- surname, firstname
table2: [surname]
I'm trying to select all the records in table1 where [table2]![surname] is part of [table1]![name]
I've tried using
Like "*"&[surname]&"*"
This returns everything rather than the similar names.
I think this should be simple but can't work it out.
General :: Compare Two Tables And Update Changes
May 15, 2013i import data into a Landing table, this will always contain d days woth of data, i then run an apend query into another table were the records are stored up to 20 days, the problem i face is some of the record data may change for eample a field called ShippedQty may be 0 in a monday but on tuesday it may say 5
Import on monday
Product ShippedQty Date
123____ 0 ________01/01/2013
Import on Tuesday
Product ShippedQty Date
123____ 1 ________01/01/2013
I need to look at any changes and updatein the master table, is there a way to do this.(I cannot change the import as its the only way i can get the data)
General :: How To Compare Sum Of A Few Numbers From A Set Against A Given Number
Nov 1, 2012I have a set of numbers, say (these could also be values of a column in a table)
FieldA = { 11, 16, 20, 23, 30, 37, 40, 50 }
I have a number, say 196.
I want {16,23,30,37,40,50} to be returned as these numbers add up to 196.
Note :
1) There is no possibility of two solutions with the kind of numbers that I may be using.
2) A solution using excel is also OK, though, personally I would prefer access.
Edit :
3) {16,23,30,37,40,50} - Each value is a separate record i.e. they are not in a single field, rather :
16
23
30
37
40
50
General :: Compare Data In A Column And Update If Required
Feb 28, 2013I have a table "Customer Plans" with 100s of rows of data with 3 columns
'Name', 'Company', 'Plan Type'
I want to create a new column or table "Customer Details" with the the column name 'Customer' under the following criteria:
If the data in 'Customer Plans.Company' Column meets a certain criteria as in = "Corp&Gov" or "Head Office" or is "blank", I want it to populate the new column/table "Customer Details.Company" with the corresponding data in the 'Customer Plans.Name' column
e.g.
if
Name = Anna B
Company = Corp&Gov
then
Customer = Anna B
I want to populate 'Customer' with Anna B for that record and so on down the list of data in 'Company' column.
If however the 'Company' column doesn't meet the criteria, I want to populate the new column/table 'Customer' with the current data in 'Company'
if
Name = Anna B
Company = Dealer
then
Customer = Dealer
General :: Compare 2 Tables Where One Record Matches And Another Doesn't
Jun 26, 2013I have table A with a list of Work Instruction references (WIREF) and a Issue number (ISSNO).I have Table B wth employees and they too have a Work Instruction (empWIREF) and Issue number (empISSNO).I want to check if the Work instruction Issue has changed since the employee was trained
check:
WIREF = empWIREF and ISSNO <> empISSNO for all records in table B
General :: COMPARE DATE Variable To DATE / TIME FIELD
Feb 18, 2014In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
View 13 Replies View RelatedUnit Of Strings
Nov 8, 2005Hi,
I have 2 strings:
str1 = SELECT Field1, Field2, Field3, Field4 WHERE (Criteria1)
str2 = SELECT Field1, Field2, Field3, Field5 WHERE (Criteria2)
I want to show in listbox only "Field1" from str1 and str2.
How unit 2 strings?
Thank You in advance.
How Unit 2 Strings?
Mar 31, 2006Hi all.
I have 2 strings:
strSQL1 = SELECT FieldA FROM table WHERE FieldA = xxx
strSQL2 = SELECT FieldB FROM table WHERE FieldB = xxx
I need unit this two strings to third string strSQL3.
I must have result StrSQL3 = SELECT FieldA, FieldB FROM table WHERE FieldA = xxx and FieldB = xxx
Is it possibly unit strings (not Select Query)?
Adding Strings Together
Nov 3, 2007So I need to know this. How do I:
*Add 2 text fields together with a space between?
*Truncating field 1 to the first character and field 2 to the two first characters?
Sorry for my noob questions :D
> Than Comparision In Strings
May 23, 2005Hi. I am trying to compare 2 string values.
5:00
5:45
9:54
10:15
Lets say those are my values. Well in the formula >"5". 10 Will not show up correctly. Does anybody have a piece of code or something that will correctly display 10 as >"5". Any help greatly appreciated. Thanks
Joining Strings
Aug 23, 2005Seems like a simple question but I am stuck.
I need to join two strings from different records in a table
table eg
ID Desc
1 blah blah
1 more blah
2 blahring
2 blah
3 and last one
I want to join all the "Desc" strings that have the same ID to get
1 blah blah more blah
2 blahring blah
3 and last one
Can anyone help?? :o
Thanks
Paul
Splitting Strings
Dec 15, 2005I am normalizing data from a spreadsheet of just over 4000 records. The spreadsheet has a "Model" field that contains both the model number and a model description, separated by a space. I would like to split this field into two different fields: "Model_Number" and "Model_Description". The model number is one word of varying lenths.
Any ideas?
Null And Zero Strings
Nov 2, 2006Hello,
I am having trouble with a query. I have a number of blanks for a field and I tried using the expression from the help:
=IIf(IsNull([fieldname]),"Unknown",Format([fieldname],"@;LS"))
But my result set is still a lot of blanks.
This is the expression I was trying to use:
IIf(IsNull([tblMainpersondata]![MailingAddrLine2]),[tblMainpersondata]![MailingAddrLine1],[tblMainpersondata]![MailingAddrLine2])
On most of these, the result was a blank, instead of MailingAddrLine1. If there was visible data for MailingAddrLine2, it appeared.
Any help or suggestions would be greatly appreciated!
Comparing Two Strings
Jan 29, 2007Hey Guys,
Just wondering how I would go about comparing two strings, and spitting the results out a similarity percentage?
E.G. (String 1) Postal Address: "11 John St"
(String 2) Street Address: "11 John Street"
(Output): 80%?
Cheers
So Confused With Strings!!! Please Help!!!
Nov 15, 2004Hi all,
Please can someone help. I am trying to pass a query to the database from vba but can't seem to get the string concatenation of fields correct.
Clinical area and title of audit are database fields. Any ideas please? Thanks!
Here is what I have:
StLc = "[Clinical Area] = """ & Me![Combo309] & """ & """ And """ & [Title of Audit] = """ & Me![Combo309] & """"
Counting With Strings
Dec 18, 2004Hoi,
I have a loop where i use a string (strCount). The first time I run the loop strCount has de value A. The second time strCount must become B, the third time C, etc
I already tried to strCount = strCount + 1 but it did not work
does someone know how to do this?
greets
Koen
sorry formy wad english
Help With Normalization (strings, Oh My)
Feb 9, 2005This is a database that was handed off to me for upgrading.
The person who initially created it made the Name field in the table contain the entire name... So a single entry in the field looks like
CHRIS R LOUNSBURY
The table is in the attached table. This is merely a sampling. The main table has over 3,000 names in it. Is there an easy (or maybe not so easy) way to automatically parse that data and split the names into their own fields.
An example would be the string CHRIS R LOUNSBURY. Run code which takes the string left to right to the first blank space, and splits it off into First Name field. Take the last part of the string (right to left) to the first white space and put it in the Last Name field.
Is this possible? Or do I need to find myself a temp employee to data entry all this over again
Declaring Strings
Oct 1, 2007Hi all
I have a Row source string which I use quite often. Currently, I declare it in each procedure and copy and paste it in. The problem is when it needs changing, I need to change it in a billion places.
can I declare the string as public and set it as public too so that I can just make a reference to it rather than copy and paste entire slabs of code into each event??
perhaps I need to place it into a module?
Suggestions are much appreciated.
How To Sort Alphanumeric Strings
Nov 1, 2006Access does not have an option to sort alphanumeric strings properly with the result that sorting a column with the following (sort of) data is almost impossible.
Flat A1
Flat A10
Room A13
Room A2
Room A21
B5
Flat 5b
10k1
10 k3
10 k12
10 k20
10 k2
etc
I need to be able to get addresses sorted correctly and they always have a mixture of alphabetic and numeric characters.
The steps I have followed to try and achieve this are as follows:
Create a matchfield containing the data for sorting (typically the street number followed by the flat number/name)
Split the matchfield into separate fields where there are spaces. "Room A13" becomes "Room", "A13"
Then split the fields/columns by separating the alphabetic and numeric portions - i.e. "Room A13" becomes "Room", "A", "13". This I have not been able to achieve successfully.
(Thinking about it the first step of separating by spaces is probably not necessary. All that is needed is to separate the numeric and alphabetic data)).
Once you have separated the data into alaphbetic and numeric content a sort is straightforward.
Why can't Access cope with what I would regard as a fairly basic requirement (i.e. to be able to sort alphanumeric strings correctly).
Does anyone have a suggestion how I can solve this problem until Access is improved?
Importing Text Strings
Jan 14, 2008hi guys,
i am importing a tab seperated file into a table, and one of the fields is a user comment.
all is well -- until a record is imported that contains comment with a carriage return followed by some more text. the text after the carriage return is imported as the next record.
if the file is tab seperated, why is the carriage return causing the import to act in this way?
thanks
rik.
Problem With Using Expressions With Strings
Mar 2, 2006Im relatively new using queries.
I'm confused. I developed a subset of data in the same general format as our main database. When running select and update queries on this subset, I am able to use Left, Right and Len expressions such as "Right([Year],2)" and "Right([Latitude],Len([Latitude])-3) to update and generate new fields of edited data. However when I use the same expressions on the same tables and fields in the main database, I get a reply such as Function is not available in expressions in query expression 'Right([Year],2)'. A coworker says he has encountered the same problem. Has anyone else encountered this and found an interpretation/solution????
Qry To Find Strings With Len=10 And No Same Letter Twice ...
Apr 5, 2006I have a two tables with about 44.000 words that I need to search.
I need to find words that are exactly 10 characters AND where each letter is only represented once ...
Anyone that has an idea how to write that query?
Any help would be highly appreciated.
Thanks from cold Denmark,
Steff