Problem With Using Expressions With Strings
Mar 2, 2006
Im 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????
View Replies
ADVERTISEMENT
Jun 6, 2007
Hello
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.
View 1 Replies
View Related
Mar 4, 2008
I have a table that has questions that require Yes/No answers, simple done, however now for the tricky (or at least for me, VV new to Access) I need one of the fields to add up all the Yes and asign a value so for example:
Q1 Q2 Q3 Q4 Q5 Result
Y N Y Y N 3
How do I do this, I asume it is down to the expression for 'Field' Result but what should that expression be and where do I put it?
View 7 Replies
View Related
Jul 29, 2006
Help me what is wrong with this expression:eek: :
=IIF( [eBay Method] ="Auction",
IIF( [Start up Price] <=.99,.2,
IIF( [Start up Price] < = 9.99,.35,
IIF( [Start up Price] < =24.99,.6,
IIF( [Start up Price] < =49.99,1.2,
IIF( [Start up Price] < =199.99,2.4,
IIF( [Start up Price] < =499.99,3.6,
IIF( [Start up Price] >499.99,4.8," "
))))))),
IIF( [eBay Method] ="Buy it Now",
IIF( [Start up Price] <=9.99,.05,
IIF( [Start up Price] <=24.99,.1,
IIF( [Start up Price] <=49.99,.2,
IIF( [Start up Price] <=49.99,.25," "
))))))
View 4 Replies
View Related
Nov 8, 2005
Hi,
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.
View 4 Replies
View Related
Mar 31, 2006
Hi 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)?
View 4 Replies
View Related
Nov 3, 2007
So 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
View 6 Replies
View Related
May 23, 2005
Hi. 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
View 3 Replies
View Related
Aug 23, 2005
Seems 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
View 1 Replies
View Related
Dec 15, 2005
I 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?
View 5 Replies
View Related
Nov 2, 2006
Hello,
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!
View 2 Replies
View Related
Jan 29, 2007
Hey 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
View 5 Replies
View Related
Jul 10, 2007
Hello,
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
View 13 Replies
View Related
Nov 15, 2004
Hi 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] & """"
View 1 Replies
View Related
Dec 18, 2004
Hoi,
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
View 2 Replies
View Related
Feb 9, 2005
This 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
View 14 Replies
View Related
Oct 1, 2007
Hi 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.
View 12 Replies
View Related
Jul 13, 2005
I'm using Windows 2K SP4 and have just installed Office 2003. Everytime I open Access I get a message asking if I want to block unsafe expressions (presumably macros). How can I turn this off?
BTW I've downloaded all of the security updates, which are recommended by MS.
View 7 Replies
View Related
Apr 5, 2007
Hi,
Can anyone tell me why it is that when i have written a DB on one machine using Access 2003 and then subsequently put that DB on to another machine i get an error relating to unsafe expressions?
why does this happen and is there a away in which can stop this error message from appearing?
I am trying to implement the DB i have written at work but it does not look good when i get a constant error message appearing when ever i open the DB.
Thank you in advace for your help
Regards
Bev
View 3 Replies
View Related
Feb 29, 2008
1)I have a list of stock opening prices and closing prices for March. i need an expression or function that will calculate which company's stock price changed most during the month (difference between opening and closing price). 2)In the stock table, I have dates given to me in the following format mm/dd/yyyy. Lets say I want it only to display the date so it only shows the month but not the year of the day. What function will give me this result?Thanks
View 1 Replies
View Related
Jan 8, 2007
I have a complicated question here. I am currently building a db for career development in my workplace. The main fields that i am having some issues with is the following. I have three fields labelled as the following:
1)Last Review date
2)Next review date ( i have this entered in as an auto generated date-- will add one year from the last review date)
3)Status
Now here is my question:
I want my status field to auto generate as well-- say if the last review date was in 2005 and the next review date was dec.12/06-- then the status would currently be overdue.
Is there any expression to use to auto generate the status field to say whether it is overdue or completed? or is there any easier way to do this?
View 5 Replies
View Related
Jul 8, 2005
I’m starting to wonder how I ever made anything work, before I found this place.
I built 2 queries and put them in a query
They share a common field
The join type is all record from Query1 and Matching from Query2
Next I added a count field for each
Then I added a calculated field that subtracts the count from Query1 to Query2.
So far it all works
When I add >0 to the criteria for the calculated field a dialog box pops up asking for the values of the fields expressed the calculated field.
View 2 Replies
View Related
Jul 19, 2006
i have a master table with various lookup feilds. i want to create a querie that creates a text expression feild called description and it is based on the info provided in the lookup feild. problem is when i create this querie from the location master it provides the id's from the lookup colums and not their text.
any solution
View 4 Replies
View Related
Aug 1, 2006
Hi experts
I have some problems consists of using Conditions.
I have 4 tables in Access:
tbl1_School names, addresses, ID_Number etc..
tbl2_ School features with Number of Clesses, Number of Students, total Area etc.
tbl3_teaching stuff with names, occcupations, Ages ets. and
tbl4_nonteaching personal with names, occupations. ages, etc.
References is ID_Number of schools
I make Query with relations, but do know how to build Expressions Builder for this:
Question 1.
a)If school have between 300&500 Students there are ig.18 teachers,
b)if schools have between 500&800 Students there are ig.25 teachers and until 1500 Students there are ig. 50 teachers.
Question 2.
c)if school have area until 400 m^2 there are needed ig. 8 nonteaching personel,
d)if school have from 400 till 600 m^2 there are needed ig. 15 nonteaching personel.
Numbers I specified like examples ig. were prescribed bye regulation.
If someone want to help me how can I use Expression Builder or VBA Code it would be appreciated.
It means I should use Condition Commands.
Thank You for help.
View 6 Replies
View Related
Jul 17, 2007
Is there anyway to use an if statemet to define the criteria for a query?
as in
if forms!RunReport!cboname <> "" then
query all names that equal forms!RunReport!cboname
end if
also is there any way to use a dlookup function (or another similar function)
to define several criteria for my query
ex.
i have a table, "modules", with a list of modules, each classified as "digital" or "analog" and i want to view all records containing analog modules from a different table, "info"
thanks for any help in advance
View 4 Replies
View Related
Feb 29, 2008
Hi all!
I'm new to this forum and could use some help on a simple Access app. I have attached the relations for the db. I need to collect some data to a subform as:
tblFunctionCodes.FunctionCode,
tblAccounts.AccountNumber,
Monday (hours),
Tuesday (hours),
Wednesday (Hours),
Thursday (hours),
Friday (hours),
Saturday (hours),
Sunday (hours)
Where I have the tblEmployees.EmployeeID, tblDates.DateWeek and tblDates.DateYear as variables.
The weekdays need to be an expression with my current design, which seems to work ok from Monday to Saturday, but when I throw in Sunday Access says the query is too complicated.
I have this query to get the data, but with no expressions (can't get it to work):
SELECT tblFunctionCodes.FunctionCode, tblAccounts.AccountNumber, tblRegistrations.Hours, tblDates.DateWeekDay
FROM tblFunctionCodes INNER JOIN (tblEmployees INNER JOIN (tblDates INNER JOIN (tblAccounts INNER JOIN tblRegistrations ON tblAccounts.AccountID=tblRegistrations.AccountID) ON tblDates.DateID=tblRegistrations.Date) ON tblEmployees.EmployeeID=tblRegistrations.EmployeeI D) ON tblFunctionCodes.FunctionCodeID=tblRegistrations.F unctionCodeID
WHERE (((tblRegistrations.EmployeeID)=[intEmployeeID]) AND ((tblDates.DateWeek)=[intWeek]) AND ((tblDates.DateYear)=[intYear]));
I've been trying to use that as a foundation for other queries but I just can't get it to work. Does any of your guys have any ideas? Maybe an INTERSECT query??
Anyway I'm out of ideas and very pressed to makes this work. Any ideas are welcome!
Thanks in advance, jon.
View 10 Replies
View Related
