I have a query that is called up when data is selected from a form and you hit the submit button to go get the report. The report calls up the query which all is working except the if statement in the query criteria. Here is a look at the code.
Like IIf(IsNull([Forms]![TPKG_Status_Generator]![System_List]![column(1)]),"*",[Forms]![TPKG_Status_Generator]![System_list]![column(1)])
What it should do is if the field selection in the form is left blank and they hit submit then the query should return everything. If they pick a system number then it should only display the items in the query that have to do with that system. My problem is that no matter if I leave it blank or pick and item in the drop down box it still displays every line item. Any thoughts?
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 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])));
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 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",
I am writing some iif conditional statements in one of my tables to assign new values in that table.
Code: FP: IIf([cohort with status].[Intake Date] Between #04/01/2012# And #26/04/2012#,201201) Or IIf([cohort with status].[Intake Date] Between #27/04/2012# And #24/05/2012#,201202)
When I run it, I am getting some -1 values in the new column FP.
but if I get rid of the second IIF, then it works.
how do you combine multiple IIF statements in a query?
My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.
Can anyone give me any guidance on how to use IFF statements in microsoft access? or Has anyone got a link to a good website that can help me? I need to use the formula below to determine if the quantity of an order from a store is greater than the quantity that is in stock at the warehouse and produce a message message.IIF([quantityordered])(=[quantityinstock]"enoughstock","notenoughstock") I just dont know where to start, i didnt even know you could enter formulas in Microsoft Access so i really need help
Hi, I have another problem with my Access database now...
I am trying to disable fields when a certain value is chosen. I can do it for one field with this code:
If ALLOCATION_LP = "Other" Then OTHER_ALLOCATION_LP.Enabled = True Else OTHER_ALLOCATION_LP.Enabled = False End If
That one works perfectly well, but I have another one that needs to disable two fields instead. If they choose "No Extant PP", I want it to disable two fields; one combo box - PLNG_PERMISSION_STATUS_2 (which I can get to and it works) and also an "Other" text box - OTHER_PLNG_PERMISSION_STATUS_2 (this is where i get stuck). I have tried two If statements, but whether I'm writing them wrong, I'm not sure.
'construct a where clause as required If Nz(Me.Manufacturer.Text, "") <> "" Then Where1 = "WHERE MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' " End If
Me.list.RowSource = _ "SELECT ID, Description, Par, MaxCoins, PayLines " & _ "FROM MachineTypeQuery " & _ Where1, Where2 & _ <- how do i get something like this to work "ORDER BY Description;"
Exit Sub Where2 comes from another control and has to be filled in before they get to this control. also "where1" and "where2" are set as public stings
I want to check eight Yes/No fields. If none of the boxes are checked I want to display a msgbox. I can link seven of the fields (no problem), when I add the eighth it doesn't work.
Am I limited to seven fields in my statement? Is there a way around this?
Any help would be appreciated.
============
If Me.Printer = 0 And Me.Hardware = 0 And Me.Software = 0 And Me.MSOffice = 0 And Me.Network = 0 And Me.NewComputer = 0 And Me.Profile = 0 And Me.Other = 0 Then
MsgBox "You must enter the type of problem(s) addressed with this customer", vbQuestion, "Type of Problem(s)"
My database has a field named "Note". This field will either have a "Y" or "N" in it. At the bottom of a report I'm writing, I want to count the number of "Y's" (not the "N's") at the bottom of my report. Can someone tell me how to do this? Thanks so much in advance!
i have a list of values that i'm trying to run through an iif, like so:
IIf(([Warehouse Location].[ToAISLE] Like "1" Or "2" Or "3" Or "4" Or "5" Or "6" Or "7" Or "8" Or "9" Or "10"),([Product Inventory with totals].[Available]+[Warehouse Location].[Qty]))
im doing that in a query, not the vb code. now heres my problem: that's not the correct syntax for the Like blah blah blah part. does anybody know the correct syntax for this?
Within my database I have 5 fields that will have either "Yes" or "No" data. I want to be able to have one of these fields look at the 4 others and if they meet a condition have the background of the field change colors.Here's a breakdown of what I'm trying to do:
Field1 Field2 Field3 Field4 Field5
Yes Yes Yes Yes No No No No No Yes
If Field5 has a "Yes" and Fields 1,2,3,4 are all "No" then I want Field 5 background to change color to indicate that a criteria was met.
Hi. I need to export a set of sql statements from my Microsoft Access database in order to put it into my new mySql server. I know that its possible to get the statements off the mysql database, but i cant figure out how to do it in microsoft access. Any help is appreciated
Hello. I have a problem implementing two separate SQL statements in dreamweaver from an Access 2003 database. The queries work fine in Access but is causing many problems in dreamweaver as they are separated.
I very much so need help in combining these two statements to form one SQL statement. Any help will be greatly appreciated. Thanks again.
SQL Statements
FIRST QUERY
SELECT tblApp.Time FROM tblApp WHERE (((tblApp.BranchID)=[Forms]![frmApp]![branch]) AND ((tblApp.Date)=[Forms]![frmApp]![date]));
SECOND QUERY
SELECT tblTime.Time FROM tblTime LEFT JOIN qryAppointmentsOnDate ON tblTime.Time = qryAppointmentsOnDate.Time WHERE (((tblTime.Branch)=[Forms]![frmApp]![branch]) AND ((qryAppointmentsOnDate.Time) Is Null));
I've got a query that is defeating me, but that might be due to a week's worth of insomnia related sleep problems. My issue exists in the following query, of the four IIF statments in the HAVING portion of the query and the need to select all if the statement is false for each one of the four queries, the way it is now partly works by returning all results where none of the values are null, I need to get null results as well. I've tried not including the false action for them, a wildcard to select all and just can't get around this problem. Any help appreciated.
SELECT DISTINCT vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number, [AOI Category groups].ProductType AS Category, dbo_Contact.Source_of_Inquiry, dbo_Company.Rn_Descriptor AS Store_Name, vw_mdb_SalesSummary.Job_Number, Sum(vw_mdb_SalesSummary.Extended_Price) AS Sales1, vw_mdb_SalesSummary.Designer_Code, vw_mdb_SalesSummary.Manager_Code, 0 AS Sales2
FROM ((dbo_Company RIGHT JOIN vw_mdb_SalesSummary ON dbo_Company.Store_Number = vw_mdb_SalesSummary.Store_Number) LEFT JOIN dbo_Contact ON vw_mdb_SalesSummary.Job_Number = dbo_Contact.Job_Number) LEFT JOIN [AOI Category groups] ON vw_mdb_SalesSummary.Category = [AOI Category groups].SalesCategory
WHERE (((vw_mdb_SalesSummary.Order_Date) Between [Forms]![frmLandS]![currentStart] And [Forms]![frmLandS]![currentEnd]))
GROUP BY vw_mdb_SalesSummary.Channel, vw_mdb_SalesSummary.Store_Number, [AOI Category groups].ProductType, dbo_Contact.Source_of_Inquiry, dbo_Company.Rn_Descriptor, vw_mdb_SalesSummary.Job_Number, vw_mdb_SalesSummary.Designer_Code, vw_mdb_SalesSummary.Manager_Code, 0
HAVING (((vw_mdb_SalesSummary.Channel)=IIf([Forms]![frmLandS]![Channel]<>"",[Forms]![frmLandS]![Channel],[Channel])) AND ((vw_mdb_SalesSummary.Store_Number)=IIf([Forms]![frmLandS]![Store]<>"",[Forms]![frmLandS]![Store],[vw_mdb_SalesSummary]![Store_Number])) AND (([AOI Category groups].ProductType)=IIf([Forms]![frmLandS]![AOI]<>"",[Forms]![frmLandS]![AOI],[ProductType])) AND ((dbo_Contact.Source_of_Inquiry)=IIf([Forms]![frmLandS]![source]<>"",[Forms]![frmLandS]![source],[Source_of_Inquiry])));
I have a large database with about 200 queries. I am having a problem with the last few I added. I run them from VB. When I run them the SQL statement disappears, sometimes. If I go to the SQL View instead of the Design veiw all that is left is Select;. I the Design View there is nothing.
