For whatever reason when I try to add up two columns in a query instead of adding up the two numbers it displays it as a text string. So if one column has a 5 and the other is a 2 I am looking for the calculated column to reflect 7, currently it is showing 5,2.
I have added up items in the past so I am unsure what the problem is
should be [rev bid amount 1]+[rev bid amount 2].
I have a table with 4 columns :
Product Price Quantity Supplier
Product1 5 240 A
Product1 7 19 B
Product1 6 12 C
Product2 96 0 A
Product2 98 23 B
Product2 99 44 C
There are 3 suppliers for the products (name of the suppliers are A, B and C).I want to make a query with the following result :
Product1 5 240 A
Product2 98 23 B
In other words :Showing a grouped list (grouped by products), with the lowest price of the supplier who has stock (quantity >0).I can make a list of grouped products with the lowest price, but it's not possible for me to show the stock and the supplier that's related with it.
I have a cross tab query that displays data by customer (rows) and MONTHS (columns).
However I need the columns to be the 12 months of the year 1 to 12.
However, if the selected data for a particular customer does not have any records in a month then I get an error in the report as the cross tab query only selects the months with data.
How do I get the report to show 0 or a blank in these columns
I have a multi-select list box that runs a "For intCounter" and builds a sting into a text box. That works fine the text box will populate as designed <"Closed" OR "On-Going">.
(I know that I should be dynamically creating the query in the first place ... normally I would ... but this is a quick fix that I want to get it into an existing application, while the redesign requirements are being written.)
Now I want to pass that string to a pre-existing query, but I'm not sure what to write in the criteria section.
I've tried...
Like "*" & [Forms]![ViewReports]![TextPickList] & "*"
IIf([Forms]![ViewReports]![TextPickList]="","",[Forms]![ViewReports]![TextPickList])
and just plain old ...
[Forms]![ViewReports]![TextPickList]
but my query is coming up blank. What do I need to write into the criteria section of this saved query?
thanks in advance!
I have a simple select query , and I want to add a field that will take the value from another field and increment it based on a condtion.
i know i need a case statement but not sure how to go about this ..
column 4 is my desired result
i want to increment the text string from column 3 based on column 3 and restart the increment based on column 1
values of A would increment in the following manner 01,11,21,31,41 etc
values of O would increment 02,12,22,32,42,52,62
i'd also like the option that if a new value were introduced to increment it
values of x would increment 80,81,82,83,84
Example data .. apples and oranges ..
column1 column2(name) column3(type) column4
mary braeburn A 01A
mary blood O 02A
tom cortland A 01A
tom fuji A 11A
tom navel O 02O
tom mcintosh A 21A
[Code]...
Been a while since I put hands on access. Working with a very simple database.
Working with 1 table, 1 form, 1 query.
Query has several fields. Field of interest contains names of counties.
In query design view I can type, "CountyA" Or "CountyB" or "CountyC" in the criteria and the desired results are returned.
On form I've created a text box and an open report button. The report I'm trying to open uses the query as the data source. I reference the text box on the form as the criteria for the query as follows:
[Forms]![frm_LMIBasicReport]![txtCounties][Forms]![frm_LMIBasicReport]![txtCounties]
If I enter the the name of a single county into the form it executes perfectly. If I try to enter multiple counties it fails. This is true whether I just enter the county names or replicate the exact criteria string I use in the source query. For example
CountyA works and returns desired values for County A
CountyA Or CountyB or CountyC fails. The report opens but no records are returned
"CountyA" Or "CountyB" or "CountyC" = exactly how it is entered in the query if I'm not using the form opens report but no records are returned
How can I pass multiple values from a single text box to the query.
While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.
I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"
Is presented in Group by as: "砅5"
I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.
File Corruption? anything to do about it?
:confused:
I have a Microsoft Access table with the following columns: A,B,C,D,E,F.
In first row of Column A, I have the following string value: "Al,Peggy,Kelly,Bud,Buck"
What I would like to do is parse this string as such:
Column B:"Al"
Column C:"Peggy"
Column D:"Kelly"
Column E:"Bud"
Column F:"Buck"
Is there a simple VB funtion to accomplish this?
Hi,
I have created a form (Form1) based on a table (Table1) and also a Query based on this table (Query1). I calculate a field in the query (Expr1: [column1] + [column2])
Now i have created a text box on Form1 that should display Expr1.
I know I could also calculate the value directly via the expression builder, but I would prefer to simply display Expr1 from my query. Which is the syntax I have to use in the Expression Builder?
I tried "= [Query1]![Expr1]" but it does not work.
Thanks for your help
Tigrou
I have a text box, I have pasted this into the control source:
=[qryIndividualFaultVolumes]
then on a button I have this code:
me.qryIndividualFaultVolumes.requery
However, I get a Method or data member not found error when i click the button?
I have a listbox that is populated with data from a table. I would like to use one of the columns data as the where clause of a sql string that will populate another form, how do I get the selected items column data that I need into a variable? This is in Access 2010 vba.
View 9 Replies View RelatedThe user will select two dates. Start and End date. The user will hit the run button and the application will read an excel file with employee data and only display/output the records within that date range selected (employment date).
Code:
Option Compare Database
Function DeleteTable()
'Delete old records from AllEmployeesData table
On Error GoTo DeleteTable_Err
[code]....
I am not getting the query displayed on the datasheet after it is done executing. It's just a blank sheet. Also the output file is just this:
Code:
ssn
last
mi
first
employ
I did a quick query test using SQL in Access and it worked fine there. I am guessing my error is somwhere in this section:
Code:
'--Display query result on Datasheet and Output query to text file
With db
Set qdf = .CreateQueryDef("NewHireQuery", strSQL)
DoCmd.OpenQuery "NewHireQuery"
.QueryDefs.Delete "NewHireQuery"
End With
db.Close
qdf.Close
Set qdf = Nothing
[code]....
I have a form where I want a textbox [txtMaxOrdLimit] to be visible only if another text box on the same form [PaNumber] contains the letter D in the string. This is the code I have on the forms On Current property but I'm missing something because textbox [txtMaxOrdLimit] doesn't show on the form at all.
If Me.PaNumber = "*D" Then
Me.txtMaxOrdLimit.Visible = True
Else
Me.txtMaxOrdLimit.Visible = False
End If
Here's the form I'm trying to Create:
VEH POS NAME
A21: TC: CPT Somebody
G: SGT Someoneelse
D: PVT Noone
A22: TC: SFC Smith
G: SGT Jones
D: PVT Doe
and so on and so forth.
The VEH and POS are just going to be Labels in a form....no prob. Each Soldier's Squad and Team (for mounted Vehicle and Position) are stored in the Personnel Table. The below query is for vehicle A7 (ACTUAL would be the same as TC above). The query works. I just need to know how to get the result to display in a text box. What I'm planning on doing is creating text boxes for each posistion with these small select queries, so when I update the SQD and Team in the Personnel Table it updates on this form. Or is there an easier way to do this?
Dim strSQL as string
strSQL="SELECT [tblPERSONNEL]![RANK] & " " & [tblPERSONNEL]![LAST_NAME] AS NAME
FROM tblPERSONNEL
WHERE (((tblPERSONNEL.SQD)="A7") AND ((tblPERSONNEL.TEAM)="ACTUAL"))"
I have a two tables in my database related to employee ratings
Employee
TokenID
Division
Band
PMRating
TokenID
H1
Now this statement
Code:SELECT DISTINCT Employee.Band FROM Employee
This would display me all the band levels within Employee table in each row..
Is there any way I could built a query to the Band names as seperate columns?
i'm doing this so as to display the average H1 rating for each Band level ..that is grouped by Employee.Division..
I have the need to display the return of my "Sum Query" to display in a text box.
I need the attached below value (40500) in the "SumofQuery" attachment, which updates every 5 minutes to display on the "StatusBoard" attachment text box.
I have been searching for a good 10 hours on how to do this and still cannot find it.
We have a button running a SQL query via VBA, how can I make it so the results doesn't show up in a table/preview?Also, I know it's for the SQL forum but how can I make a text box to show the results of a query to display it on screen?
View 8 Replies View RelatedI have an Access 2009 report that displays the sums at the bottom of some columns. Is there a way to calculate and display the percentage one sum is of another?
Column1 Column2
Total 12,000 9,800
Percentage 100 % 81.7 %
I have written a query where it displays sorted data for two columns, but problem is its sorting on one column but not on another.
When checking the query separately its giving proper output, but in form view its sorting only on one column not on another.
I think the answer to this question is going to be a flat 'No', but here we go.
I want to have something like a datasheet, except with with records shown as columns instead or rows.
For clarity, a datasheet displays records as rows, like this:
field 1 field 2
record 1 record 1
record 2 record 2
I'm wanting to present the data the other way around, like this:
field 1 record 1 record 2
field 2 record 1 record 2
Just to complicate matters, the number of records to be displayed is variable, so it should add more columns as required.
About the only option I can think of is to create a subform with fields stacked in a column without labels, and try to dynamically stack the forms as needed, hooking them up to the correct data on the fly.
Alternately, I wondered if I could dynamically generate a recordset with each record containing the field value of multiple records. I can see myself getting into trouble trying to update data this way however.
I am having trouble getting all columns in an Access 2007 report to show up in report view as well as to print. It seems like this is mostly occurring in columns that occupy the center of the report.
View 1 Replies View RelatedI have a combo box that takes its record source is a Query, this all works fine and when i click on the Combo box, I see 4 columns of data but when i select the record, only the first column displays, is there a way that I can display all 4 columns in the selected combo box, or will I have to have 1 combo box and 3 text boxes to display the data, is so how would I do that.
View 2 Replies View Relatedhow can I display the scores of a student in horizontal?
I have two tables, one with students information and another for students' score.When I query may table using this
Code:
SELECT Student.StudentName, WrittenScores.Score
FROM Student INNER JOIN WrittenScores ON Student.[ID] = WrittenScores.[StudentID];
It resulted to this
All I want to do is something like this
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
How to display in a report a field with 50 records in two separate columns of 25 records.Is this possible or am I completely off the reservation?
View 2 Replies View RelatedMy question is that can we do multiplication of data of two columns and result is automatically displayed in third columns in datasheet view.? Is it possible ?
View 1 Replies View Related