Modules & VBA :: Write SQL Query And Design Report With Proper Field Positions
Jul 1, 2015
I need to display the year, Date, agent name, amount field, note and sub total for each year.I need to write the SQL query code in VBA and i designed the report with proper field positions.
YEAR column: Display Value only once for the FIRST ROW for Maximum value of Date field value for each set of YEAR. If it has only one record, it display that year.SUB TOTAL Column: Display Total Value for Amount field only once for the LAST ROW of each set of year record (Mininum value of year). How to make it available the values for first and last record alone ?
I just wrote my SQL code based on your inputs in my editor..Based on the input from Plog (from this forum), i wrote a code which listed below.
Code:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "SELECT CessioneCredito.Anno, CessioneCredito.Data_Movimento, CessioneCredito.Note_Liq_Cessione_Credito, CessioneCredito.Importo, " & _
" Agenzie.Denominazione, DCount("[Anno]","CessioneCredito","[Anno]=" & [Anno]) AS GroupSize,
[Code] ....
While saving it, it shows the code in RED COLOR due to compilation error.As I am writing the query in Me.Recordsource = "SQL query", where i should place the below listed code in the query as per Plog???
Change the Year Control Source to this:
=IIf([GroupPos]=1,[Year])
Change the GroupTotal Control Source to this:
=IIf([GroupPos]=[GroupSize],[GroupTotal])
If this is not possible in Me.recordsource, then its advisable to use recordset like writing in two different queries.
Code:
Dim ds1 As Recordset
Dim ds2 As Recordset
myquery1 = "SELECT CessioneCredito.ID_Agente, CessioneCredito.Data_Movimento, CessioneCredito.Importo, CessioneCredito.Anno, CessioneCredito.Note_Liq_Cessione_Credito, Agenzie.Denominazione
FROM CessioneCredito INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))"
New to Access, and having a heck of a time learning it...or rather learning how to correctly design databases.
All my expertise is with Excel. I'm creating a project where I use Excel to parse a non comma delimited text file, then feed certain figures into an Access database. This is all through VBA.
It wasn't until yesterday that I realized I had a problem. There are two text files with data that makes up one complete record. With what I already have built, and with what I have tought myself (ADO w/ VBA wise) I tought the easiest solution would be to create two tables that will hold the data from each respective text file. This is what I'm working with:
A store has a department with 5 areas of measurement that is collected daily. One complete record would be like this:
Date | Store | Dept | Sales | Cust Count | Item Count | Avg Price | Mix
...and there are (right now) 3 stores and 15 departments that are watched in this project. What I came up with for a table design was this. Fields with an "!" prefix reflects primary keys.
Table1 (using data from txt file 1): !Date | !Store | !Dept | Sales | Mix Table2 (using data from txt file 2): !Date | !Store | !Dept | Cust Count | Item Count | Avg Price
In each table, I have to have a compound primary key to make up what is a unique record. I just learned I could use a compound index and an autonumber as my primary key. Either way, I'm using the 3 primary keys in each table with a 1 to 1 relationship. This seems to work if I make a query.
Now, will the way I did it hold up? Is there a better, more correct way to do it?
I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.
I want to extract the day number from the Date field, and write it to the Day field.
I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".
So far, my code looks as follows:
Code: Private Sub Command16_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim sqlString As String Dim dataDay As Byte 'Open connection to current Access database Set db = CurrentDb()
[Code]...
I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.
I am trying to improve my code by making it more readible. The following code works, but it is certainly not the most efficient way. I`m trying to write a loop to make certain elements in an Access report visible/invisible, but I can`t address the visibility property of these items while iterating over i.
see below the code . The select statement searches the Printpoolno value from the top to bottom in table tblmaster. As in my table tblmaster there are thousands of records and it takes long to search for that Printpoolno from the table . Is there anyway we can write a query that will search the table from bottom to top as the Printpoolno will always be in the bottom records and not in the top records.
Code:
Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim r As Long
I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough.
Beloved Access gurus,I am wondering if it's possible to export specific columns into certain positions in the textfile? I haven't seen this done with Access before so i dont know. I have an access table that i would like exported periodically into a textfile (meaning, i have to do this with vba).For example - loop through table, exporting into textfile:column 1 ---> start on 1st line Position 20 in the text file (after 19 spaces)column 2 ---> beginning of next line.column 3 ---> back to previous line, position 40.Is this possible? If so, any thoughts or suggestions will be greatly appreciated.
I have a field (date field) that when I try to imput data will tell me that that what is being imputted is not in the correct formate or to large for the field settings. It is in the right formate etc. Is the field size applicable to just that field or overall everything entered in that field in the whole database? It's a decient size data base and I'm wondering if everything in that field is over the size, but then how can that be?
I have a report rptEnvelopes to print envelopes. In the report I have text box called txtRecipientName . I have a qryAddress with fields notNo, IDCard, Fname, Lname where notNo is a field that takes its value from a combo box called cboSelectEnvelope from a form frmPrinting. I then wrote the following code:
Private Sub Report_Open(Cancel As Integer)
Dim dbLet As Database Dim rsLet As Recordset Dim sqlLet, criteriaLet, txtRecipientName As String Dim stLet as String
Set dbLet = CurrentDb sqlLet = " Select DISTINCT IDCard FROM qryAddress " & _ "WHERE notNo = " & Forms!frmPrinting!cboSelectEnvelope Set rsLet = dbLet.OpenRecordset(sqlLet, dbOpenSnapshot) If Not rsLet.EOF Then rsLet.MoveLast stLet = "rptEnvelope" criteriaLet = "notNo=" & Forms!frmPrinting!cboSelectEnvelopeReports 'This is where I go wrong: 'How to I declare txtRecipientName, on rptEnvelope, to get the value rsLet!Fname&" "& rsLet!Lname DoCmd.OpenReport stLet, acViewPreview, , criteriaLet End If rsLet.CLOSE
NOTE: Private Sub Report_Open is called from the On Open Event of rptEnvelope
I have a front end that is connected to three back end files. The front end is on my local computer while the back end files are on a network drive.
There are a lot of calculations that go into the queries and intermediate queries. For a report, I have based it on a UNION query.
But when trying to design the report it takes about 45 seconds just to do any one thing, e.g.; - Add Groupings - Add Grouping Headers//Footer, sorting option - Add bound textbox :eek:
Needless to say this is very annoying. :mad:
The union query itself runs fine (takes about 15 seconds to run) and returns about 12,000 Rows. The union query looks like this (I changed the field names to make it read easier, hopefully);
SELECT a1, a2, a3, a4, a5 FROM qry_A;
UNION SELECT ALL a1, b2 AS a2, b3 AS a3, a4, a5 FROM qry_B;
UNION SELECT ALL a1, c2 AS a2, c3 AS a3, a4, a5 FROM qry_C;
UNION SELECT ALL a1, d2 AS a2, a3, a4, a5 FROM qry_D;
UNION SELECT ALL a1, e2 AS a2, a3, a4, a5 FROM qry_E;
UNION SELECT ALL a1, f2AS a2, tblG.f3 AS a3, tblG.f4 AS a4, a5 FROM qry_F;
One solution I came across when searching the forums was to use an Append Query to append the query results to a table and base my report on that. This does indeed fix the problem.
But what I was wondering if it was is my query design that is causing it to be slow or is it just the fact that I am returning 12,000 rows? :confused:
In case it matters, I wanted to mention that I can’t use the report wizard to create the report. When I select the union query, the fields will be showed for awhile then they just disappear. That in and of itself doesn’t cause any trouble since I am creating the report using the design view and not the wizard.
Does anyone know how I can use the Proper Function in an append query.
I have a field whereby all the data is in UPPER case, but I only want the first character of each word to be upper case.
I have looked up the Proper function, which should do the job, but when I apply it in the specified field I get an "Undefined function "Proper" in expresssion message
my expresssion is as follows: salutation: Proper([strSalutation])
(I use the sample database "Northwind" for my question)
I wish to do as follows: I added a field to the "customers" table, and name it "customer status". This field should be updated by update query as follows: I wish that for each customer that his total purchases wil be calculated through the "order details" table. If I multiply the "unitprice" in "quantity". I know how to make totals query, so I can see the total amount of orders per customers.
I wish that the "status" field will be update as follows: if the total amount per customer is higher than 100,000 the status will be update to "Gold Customer", if it is between 50,000 and 100,000 it will be updated to "Silver Customer" and the rest will be update to "Standard Customer"
I have some queries that I created in 97 and convert to Access 2000.
However, when I go to query DESIGN view, I would like to see all the contents of long functions (iif) that I wrote for some fields as a result of running the query, but each field only shows a portion of it (as if it was truncated). I also use the "Zoom..." of that field or Shift+F2, still the same thing, only shows portion of it, the last portion was truncated.
Do any of you know HOW to show all, so I can check and revise if I need to, pllllllease.
I am trying to hardcode in the field name. This is what I want to hardcode "TEXT(ROW(A17),"-0")" but the parenthesis and quotation marks are causing a syntax error. It appears like this
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
I have header table and a distribution table that both need to be written to a txt file. They need to be written in a certain order Header. Distribution. Header. Distribution. etc. There can be multiple distribution lines for the header line.
So far this is what I'm trying and its not working right. ~Legend~ hrst = header record set drst = distribution record set tblHeaderExp = header table exporting from tblDistExp = distribution table exporting from ~End of Legend~
I am just querying a single table, no relationship involved with another table. As you can see form the attached jpeg, the ZIP field in some cases is empty. I would run a search using Is NULL but the field is NOT numerical. It's a long story but I had to make this field a TEXT field. Basically, what statement do I have to insert in the criteria field to just pull up the EMPTY ZIP fields?
How do you write complex nested IF THEN ELSE END IF statements..How do you know what IF statement to put first?Then how would you know where to put ELSE, or END IF?Before you start with the VBA, what are the foundations for making it work successfully, know where you are, know where to put END IF etc?
I've seen many times when using VBA people set up an initial foundation for writing code. For example when needing to put something in quotes, they do "" then enter the text in between, so they don't get errors on running (A very simple, crude example I know, but is there a similar basis for nested IFs).
I have a Date Field and I would like to complete another field depending upon the contents of that date field.
In this way: if the Date is, say, 4/27/12 I want the other field to read: April 2012 and so on. Reading the month, putting it into words, reading the year, writing it out. This second field is NOT a date field.
I have the requirement to write EACH record from a table to its own CSV file with name of the file being combination of 2 fields
So let's say I have Table1 with 3 columns (Field1, Field2, Field3) with following content :
Field1 Field2 Field3 AA 1 ABC AA 2 DEF AA 3 GHI
I should get 3 files with names AA1.CSV, AA2.CSV and AA3.CSV and each file contains its respective row from the table.
I tried to do it with DAO Recordset, but I do not find a way to write only the current record from recordset while looping.
See below the code I was using, but issue is that code does succesfull creates the 3 CSV files as per above example, but in each file it writes ALL 3 ROWS instead only the respective ROW.
Code: Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim strFilename As String
I have a function to write some data to a text file (as an activity log, separate to the live data contained in the DB)
It uses the FileSystemObject.OpenTextFile method and works fine
However, I'm conscious that, although perhaps unlikely, there is the possibility (which is enough for me to worry about it) that multiple users could trigger the function simultaneously, which could cause a conflict when multiple FE's try to write to the same log file at the same time.
So I'm trying to add some code to prevent this from happening, by checking if write-access is available before proceeding.
Below is the piece of code I've added. Was considering, as an alternative, looping until the objFile is no longer Nothing, rather than depending on the Err.Number?
Code: Dim objFSO As Object Dim objFile As Object On Error GoTo ErrorHandler ... Set objFSO = CreateObject("Scripting.FileSystemObject")
