In my table I have the following fields that are tick boxes
Vee Cathedral Round Bilge Bilge Keel RIB Semi-Displacement Keel Lifting Keel
Each record can take a couple of these options. For example record 1 could be ticked for a 'vee' and a 'cathedral' whereas record 2 could be ticked for all of them.
I have been trying to use these to make a search form. I want to be able to chose on ie. 'Round Bilge' and it will search for a record that is ticked 'round bilge'. I have done this for both a combo box and a list box and have used the formula for each field [Forms]![Boatsearch]![hulltype]
When I have clicked run query I have an error message saying: 'This expression is typed incorrectly, or it 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'.
During the course of it's development I have created about twenty tables whose sole purpose is to house data for use in combo and list boxes. They have no other purpose.
I am now in the process of creating the maintenance side of this project and how to manage these lists. In the past I have created a form with a multitude of subforms to manage these lists.
I've got a field in a table that is a multiple drop-down list. In the form, I don't want it to be a drop down list, but I want the options in the drop down list to be checkboxes instead (not within a drop down).
Hi, all... welcome back to the new Access World Forums... glad we're still operational, even if some data was lost. It's the minds, anyway, not the data...
I have a query that pulls from 3 transaction tables.
tblTransactions - lists purchaser, amt tendered, other details tblPurchased_Items - lists products and services paid for tblTherapy_Treatments - lists treatments paid for
(Note: these are not separate lists of available items. They're two subforms' tables, one that deals with insurance and one that doesn't.)
I can pull this data fine, but in a case where someone had 1 treatment, but purchased 2 products, the query lists the treatment twice. I do have unique IDs for each table's items, but am not sure how to "list" each transaction once, but each thing paid for once, also.
For example: Joe Blow comes in and has physical therapy and buys 2 books.
My query will show (bogus fields for illustration purposes): TransactionID Name TherapyID TherapyCost ProductID ProductCost 33 Joe Blow 10 180.00 15 12.00 33 Joe Blow 10 180.00 17 19.00
So, if I total TherapyCost it really gives me twice the correct amount.
Can someone point me to a solution? Pardon my rank ignorance, yet again.
I need to get a query back that may have more than the given number of records on either side, but need to not duplicate in either of the two columns. Works better to show what I need, I think...
Query needs to return: PersID.....Name.....Assist.....Device
Base data looks like this (after using query with Person and AssistType and Device tables):
What I've been asked for is something that looks like this:
1.....Adams.....Dress....Walker 1.....Adams.....Bath.....Lift Chair 1.....Adams.....-----....Wheelchair.......... ===> Null or blank in the empty column is OK; I can work with those 2.....Jones.....Bath......-----............................................. 3.....Case.....Dress......Wheelchair 3.....Case.....Bath......----- 3.....Case.....Feed......----- 4.....Doe......-----.....Walker 4.....Doe......-----.....Cane
I can print it with two subreports (one for each column), but that is running slower than I would like. It's workable if we absolutely have to do it that way, but I'd rather see if I can get it out in one query and use the grouping functions in the report (there are some other fields involved, but they don't really affect what I am doing here)
Is there any way to do a query (even a couple of queries if it takes that to set it up) that will give me what I am looking for?
I have been working with Access for some time managing some databases for my company. But now I have some problems making the database easier to use by automatically creating a Query based on different criterias.The code looks like this:
Sub Multi_value() 'Definitions Dim strSQL As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Set db = Application.CurrentDb
[code]....
The problem is that the "Business Field.Value" is inserted into the Query-criteria with brackets which for some reason are not supposed to be there when I use the .Value-parameter instead of the "Business Field"-parameter.
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform 'Application.TempVars.Add "varcountryselect", "*" SELECT in the listbox "lstlocationsperproject" SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0) Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
I have a (crosstab) query that summarizes information in the way that I need for a module that I have written. However, I've noticed that the each time the module tries to pull a value from the query (every step of a Do loop) it takes seconds, compared to a "real-time" pull from a table. I have had a program running for about 2 days now, whereas a similar program that pulled data from only tables took only 15 minutes. I don't forsee needing the data in its original form.
How do I convert a query to a table? (Export the query to Excel and then import.)
someone wrote this query for me in SQL and there is one part of it I don't understand can someone tell me how to convert this line for access? I don't know what || is
SELECT WE.mth_welfare, WE.id_case_welfare, WE.id_case, OB.id_member, SUM(WE.amt_mtd_assist_expnd), SUM(WE.amt_ltd_assist_expnd), SUM(WE.amt_ltd_assist_recoup), TO_NUMBER(SUBSTR(TO_CHAR(WE.mth_welfare),5,2) || SUBSTR(TO_CHAR(WE.mth_welfare),1,4)) an_mth_welfare FROM noldba.welfare_balance_by_obligation WE, noldba.obligation OB WHERE WE.id_case = OB.id_case AND WE.seq_order = OB.seq_order AND WE.seq_obligation = OB.seq_obligation
AND WE.mth_welfare <= 200708 AND WE.dt_END_validity = '31-DEC-9999' AND WE.dt_end_validity = OB.dt_end_validity AND OB.dt_beg_obligation = (SELECT MAX(c.dt_beg_obligation) FROM noldba.obligation c WHERE c.id_case = ob.id_Case AND c.seq_order = ob.seq_order AND c.seq_obligation = ob.seq_obligation AND c.dt_end_validity = '31-DEC-9999') GROUP BY WE.id_Case, OB.id_member, WE.id_case_welfare, WE.mth_welfare ORDER BY WE.mth_welfare DESC, WE.id_case DESC, OB.id_member DESC;
I have this query which I use with a program called "Query Reporter" - it runs well and gives me the correct answers. Select COUNT (b.case_id), sum(a.amt_to_distribute) Held_amt, iv_d_do_code, cd_reason_status--HELD REASONS-DH-DIST,NX-NO OBL,XS-EXCESS OBL,MW-MANU WKR,HM-NO MHIS,CC-CLOSED From vrcth a, vcase b Where A.Cd_Status_Receipt = 'H' And A.Dt_Distribute = '01-JAN-0001' And A.Dt_End_Validity = '31-DEC-9999' AND Not Exists (Select 1 from vrcth k -- Checking receipt is not backed out where a.dt_batch = k.dt_batch ANd a.no_batch = k.no_batch And a.cd_source_batch = k.cd_source_batch And a.seq_receipt = k.seq_receipt And k.ind_backout = 'Y' And k.dt_end_validity = '31-DEC-9999') And ((trim(A.Id_Case) IS NOT NULL -- When receipt is case identified AND A.Id_case = b.case_id ) Or ( trim(A.Id_Case) IS NULL -- When receipt is payor identified ANd b.case_id = (Select Min(y.case_id) from vcase Y, vcmem Z Where A.Id_Payor = z.member_id And z.relation_code IN ('A','P') And z.case_id = y.case_id ) )) group by iv_d_do_code, cd_reason_status order by iv_d_do_code;
What I want to do is to rewrite the query in Access and get the same results. This is what I came up with which is returning nothing. Would someone please give the two of them a look and tell me what I'm missing the in Access query. Thanks
SELECT Count(b.CASE_ID) AS CountOfCASE_ID, Sum(a.AMT_TO_DISTRIBUTE) AS Held_amt, b.IV_D_DO_CODE, a.CD_REASON_STATUS FROM NOLDBA_RECEIPT AS a, NOLDBA_INT_CASE_STATUS AS b WHERE (((Trim([a].[id_case])) Is Not Null) AND ((a.ID_CASE)=[b].[case_id]) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_DISTRIBUTE)=#1/1/2001#) AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False)) OR (((Trim([a].[id_case])) Is Null) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_DISTRIBUTE)=#1/1/2001#) AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False) AND ((b.CASE_ID)=(select min (y.case_id) from NOLDBA_INT_CASE_STATUS Y, NOLDBA_INT_CASE_MEMBER Z WHERE A.ID_PAYOR=Z.MEMBER_ID AND Z.RELATION_CODE IN ('A','P') AND Z.CASE_ID=Y.CASE_ID))) GROUP BY b.IV_D_DO_CODE, a.CD_REASON_STATUS ORDER BY b.IV_D_DO_CODE;
I have three tables in a database horse, entry, prize and I am supposed to get the result by one query. A list is required of horses that have career winnings better than that of the average horse. List the horse_id, horse name and the career winnings.
A career winning is the sum of all money won by a horse. The solution must include horses that have not won any prizes when calculating average winnings. Remember to exclude the dummy horse entries for Unknown Sire and Unknown Mare.It is way too complicated for me to get the result by one queries as its nested critera.my solutuin is to create 5 queries with each one getting to the result a bit closer.bit by bit, basing on every previous query, my final query gets the result.Below are my 5 queries in numeric order:
1:SELECT entry.event_id, entry.horse_id, prize.Money, horse.Horse_name FROM horse INNER JOIN (prize INNER JOIN entry ON (prize.event_id = entry.event_id) AND (prize.place = entry.place)) ON horse.Horse_id = entry.Horse_id;
2:SELECT horse.Horse_name, [careerwinningssub1].horse_id, Sum([careerwinningssub1].Money) AS careerwinnings FROM careerwinningssub1 INNER JOIN horse ON [careerwinningssub1].horse_id=horse.Horse_id GROUP BY horse.Horse_name, [careerwinningssub1].horse_id ORDER BY [careerwinningssub1].horse_id;
3:SELECT horse.Horse_id, horse.Horse_name, careerwinningssub2.careerwinnings FROM horse LEFT JOIN careerwinningssub2 ON horse.Horse_id = careerwinningssub2.horse_id GROUP BY horse.Horse_id, horse.Horse_name, careerwinningssub2.careerwinnings HAVING (((horse.Horse_name) Not Like "unknow*")) ORDER BY horse.Horse_id;
I have a database with a query that returns a list of printers. This is created by finding all the printers in the database that take a perticular cartridge by code. Sample below.
PrinterName ----------------- Some Printer 1 Some Printer 2 Some Printer 3 ETC
What I want to do is take the results from the query and convert it into a single line of text and place it into a column of a table related to that cartridge code.
So if say the cartridge code '12345' returns a list of printers such as;
printer 1 printer 2 printer 3
I want to take this data and turn it into a line like this;
printer 1, printer 2, printer 3
I then want to take this new line and place it in a column called Desc in another table relative to the original cartridge code.
table should look like this after query. BEFORE: CartCode | CartDesc | Price | Desc 12345 | Something here | 10.00 |
I just started working for a company the other week and they threw this query onto my lap with an unrealistic deadline. My programming experience is primarily in VB6, so I am not completely familiar with coding Access queries.
I was given code that was written for SQL, and the programmer that came up with it is unfamiliar with Access, so I am stuck trying to decipher this. If anyone could help me out, I would greatly appreciate it, as I am thoroughly confused and don't know where to begin. Here is the programmer's original code: while exists(select top 1 accNum from NoteFile where patindex('%' + char(10) + '%',dbtrref) > 0) begin --Insert note into table insert into #TL19Note(AcctNumber, Note) select accNum, (case when patindex('%' + char(10) + '%', dbtrref) <= 0 then dbtrref else left(dbtrref,patindex('%' + char(10) + '%',dbtrref)) end) from NoteFile
--Delete note from Note String update NoteFile set dbtrref = right(dbtrref,len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) + '%',dbtrref)))) where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) > 0
--Delete rows with no more notes delete from NoteFile where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) <= 0 or patindex('%' +char(10) + '%',dbtrref) <= 0 end
--Eliminates any that are CRLR or just spaces delete from #TL19Note where len(Note) < 10
--Update the date update #TL19Note set NoteDate =left(Note,patindex('% %',Note)-1), Note = right(Note,len(Note)-patindex('% %',Note))
--No '@' means no time? update #TL19Note set NoteTime = '0000' where left(Note,1) <> '@'
--delete the '@' - we know which ones have it because NoteTime is null update #TL19Note set Note = right(Note,len(Note)-patindex('% %',Note)) where left(Note,1) = '@'
--Update NoteTime, take time out of note update #TL19Note set NoteTime = left(Note,2) + substring(note,4,2), Note = right(Note,len(Note)-patindex('% %',Note))
I have a query which pulls together several other queries. It works fine in Access but I also need it to work on a MS SQL server.
the SQL is as follows;
SELECT Client.ClientName, Entity.EntityName, IIf([101].[ProductID] IS NULL, [-], [X]) AS TA, IIf([301].[ProductID] IS NULL, [-], [X]) AS TR, IIf([302].[ProductID] IS NULL, [-], [X]) AS CU, IIf([304].[ProductID] IS NULL, [-], [X]) AS TC, IIf([502].[ProductID] IS NULL, [-], [X]) AS FA, IIf([503].[ProductID] IS NULL, [-], [X]) AS MO FROM Client LEFT JOIN Entity ON Client.ClientID = Entity.ClientID LEFT JOIN VIEW_PROD301 AS 301 ON Entity.EntityID = [301].EntityID LEFT JOIN VIEW_PROD302 AS 302 ON Entity.EntityID = [302].EntityID LEFT JOIN VIEW_PROD304 AS 304 ON Entity.EntityID = [304].EntityID LEFT JOIN VIEW_PROD502 AS 502 ON Entity.EntityID = [502].EntityID LEFT JOIN VIEW_PROD503 AS 503 ON Entity.EntityID = [503].EntityID LEFT JOIN VIEW_PROD101 AS 101 ON Entity.EntityID = [101].EntityID;
I want to have a question in my query that does not get answered by date but by a concatenated field named "MonthYearPeriod".
We always forget how many days in a month. But we remember the month and year. So for say a Sales Range of Nov 2006 to Oct 2007, we prefer to respond to the criteria question as: 11-2006 and then 10-2007.
FiscalMonth and FiscalYear are both Long Integer number fields in the table.
Query field concatenated: MonthYearPeriod: [FiscalMonth] & "-" & [FiscalYear]
criteria line: Between [Month-Year START DATE] And [Month-Year END DATE]
However, i get other crazy time periods. So this is not working. Maybe on the Query field box, I need extra coding to convert the number to text?
I assume that a Dash sign between the month and year are fine. Thanks
I have a string value that comes out of a query in the form of
01012015 02012015 12012014
etc...
where the first 2 digits are the month, the next 2 are the day, and the next 4 are the year.How do I convert these values to a valid date that can be queried on?
I have a main form where the user can click on a button that opens another form. In this form, the user can make selections from one listbox and they are copied to another listbox. How can I save the contents of the listbox into a field that is part of the record on the main form? Can this be done? I have a listbox on the main form where the selected items will be displayed and each record of the main form will have different values for the listbox.
I am building a query for a university and the table i am working from has a column with every course run by the department.
How do i create a query that allows me to add a drop-down menu to the course instead of having to write out the course name everytime i want to query the number of pupils on a particular course.
I have two queries which are both similar. They consist of a list of names with a ‘count’ function to see how many times that the name occurs. Some of the names in the two lists are identical and some are different.
I am looking to create a query (or smoothing) that takes the two sets of numbers, matches them by the name (NameID). If the name does not exist in one of the lists then put a 0 (see example below). Once I have this I then want to create a stacked bar graph to show the results.
I have two list's of names. I'm able to run a query to find the matches but the ones that don't match i also need to id? For ex. List a has 200 names List b has 130 names, how can i find the 70 names that don't match?
Ok! So I've binned the pivot table and gone for a continuos form option that allows the double click event that I'm after. A new problem has appeared.
On my main form I have 3 combo boxes (programme, product & company). When I click on any of the combo boxes i get a list of everything in the column it relates to. That works fine but it looks very cluttered. I'd like to have everything listed once only so that when i click on a programme it will filter the subform to show only companies working that programme.
Hopefully this little thingy I've put below will explain it a bit clearer
At the moment the programme combo box is displaying....
I havea combo box on a form which looks up values from another table. The table containing the values which are looked up has another field where the data type is Yes/No. I want to limit the options in the combo box to just those which are set to Yes in the source table.
I know I need to use WHERE in the code for the combo box, but cannot find the correct syntax.
