Using IIF Criteria To Choose Between Two Different Like Selects
Aug 26, 2014
I am setting up an Access database for a small school. The database contains a record per person. When we get a duplicate lastname field we set a family_id field to lastname followed by a number.
So for example the first Smith family needs to family_id. But the second Smith family gets a family_id of smith1. To select an ordered list of Families I need a query that will use the family_id if it is set and if not use the the lastname to collect family members.
My query uses a presorted table, CurStuByGrdTbl , of current students to produce the families directory by grade and alphabetized.
The WellSchoolCommunityAll table is the entire database. So if the student entry has a valid family_id, matching pattern "*#" then
I perform a Like with family.id otherwise I perform a Like with lastname.
This query gets me nothing, no records.
IIf(([CurStuByGrdTbl].[family_id]="*#"),[CurStuByGrdTbl].[family_id] Like [WellSchoolCommunityAll].[family_id],[CurStuByGrdTbl].[lastname] Like [WellSchoolCommunityAll].[lastname])
I created a form that allows the user to choose the criteria that they want to see on a report using =Forms!formname!controlname in my query. It works great but I want to also allow the user to choose nothing and return all records instead of limiting them to choose just one type of record. Is this possible? Before I created the form my query had the [Enter parameter] on one criteria line and [Enter parameter] Is Null on the next criteria line and that was working great for my use but I need to create a simple form for other users.
Hello, i was wondering if you could help. I want to run an update query, taht updates prices in a table. But i would like to be able to choose the criteria when the query runs, for example a message box appears, that will allow me to enter a certain amount for the prices to change by. Could someone guide me on how to do this? thankyou, its much appreciated
I am building a database that calculates freight costs for shipments.
I have one table that has order data with fields such as [Order Number], [Origin], [Destination], [Service Level], [Weight].
The service level is one of 4 values: PRE 9, PRE 12, NEXT DAY, NEXT DAY +1.
The origin and destination use country codes such as BE, AT, CZ, PL for Belgium, Austria, Czech, and Poland respectively.
I also have rate sheets from carriers such as DHL and UPS and they provide their rates in the following format (numbers are made-up as real rates are confidential): [Origin], [Destination], [Service Level], [0 to 0.5 kg], [0.5 to 1.0kg], [1.0 to 1.5kg], [1.5 to 2.0kg], etc all the way up to 30kg. The rate is then in the appropriate column and the row gives the origin-destination-service level combination.
(sample freight rate table uploaded as zipped excel sheet in attachment)
I need to return the rate from the appropriate row based on the [Origin], [Destination] and [Service Level] fields (easy enough), which is easy enough.
The hard part is selecting the correct field to return. I tried usign a Choose expression but Access rejected it saying it was too complex (maybe because too many choices?).
So I'm stuck. And working towards a deadline... :-)
I have this code that works fine exept for one thing. If I have two names that are the same it deletes them both. currentdb.execute "insert into charmer91 (last,first) values ('"me.list0 & "','" & me.list0.column(1) & "')" currentdb.execute "delete table1 (last, first) from table1 where last ='"&list0 &"'" How can I change the delete statement so it only deletes the selected name I have in list0, ie.. the highlighted one? Basically Im moving data from one table to another via listboxes.
So instead of having all of my data on a single table which is filtered out as I make selections in my initial 3 combo boxes, I'd like for each set of data to be on it's own table. Instead of filtering out the irrelevant data in a single table, I'd like the initial 3 combo boxes to instead filter out the irrelevant tables. My main reasoning for doing this is that I figure it would first off save me much trouble in the future when editing data within the tables and also that one huge data table would slow down Access eventually.
I'm creating an Access data entry form for computer installations.
Basically the entry form will choose a software and license, then select one or more computers to install the software on.
Then later we can create a report that can track what softwares are installed on the computers, or track what computers has what softwares.
Now my question is for each record of the installation entry form, after we select the software, then we will choose a computer name or more computer names. If just one computer is easy, but how can I choose multiple computers, which control should I use? and how can I store the computer name list in the installation table, do I have to make each computer installation a record in the table or put multiple computer names in one record in the field like memo, but if I use memo, later it seems i cannot group or search memo field?
Can anyone give me some ideas about what is an easy design solution or samples about these?
why this query pulls every record twice and what I can do to fix it?I did it in design mode because I'm an amateur, but here is the SQL.
SELECT [Patient Data Table].[First Name], [Patient Data Table].[Last Name], [Patient Data Table].[C MRI Facility], [Patient Data Table].[C MRI Sched Date], [Patient Data Table].[C MRI Scan Done] FROM [Patient Data Table], [Date Range] WHERE ((([Patient Data Table].[C MRI Scan Done])=Yes) AND ((Month([C MRI Sched Date]))=[Date Range]![From Month]) AND ((Year([C MRI Sched Date]))=[Date Range]![From Year]));
I'm trying to run a pass thru query into a sybase server from Access. The query contains multiple selects which Access doesn't appear to like very much. The query works fine when run in Sybase.
Is there any way of circumventing this problem?
I could split the three selects into three seperate queries to view the individual results but the third set is dependant on the previous two sets and so therefore is difficult (impossible?) to seperate out as a third individual query.
into #temptable1 from SET_VALUATION s, INSTRUMENT i where s.id_imnt = i.id_imnt and s.id_posn_ref is NULL and s.id_imnt_swap is NULL and s.am_pnl_eqty > 0 and i.id_typ_imnt != 'SFX'
into #temptable1a from SET_VALUATION s, SET_VALUATION_HIST sy, REFERENCE_DATA r where s.id_prtf = sy.id_prtf and s.id_imnt = sy.id_imnt and s.pr_imnt_mtm_prev != sy.pr_imnt_mtm and s.am_mv_prev != sy.am_mv and r.dt_bus_lst = sy.dt_bus
into #newtemp from #temptable1 t, #temptable1a tt where t.Portfolio_ID = tt.Portfolio_ID
delete from #temptable1 where #temptable1.Portfolio_ID in (select Portfolio_ID from #newtemp) delete from #temptable1a where #temptable1a.Portfolio_ID in (select Portfolio_ID from #newtemp)
select "Where id_posn_ref is NULL and id_imnt_swap is NULL is the only criteria" select * from #temptable1 select "Where SET VAL's pr_imnt_mtm_prev does not equal SET VAL HIST's pr_imnt_mtm, is the only criteria" select * from #temptable1a select "Where the output satisfies both Where id_posn_ref is NULL and id_imnt_swap is NULL AND Where SET VAL's pr_imnt_mtm_prev does not equal SET VAL HIST's pr_imnt_mtm" select * from #newtemp
drop table #temptable1 drop table #temptable1a drop table #newtemp
Simple query, but I need to roll the results up with others and a null return is hurting me. I have been chasing my tail around with the nz(), input masks, IIF statements, only to prove my ignorance and possible insanity for expecting different results.
Here we go:
SELECT Count([LDB LOG].DSTHTL) AS ["tot120"] FROM [LDB LOG] WHERE ((([LDB LOG].[LDB Rcvd]) Between Now()-120 And #1/1/2008#)) GROUP BY [LDB LOG].[T-7 Date] HAVING ((([LDB LOG].[T-7 Date]) Is Null));
This is being used to count open items into age buckets, the above is the greater than 120 days (discounting anything prior to 2008). If the T-7 Date is null the item is open. What is happening is that no records are being selected, resulting in the null value back.
Any help would be greatly appreciated as I have spent an unrealistic amount of time on this.
I have created a database.On opening the [Company] form opens up.After pressing the 'Add new company' button it creates a new record. when typing immediately into the 'Company Name' field, access highlights all the text whilst your typing making you delete what you have just typed.I have tried several macros and vba at various different events to try and solve including 'Gotocontrol', 'refesh'. I have even tried SelStart.
I have a field that is pre filled in with a value. The name is "Business".Then there is a combo box with various values. If the user would select a value that does not match with values that can be selected if in "business" a value is selected, it should show a message that the selection is wrong and user needs to select the correct one.
Example:If in the field "Business" the value "Food" is selected, then the possible values to be selected in the combobox would be: Fruit or Meet or Fast Food. If the value in "Business" would be "Wood" only selections could be made: Talble or Chair or Cabinet.So, if a user select "Food' in "Business" and in the combo box "Chair", it should show a message that the wrong selection was made and don't let them use the wrong one.
I open a form (EntryBasic) for users to enter data in, which is then saved to the only table in the database (MasterTable). The form (EntryBasic) has a list box with four choices: red, white, blue, orange. The button that opens the form is placed on another form (MainForm) The form (EntryBasic) record source is MasterTable.
A user is on the MainForm and needs to add a new record related to red. When user clicks on the button (NewRedEntry), I need the form to open and the list box pre-selected to red.
Is there a macro or VBA code that might simply execute this action, preferably something that can be attached to the NewRedEntry button?Even better, if there are two list boxes, code such code be applied to same NewRedEntry button, where both list box values are pre-selcted with the click of this button?
I am trying to find out if there is a way to track the order in which items are selected from a list. I am a dabbler and any keyword searches that I can think of don't bring up what I am looking. So, here is what I am trying to do:
I have a table of symptoms with 3 fields (ID, Category, and Symptom).I have a combo box that will allows the user to pick a category (using select Distinct on category field). I then have a list box populated with all the symptoms that have a category of whatever the user selected. The problem I am having is that I need to somehow track the order in which the user selects symptoms and then save that order for future reference and to be printed on a report. The order is important because the most severe symptom needs to be listed first.
I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.
I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3
My query is: SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header FROM tblSomething WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));
The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.
I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.
I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,
Code: SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));
So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?
Is there a way to automatically choose a value such as 0 if the expression goes to a negative number?
E.g. I have a query that will subtract a numeric field from 10. If 10 - [numericfield] is less than 0, then I only want it to display 0. Otherwise, it can display the value if it is positive.
I am looking for some help with a particular problem. I have a report based on a query and at the moment it is ordered by a particular column.
What I want to be able to happen is that the user chooses what they want the query (and subsequently) the report to be ordered by before they run the report by means of a combo box or something like that.
Hi, I have a requirement to filter some queries periodically on the date field. What I want to do is create a form that lets you choose which query to use and filter and not create alot of single forms. The filter will be only be applied whilst the form is open.
i would like in a form for a combo box to be able to select an item from a table and input relating information automatically into other boxes in the form..
I have 3 tables: Table 1 has product code and product description. Table 2 has invoice number company details, address etc. Table 3 has product code and product description qty and invoice number.. Table 3 relates to table 2 by the invoice number and table 3 product code looks up the product codes available in table 1 and also table 3 looks up the list of products descriptions in table 1 using the combo wizard. This means the wrong code can be put with wrong description. What i would like to know is how i select a product description and the product code in the form fills out automatically?? i hope this makes sense please helppppp!!
I would like to set up a query that uses two tables. One of the tables, the 'main table', wil be used every time the query is run. Each time a new record is created in the main table a 'new table' is created containing a subset of data - the name of this table uses a ref ID from the main table. I would like to be able to select which 'new table' to use in the query as part of a parameter query.
eg. Main table record 1 has a field JID of J0001 and a corresponding 'new table' titled J0001. record 2 in main table has JID J0002 and creates a 'new table' J0002 etc
so I would like to set the query up such that when a record is chosen in 'main table' the query knows the name of the 'new table' corresponds to the JID of the record in 'main table' ....
Have seen the same question asked but no answer - is this possible ?
I want to have users be able to choose query "x" "y" or "z" from a listbox. Upon selection of query "x" "y" or "z" from the listbox, I want the query to run? I want to run this query from form "RunQueries" Does anyone know how I can do this? Thank you in advance for any suggestions, I appreciate all the help I can get
I built a db for tracking and entering sales info but I'm having a problem with one of my combo boxes. To make entering data easier, I created a combo box that you can select a sales persons name from, then based on who is selected, their Title and Office location also populated.
The problem I have is when I want to manually type in the Name and other info for a temporary sales person. How can I allow users to choose from the drop down or just type it in if that person is not in the list?
