Concatenation of query text


Can anyone please give me an Access 2000 VB module that will concatenate [Portia - 1 query.text1] with [Portia - 1 query.text2] and type the result in textbox [text]
Posted On: Monday 22nd of October 2012 05:32:03 AM Total Views:  338
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Distinct records from multiple columns in SQL query

I am using ASP with MS Access Database and have the created the following query for my recordset Code: SELECT galleryID, picCat, galleryName, galleryDate, picID, picName FROM tblGallery INNER JOIN tblPics ON tblPics.picCat = tblGallery.galleryID I now wish to display the recordset in a repeating table but want to remove any records returned with the same Code: galleryID I have been searching for two days and although I have found a lot of similar issues which suggest using DISTNCT, GROUP BY etc... I have not yet been able to find a solution that works. I am now running out of time for my deadline so any feedback/help would be highly appreciated Cheers Kaden712
VIEWS ON THIS POST

165

Posted on:

Monday 22nd October 2012
View Replies!

Non-updatable query issue

I've got a small DB that's been developed for materials management duties at an electronics manufacturing business. We're a contract manufacturer, who builds circuit boards of our customer's designs, using their Bill of Materials. My problem is, I need to pull a new piece of data into a form, and that requires aggregation... and I'll explain why below. (the form is built on an updatable query over a star-schema design) That aggregation (I think) is causing the entire query to become non updatable - I need a solution! About the query: It's built on a star schema, the dimension tables are used to populate drop-down selections in the form, and the only table being updated is the central fact/matrix table. The sourcing of the parts for these electronic assemblies throws an interesting twist- Essentially I have three potential part numbers for any given part that goes into these assemblies: BOM PN - the part number specified by the customer. Used PN - sometimes, purchasing has to buy an alternative part that has the same characteristics. Customer PN - Customers sometimes assign their own part number for reference, particularly when they don't require a specific vendor's part. So, my Parts table (a dimension table) contains data on parts tht we've actually purchased: Mfg PN - which ties to Used PN - parts we've actually bought, and are therefore in our inventory system. Manufacturer - The manufacturer of that part number. Description - straight off the customer's BOM. The Form: Right now it's nearly as simple as my Parts table - it's got controls for MfgPN, Manufacturer, and Quantity. To pull in the Customer PN for reference, I have to pull it from the Assembly Details table, and that means I need to basically do a "select distinct", since the corresponding Mfg PN's are in there many times. How can I include the Customer PN, so that it corresponds accurately with the existing data on the inventory management form - without making that query non-updatable
VIEWS ON THIS POST

269

Posted on:

Monday 22nd October 2012
View Replies!

Howto Stop query from asking for value?

I have a query Ive made that does some math with current values. Fields: Month, Delay Min Before, Delay Min After, Delay Min Saved, Percent: Round((([SumOfDelay Min Before]-[SumOfDelay Min After])/[SumOfDelay Min Before])*100,1) The last one is to get a percent saved from Before and After. My issus is when I run this query it will ask for "SumofDelay Min Before" and again for "SumOfDelay Min After". If I just click thru both and the query will output the correct numbers for me. From this query I made a sub from. The trouble is when I open the form for use it asks me every time for both values when the refresh button is hit. Im thinking Ill have to make a 2nd query with the last field to get this to work correctly. Sound right Edit: more info The query has "totals" clicked. Month is Group by, Before/After/Saved are all SUM, Percent is Group by.
VIEWS ON THIS POST

151

Posted on:

Monday 22nd October 2012
View Replies!

Unequal query relationship

Hi i need some help pls! I have a local table (Table A) with some data in and i have a link to a table (Table B) which is populated from sql/odbc. I set the join to include all records from A and only those from B that are equal, when I try to run this i get an error stating: "that it could not be executed as it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that in your sql statement" I have no idea how to do this, can you help pls
VIEWS ON THIS POST

125

Posted on:

Monday 22nd October 2012
View Replies!

Select query access 2000

hello there I have a problem. I have a really simple table here: table1 with two fields A and B. The content of the table is: a1, b11 a1, b12 a1, b13 a2, b21 a2, b22 a2, b23 A is primary key. My question is: I need to build a query which will get these rows a1, b11/nb12b13/n a2, b21/nb22/nb23 from the content of the table1 that I mentioned. Would you please suggest some way to build a select which would get these two rows I could think of a query which would look like something like this: select A, T1.B,"/n",T2.B from table1 as T1, table1 as T2 where T1.A = T2.A but this not only does not solve my problem but it is my closest approach to a possible solution.
VIEWS ON THIS POST

237

Posted on:

Monday 22nd October 2012
View Replies!

How to query for query text?

Hi all. This is probably a pretty easy one, I've just never done it so I'm not sure how to get at the information. I need to get my hands on some code which will query for the text of all my queries. I basically need to search through all my queries (this would include ones behind forms, but if I can just get at the text in the standalone queries, it would be a start) in order to search for a few tables/queries being used in those queries, so I can alter them to a new approach. What I want to do is spit all the query strings out into a text file or something and search through to find out which ones are utilizing the queries/tables in question, so I can start working on changing them. Is there a way to do this with the querydefs collection or with the msysqueries object
VIEWS ON THIS POST

