Hi everyone,
I am writing a query that is master-detail in principal. But need other data from other 2 tables. I wonder if I can construct the query so that it contains the result set of more than one SQL statement. Thank you for your kindly help.
Gary AU
I have a query that I just can't figure out. We are a facility that works on rail cars. We have over 200 cars in house at the moment and the database has a daily production report in it with the status of all of the cars.
I need a query that will pull out the "AAAA" and "XXXX" cars from the list only IF the cleaning field or the mechanical inspection or BOTH fields are blank.
I have a table where I have 5 columns, (BatteryL1, BatteryL2, BatteryR1, BatteryR2 and BatterySize).I want to update BatterySize, with a value if any one of the above listed columns contains a specific value using something like 'LIKE' in the query.
For example:
UPDATE Customers
SET BatterySize=13
WHERE BatteryL1 LIKE '%13%'
[code]...
I'm trying to compile a number of IIF statements.
I have 6 IIF statements, but would like to put them all in to one IIF statement.
Could anyone help me with this one?
OrderTrack: IIf([O_SRC_CD]="2005WMDT","Web","Phone")
OrderTrack: IIf([O_SRC_CD]="2005WMKE","Internet","Phone")
OrderTrack: IIf([O_SRC_CD]="DOGNET23","Web","Fax")
OrderTrack: IIf([O_SRC_CD]="CANTRY3J","Web","")
OrderTrack: IIf([O_SRC_CD]="MAMTWOSE","Web",",")
OrderTrack: IIf([O_SRC_CD]="2005W999","Web","Mail")
Thank you for your help and time
Corey
I have a query which works fine for one if statement, but i neewd to have 10, whats the best way of sorting this, can it be done in one query, or should i use a module or should i use the code?
my if statements are as follows:
IIf([eventcode]="n204",[totaltime]-[totalcipstd],0)
IIf([eventcode]="n301",[totaltime]-[totalPaststd],0)
IIf([eventcode]="X117",[totaltime]-[totalMaintstd],0)
etc....
can someone please help me sort this...
a problem may be that the list of eventcodes may increase... again whats the best way to sort this.
Andy
I would like to update a lot of fields in my tables; about 120 different fields in 12 different tables. I would however like to keep it simple by using the SQL editor in MS Access by writing all the UPDATE queries and leave them to run while I do smething else. It seems to me however that you can only run one query at a time using the SQL editor. I thought I could separate one SQL editor from the other by using semicolon as in Oracle SQL editor etc., but that does not seem to work. Does anybody know how to do this in Access without writing a VBA code etc.?
View 3 Replies View RelatedI'm trying to put multiple IF statements into one procedure like the below:
Dim Answer As Integer
Answer = MsgBox("Have You Selected The Correct Outcome?", vbCritical + vbYesNo, "Continue Request?")
If Answer = vbYes Then 'Works fine here
'The bit I can't get right
[Code] .....
I have a list of projects and I need to display their status (Red or Green) in a text box.
My fields are [PercentComplete],[StartDate],[EndDate],[ReportDate]
There are two ways a project could have a red status.
1. [PercentComplete] < 100 AND [ReportDate] < [EndDate]
OR
2. [PercentComplete]<100 AND IsNull([EndDate])
There is one way it could have a green status:
[PercentComplete] = 100 AND Not IsNull([StartDate])
This is the best I could come up with for the Iif statement, but I get "invalid argument" which I suspect relates to the AND portion.
Code:
Iif([PercentComplete] < 100 AND [ReportDate] < [EndDate], "Red",
[PercentComplete]<100 AND IsNull([EndDate]),"Red",
[PercentComplete] = 100 AND Not IsNull([StartDate]),"Green","")))
I've a nice formula which work the majority of the time, the only time this doesn't work is Bank Holidays. Is there a way to build on this:
>=Date()-IIf(Weekday(Date(),2)<2,4,2) And <=Date()-1
The above simply runs a report for the last working day, expect if today is a Monday then it runs Fri-Sun.
Im assuming if I know the dates of the Bank Holidays I could hardcode them into the beginning with multiple IIF statement followed by the one above??
I am trying to get the output of several nested IIF expressions to show in only one filed in the table created. The output needs to be captured in a table. I am staring with create table to get it to work before I go ahead and use the sql to update the original table (the table the data is from)
I can get each to run fine independently and the whole thing runs too, the issue is it wants to put each expression in a new column, thus it is forcing me to label the expression.
Can I change this to only output in one column or add some more sql to do so?
Here is the SQL (table and filed names have been changed to protect the innocent)
BTW, I cannot change the Table the data comes from, I am stuck with the single table structure (BOO).
************************************************** *************
SELECT TABLE1.ID,
IIf([TABLE1]![STAGE]="D",
IIf([TABLE1]![PROJ_ACTUAL_D_DATE] Is Not Null,"Tier 1",
IIf([TABLE1]![APPROVAL_D]="Y","Tier 2",
[Code] .....
I'm trying to run about 300 update statements and keep getting a syntax error of missing operator... what could be wrong?
UPDATE Applicants set [NBCC ID] = '351174' where SSN = '136861387'
UPDATE Applicants set [NBCC ID] = '350960' where SSN = '138885740'
UPDATE Applicants set [NBCC ID] = '350817' where SSN = '140082154'
UPDATE Applicants set [NBCC ID] = '351013' where SSN = '145766662'
:confused:
Hi I need help inserting IIF statements as criterias in my query.
Here's the query I have created from a table called XML_EXPORT which consists of a list of ORGANIZATIONS with various fields. What I'm trying to do is display:
-the name of the EXEC_NAME_1 (primary executive's name)
-the ADDRESS
-the CITY
-the POSTAL_CODE
-the PROVINCE
-the PUBLICATION
-the ORG_LEVEL_1 (the organization's main name)
-the ORG_LEVEL_2 (the organization's departments 2 to 5)
-the ORG_LEVEL_3
-the ORG_LEVEL_4
-the ORG_LEVEL_5
Now not every record contains data in ORG_LEVEL_2 to ORG_LEVEL_5 so we want to display only ORG_LEVEL_1 and the last ORG_LEVEL that is not blank.
i.e. If a record has ORG_LEVEL_4 and ORG_LEVEL_5 blank then we want to only display: ORG_LEVEL_1 and ORG_LEVEL_3 (even though there is something in ORG_LEVEL_2)
Here's my query:
------------------------------------------
SELECT XML_EXPORT.EXEC_NAME_1, XML_EXPORT.ADDRESS, XML_EXPORT.CITY, XML_EXPORT.POSTAL_CODE, XML_EXPORT.PROVINCE, XML_EXPORT.PUBLICATION, XML_EXPORT.ORG_LEVEL_1, XML_EXPORT.ORG_LEVEL_2, XML_EXPORT.ORG_LEVEL_3, XML_EXPORT.ORG_LEVEL_4, XML_EXPORT.ORG_LEVEL_5
FROM XML_EXPORT
WHERE (((XML_EXPORT.ADDRESS) Is Not Null));
------------------------------------------
Can anyone show me how the Iif statement syntax would be ?
Allan
I had been using some SQL in Access with many IIf statements. I understand the equivalent in SQL is CASE, WHEN, END.
However, I'm really stuck with the following:
oh..ISNULL used to be Nz as well
IIf(ISNULL(ZI.InvoicesRaised,0)=0,0,IIf(IIf(ISNULL (ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)>0,30,(ISNULL(ZA.TotalDebt,0)*30)/ISNULL(ZI.InvoicesRaised,0)),0),0))
+IIf(ISNULL(ZIMinus1.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0))*30)/ISNULL(ZIMinus1.InvoicesRaised,0))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0))*30)/ISNULL(ZIMinus1.InvoicesRaised,0))),0),0))
+IIf(ISNULL(ZIMinus2.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0))*30)/(ISNULL(ZIMinus2.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0))*30)/(ISNULL(ZIMinus2.InvoicesRaised,0)))),0),0))
+IIf(ISNULL(ZIMinus3.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0))*30)/(ISNULL(ZIMinus3.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0))*30)/(ISNULL(ZIMinus3.InvoicesRaised,0)))),0),0))
+IIf(ISNULL(ZIMinus4.InvoicesRaised,0)=0,0,IIf(IIf (ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)-ISNULL(ZIMinus4.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0))*30)/(ISNULL(ZIMinus4.InvoicesRaised,0)))),0)>0,IIf(ISNULL(ZA.TotalDebt,0)<>0,IIf(ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0)-ISNULL(ZIMinus4.InvoicesRaised,0)>0,30,(((ISNULL(ZA.TotalDebt,0)-ISNULL(ZI.InvoicesRaised,0)-ISNULL(ZIMinus1.InvoicesRaised,0)-ISNULL(ZIMinus2.InvoicesRaised,0)-ISNULL(ZIMinus3.InvoicesRaised,0))*30)/(ISNULL(ZIMinus4.InvoicesRaised,0)))),0),0)) AS DD
Do I have to put in some seriously heavyily nested CASE statements or are there some other ways round this?
Hi,
I am going to be creating a iif statement update qery, to update a column on dates, E.g. would be....
IIf([FinMonth] between 01/01/2008 and 31/01/2008,1)iif([FinMonth] between 01/02/2008 and 30/02/2008,2)iif(etc etc
2 questions??
1) Is 12 nested iif's statements are allowed within one IIF statement?
2) iS THERE AN EASIER WAY TO DO THIS??
Hope this explains enough.
Thanks
I have 2 queries serving useful routines, one to filter out duplicate addresses for a mailing label routine, the second to convert tick boxes into Y/N answers for a Tab Delimited Text file export.can I combine the 2 SQL statements in the 2nd query without amending them in any way.
The SQL statements are;
FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-News List], a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (((a.[e-News List])=False) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])));
[code]...
im working in MS Access 2007.what im trying to do is have a query run specific parameters if a check box is selected. So if the check box is selected than the query filters the "Tranche" column so that the only records that shown are records that have the "Tranche" coloumn = 1.
I know this can be done either in vba code or in the criteria section of a query but i dont know that appropriete language for either.
In vba code i was able to get this far. But..... i dont know how to call the criteria line from a query?
This is my very simple unfinished code.
My query is called [Tranch Query] and the column i want to filter with is call [Tranche]. The check box is called [Check0].
Private Sub Check0_AfterUpdate()
If Check0 = True Then
'How do i set the query criteria?????
End Sub
I am working with Access 2007. I have a little problem building the iif statement within a query criteria in [Actual Date] which is the following:
iif([Actual Date] Is Not Null And [Document]=0;>#01.01.2012# Or Is Null;999)
When I enter this I get no records out of about 8000. The table fields behind the query are in the right format (date/time and number).
Hi,
I'm doing a report on the features that Microsoft Access provides for supporting multimedia data. I'm having trouble finding any resources on the web.
Does anyone know of any and can point me in the right direction?
Thanks in advance for your help.
k
What type text format that MS ACcess support?
I had copy/paste from MS Word, FileMaker Pro and DOS into MS Access Memo field. They act very strange. Is it t a way to copy these text out to some othher format to script them to MS Access supported format and paste them back into MS Access?
Hi!
I want to create a database using MS Access that can be accessible by 80 users for information search purpose. At the same time at least 20 users out of 80 can make modifications. Need suggestion will that be possible in access.
The database will store in shared drive. Also want to know how to lock a record let say if user1 has access certain record for modifications than the second user can access database but not allow to modifying the record which is already under modification phase.
Appreciate any help on it.
Regards,
Nick
I am thinking about creating a DB using Access for a business. However, my wife mentioned to me that her company is looking at software for database work and has some Access DB's they are trying to convert because, according to a technical support person, Microsoft intends on getting rid of Access as a DB! I am posting here to ask anyone, professional or otherwise, if anyone has heard that Microsoft intends to stop supporting or implementing Access. Is Access 2003 the last version? I haven't been able to find out anything along this line. I thought, maybe Microsoft is thinking about supplanting Access with SQL Server. Before I go to the toil of building a DB system in Access, I want to know I am not wasting my time doing it. If anyone on this forum has heard a thing about Microsoft dumping Access, please let me know. Thank you. :o
View 8 Replies View RelatedAnyone know if Access supports triggers?
You know - "CREATE TRIGGER ..." etc in standard SQL
(nb. without ODBC connecting to another DBMS that does support it)
I find no mention of it in the manuals or options but Access seems to have most things.
Thanks in advance,
ScubaJoe
Hi,
I have a database which contains fields with information in many different languages. I seem to lose my japanese content from time to time.
I cut and paste the information from a Word document and paste it into an Access field. It seem to work and I get the japanese text pasted. But I have lost it and now I just have all text replaced by small squares. How can I retrieve my japanese text?? What should I be looking for if I want to maintain a database in MS Access using Japanese, Chinese and other foreign language text in it?
Bjorn
Hi,
I want to disable a button right after click it. Because I could not disable a control that has got the focus, i tried to shift the focus to another control; however, all controls that I tried to shift the focus to don't support the method (SetFocus = true).
I want to do this on a subform's control, but I keep getting this error:
Object does not support this property or method.
Any susggestions will be very much appreciated.
B:)
I have an access database that I developed while working for tech support at earthlink a while ago. If anyone is interested, I'd be happy to clean it up and post it in the examples. Some of it's features include: Tabbed Browsing Posting and manipulating web forms HTML Scraping Injecting information into other programs via APIA screenshot of it is below
View 3 Replies View Relatedi'm getting this message when i try and change a record in a table via a recordset... but i am using CursorType = adLockOptimistic which i thought let you make changes to the table
here is my code so far
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.CursorType = adLockOptimistic
rst.Open "TBL_TmpSubmission", CurrentProject.Connection
If rst.RecordCount > 0 Then
Do While Not rst.EOF
MsgBox rst!PropertyType, vbOKOnly, "debug"
If DCount("[PropertyType]", "[TBL_PropertyType]", "PropertyType = '" & rst!PropertyType & "'") <> 1 Then
rst!PropertyType = DLookup("[PropertyType]", "[TBL_PropertyType]", "IDPropertyType = " & rst!PropertyType)
MsgBox "property changed", vbOKOnly, "debug"
Else
MsgBox "good property", vbOKOnly, "debug"
End If
rst.MoveNext
Loop
End If
rst.Close
am i using the wrong combination or cursor and lock type here? reading the help it seems i should be able to make changes to the table.