Questions About Possible Expressions
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 Replies
ADVERTISEMENT
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
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
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
Jul 6, 2005
Hi all, I'm writing a database basically as a favour to someone. I'm a sql analyst/programmer so my access and vba skills are a little limited and I hope someone can help me with a problem I have.
Part of one of my forms is a flow chart style set of Yes/No questions and the user answers each question. For each question I have created a (hidden) text box which calculates a score (5 for no, 10 for yes) which I have done with a IIF expression in the Control Source of the text box. These are then totalled to give an overall score.
This score is then used to calculate a grade (A or B) and a percentage score using the same IIF method in the control source.
What I want to do though, is use this grade and percentage and write it to the individual record as data in the table - to use in reports and such like.
Is there any way I can do this? I've tried a few bits of VBA but to no avail. Is there a way that this can be done? Or have I approached this in entirely the wrong way??!!? :confused:
Any help would be greatly appreciated!
View 2 Replies
View Related
Nov 24, 2005
Hi,
Is there a way on Access that when a total is calculated using an expression on a form that it could update the table itself with that total?
Thanks for any help!
Roly
View 1 Replies
View Related
Oct 10, 2005
i'm trying to use a switch function in a query but i'm unfamiliar with the format a bit. here is what i'm trying to do...
switch([Radios]![Warranty] and [Radios]![HousingWarranty]=true,[Radios]![Change front housing]*(25/60*28), [[Radios]![Warranty] And [Radios]![PortableHousingWarranty]=true], [Radios]![MaterialsUsedPrice1])
basically first statement is condition second is execution. I need to add more but i'm getting an error from this much...if I was doing this in vb.net I would just do if, ifelse, ifelse, ifelse...but...
Thanks,
View 2 Replies
View Related
Jan 9, 2007
I have recently deployed an application at work [work pcs all running A2K2], built with A2K3 on home computer.
I split the database housing the backend on our server, named server, and using frontends on users pcs.
I am running a VB script that takes information from another linked db on server (so everything would be housed together and not on users pc) this script runs a query converts to xml, opens FTP and uploads the query along with .pdfs to the internet.
I can get it to work from home, no problem. I can get it to work from my pc at work.
But when I try on other pcs it can not find the db (error msg) or opened by another user in exclusive mode, yes I explored this option I dont think this it. 1) Cause the frontend can see the backend and everything works from there. 2) That pc is the only one with an open frontend. 3) When I try opening the actual db from the networked shared folder I get an unsafe expressions alert and can not open. The VB needs to open pull from the 2nd db. Both the backend and 2nd db I get this message.
Can anyone tell me how I can change this, the msg box says its out of trusted network. So I copied and actually opened on local pc, I could open it. Move it back to server and no such luck.
Thanks for any posts, I am having to get this working before I load for other branch offices.
I have googled and searched forum, seems everything is geared towards "sandbox".
View 4 Replies
View Related
Feb 22, 2006
First off, I am not sure if this belongs in the queries forum but it seems most of the question is geared towards a table so I have posted it here.
At the moment I have a query that contains several fields that use nested IIf statements to determine which set of rules to apply. I was wondering if it is possible to somehow set the IIf variables up in a table and then for just to lookup the table in the query.
For example, one of my simpler IIf's looks like this...
Cost:
IIf([AType]='Skill',
IIf([StartDate]<#01/08/2004#,
IIf([AOP]>0,
([BasicRate]-[Frame])/[AOP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion])
So I was thinking could I setup a table to show:
FieldName AType StartDate AOP Calculation
Cost Skill <#01/08/2004# >0 ([BasicRate]-[Frame])/[AOP]/100*[Proportion])
Cost <>Skill Is Not Null Null ([BasicRate]-[Frame])/[OP]/100*[Proportion])
and then somehow perform the calculation rather than just look it up.
The main reason is it would be so much easier to see what is being calculated rather than try and weed my way through nested IIf statements.
Can anyone shed any light on this or maybe even a better way to accomplish what I am after?
View 1 Replies
View Related
Nov 28, 2005
Is there a way or what is the expression used if I want to do a running total in a query under a field called "Amount Donated". In addition, I would like to filter by selection using the DonorID as the primary key to show the total.
Thanks.
View 1 Replies
View Related
Feb 6, 2006
I'm trying to create a query which will retrieve holidays for a specific week, so it would allows me to enter WeekDesc (e.g. week 1) and Line (e.g. Line 1) and then retrieve some information relating to holidays. I have gotten this far already.
However, I've just realised however that for example, if I enter Week 1 and Line 1, it will only retrieve those records for which (in the Holidays Table) have Week Description as Week 1 (this other fields in the table are HolidayID (pk), PersonID (pk), StartDate (pk), EndDate and Approved (checkbox). However if the holiday runs for over a week and runs into Week 2, if i run the query for Week 2 and Line 1, it will not retrieve that holiday.
View 8 Replies
View Related
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 2 Replies
View Related
Mar 28, 2006
Could someone please point out to me what's wrong with this query?
Sum(IIf([ProgCode] LIKE "004*" Or "0058",1,0))
I get a syntax error when I try to run it. If I take out the "OR '0058'" part, the query will evalute properly. However once I try to add in the OR statement it stops working. I have also tried "OR LIKE '0058'" and recieved the same error. In addition to that, I have also tried the equals operator instead of the LIKE operator.
Thanks
View 2 Replies
View Related
Mar 22, 2007
I want to be able to update stock levels by clicking a button on a form. The button executes code to run the updatequery. Details of Items are listed on a sales form. The fields include item code and quantity sold. Details of the items are stored in a table called 'Items' - fields include item code and quantity in stock. I want the clicking of the button to compare the item code in the form with the item codes in the Items table, and then reduce the quantity in stock by the quantity sold in the form.
Any help given would be much appreciated.
thanks
View 1 Replies
View Related
Jun 11, 2007
I use a left join to join table A with table B. Table B has less matching records , so in the resulting table the field [RESULT] which comes from the table B has empty values.
If I use an expression for a new field PROCENT that involves [RESULT] I got #ERROR in all records with missing [RESULT] value. I tried various IF statements , but even IsError () function produces #ERROR.
It seems that ACCESS 2002 fails to detect missing values and gives an error for any operation. Is there any workaround for this problem?
Example
PROD_PERCENT: IIf([RESULT]>0,[RESULT]/[NETTO],0)
View 5 Replies
View Related
Mar 17, 2008
Hello again,
I have a query which has a parameter called [Enter Date as DD/MM/YYYY].
This filters out records from my table which match those entered by the user.
However, when the query is run, Access is asking the user to enter the criteria twice. The first time it is labelled Expr1, the second time it is Enter Date as DD/MM/YYYY. There is obviously something wrong in my query, can anybody point me in the right direction here?
Thanks in advance;)
swifty
View 4 Replies
View Related
Dec 18, 2006
Hi! I'm trying to create a query using option groups, i have two options the first one is STOCK and the other is PD.
If i select stock the the query i want to create to introduce it on a form is:
I have 5 fields
-denominación
-reserva
-almacen
-cantidad
-stmax
If i select stock
then the query is
If (reserva +almacen)<cantidad then on a sub form have to appear the table with all the rows where (reserva +almacen)<cantidad.
but if i select PD only have to select on stmax the rows where value=PD
PLEASE i need help with this....
View 1 Replies
View Related
Feb 27, 2005
hi all, Im working on a simple timesheet to keep track of time worked, but ran into two problems.
1.) The expression i'm using works fine for some times, and others not so good (get negative number).
2.) It only figures the hours but not the mins. (it rounds them up).
Does anyone one have a sampale DB or know a expression dealing with figuring out time differences.
If you need more explaintion on what i'm trying to do I've attached a sample DB.
Thanks,
Rich M.
View 1 Replies
View Related
Mar 6, 2006
i'm struggling here
i have a simple cross tab query. i'd like to put a parameter value as criteria. lets say i hard code the criteria with 2006 it works fine. but when i reference it to the value in a form e.g forms!frm_navmaster!txtyeardate then i start having problems. i don't know to start. i've tried using the expression and where values in design view....uuggghhh.
any ideas are appreciated
View 3 Replies
View Related