167

Posted on:

Monday 22nd October 2012
View Replies!

Requery Question

I have a list box on a main form. When the list box is updated it should requery within per the after update procedure two subforms [sfrmAgency] and [sfrmEmployment]. The after update procedure is returning an error stating that it "can't find the field 'sfrmAgency'...", however [sfrmEmployment] works okay. The same thing happens on my OnCurrent procedure for the main form. The after update procedure includes: [Forms]![frmPerson]![sfrmEmployment].Requery [Forms]![frmPerson]![sfrmAgency].Requery
VIEWS ON THIS POST

156

Posted on:

Monday 22nd October 2012
View Replies!

can anyone modify this query statement

hi im a first time user of access, can anyone modify this query statement... select IDno, LName + ', ' + FName as Name, course, Status, (select count(io.AC) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed = '2008/03/01' and io.DBorrowed
VIEWS ON THIS POST

284

Posted on:

Monday 22nd October 2012
View Replies!

Displaying query results in a form

Hi , I have a query which totals the amount of all invoices for a specific project number. I would like to take this total and have it displayed on the form from where overall project information is inputed. Right now I have a table which shows general project information and a table showing invoice infomation (linked to the general table via a project #) I also have two forms, one for general info and there's a button which opens the invoice form. When the invoice form opens, it opens an invoice with the corresponding project number shown on the general form. I would like the total invoice amount (from the query) displayed on the corresponding general form. ex: in the general form for project# 100, I click invoice once and enter $50, I click invoice again and enter $100. The query automatically shows a total of $150 for project# 100. I would like this $150 displayed on the general form for quick reference.
VIEWS ON THIS POST

152

Posted on:

Monday 22nd October 2012
View Replies!

How do i pull subform data to a query?

, I have a form with a subform. I have the subform running a update query on a command button in the subform. The subform has the textboxes and I cant seem to get the query to pull the data from those textboxes. I have used the normal [forms]![form_name]![textbox_name] in the past but everything I try to pull the subform textbox data is not working. I have tried [forms]![form_name]![subform_name]![textbox_name] [forms]![form_name]![subform_name].[form]![textbox_name] [forms]![form_name]![subform_name].[form].[textbox_name] Nothing works. What is the correct syntax for this
VIEWS ON THIS POST

248

Posted on:

Monday 22nd October 2012
View Replies!

[Reports] Help with query-reports

, I have made a custom form to open up when I start a query which then enters the information I enter in the form, into the query. The query then produces the results in a report. My problem is, I wanted it to display all the records when I leave a field blank. BUT when I enter a "Location/Contract", it brings up the records I want with that location/contract but it also brings up some records that don't have an entry there. Am I making sense If not, here is some screenshots: I enter "Bridge Cross Rd" And it brings up those without a location/contract... Any Ideas The code for my report is: Code: Option Compare Database Private shadeNextRow As Boolean Const shadedColor = 13356495 ' Const shadedColor = 15726583 ' alternative shade colors ' Const shadedColor = 14078404 ' Const shadedColor = 13356495 ' Const shadedColor = 14281974 Const normalColor = 16777215 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) On Error GoTo Detail_Format_Error ' Choose a color based on the shadeNextRow value If shadeNextRow = True Then Me.Section(acDetail).BackColor = shadedColor Else Me.Section(acDetail).BackColor = normalColor End If ' Switch the color for the next row shadeNextRow = Not shadeNextRow Detail_Format_Exit: Exit Sub Detail_Format_Error: MsgBox "Error " & Err.Number & ": " & Err.Description Resume Detail_Format_Exit End Sub Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If PrintCount = 1 Then txtPageSum = txtPageSum + Cost End If End Sub Private Sub PageHeaderSection_Print(Cancel As Integer, _ PrintCount As Integer) txtPageSum = 0 End Sub Private Sub Report_Close() DoCmd.Close acForm, "Master" End Sub Private Sub Report_NoData(Cancel As Integer) MsgBox "There are no records to report", _ vbExclamation, _ "No Records" Cancel = True End Sub Private Sub Report_Open(Cancel As Integer) ' Set public variable to true to indicate that the report ' is in the Open event bInReportOpenEvent = True ' Open Sales By Category Dialog DoCmd.OpenForm "Master", , , , , acDialog ' Cancel Report if User Clicked the Cancel Button If IsLoaded("Master") = False Then Cancel = True ' Set public variable to false to indicate that the ' Open event is completed bInReportOpenEvent = False End Sub And my Form: Code: Private Sub cmdCancel_Click() DoCmd.Close End Sub Private Sub Form_Open(Cancel As Integer) If Not bInReportOpenEvent Then ' If we're not called from the report MsgBox "For use with the Reports only", _ vbOKOnly Cancel = True End If Form_Open_Exit: Exit Sub End Sub Private Sub OK_Click() Me.Visible = False End Sub And for some reason this is what my Query looks like:
VIEWS ON THIS POST

197

Posted on:

Monday 22nd October 2012
View Replies!

Calculate Yes/No fields in a query

In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return If so, can you share how this could be accomplished.
VIEWS ON THIS POST

412

Posted on:

Monday 22nd October 2012
View Replies!

Replacing query

hi. i are replacing queries in my tables to get the database ready to convert to SQLServer. Am having a problem replacing a query that is brings in a table for a drop down and inserting the selection in the record. during creation, the record shows a date, id, task, hours. the datatable needs date, id, task_code, task_name, task_job, hours. On "task" the drop-down allows for selection of several choices that are 3 columns wide. i can get the columns to show but only the first column currently goes in my new table. the table's columns that the selection comes from is "task_code,task_name, task_job". columns are needed as different areas use the same task code but use different jobs or names. I was told to use a insert into statement, however am having problems with it. have as follows: SQLStatement = "SELECT [Task_code], [Job_name], [Task_name] FROM tablea; " rec.Open SQLStatement, , adOpenDynamic job = rec(1) task = rec(2) rec.Close SQLStatement = " Insert into [tableb]([Job_name],[Task_name]) " & _ " values (" & job & ", " & task & " ) " rec2.Open SQLStatement, , adOpenDynamic db.Execute (SQLStatement) any point in the right direction would be greatly appreciated.
VIEWS ON THIS POST

419

Posted on:

Monday 22nd October 2012
View Replies!

ERROR: Operation must use an updateable query.

asp code: Code: error: Code: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. /site/content/register_act.asp, line 13 It's says it has something to do with permissions... I set writing & reading permissions for the db and the directory that contains it through contorl panel->Administrative Tools->Internet Information Services I have no "security tab" when I rightclick->properties the db file or it's directory even though the file system is NTFS in spite all these when I rightclick->properties the directory that contains the db I see a "read only" square marked in the general tab-attributes, when I try to unmark it seems to be unmarked but when I rightclick->properties again it's marked again... what should I do
VIEWS ON THIS POST

393

Posted on:

Monday 22nd October 2012
View Replies!

Relationships query

hi.. Im struggling to get the relationships correct within my database. (4 tables) It's for a shopping cart application Can someone please help: tblcustomerdetails: custid forename surname addline1 addline2 postcode email telno tbllogin customerid username password tblorder orderid orderdate orderqty orderprice tblproduct productid productname productprice productquantity productimage
VIEWS ON THIS POST

356

Posted on:

Monday 22nd October 2012
View Replies!

access query - continued

I sent message before I finished explaining my problem! The query to get the totals includes queries with the employee, vehicle, material, and barricade costs all sorted by activity code but for it drops all activities that don't have costs in each column--if there weren't any costs entered for material or barricades for the month, they get dropped. How do I get everything even with some fields are null
VIEWS ON THIS POST

393

Posted on:

Monday 22nd October 2012
View Replies!

Update query help

I have a table with trailer numbers currently on the yard. I also have a prompt to enter trailers as they are leaving. I would like an update query to then remove the record from the OnYard table. Is this possible
VIEWS ON THIS POST

392

Posted on:

Monday 22nd October 2012
View Replies!

Lookup value from a table to input in a query

I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions Thank you! KellyJo
VIEWS ON THIS POST

390

Posted on:

Monday 22nd October 2012
View Replies!

Automatically update value in the subform from Crossstab query

, I have a main form which has 2 subforms. The main form is the name of the Student. The first subform is the data entry form. It has 2 fields. One for the Trimester - a drop down which has "1, 2, 3" listed. And the second field is the benchmarks (again drop down). Now the second subform is a more elaborate way of viewing (only for viewing purposes) which standards have been chosen for which trimester. Now this form's control source is a Crossstab query, which updates the Trimester field for each of the benchmarks for the particular student. Now my problem is I am trying to update this subform which is for viewing purposes only, automatically, so that the moment a benchmark is chosen on the other subform, the [trimester] field automatcially gets updated on this form. the benchmarks are listed on this form (view form) and a field which is the trimester field. Data entry is not allowed in this form. So the information has to get updated automatically. It does when I close and open the form, but thats not how it should work. I tried everything I thought possible (requery, refresh, a button to click) but couldn't get the form to update. Please help! I would really appreciate if you could help me find a solution.
VIEWS ON THIS POST

444

Posted on:

Monday 22nd October 2012
View Replies!

Requery value of combo to listbox

, I have a form with a text box, a combo and a listbox. The textbox stores the name of the user. The combobox gets its data from a query. I would like that when I open my form, by default the list box will show all the records of a table named Master and then, I would use the combo to requery the list by selecting another user. records under that criteria, will have to show up in the list box.
VIEWS ON THIS POST

411

Posted on:

Monday 22nd October 2012
View Replies!