I have a combo box that selects a record on a form.
The combo box is based off of a query and I want to be able to filter a field on the query that the combo box is being populated by.
The problem is that it's a yes/no field I want to setup criteria for and I haven't been able to get an IIf or Switch statement to work correctly.
The issue is that I want to filter the combo box from either two separate combo boxes and a check box or from a group of radio buttons or a combination of the two.
The idea was to have a drop down with a list of enrolled members. The second drop down would requery the first one and would allow you to select a class. The third drop-down would allow you to select, "All", "Students", or "Teachers". The check box would then allow you to view archived members who have a "Yes" in the Graduated column of the query.
The class drop-down I have figured out fine on how to filter. The issue lies within the selection of a member type. I tried using radio buttons for "All", "Students" and "Teachers" but the problem was when I built my IIf or Switch statement, I couldn't figure out how to make the "All" values not put any criteria into the query. My statements looked like below:
On I form I have a combo box called cboMobileStatus.Its row source type is "Value List".The row source is: 1;" All";2;"Has";3;"None".I have a query based on table "tblCustomerContacts" and I want to limit the records returned based on the value of the combo box.So if the user selects:
* All (1) I want all records returned. * Has (2) I only want records that have a mobile number returned * None (3) I only want records that do not have a mobile number returned
The field mobile is text (to keep the leading 0)I thought of using the Switch function in the criteria of the query for field Mobile like this:
Code: Switch([Forms]![frmPrintCustomer]![cboMobileStatus]=1,([tblCustomerContacts].[Mobile]) Like "*",[Forms]![frmPrintCustomer]![cboMobileStatus]=2,([tblCustomerContacts].[Mobile]) Is Not Null,[Forms]![frmPrintCustomer]![cboMobileStatus]=3,([tblCustomerContacts].[Mobile]) Is Null)
but the query does not like it!Am I on the right track, and if so, how should I modify the code?
I am new to Access and was trying to figure out a simple Count. What I want to do is Count Column A if Column B has "".
I have looked around and am sure this is easy, can anyone point me in the right direction. I use this for another item but dont know what to add to make it work like I would want.
When I run a query, whenever the criteria is not met, I want the column header for the row to not show up in the query.
I am running a select query and I have 10 items on the query, I select what I want via check box and then hit OK to run the query. I want the fields that are not checked to not show up in the query at all. Right now I still get the column header, any way to get rid of it. Thanks a lot for any help.
This seem to be simple for single criteria but i am not able to do it multiple. I want to restrict user to customer list they r able to see in form combo box drop down list.
users are in employee TBL employee group criteria is in tbl_Groups which contains value Admin,Read Only, manager etc employee r grouped through tbl_AssignUsersToGroups. customer r grouped in customerType TBL. i.e class1, class2 etc created EmployeesClassTypeRestriction TBL to store restrictions for employees
Restrictions criteria:
1-employee can view only specific class which includes all customers in that class 2- employee can view only specif customers in a class only. 3-employee can view only specif customers in a ANY class. 4- No criteria No restrictions and can view all customers in all classes( For Admin )
Tried to used Tempvar criteria in customer combo box qry but it works for above point 1 only.
I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.
Background : In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.
I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).
My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.
I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done?
Some help from cyber space is what I need me thinks :confused:, I am using a switch function in an access query to convert 24 to 624 and 28 to 628 and so on for about 5 entires however there are numerous entries that might not meet my criteria that are errorrs can I just simply place in there a default option if the rest are not made like in a case statement.
I know somebody is going to suggestion placing this in a macor/function but would prefer to not have to do that if possible, but if I do I dot.
Any help greatly appreciated. Come on Cyber Space dont let me down! :D
Cheers, Tempest "Life is like a box of choclates you never know what your gonna get"
I'm using the simple test expression below to assign a value of 1 to records with [saledate] between the two dates indicated in the code below and I'm just getting a null result. Can the between function not be used in this way?
Code: Hughes Test: Switch([saledate] Between 1/1/2014 And 3/14/2014,1)
i'm trying to use a switch function in a query but i'm unfamiliar with the format a bit. here is what i'm trying to do...
switch([Radios]![Warranty] and [Radios]![HousingWarranty]=true,[Radios]![Change front housing]*(25/60*28), [[Radios]![Warranty] And [Radios]![PortableHousingWarranty]=true], [Radios]![MaterialsUsedPrice1])
basically first statement is condition second is execution. I need to add more but i'm getting an error from this much...if I was doing this in vb.net I would just do if, ifelse, ifelse, ifelse...but...
I am trying to use a Switch expression as a column heading in a crosstab query to generate statistics for how much work is done per client (Requester). Each requester is referred to by acronym, so it may look like "DOD/OUSD/FTT/ATA/B", but I want that column to include everything that starts with DOD, instead of listing each instance separately. This is my expression so far:
When I run the query, the column "ATAP" shows up perfectly with all the correct info, but it's as if I never entered "DOD*". Nothing appears, and since I didn't add anything in the Switch function for what to do with other requesters, the second column shows up as <>.
Do wildcard *'s not work in Switch functions? I don't get any errors, but this is definitely not the information I'm looking for.
Is there any other way to include all Requesters that start with DOD in one column?
This is my SQl if that's helpful: TRANSFORM Sum([Q and D Database].[Word Count]) AS [SumOfWord Count] SELECT [Q and D Database].[Source/Target] FROM [Q and D Database] WHERE ((([Q and D Database].[Out Date]) Between #1/1/2006# And #12/31/2006#)) GROUP BY [Q and D Database].[Source/Target] ORDER BY [Q and D Database].[Source/Target], Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP") PIVOT Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP");
Hi guysI have a problem with filtering a query in criteria using either iff or switch builtin function. my codes look like thisLike Switch([Forms]![frmMain]![grpReq]=1,"SysReq*",[Forms]![frmMain]![grpReq]=2,"ACR*",[Forms]![frmMain]![grpReq]=3,"ACSR*",[Forms]![frmMain]![grpReq]=4, not like "SysReq*" or "ACS*")the function works fine except for value 4 highlighted in bold. The query just didnt return the result i want. I suspect its the switch function not liking to evaluate the "Not like" part. I dont know how to go around this as i can only base on the prefix, namely SysReq, ACR, ACSRThank you for your ideas...
I wanted to have a switch board available to my users. On the switch board I wanted to have a combo box where the users have multiple choices. For example, if a hotel was offering choices. The choices could be:
Room Reservation Conference Room Reservation Catering Dance Hall Transportation Local Guide Information
After they select one of the choices, I wanted to put command buttons on the side that would take them to a form specifically related to the choice they made. So, some of the command buttons might be:
Send an Inquiry Call Make an Online Reservation View information only
I haven't seen any Switch board that has choices so I don't even know if this is possible. If so, I'm happy to try.
Meanwhile.... I've never done VBA or any type of programming. I'm a beginning Access user.
You know on forms, you have those little Left and Right arrows (< >) at the bottom of the form, and you can click on them to switch between records... yeah?
Well, how can I make the form switch between records with VB. Lets say I want a VB script that jumps to the 5th record in the table, how would I do that? Normally you would just click the Left arrow 5 times... but I want to do it with code.
I have a query with data grouped by time, with the goal being to combine values for each 5-minute time interval over the course of several months into a single 24-hour period (resulting in 288 records). Most of the time values, when converted to decimals, are between 0 and 1. However, some of the times are actually greater than 1. They still display correctly in time format, but are unable to group with the rest of the times since they have different values.
I need to construct a statement to a.) Keep the given value if SensorTime<=1 b.) If SensorTime>1, subtract an integer value (between 1 and 4) so it is between 0 and 1 and will then group with the rest of the times
I experimented with a SWITCH function but I'm not sure how to specify "1<SensorTime<=2." I think the current language will subtract up to 10 from numbers less than 1 (-1 if less than 2, -2 if less than 3, etc.). Here's what I have so far:
SELECT Sensor3.SensorDate, Switch(Sensor3.SensorTime<=1,Sensor3.SensorTime,Sensor3.SensorTime<=2,(Sensor3.SensorTime-1),Sensor3.SensorTime<=3,(Sensor3.SensorTime-2),Sensor3.SensorTime<=4,(Sensor3.SensorTime-3),Sensor3.SensorTime<=5,(Sensor3.SensorTime-4)) AS Expr1, Sensor3.SensorTime, Sensor3.Volume, Sensor3.Trucks FROM Sensor3 WHERE (((Sensor3.LaneName)="NB1" Or (Sensor3.LaneName)="NB2" Or (Sensor3.LaneName)="NB3"));
Can anybody suggest a way to subtract the correct integer value from each time to get a number between 0 and 1?
Using a toggle box to ask the following question, i have data that contains either OPEN or CLOSED against each record. I tried using the following in the criteria for the specific data to either select all the OPEN records when the box is clicked and i would like to select all records OPEN and CLOSED when it is nto clicked
Is it possible to create a VBA on the AfterUpdate() that flips a value based on a condition?
For example, if in a text box (Gender) there is a value of "Male" and in a combo1309 the value is "No", can Gender be switched to "Female" automatically?
I have a front-end/back-end database that another programmer has done some work on for me. He worked on his own copy which wasn't linked to our data. Now I'm ready to integrate his work. What I would like to do is take his improved front-end and link it to the existing back-end tables. All the table names are the same, and none of the fields have changed. Is there an easy way to do this?
I need command button(s) to switch between subforms on a mainform in only one subform window (one subform already set as default). So in other words, I don't want a command button to open up new forms (I know how to do that); I need to minimize the amount of subforms and forms I have open by only having a few major user interfaces.
Sorry for the terse beginning, I thought ^ would help when u run your mouse over the topic and get the first line of the message so ppl knew what I was on about instead of "hello please help me" hehe. Now for greetings: Hi folks, great site, helped me silently for awhile but now I gotta ask for help.
In the scheme of things, I'm still a newb; I've learnt a whole lot real fast, but I need a "short cut". I've looked in the forum and found one relevant message but it was a little too brief (a higher knowledge level than mine).
The message I referred to earlier mentioned "toggling visibile property" (?) and also "setfocus" - I need that clarified and fleshed out please. *Note* My Visual Basic: IsNull, Else If Not IsNull (Me.ShockingandBasic). Me.Junkee Brewster = learns really fast when shown. I know a bit about command buttons and I don't usually have problems with forms or anything like that, but I cannot conceptially understand how I can have extra subforms linked to show up in my default subform window
I would rain diamonds (I'm working on it) on the person who has the time/patience to give a little "step-by-step" dialogue (including how to chuck in those extra subforms); don't be afraid to patronize me, spell it out if you like (err, please do infact) :) Thankyou kindly.
I would like to create a command button on the header of a form. The button should perform a toggle switch: click once to activate the task and click it again returning original state. I can create the command button to perform a task but do not know how to return to original state. The button should have indicator showing which state is in.
