Hello, I am working on a database to analyze weekly purchase results. With the goal being to determine the response rates for each promotion source that was used.
I have a table that contains PromoName, PromoCode, PromoQuantity, PromoSource and PromoDropDate. In another table I have imported 8 weeks worth of results and use the fields PromoCode and DatePurchased. An additional field called TransactionValue is auto assigned to the number value of 1.
I was able to create a query that looked at all the records with the same PromoDropDate and PromoSource field and then summed the values in the TransactionValue.
I would like to be able to see the results by week for 10 weeks but am not sure how to proceed. For example if the PromoDropDate is 1/10/08 I could look at the results based on DatePurchased being between 1/10/08 and 1/16/08, and then for 9 week periods after that.
I welcome any and all suggestions on the best way to approach this. thanks.
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Between [Forms]![Benefits]![date1] And [Forms]![Benefits]![date2]
(Benefits being the form). If I enter date range 01/01/2005 to 01/05/2005 I receive all data from 01 January to 01 May - but including different years i.e 2002, 2003, 2004 etc.
I have a query that has 4 fields that are text fields in an external database. I have text boxes set up on a form which contain either null or >0 (depending on an option button selected). I want to pass that >0 to one field in the query and null to the other three. I have the following as criteria in my query: [Forms]![frmSelectPayment]![txt2ndPayment] (same one for each of the 4 fields except the form field name changes)
Below is the code that sets the text boxes that are sent to the query: Select Case [FraSelectPayment] 'depending on which pmt being pulled Case 1 '6 payment plan only stDocName = "qryGetPlusARImport" Select Case [FraChoose] Case 1 Me.txt2ndPayment = ">0" Me.txt3rdPayment = Null Me.txt5thPayment = Null Me.txt6thPayment = Null Case 2 Me.txt2ndPayment = Null Me.txt3rdPayment = ">0" Me.txt5thPayment = Null Me.txt6thPayment = Null Case 3 Me.txt2ndPayment = Null Me.txt3rdPayment = Null Me.txt5thPayment = ">0" Me.txt6thPayment = Null Case 4 Me.txt2ndPayment = Null Me.txt3rdPayment = Null Me.txt5thPayment = Null Me.txt6thPayment = ">0" Case Else MsgBox "Please select a payment" FraChoose.SetFocus Exit Sub End Select
DoCmd.OpenQuery stDocName, acNormal, acEdit
If I run the query alone I leave all fields blank and put >0 on the one I want to search by it works fine (695 records returned) but when I run it through code, nothing is returned. I really hope this makes sense to someone! Can anyone see what I am doing wrong? Thanks
having trouble using a text box on a form to set the search criteria for a particular field within a query.
Ideally i would like to enter the criteria in a textr box then click a button which sets the criteria and opens the query results in a report,
I have designed the query but cant which works if you go into the design and enter the criteria. the problem lies with getting the text box on teh form to set teh criteria.
If i use the expression builder to set the criteria to the same value of the "text" within the "text box" on said form the following happens,
If i open the query itself it opens a small window and displays the "expression" that i entered in the criteia box, above the data entry. the query does work this way but dont understand why the expression is displayed??? The text box on the form also becomes locked, not allowing data to be entered.
I have tried to link the query direct to the text box. also tried creating a table which has data entered via form then linking the query criteria to a field in the table.
Could some one point me in the right direction please
I've set up a form with a button to open a report based on the current name on the form. The idea is that as you look through the different pages on the form you can open a report for whatever one you're on and print it.
In my query I have a name field where I put the criteria: Forms!Formname.Textboxname
By clicking the button on my form I'm able to generate a report based on the name that appears in that textbox. It works great when I initially put it in but if I close the form (or query, or report) and open it back up it is blank.
Is this even possible with a text box? It seems like it when I open it, it has nothing to go off and that's why it's blank. I just don't know how to fix that.
I need some syntax in setting 2 criterias for a DLookup query.
I've attached a sample db with 2 tables: Main & Timesheet
I need a "combo" query showing (on the same line) all Qty for Transcodes N, 1 & 2 where the Staff number and TSNum is the same.
I'm sure about the logic but the syntax is letting me down.
I can pull in 1 of the criteria E.g.:
OT1: DLookUp("Qty","ExOT1","[Staff] =" & [Staff])
But can for the life of me not script the second criteria in.
So in my result of ExCombo I'm getting Staff 11 showing 4 under OT1 while I know the result does not apply for TSNum 29832 as indicated hence the need for the 2nd criteria.
Since I have to change some of the values to text inside the query it might be best to have a look at the attached db rather than just suggesting the correct syntax .
I have this criteria which should collect a date range (cboDate and cboDate2), it works well in collecting the date range if i put separate days (like 6/17/2006 and 7/18/2006, it'll collect the data matching those dates), but if i put the same day, say i want to get all the data for 6/17/2006. And cboDate and cboDate2 are both 6/17/2006. With this code, nothing comes up. Can you help me?
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
I want to create a query based on 3 combo boxes but have it so that if the field in the second or third combo box is not populated the query still runs.
Right now i have the Criteria set for the three columns that i wish to sort by as seen below.
This gives me the correct query result but im forced to make a selection from each combo box. Is there a way to progamme it so that if I only make a selection from the first combobox and leave the others blank i can still get results in a query?
I have a problem in setting the criteria of queries.
I have two inspection methods: ABC and XYZ. Every two years, ABC will be carried out, and all other years, XYZ will be performed. However, information to which factory XYZ is performed is required. If ABC is carried out, then the information can be simply "N.A".
But when I do the query, I therefore need to add an extra field which will show "N.A" if ABC is performed, or the factory name if XYZ is performed (factory name can be retrieved from another table).
Hi, I have a table I am trying to set up a query. I got into the query and set up the fields as follows: Name, Address, Landlord, Lease Date, 90 Day Reminder, 30 Day followup.
I want to show all these columns in my query. I need to set up formulas for the 90 Day Reminder and the 30 day followup from the Lease Date field. The 90 Day Reminder is the date 90 days prior to the Lease Date, and the 30 day followup is 30 days after the lease date.
I am having trouble setting up the formulas. I did it in design mode, and I tried filling in a formula and when I tried going into the dababase mode, it asks for for a parameter. I am having difficulty understanding what to fill in for the parameter. I am not sure about the formulas either.
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
I have a table with 20,000 names in column 1. Columns 2-8 are Yes/No questions. I would like to set up a query to see how many people answered Yes for any combination of questions. I think a crosstab is needed, but I just can't for the life of me think how to do it. Any ideas?
I'm making a database with 50 fields for the shops of my company. Among these fields, the data of 20 fields must be submitted to us (head office). The data of the remaining 30 fields (like customer address) is just for generating receipts.
At the begining, I set the validation rule of that 20 fields to be "Is not null" in the Form. If the required data (20 fields) is not entered, messages will appear and the record cannot be saved. Then I use the Query function to extract that 20 fields. However, our staff said they might not be able to obtain the information of the 20 fields in the first time. The customers may send them the info later on. So, it not good for me to set any validation rule in the Form.
I would like to ask how I can ensure they submit the data of the 20 fields if I release all the restrictions in the Form? Could I set any validation in the Query so that error message will appear if not all the required fields are filled?
I am trying to access the same query/report from different forms. For example I have a StudentAttendanceForm where users select a StudentID and then open the report. The query criteria for ID# is [Forms]!
[StudentAttendanceForm]![StudentID].
Can I access this same report from an event on another form. I'm thinking that if I can some how assign the StudentID from the form I want to access the report from, to the criteria on the StudentAttendanceReport Query, this is possible.
I am new to code but I am thinking something like:
SET [StudentAttendanceQuery]![StudentID] TO [StudentEnrollmentForm]![StudentID] Open StudentAttendance Report
The StudentEnrollmentForm is the other form I want to access the report from
You may need a crystal ball for this one. Let me know what other info you need.
In general I am finding I am copying and tweaking queries to meet specific user needs. For example some want to access a single student and other need "batch" reports.
Hello Friends, I have Query1 that should get one parameter , how to set this parameter in a code.
Below the code.
Private Sub Command0_Click()
Dim MyDB As Database Dim MyRS As Recordset Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookRecip As Outlook.Recipient Dim objOutlookAttach As Outlook.Attachment Dim TheAddress As String
Set MyDB = CurrentDb Set MyRS = MyDB.OpenRecordset("Query1") MyRS.MoveFirst
' Create the Outlook session. Set objOutlook = CreateObject("Outlook.Application") ' Do While Not MyRS.EOF
Set objOutlookMsg = objOutlook.CreateItem(olMailItem) TheAddress = MyRS![EmailName]
With objOutlookMsg .To = TheAddress .Display End With
MyRS.MoveNext Loop
Set objOutlookMsg = Nothing Set objOutlook = Nothing End Sub
I have a query where one field is Date:Now() so that todays date is forced in. However, it is giving me the date and time. I only want the short date, how do I achieve this???
i have a query that returns a dataset that looks like this:
Account Status 123 Paused 123 Paused 123 Not Paused
I want to evaluate these results and in a second query, for the account 123 return the value "Not Paused" if there is at least one status set to "Not Paused".
I know its probably a simple one but I am new to access, so...
I have a simple membership database and in it I keep a record of who I am expecting to attend on a particular date. This is achieved through having a seperate field for each meeting on a simple yes/no type.
I would like to be able to print a report from a form for a particular date showing everyone that would be attending. I have a list box that shows all the fields. What I cant do is set the date I want in the query from the report, can anyone help?
I have a crosstab query to summarise the counted string values from another query: E.g.;
TRANSFORM Count(Table1.Viable) AS CountOfViable SELECT QryTable01.productName FROM QryTable01 GROUP BY QryTable01.productName PIVOT Table1.Viable;
As there are some null values returned (ie blank cell in the pivot table)
I used the Nz function to make this zero but when the query results are used in a report I want to add the rows to get row totals... but the result is as if they were string values;
So if I add a text box in the report with = [viable] + [Not viable] t Then the report row with the values:
I have a combo box field on a Student Details form that displays the Student's class number. For a new record, I am able to set the default value in the control's Default Value property with a literal in quotes (ie., ="2015-1"). But I want to set the default value from a query field. However, when I specify the query field in an expression ((=[Current Class]![Class Number]), "#Name?" appears in the field when initiating a new record on the form.
The control source for is the field in the Students table ([Students.[Class Number]).
The Row Source is a query of the Class table.
How can I set the control's default to the Class Number value in the Current Class query?
Please would someone be able to help me? I have created a union query however, one of the columns, has not picked up the same format as it has in the tables. As in the tables it has this format '00000'.
Please woud you be able to advise me how I can change the format on one of the 'columns' in my union query. As one column is 'numbers' and the other is 'text'. I need to change the number column so the format is '00000'.
I am trying to create a new Table using a MakeTable Query
using the following sql:
Code:SELECT qCPPlannedStopsOnTargetTotals.WeekNumber, qCPPlannedStopsOnTargetTotals.Line, qCPPlannedStopsOnTargetTotals.Description AS Above, qCPPlannedStopsOnTargetTotals.[%] INTO mkCPPlannedStopsAboveFROM qCPPlannedStopsOnTargetTotalsWHERE (((qCPPlannedStopsOnTargetTotals.Description)="Above"))ORDER BY qCPPlannedStopsOnTargetTotals.WeekNumber DESC;
However i want the new table to have a Primary Key, (Week Number) can i set this as the table is made?
