hi
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".
How can i do this?
Thanks in advance.
I have made a query and i want a tickbox to be ticked on the results.. (an escalated tickbox)
for some reason i cant make a macro with the following:
OpenQuery
SetValue
The query i'm using to detect if something needs escalating is quite complex, which is why i cant use an openform or similar?
Any help would be appreciated. James.
i've got a Form that contains Subform with an embedded Query that contains 2 tables only (Payments & Invoices) the Join properties between them show all records from payments and what matches it from Invoices where the joined fields are equal (Invoice no).. So, when i enter certain data in the main form the Subform show the results for it from Payments table and only one field needed from the Invoices table !!
The problem is.. i can't edit or add any data in the Subform results with the previous setting, but when i completely remove the Invoices table from the embedded Query then swift to the Form and it's Subform.. i become able to edit and add data in the Subform easily... !!
So, how to enable the edit/add in the Subform with the 2 tables in the embedded Query ?! Cause it's really needed to show that field from the Invoices table.
I am trying to create an Access 2003 database that contains several types of demographic information about the employees in my organization (40+ employees). I have created a database framework, but I feel as if I am not doing things as efficiently as I could be. I was wondering if anybody could look at my database and let me know if they notice anything in my tables or fields that could be better/fixed. I would appreciate it so much.
I have attached the database in a .zip file to this post (it wouldn't allow me to attach it as an .mdb file, so I zipped it).
Thanks so much. Feel free to reply via this thread or email me with any comments/help you can give me. Thanks so much again!
TiM
Hello,
Hello I keep getting the error after I copied a form
and saved under a different Name
++++++++++++++++++++
A problem occured while Microsoft Access was communicating with OLE Server
or ActiveX control.
There may have been an error evaluating the function , event or Macro
+++++++++++++++++++++++++
Strangely, I keep getting this error when loading
and clicking on a control in the ORIGINAL form
while the copy seems to work without errors.
I have deleted some tex box controls from the first
form and added a few new ones in the copy.
I have tried a copy of the database by deleting the
New form, but still get the same error.
I have run DEBUG on both form codes and
everything seems fine.
I have checked the refrences and find none missing.
Is there any way to resolve this issue. Or am I doing
something fundamentally wrong by copying
and modifying the form ?
I have attached the file with the two forms
Guys, any idea how to evaluate the command line options used when an Access application is started from a command line.
For example I use the following command line: C:..MyDatabase.mdb /parameters:110001110
Then, in VB, I evaluate the string "110001110" and do the further job.
Is there any property or function to return the command line option string?
Thanks
I was not sure exactly how to word the title so that it made sense, but I have strDocName assigned a report name and would like to use the value of the string on the left hand side of an evaluation. In the example below (which changes the caption of a report depending on the week) where stDocName appears in the "Reports!stDocName.Caption = ", I wish to actually use the value stored in the string. Does that make sense? This is what I have:
Code:stDocName = "rptTopScores" DoCmd.OpenReport stDocName, acViewDesign, , , acHiddenReports!stDocName.Caption = Forms!frmDoStuff!LeagueName & " - Match " & Forms!frmDoStuff!txtWeekNum & " - Media Report"DoCmd.Close acReport, stDocName, acSaveYes DoCmd.OpenReport stDocName, acPreview
This is what I want it to evaluate as:
Code:stDocName = "rptTopScores" DoCmd.OpenReport stDocName, acViewDesign, , , acHiddenReports!rptTopScores.Caption = Forms!frmDoStuff!LeagueName & " - Match " & Forms!frmDoStuff!txtWeekNum & " - Media Report"DoCmd.Close acReport, stDocName, acSaveYes DoCmd.OpenReport stDocName, acPreview
And yes I know I could just use the second code segment, but then I wouldn't be able to use the code snippet without remembering to edit it. I've spent a couple hours trying to find the correct method to no avail. Any assistance out there?
I am having problems getting Access to evaluate whether a textbox is empty using VBA behind my form. I'm currently using Access 2000 on a Windows98 machine, but I also tested this in Access 2003 on an XP machine and got identical results.
The way I’d like the code to operate is if the “Product1” field is empty, the value taken from another form would be pasted there, and if it isn’t empty, go to the “Product2” field and if that field is empty, paste it there, otherwise go to the “Product3” field, etc.
I have tried using If IsNull(“Product1”) Then… but Access seems to think that the unbound textbox with no default value isn’t empty.
I tried using If ("Product1" <> "*") Then… but Access again seems to think that there is something in that unbound/no-default value textbox.
My code looks like this:
If ("Product1" <> "*") Then
DoCmd.GoToControl "Product1"
DoCmd.RunCommand acCmdPaste
DoCmd.OpenForm "ActiveProduct", acNormal, "", "", , acNormal
DoCmd.GoToControl "Description"
DoCmd.RunCommand acCmdCopy
DoCmd.OpenForm "Order Form", acNormal, "", "", , acNormal
DoCmd.GoToControl "ProdDescr1"
DoCmd.RunCommand acCmdPaste
DoCmd.Close acForm, "ActiveProduct", acSaveNo
DoCmd.GoToControl "ProdQty1"
DoCmd.Close acForm, "Product Lookup form", acSaveNo
ElseIf ("Product2" <> "*") Then
DoCmd.GoToControl "Product2"
DoCmd.RunCommand acCmdPaste …
End If
I admit that my VBA skills are limited, but after having scoured these forums as well as Access help and examining the logic of my code, I am completely stumped. Any suggestions you can offer would be greatly appreciated!
Thanks!
Code:
'Pacer frame set to proper background
If Me.FrmPacer.Value = 1 Then
Me.LblPacer.BackColor = vbGreen
ElseIf Me.FrmPacer.Value = 2 Then
Me.LblPacer.BackColor = vbYellow
ElseIf Me.FrmPacer.Value = 3 Then
Me.LblPacer.BackColor = vbRed
[code]...
I have multiple frames on one subform that when selected sets a unassociated labels back color. Then when cancelled by undo code the back color stays on the color that it was changed to on afterupdate event from the frame with option controls. I would like to loop thru but could not come up with the proper method if it is even possible.the above code works after refreshed, but just wondering if there is a shorter or more efficient method.
Error: Error evaluating check constraint in the validation rule
What does this mean?
I have used a combo box in the table to input into 2 fields - one restricted to numbers 1 to 5 and the other restricted to letters A to E
Message comes up when I try to change the view or press submit. Does it have anything to do with the field type?
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
I'm struggling with a query to evaluate current and prior record data. I have a query producing 5000 records. I need to group records by Case ID and compare current date record to previous date record to determine if a team and worker name has changed during the year. I need to count how many times a cases is transferred to and out of a particular team from the beggining of the year. i.e., On 1/1/2014-Team1 has an inventory of 500 cases.
During the month 25 cases are transferred into Team1 and 15 cases are transferred out of Team1. So on 2/1/2014 Team1 begins with an inventory of 510 cases. Throughout the year cases come and go from and to Team1 each month so need to figure out how to create a query to count each change. See attachment displaying how the data is listed and how I invision it to work with the In/out column counts.
Specifically, at the begginig of the year (1/1/14) for case ID 1003 you can see it belongs to Mary in Team1 for January and February. Then in March the case is transferred out and went to Joe in Team 3. So for Mary a "-1" is recorded as a negative count for that Case ID. If later in the year the case is transferred back to Mary a "+1" would be recorded. Respectively evaluated for each of the 5000 records to get a total count for each of the teams by Case ID throughout the year.
I've started with DLookup, tried comparing current month to previous month using DMax. It works as long as I only select one case ID used in a separate query but if I use the whole subset (5000 records) it fails. I can't figure out how to group each set of case IDs and then apply the query.
My attempted query: DLookUp("[Team]","[Team_qry]","[Team_qry]![Date]=#" & CStr(DMax("[Date]","[Team_qry]","[Team_qry]![Date] <#" & CStr([Date]) & "#")) & "#")
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.
Can someone help me???
Thanks,
Marilyn
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?
Can i edit a query to equal a SQL string just like i do for my reports? for my reports i say:
DoCmd.OpenReport "Check Filter", acViewDesign, , , acHidden
Reports![Check Filter].RecordSource = strRecordSource
DoCmd.Close acReport, "Check Filter", acSaveYes
i tried to say
DoCmd.OpenQuery "Query1", acViewDesign, acEdit
Queries![Query1].RecordSource = strRecordSource
DoCmd.Close acQuery, "Query1", acSaveYes
i get an error in the middle line...how do i do what i'm trying to do?
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'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?
Thank you.
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.
I have set up a query to pull data from within a date range.
I have written an SQL Statement to amend the format of the date field:
Effective Date: IIf([Date_Effective_From]="00000000",Null,DateValue(Mid([Date_Effective_From],7,2) & "/" & Mid([Date_Effective_From],5,2) & "/" & Mid([Date_Effective_From],1,4)))
Within my criteria I have:
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.
Can anyone suggest anything I may have missed?
Thanks
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???
Thanks!
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
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?
thanks
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:
Viable Not viable
14 12
displays as 1412
So how do I add the values in the rows???
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?
Looking to be pointed in the corect direction,
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
many thanks
Cheers
watson