I'm trying to limit the records on a subform via an option group selection. The group has 2 options: optionTrue (option value = 1) and optionFalse (optio value = 2). I have the following code in my query criteria of the true/false field.
IIf([Forms]![FrmHome]![frameProcessed]=1,-1,0)
The false part works, but the true part doesn't. I've tried many variations using true/false, using checkboxes, etc. and nothing works.
The database is SQL Server if that matters.
Any suggestions?
Also, is their a way to have an option for True or False or ALL?
In a form, the value of any field may determine if the other field will be true or false. For example in my form, inventory, if value in code is equal to 2 then the Field Table will be automatically false.
I have a query that shows banned users (I work in a school). When a student's ban has ended, a tick box is checked in a form linked to tblBannedUsers to show they are no longer banned.
The query itself simply queries all records in tblBannedUsers with a username field (UserID) taking search criteria from a box on a form.
However, I want to filter out the students that are no longer banned (those with a tick in the checkbox). To do this, I thought it would be a simple case of editing the query, and in the Ban Lifted field criteria, use =False to say I only want those records with a tick.
This doesn't work and I still get all records given the combo box filter (which are just filtering for a username...if I leave it blank it gives all records via Is Null).
What should I be putting in the criteria to filter out those records with a ticked checkbox?
Thanks,
Steve Swallow
EDIT: I've just done a test query and <B>No</B> is the criteria to use, but when I use this criteria in my query which also take data from a form's combo box it ignore the <B>No</No> criteria.
In access report, I'm trying to change color of text in specific records based on the true/false value in another record. Works in forms using conditional formatting, but won't seem to work in a report.
Here's what works in forms : IIf([2009 Symposium]=true, forecolor=255 ....this changes the records to red.
But using the same expression in a report doesn't change the text color.
I'm really not sure how to go about this. I'm creating a course booking system and when creating a booking I need to check for current bookings with the same employee and course id's (i.e. the employee is already booked on the course). The query takes the employee and course id's from a form, and is initialised when the 'book' button is pressed. It correctly selects if the person is already booked on the course but I want the query to return a value to the form i.e. if it returns null/false then the booking can be created but if it returns a record/true then the booking already exists and a message box can be displayed.
I'm not sure if I'm going about this the right way, can anyone suggest how this can be done as described above or suggest a better way of doing the task?
Is it possible to filter a forms records by using a boolean True/False field. I want to show all current records for option 1, Expired records for option 2, and all records for option 3. The form is based on 'tblMembers' which has a field [Expired] which is a boolean yes/no field formatted to true/false.
There are around 100,000 records to update. Would a SQL Statement be more efficient?
It is a local table being used to sum up the results of a handful of rules. The columns can only hold True or False (datatype) If and only if all columns are True - then true
MyRow T T T T T T T T T T - Sum in next column is T MyRow T T T T T T F T T T - Sum in next column is F
Speed is very important. The Recordset for a single row is still open on the Currrent Record since the Update just finished.
Code: RS_RE_1SegStatusProfiled.Fields("Total") = (RS_RE_1SegStatusProfiled.Fields("RE_1") AND CStr(RS_RE_1SegStatusProfiled.Fields("RE_2") AND (RS_RE_1SegStatusProfiled.Fields("RE_3") ' and so on
My guess is that since the recordset is open to the current record on a local table, it will be efficient to just re-read all of the values and And them together.
Since I have code writing to each record, I could also go through all the extra assignment of a local variable.
I have a form that shows the records found in a query if the completed tick box is false. this is fine, but i have also put the tick box on the form, and wish the user to tick it when they are done how ever they, get a beeping error and wont let you change it to true. Can anyone suggest the resolutoin for this.
I want to create an opion group with two rad buttons in it. The values of those button wont be values but as true or false in order to use it in another text box to performa calculations. EX.
=IIf([radNew],"Hello",IIf([radUsed],IIf([txtAgeofCar]<184,[txtExciseinTotal]=Null,IIf([txtAgeofCar] Between 185 And 365,[txtExciseinTotal]*(-0.15),IIf([txtAgeofCar] Between 366 And 1095,[txtExciseinTotal]*(-0.2),IIf([txtAgeofCar] Between 1096 And 1825,[txtExciseinTotal]*(0),IIf([txtAgeofCar]>1825,[txtExciseinTotal]*(0.25))))))))
As you can see radNew and radUsed are the Options Buttons. Its not working in an option group.
My client has an Access database where the value of a checkbox is stored in a field called Exclude_PropCount as -1 or 0. I am trying to get this form working online, but have been unable to successfully store this value in the db. I have set up a simple page to test it, and my relevant code is this:
sql_save = "INSERT INTO HVM_Data (VC, Exclude_PropCount) VALUES ('a9', '-1')"
con.Execute sql_save
set duplicateVC1 = db.Execute("SELECT Exclude_PropCount FROM HVM_Data WHERE VC = 'a9'")
response.write duplicateVC1("Exclude_PropCount")
But no matter whether I have '-1' or '0' in the INSERT statement, the response.write line always returns "False". And of course I've tried inserting "true" and "false", but it only accepts an integer.
As exceptionally bad timing would have it, my server is slightly fubar and I can't download the database to my PC to see what's actually being stored there, but looking at the above code, do you see anything I'm doing inherently *wrong*? This is my first time with checkboxes so I won't be surprised if that's the case. Hints?
Using DoCmd.TransferSpreadsheet I'm exporting a table which has a couple of Yes/No fields formatted as Yes/No. However when I export, they appear as TRUE/FALSE? Am I doing something wrong?
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,
My first question is how do I append 00001 so I can have a value for the false side of 9123400001 instead of 912341...My second question is why does it prompt me to enter parameters? It also produces all false values from the if statement...What I am trying to do is use a column that has either a Y or a N and using the if statement to correspond with different formulas depending on the Y or N.This what I have so far.
I have created my tables and form and am now trying to run reports to organize the data. I have figured out how to group the individuals by group and treatment, but can't figure out how exclude individuals from the final list if they are already in another. As background, some of the individuals are eventually excluded from the experiment, though I keep the initial data. When the exclude individual checkbox is checked ("True" on the report) I do not want the individual to be listed in the "False" list, even though there are entries for that individual when that checkbox wasn't checked (when it was "True"). This is so I can get an idea of the current totals in each group.
Implant Period >Treatment >>Exclude individual? >>>Individual ID
I feel like this should be a fairly simple task, but I cannot figure it out... Maybe there is coding to exclude individuals from the "False" list if they are already listed in the "True" list?
I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!
I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!
I have 3 queries that pull from 3 dfferent tables. All 3 tables contain a field called Acct but the other fields in the 3 tables are different. What I would like is to have a form field that the user can input an account number and depending on which query the account number is in, that query and result will display. I have been trying the dlookup function but can't seem to get the coding down. Also, if the account number is not in any query I would like a message box to display saying it can't be found. Thanks for any help!
I export a query as csv to upload to another database on a web site. When that csv file has been exported I'd like the query to be emptied, so that the next time I export I don't export duplicates. What's the easiest way to do that?
I've been thinking of having a field in the table with a check box that is checked when the file is exported. The query then selects only those records where that box is not checked.
But how can I check those boxes on Export rather than when I run the query? The reason being that I may want to preview by running the query, without having that affecting the check box.
I have a from which has a sub Form in it which uses a query of a table which is filtered, In the main form I want a sum of the data in the subform for a particular field if TRUE.
In the main form I have a text field with the Syntax =DSum("[Cost]","RepairInformation","[Labour] =.True."), but this returns an error, I have no issues for the total cost for with I have a Text Box in the subform which returns the Total cost in the main form.
What are my options to have a conditional sum for a particular condition.
