the sign CL Amt is > and the CL AMt is 500'000 and I want to have >500000 in the criteria of the query in order to have only the amount above 500'000.
But is does not work.
I get the message : "The expression is typed incorectly or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables" If some one has a solution would be very appreciated.
I'm having difficulty coming up with a way to exlcuded a group of dates from my Query. I am trying to have a set of dates come up without the dates, between 7/1/2006 and 7/31/2006.
I've tried <> Between #7/1/2006# And #7/31/2006#, and a few others with quotes and such. Any help would be greatly appreciated.
I am trying to store binary values (as decimal long integers) and then use bitwise comparisons to update the values. MS help suggest that the standard logical operators (And or etc) can do bitwise comparison as opposed to comparing boolean logical values for expressions; but I have found no way of making this work.
Is this possible? Any ideas. Doing it mechanically, by splitting the decimal into the relevant parts for any bit is very tedious!
Ihow to formulate a calculated field properly that would look for records that has at least 'annual report' in a specific field.
For example, a record with a field ([DocumentType]) with "2013 annual report", another record with a field ([DocumentType]) with "2012 annual report" and then I would like to count in those records and exclude the other records.
I am currently learning the Totals in Access 2003, using the Group By and it's drop down menu which has all the Min,Max,Sum Count etc Expressions.
I have a table of which I need to create a query, the table contains info with Mechanics names and jobs that they have done, often a mechanics name appears more than once in the table.
Which Expression do I use to query the number of mechanics in the table? When I use the Count the query contains all of the mechanics names including the the ones whose names appear more than once! :confused:
Any help would be gratefully appreciated I look forward to any replies thank you.
I am preparing a database application that will be used by one person in an organization. Frequently , (every year or two) the program will be given to another (usually a laptop) user in the organization as that person assumes the task of the program’s operation and responsibilities.
I would like the user to load the program from the installation CD (made with Wise Installation System and SageKey softwares) on each computer each time there is a brand new user and each time a subsequent user takes over. However, each successive user needs to have the data files passed from the last user.
At face value it seems that having a front end/back end arrangement might be the most desirable. Can anyone jump start me on the best way to accomplish this process to EZ data transfer?
I have a report where my customer wants to be able to input a value and then be able to select whether the report shows values above or below that value. I have a combo box that has 2 values (1=Less than or equal to, 2=greater than or equal to). There is a text box where he inputs the rate to compare against (e.g. $75). When he hits submit, the application stores the values of the combo box and text box into global variable and then I have a public function that can be used to retrieve the values. GetHRate() gets the value to compare against and GetHRateCompare() gets the value to indicate <=(1) or >=(2).
In my query for criteria for rate I have this expression: IIf(GetHRateCompare()=1,"'<=",">=") & GetHrate()
I've tried every combination of double quotes, single quotes, no quotes moving the GetHRate inside the IIf statement and nothing seems to work.
If I just hardcode <=GetHrate() into the criteria it works perfect but that does not achieve my goal of letting him select over/under at run time. Here's the full query:
SELECT qryCustomers_AverageTimeByScheduleID.ServiceName, qryCustomers_AverageTimeByScheduleID.ScheduleID, qryCustomers_AverageTimeByScheduleID.SumOfTotalTim e, qryCustomers_AverageTimeByScheduleID.PropertyID, qryCustomers_AverageTimeByScheduleID.PropertyName, qryCustomers_AverageTimeByScheduleID.MonthCount, qryCustomers_AverageTimeByScheduleID.AverageTime,
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.
It all works pretty much fine, however, the problem occurs because the iif statement tells the code to use either the fixed_price_list table or the price_list table depending on the value of Use_fixed. the iif works fine, its the WHERE clause. because the iif statements tell the query to look at one table or the other, the WHERE clause includes statements from both tables e.g.
queryString2 = queryString2 & " AND price_list.min_quantity <= part_select.quantity " queryString2 = queryString2 & " AND fixed_price_list.min_quantity <= part_select.quantity "
but some parts dont have information within the fixed_price_list table, and thus are not being found in the results due to the WHERE clause. can anyone help?? i tried using a left join but it didnt work how i wanted. in a perfect world i would be able to include a iif statement in the where clause e.g.
queryString2 = queryString2 & " iif Use_fixed =1, AND price_list.min_quantity <= part_select.quantity, AND fixed_price_list.min_quantity <= part_select.quantity) "
This should be simple! I'm looking at something wrong. I have two If-Else statements on the "onCurrent event of a form. ONly the first one fires. Is there something rong with the syntax???
Private Sub Form_Current()
If Me.OptInterviewed.Value = -1 Then Me.InterviewDate.Visible = True Else Me.InterviewDate.Visible = False
If Me.OrientationDate.Value = -1 Then Me.OrientationDate.Visible = True Else Me.OrientationDate.Visible = False
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?