Nested IIf Nightmare
Oct 20, 2006
Poor old Access. I think I've made it wave a white flag, but based on the following expression I am trying to get it to evaluate, I'm not too surprised...
The situation is that I have a large number of tick boxes on a form, which nicely filter through to a series of fields in a table whose values are either -1 or 0 (according to if the box is ticked or not). Unfortunately, I now need to generate a small piece of text for a report based on which boxes have been ticked. Deep breath...
institution:
IIf([people].[memctf]=-1,"CTF",(
IIf([people].[memwt]=-1,"Westcott",(
Iif([people].[memwy]=-1,"Wesley",(
Iif([people].[memry]=-1,"Ridley",(
Iif([people].[memwm]=-1,"Westminster",(
Iif([people].[memiocs]=-1,"IOCS",(
Iif([people].[memermc]=-1,"ERMC",(
Iif([people].[memmbi]=-1,"Margaret Beaufort Institute",(
Iif([people].[memcjcr]=-1,"CJCR",(
Iif([people].[memcym]=-1,"CYM",(
Iif([people].[memindepba]=-1,"Independent BA",(
Iif([people].[memhmc]=-1,"Henry Martyn Centre",(
Iif([people].[memregchelm]=-1,"Regional (Chelmsford)",(
Iif([people].[memregnor]=-1,"Regional (Norwich)",(
Iif([people].[memregpboro]=-1,"Regional (Chelmsford)",(
Iif([people].[memregalbans]=-1,"Regional (St Albans)",(
Iif([people].[memdiocedsips]=-1, "Diocesan (Eds and Ips)",(
Iif([people].[memdiocnorwich]=-1, "Diocesan (Norwich)",(
Iif([people].[memreg]=-1, "Regional", "Unknown"
)))))))))))))))))))))))))))))))))))))
The logic seemed to work nicely when there were just 3 options in the list, but when I try to enter the above, Access says "the expression you entered is too complex". Poor baby! :D
Does any one have any idea how I can get it to evaluate the data I need?
View Replies
ADVERTISEMENT
Feb 1, 2008
Is there any way that I can import a text file and split the data into three columns without having to go through 65000 lines of text.
It is basically a print off of a directory structure. I have a very large text file that lists the files in folders for each of hundreds of users. I need to divide the information up into three columns so that the below data:
BOBSongs
2003-07-15 12:10 PM 1,474,564 blue.mp3
2003-07-15 12:09 PM 858,204 orange.mp3
2003-07-15 12:10 PM 1,665,028 yellow.mp3
BOBsongshappy
2005-08-23 03:01 PM 917,504 jolly.mp3
2003-07-15 12:09 PM 331,005 joker.mp3
BOBfiles
2005-08-23 03:01 PM 917,504 writing.doc
ends up looking like this:
BOBsongs1,474,564 blue.mp3
BOBsongs858,204 orange.mp3
BOBsongs1,665,028 yellow.mp3
BOBsongshappy 917,504 jolly.mp3
BOBsongshappy 331,005 joker.mp3
BOBfiles917,504 writing.doc
I easily imported the file into Excel, but it is still all in a column. Any advice would be appreciated.
View 5 Replies
View Related
May 8, 2006
If any of you guys have any spare time on your hands would you mind having a look at this mess and tel me where I have gone wrong.http://www.geocities.com/andrewnorthwales/Andrews_database.mdb andrewnorthwales/Andrews_database.mdb mm this board is messing with my url if it puts us.share just change it to www. dont know why my url is not working on hereIm trying toProduce a list of available vehicles and drivers for a given input dateSearch for an availability that satisfies a prospective customer’s criteria (specific vehicle for a specific date)its my first ever database and I cant get the begger working right.it must be relationships or something, but I cant see where I ahve gne wrong.its just a fictional Weding car hire firm and I create records/orders for fictional customers.but its melting my brain __gloop...:confused:
View 5 Replies
View Related
Jul 5, 2005
Hi everyone. I was wondering if anyone could help me. I am in the process of designing a database (Access 2002, office xp), but the problem I am having is that when I am pulling in an ID number, from one form into another form, the form I want this number to land in crashes, with the warning that more than one user is trying to access the form at the same time (they are not, just me).
After having to clear this error box (a few times, it keeps reapearing) errors then appear in each of the destination forms fields. :eek:
I think it is something to do with the way the database is setup in multiuser envirionments and retry intervals etc - and I have been playing about with the values, which seems to work once, but then it goes straight back to crashing. :confused:
If anybody give me some default values that might work :o or any other remedies for this problem, this help would be very much appreciated :)
Many Many thanks.
View 4 Replies
View Related
Apr 27, 2005
Many will look at this and laugh but its real.
My Access Dbs are opened in hidden state and i dont see my forms.
This must be the cause.
I tried to hide the menubars using ghudsons code,i disabled the shift key. But i dont know what has happened.
I can nolonger view them. I have tried every method out and i think the only option is to repair OS, may be it will work.
I have enabled the database which i disabled the shift key but when i hold down the shift key, it loads but the forms are totally hidden, STRANGE! ijust see the grey access background and when i click on it twice from the task bar.It pops up.
I have tried to make another database, use the autoexec macro to call a form which enables all command bars, but this works only for this database and when i press the shift key down, this database hides the toolbars and i only see the grey access background. This is happening on EVERY DB ive developed on this machine. I thought it was the access that is totally corrupted but i tried to reinstall my office and i still get the same results
This is now a NIGHTMARE to me.
When db is shifted to another machine it works fine as before.
This is REAL but can i get some suggestions from you folks before i repair my OS.
View 1 Replies
View Related
Mar 19, 2008
Greetings,
I'm using flash mx and MDM zinc which i assume connects using the JET rather than ADO database engine.
For the life of me cannot get a simple update to work due to the dates
involved, lost count how many hours have gone by trying!!!
Within access I can easily switch quotes and hash symbols and all SQL THREE SQL queries work without problems on my database i.e.
UPDATE UserOverallResults
SET Results = 20
WHERE UserName ='Robbie'
AND Quizdate= '19/2/2008 12:15'
UPDATE UserOverallResults
SET Results = 20
WHERE UserName ='Robbie'
AND Quizdate= "19/2/2008 12:15"
UPDATE UserOverallResults
SET Results = 20
WHERE UserName ='Robbie'
AND Quizdate= #19/2/2008 12:15#
Within the database the fields are defined as :-
Results (Integer) Username (Text) and QuizDate (Date/Time) within MS ACCESS DB schema.
in Flash MX TextDate param is a STRING and All parmaters are populated appropriately (see this later in error message).
Example of existing date data stored in database in format "19/02/2008
16:48:11" as a DATE/TIME field.
My code within in flash is :-
mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " +
UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate = '" +textDate
+ "' ");
I've tried with and without Hashes still no joy ..
mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " +
UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate = Format( '"+
textDate +"', "#yyyy\-mm\-dd hh:nn:ss#")");
The error message I get from the code above is ..
SQL Query has failed for the following reason: Data type mismatch in
criteria expression SQL statement: UPDATE UserResultsOverall SET Results =
30 WHERE UserName = 'd' AND QuizDate = Format('19/2/2008 19:4:35'
,"#yyyy-mm-dd hhnss#")
My text string removes the leading zeros not sure if that would cause an
issue or not (its doesnt in MS ACCESS running query) ...
Anyone have any ideas??? Losing my mind here !!
PS I had problems using '&' instead of '+' to concatenate fields.
Cheers
Rob
View 11 Replies
View Related
Mar 23, 2006
Hello, im having a real problem running a certain query in access. My database is used to monitor drain spillages, and the query im trying to build needed to look down a column and show descrite water spills. A descrite spill is when water spills for a bit then stops. I need to show how many times this happens in a column.
The complexity of the problem (comparing one value in a column to other values in that column) coupled with the size of the dataset (600K+ rows) just baffles everything I throw at it.
I had two stabs at it. One involved a recordset - looping through and updating a new field. Recordsets can cause database bloat - in this case it bloated the db to 2GB (Accesses maximum size causing the code to crash) - this is something I've never managed before. The other involved a function that left access frozen.
I was really stuck on this until a crazy idea came to mind, i want to know if this would work.
This might sound stupid but how about some crazy thing like adding a new column and numbering the fields, then adding some kind of query that says if the value is over 0.001 then add a 1, if not leave blank. Then have something like if the column has a 1 in it then check the next, if this one has a 1 in it then repeat this step, if not then this is 1 spill. Store the amount it starts and stops to a variable and just use that number as the Number of descrite spills.
Would this work?
Thanks,
Marley.
View 1 Replies
View Related
Dec 9, 2006
Hey All,
Im using MS Visual Web Developer with an Access Database, i am trying to enter a new row of data into a database via a select statment attached to a button_click event. the code i am using looks something like this.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.AccessDataSource2.SelectCommand = "INSERT INTO [Tblissues] ([IssueDate], [IssueTime], [PlantLocation], [MachineName]) VALUES ('12/9/06', '3:00 PM', 'Gville', 'Snack2')"
End Sub
that being said. when i click the button, NOTHING happens, no error and no insert!!!
I did test the connection to the Database and its fine, i can do an update and i can read the data that exists in the database i just cant insert a new row.
This is not the first time i have delt with INSERT statments but this has me at my whits end and any help would be great.
Thanks
View 1 Replies
View Related
Sep 19, 2005
Hi
I have created a monster I think and I am not sure how or why.
I needed a query to tie together data from a range of tables and so it has about 20 constituent queries, each of which involves some calculation.
The query isn't so horrendous
SELECT tblApplications.lngApp, qryExpWkHomeHelp.WkHomeHelp, qryExpWkPrescrip.WkPrescrip, qryExpWkHosp.WkHosp, qryExpWkRent.WkRent, qryExpWkSup.WkSup, qryExpWkInt.WkInt, qryExpWkLife.WkLife, qryExpWkCouncil.WkCouncil, qryExpWkCar.WkCar, qryExpWkCar1.WkCar1, qryExpWkHp.WkHp, qryExpWkDep.WkDep, qryExpWkCont.WkCont, qryExpWkProp.WkProp, qryExpWkOther.WkOther, qryExpWkTel.WkTel, qryExpWkTv.WkTv, qryExpWkWater.WkWater, tblApplications.lngAppCon, tblApplications.bAppEleg, tblApplications.dtmAppGcSub
FROM qryExpWkWater RIGHT JOIN (qryExpWkTv RIGHT JOIN (qryExpWkTel RIGHT JOIN (qryExpWkOther RIGHT JOIN (qryExpWkProp RIGHT JOIN (qryExpWkInt RIGHT JOIN (qryExpWkLife RIGHT JOIN (qryExpWkCouncil RIGHT JOIN (qryExpWkHp RIGHT JOIN (qryExpWkDep RIGHT JOIN (qryExpWkCont RIGHT JOIN (qryExpWkCar1 RIGHT JOIN (qryExpWkCar RIGHT JOIN (qryExpWkSup RIGHT JOIN (qryExpWkRent RIGHT JOIN (qryExpWkPrescrip RIGHT JOIN (qryExpWkHosp RIGHT JOIN (qryExpWkHomeHelp RIGHT JOIN tblApplications ON qryExpWkHomeHelp.lngExpApp = tblApplications.lngApp) ON qryExpWkHosp.lngExpApp = tblApplications.lngApp) ON qryExpWkPrescrip.lngExpApp = tblApplications.lngApp) ON qryExpWkRent.lngExpApp = tblApplications.lngApp) ON qryExpWkSup.lngExpApp = tblApplications.lngApp) ON qryExpWkCar.lngExpApp = tblApplications.lngApp) ON qryExpWkCar1.lngExpApp = tblApplications.lngApp) ON qryExpWkCont.lngExpApp = tblApplications.lngApp) ON qryExpWkDep.lngExpApp = tblApplications.lngApp) ON qryExpWkHp.lngExpApp = tblApplications.lngApp) ON qryExpWkCouncil.lngExpApp = tblApplications.lngApp) ON qryExpWkLife.lngExpApp = tblApplications.lngApp) ON qryExpWkInt.lngExpApp = tblApplications.lngApp) ON qryExpWkProp.lngExpApp = tblApplications.lngApp) ON qryExpWkOther.lngExpApp = tblApplications.lngApp) ON qryExpWkTel.lngExpApp = tblApplications.lngApp) ON qryExpWkTv.lngExpApp = tblApplications.lngApp) ON qryExpWkWater.lngExpApp = tblApplications.lngApp
WHERE (((tblApplications.bAppEleg)=Yes) AND ((tblApplications.dtmAppGcSub) Is Null));
Well maybe it is pretty horrific!
However it is taking nearly two minutes to load on a fast PC (3.4GHz Pentium 4 with 1Gb RAM) so put it on a basic desktop and it will die.
I did wonder about fragmenting it into four queries and then putting one query at the top of the nest but reckon that's another layer and all the calculations need to be done.
Basically the sub queries are calculating a weekly value based upon input and periodicity of payments - i.e. if quarterly then it divides the payment by 13.
Any thoughts on how I could speed it up?
Thanks in anticipation
View 4 Replies
View Related
Sep 9, 2005
Hi
I have just designed a central training database to aid in the running of our training department.
One section is for an IT course which contains 8 modules.
The tests have the following IDs
Mod 1: ID05, ID06, ID07, ID08
Mod 2: 2.1, 2.2, 2.3, 2.4
Mod 3: 3.1, 3.2, 3.3, 3.4
Mod 4: 4.1, 4.2, 4.3, 4.4
Mod 5: 5.1, 5.2, 5.3, 5.4
Mod 6: 6.1, 6.2, 6.3, 6.4
Mod 7: 7.1, 7.2, 7.3, 7.4
Mod 8: 55, 56, 57, 58
One person may take any of the tests.
If someone completes Mod1, 2 and 7 they achieve Level 1
Similarly if someone completes Mod 3, 4, 5, 6 and 8 they achieve Level 2.
I need to run queries to find who has completed L1 or L2 or both.
Obviously they only achieve if they pass so in that table there is a status field with
Pass
Fail
Wait
As the options
Queries have never been my strong point and I was hoping someone here could help me.
I’m using XP Pro, with Office 2003 and the office 2000 db format.
Many thanks
Dazzy
View 1 Replies
View Related
Oct 26, 2006
Hi
how would one go about nested IIF
New Sal : IIf([emplsalaryaa]>80000,([emplsalaryaa]+([emplsalaryaa]-80000)/(100)*9)+7713.98)
i have the above but i need to put one more
iif
i have tried , IIF but it does not work
i don't know why?
Asad
View 3 Replies
View Related
Jun 8, 2007
My first post ever... so here goes.
My Goal:
Columns of data contain date fields & "Status" that indicates where a case lies. I'm trying to calculate days spent in a specific area (determined by status code) and total amount of time spent there all together. Status code begining w/ "I***" means case was sent to an area, w/ "R***" it returned. I have it together up to a point. My problem lies with end of expression where I want to calculate last date at specific location to now. In line 1 it would be from 5/03/07 to now since no r162 follows & blank field follows. Problem is with second IIF - it acts independently instead of giving false value from first IIF... i think.
status1 date1 status2 date2 status3 date3 status4 date4
1) I162 5/3/07 I004 5/20/07 I006 6/1/07
2) I162 4/2/07 R162 4/30/07
My failure:
stat2n4: IIf([status2]="i162" Or [status2]='i062' Or [status2]='i009' Or [status2]='i159' And [status3]<>'r162' Or [status3]<>'r162' And [Status4]="r162",DateDiff("d",[date2],[date4]),IIf(IsNull([status4]),DateDiff("d",[date4],Now())))
View 9 Replies
View Related
Oct 3, 2005
I have placed a tab control within a tab control (which I have done before). The problem is that the sub tab control appears on every page of the main tab form (encountered this before). I fixed it in the past by placing code on the on open event to make the sub tab control not visible unless the appropriate tab is selected. That worked fine.
I have tried this in the new program and find that when I set the value of the main tab form to 4 (the page index of the tab) the sub tab form will not display at all. However if I set it to zero it will display regardless of which tab is selected. Confusion has now set in :confused: . Any thoughts out there on what I have done incorrectly.
[/CODE]Private Sub Form_Open(Cancel As Integer)
'Me.OrderBy = "RankNo desc"
'Me.OrderByOn = True
If TabCtl44.Value = 4 Then
TabCtl144.Visible = True
Else
TabCtl144.Visible = False
End If
End Sub[CODE]
View 5 Replies
View Related
Mar 30, 2007
I have a nested IIF that I cannot get figured out. This is what I have so far:
Hours: IIf([Dept]="10",55,Format(IIf([EorDCode]=" 1",[Regular]/60,[OT]/60),"Fixed"))
The problem is that I need another IIF to differentiate between Regular and Overtime. It needs to do this.
If Dept = 10 Then
55 Regular Hours and 0 Ovettime hours
Else
Format(IIf([EorDCode]=" 1",[Regular]/60,[OT]/60),"Fixed"))
View 1 Replies
View Related
Aug 13, 2007
Hi,
I am facing a problem trying to calculate the commission for the order I have within my Access application.
I pull up the information from three tables to determine the commission. Commission is based on Currency then subtotal then percentage for each specific client.
Can you please look at the below query and tell me what seems to be the problem.
If the commission is anything other than USD, CAD, EUR or GBP I will calculate normal commission.
Commission: IIf(Currency="USD" Or "CAD",IIf Quantity>1000,USD_Commission2*Quantity,USD_Commission1),IIf (Currency="GBP" Or "EUR",IIf(Subtotal>100000,Subtotal*EUR_Commission2,EUR_Commission1),I If(Subtotal>10000,Price*Quantity*Commission1)))
View 3 Replies
View Related
Oct 4, 2007
I'm trying to add a set of Nested IF statements, but it's not showing me the proper result. I have a table which shows the classes that each student has taken based on the term. So I change the letter grade they receive to a numeric value, once I get that, I need to add those numeric values. Here is the nested IIF statements.
SELECT spriden_pidm, spriden_id, spriden_last_name, spriden_first_name, sfrstcr_term_code, sfrstcr_crn, ssbsect_subj_code, ssbsect_crse_numb, sfrstcr_grde_code, GPA,
SUM(IIF( ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "A" , 4.0 ,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "A-", 3.7,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "B+", 3.3,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "B", 3.0,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "B-", 2.7,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "C+", 2.3,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "C", 2.0,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "C-", 1.7,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "D+", 1.3,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "D", 1.0,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "D-", 0.7,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "F", 0.0,)
)))))))))))) as ppp
from temporary2
GROUP BY spriden_pidm, spriden_id, spriden_last_name, spriden_first_name, sfrstcr_term_code, sfrstcr_crn, ssbsect_subj_code, ssbsect_crse_numb, sfrstcr_grde_code, GPA
When I run this, it doesn't add the values, It just changes the value from the letter grade to the number and that's it. I need them to add. I'm I missing something?
View 6 Replies
View Related
Nov 28, 2007
I think I have looked at this too long!!! If "tblJobs.ServiceTypeID" is other then 1 or 7 it still returns 3%..... wheres my error!?!?!?!? :confused:
Commission: IIf([JobInvoicePaid]=True And tblJobs.ServiceTypeID=1 Or 7,(([ContractPrice])-Nz([JobSubAmount]))*0.03,([ContractPrice])-Nz([JobSubAmount])*0.05)
Thanks
View 12 Replies
View Related
Dec 14, 2007
Hello,
I'm having a problem writing a query with nested iif's. This query is suppose to manipulate a text field that with #'s. Each field is either 5 or 6 characters containing a date such as "20607" or "10607" which is in the mm,dd,yy format. Can you have a nested iif statement such as below with multiple with 3 possible true conditions and only one false condition?
SELECT [date last stmt]
Iif(Instr(1,[date last stmt],"0")=1,Left([date last stmt],2)+"/"+Mid([date last stmt],3,2)+"/"+Right([date last stmt],2),
IIf(Len([date last stmt])=6,Left([date last stmt],2)+"/"+Mid([date last stmt],3,2)+"/"+Right([date last stmt],2),
IIf(Len([date last stmt])=5,Left([date last stmt],1)+"/"+Mid([date last stmt],2,2)+"/"+Right([date last stmt],2,"error")))) AS Newdate
FROM Exercise1;
Each time I attempt to run this query I receive a syntax error in query expression"[date last stmt]"
Any help is appreciated!!!!
View 3 Replies
View Related
Sep 19, 2012
I would like to alter this statement so that any number less than 20 is "PRIMARY", any number between 20 and 26 is "SEC" and any number greater than 26 is "SEC2". I think I need to add another IIf() but I'm not really sure of the syntax.
SEC_NR: IIf((Left([BAY_NR],2)>=26),"SEC2","PRIMARY")
View 3 Replies
View Related
Jan 5, 2008
I'm at a place in my design where I don't want to go further without advice for fear of really making a mess.:o
I have Products, Customers, Customer Locations, and Customer Models that can have any number of combinations. I've joined what I think makes sense at the lowest level, customers with locations, and next model with customers/locations.
I think products is the outer most junction, because many customers can use the same product in any number of models and locations.
The attached diagram shows where I am now--multiple junction tables, and junctions of junction tables. Now each of these tables has other relationships to nomalize things, but those are not shown.
Is one more level, or nest if you will, the right way to do this?
View 8 Replies
View Related
Jun 10, 2005
I did a search for Gouping and sums, but nothing really helped me.
What I am trying to figure out: I have a table where I am trying to use 4 fields in a query.
Vendor, items, Cost, Date1
- I am trying to Group by Vendor, Sub-Group by Items (Easy, I did this)
- Now I am trying to Filter this by date1 (Easy, I did this)
But I would like to sum the Cost for the Groups, but not list every repeated item. For Example:
Vendor A Brush 100 1/1/2005
Vendor C Book 50 1/2/2005
Vendor A Brush 100 1/3/2005
Vendor B Pencil 10 1/4/2005
Vendor A Hat 50 1/5/2005
Vendor B Pencil 10 1/6/2005
Vendor B Pen 10 1/7/2005
Vendor B Eraser 10 1/8/2005
Vendor A Shoe 40 1/9/2005
Vendor D House 1000 1/10/2005
Doing a search from 1/1/2005 - 1/9/2005 we get:
FORM OUTPUT:
Vendor A Brush 200 2
Hat 50 1
Shoe 40 1
Vendor B Pencil 20 2
Pen 10 1
Eraser 10 1
Vendor C Book 50 1
View 3 Replies
View Related
Apr 26, 2006
Hello everyone,
I’m looking for some help with a “nested query”
I have four fields that typically have a quantity amount in them.
I also have a field with a date.
I would like to say something like this
If fielda = 0 and fieldb = 0 and field = 0 and fieldd = 0 and in the date field, the date is >30day from today’s date add a 1 to this field, otherwise leave it blank
Thanks a lot
Corey
View 2 Replies
View Related
Jan 10, 2007
Hello,
I have been working on a query for a few hours now to create a new field based on data in other fields in my table. Here is the code I am using:
MBkt: IIf([Table14].[OWNERGRP_1]<>"",IIf([Table14].[OWNERGRP_1]="ADVO",IIf([Table14].[PRICINGMKT]="SHAREDEX","SEZ"),IIf([Table14].[PRICINGMKT]="REP","REP"),IIf([Table14].[PRICINGMKT]="RIP","REP"),IIf([Table14].[COV_FREQ]="Weekly PCD","PCD"),IIf([Table14].[COV_FREQ]="Weekly","ADVO WKLY","ADVO MULTI-WEEKS"),IIf([Table14].[OWNERGRP_1]="ANNE",IIf([Table14].[OWNER_1]="MMSI/MAIL MARKETING/ANNE","ANNE MMSI","ANNE"),"SOLO"))
Unfortunately, I am unable to get this to work. Any help that anyone can provide is greatly appreciated.
Thanks,
-Brian
View 6 Replies
View Related
Jul 11, 2007
I'm tring to build a queary based on a field called "PLAN_CODE" and a policy issue date field. There are 3 plan codes ...select1, select 2 and select 3.
I want to pull information as follows
Select1 has an issue date 11 months ago (day the queary is ran)
select2 has an issue date 23 months ago
select3 has an issue date 35 months ago
I built a field to calculate the duration of the policy based on the issue date
Duration: (Round((POLICY_ISSUE_DATE]-Date())/365,1)). I think the format is wrong? need help with that!
I built a field to create a nested "IF" statement but the first "IF" is not pulling correctly, so I cant built the remaining "IF" statements.
Expr2: IIf([PLAN_CODE]="SELECT01N" [Duration]=-0.9,0).
Anyone have a better approach? Any input how to handle this? I'm not real good with VB code but have a general understanding of it. It's easier for me to build in the QBE.
View 2 Replies
View Related
Jul 23, 2007
Hey,
I am working on my first access project (please be patient and try to explain like to a child). My problems however are past the basic level so please bear with me ....
I am working on a datbase which deals with laboratory data from patients which have different date stamps - ie. each record has a 'valid time' (time of blood drowing) a 'transaction time' (time of entry into the DB) and 'delete time' (deleted recordes are just given a date and not really deleted).
The idea is that the user should be able to watch the status of the DB at different past dates (ie what did the doctor know about a patient blood tests a month ago when he made the decision to give him a medication which eventually harmed his kideneys). The user should also be able to change values of records, delete records (which means to add a date to the deleted date field) and to add new records.
I want to do it all using one form.
Things I have done so far:
1. Query by form which locates a specific record (Main-Form & singleRecQuery)
2. A subform in Main-Form which displays the results of singleRecQuery (Single-subform). The query and subform aer updated by a macro once I enter all the data into main-form.
3. A qury by form derived from Main-Form which locates a range of values (Range-subfrom & Range-Query)
My problem spans both forms and queries (so I am posting in both forums):
1. I want to create an update query that will draw its data from Single-subform (the form which displays the results of singleRecQuery). I have created the qury without a problem but the fields in the form are locked for entering data (maybe because they are bound). Is there any way to enter data in such a form which is based on a query and then use the new data in another query ? (I tried allowing data entery in the forms properties but it deos't work).
2. I have tried to put a criterion in the queries on the "Delete Date" field so that the quries will not display the deleted records (I have tried "is null" "is empty" and "is missing") but when I run the query with these parameters I get no records back.
3. In the form I have a command buton which runs a macro that runs the qury and then update dthe subform. Is there a way to get this button to run different macros after different events in the form or do I have to add a button for every specific task I want.
4. As I have mentioned the Main-fom is supposed to run 3 differnt queries and display the results on the same form. I know how to make a subform invisable but is there a way to place all three result subforms in the same spot ?
Sorry it is so long
Thanks
EJ
View 1 Replies
View Related
Aug 12, 2007
I'm wanting to run an update query but first I want to see the results by using a select query to make sure that I'm getting the correct results.
See the following query statement:
SELECT ([Goodrec - All] INNER JOIN WH_ACCTCOMMON ON [Goodrec - All].ACCTNO = WH_ACCTCOMMON.ACCTNBR) INNER JOIN [Trial-GL-Reference] ON WH_ACCTCOMMON.CURRMIACCTTYPCD = [Trial-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode], [Goodrec - All].CURRBAL = IIf(Not IsNull([chgoffamt]) And Not IsNull([partsold]),CCur([grossbal])-CCur([chgoffamt])-CCur([partsold]),IIf(Not IsNull([CHGOFFAMT]),CCur([grossbal])-CCur([CHGOFFAMT]),IIf(Not IsNull([partsold]),CCur([grossbal])-CCur([partsold]),[grossbal]))), [Goodrec - All].PCTOWNED = IIf(Not IsNull([origamt]) And Not IsNull([partsold]), CCur([origamt])-CCur([partsold]/CCur([origamt]), IIf(IsNull([partsold]), [grossbal]));
This is the error that I receive each time I attempt to run it and I'm not sure what I'm missing.
Error message:
MICROSOFT OFFICE ACCESS
Syntax error(missing operator) in query expression '([Goorec-ALL] Inner Join WH_ACCTCOMMON ON [Goodrec-All].ACCTNO=WH_ACCTCOMMON.ACCTNBR) inner join [TRIAL-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode]'
Any help with this issue would be greatly appreciated.
Thanks.
Govmate!
View 3 Replies
View Related
