I have a table that has 3 columns: Unique number, Date, and Results
I want the user to enter the unique number and date into the form. Then the "results" column/field will autopopulate a 0 or 1. I want it to populate a 0 85% of the time and a 1 15% of the time. This should be cumulative (meaning not every entry has a 15% chance of being 1). Is this possible?
I have a subform based on a query. In that query, I have calculated values for price+tax and another price+discount+tax. Now I have a conditional formatting that if the gross total is not equal to amount due, the amount due box will turn red. However even if they are equal visually, it still turns red. Ex. The gross total is 336.00 and the amount due is 336.00 also, so it should NOT turn red, but it does. When I click on the amount due control, the value turns into 335.999999195337. How do I tell access not to make 336.00 into 335.999999195337.
View 3 Replies View RelatedI have two functions both should have the same results but they do not.
Public Function DefaultGreeting() As String
On Error Resume Next
DefaultGreeting = "Dear " & [Forms]![frm_contacts]![Dear] & ":"
End Function
Public Function DefaultBodyText() As String
On Error Resume Next
DefaultBodyText = [Forms]![frm_e_mailing]![mess_text] / this equals this just pulled from a table "Dear " & [Forms]![frm_contacts]![Dear] & ":"
End Function
Why this SQL will return only one query maximum?
DateLimiter: (SELECT Date_Retro_Fees_Rate FROM tblRetroFees AS Alias WHERE Date_Retro_Fees_Rate = (SELECT FIRST(Date_Retro_Fees_Rate) FROM tblRetroFees AS Alias2 WHERE Alias2.Date_Retro_Fees_Rate > tblRetroFees.Date_Retro_Fees_Rate AND Alias2.Id_Product = tblRetroFees.Id_Product) AND Alias.Id_Product = tblRetroFees.Id_Product)
A picture of the table is included.
Hi there !
I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet.
Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.
So I try this :
*here is my function
Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant
Dim requetteSQL As String
Dim rst As New ADODB.Recordset
requetteSQL = "SELECT libelle " _
& "FROM operation;" _
rst.Open requetteSQL, cnn
get_nom_operation = rst.GetRows
End Function
* and here is the code that call the function
Dim res As Variant
Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1
For i = LBound(res) To UBound(res)
Set Obj = Me.Controls.Add("forms.CommandButton.1")
With Obj
.Name = "monButton" & i
.Object.Caption = res(0,i)
.Left = 14
.Top = 25 * i
.Width = 60
.Height = 20
End With
'ajout de l'objet dans la classe
Set Ge = New gere_event
Set Ge.CButton = Obj
Collect.Add Ge
i = i + 1
Next
But it doesn' work, and I don't know why...
The error doesn't show where the code is stoped, but only show the call to display this form.
someone could show me some way to find the solution please ?
Thanks a lot.
Basically in the query I need a Collumn to only output the greatest value that is given within the sub query. If that makes sense?
Eg. Tenancy start date must be the highest date against the date the property was added back to the system - for post development properties.
At present the it's returning previous tenancies from pre development.
Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?
Otherwise is there a way to make a master query ignore sub-queries if they return no records?
Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.
These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.
Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.
The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).
Here's an example of my sql statement for the modal queries.
Code:
SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]
FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID
GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate
HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))
ORDER BY Count([Toolbox Talks].TrendID) DESC;
- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.
My boss has a form based on a rather large table with a lot of records/fields and she wants to be able to have a field where she can enter something and it will seach every record in the table and return the results in a table. How do I do this?
View 9 Replies View RelatedAll using access 2010. Here's the situation. I built a searchform according to datapigtechnologies video. Used a query with criterias on fields i want to search on the form: ex.
Code:
Like [frm]![frmMyform]![MyField]
It worked fine i thought until i ran the same query outside the form without the criteria field. I filtered the query for the same result and the one used on the search form did not return all data records for that particular result set. Ex. on the searchform; I select the fields I wanted to filter then ran the query. I come up with only 9 records when it should be 18 I get with running the exact same query without the criteria and manually filter the results.
I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.
I have attached two example for your reference.
I have several queries that perform an audit on data. I want to create a form that has two buttons, multiple labels and textboxes. When the first button is pressed, it runs all queries and returns a count of the records within each query into the corresponding textbox. Then I want a a button that will clear all textboxes. I don't want to see the queries, I just want them to run in the background and the count results to be populated in the textboxes.
So for example, it would return:
Check1: 3
Check2: 6
Check3: 2
Check4: 0
I have a query that returns several calculated fields. One of them is simply derived by simple summation of the others. If this calculated field returns a negative number, I need it to show as a zero.
The only way I know how to do this is by an IIF statement :
Code:
SELECT [fld1], [fld2], [fl3], .....
IIF(([fld1]-[fld2]-fld[3])<0,0,([fld1]-[fld2]-fld[3])) AS fld4
FROM...
(The above doesn't suggest that [fld1], [fld2] etc are calculated fields - I just wrote it like that for succintness - they calculate fine, there's no issue with them...)
Is there a more efficient way of doing this? I find IIF's a bit tardy, possibly because they evaluate for both True & False eventualities, regardless of the condition, and this query is going to run against a fairly large dataset so any performance lag is going to be exacerbated.
Here's a sample of the query code for survey I made, for example
Question 1 will be
1) Male or Female?
(radio button answer_id 111) Male
(radio button answer_id 112) Female
etc etc...
My question is how do I set my query to show a graph / percentage value of total unique answer_id's in relation to the question_id.
e.g. Question 1: 66.67% chose 111, 33.3% chose 112
question_idanswer_id
1111
1111
1112
2116
2117
3119
3121
4126
4126
4126
I hope I was clear enough am quite new to Access and I really thought I could do this with pressing a few filter/sort buttons but alas I haven't found a way yet.
I have an access form which 1 person updates daily, but the task after that is split between 2 users. I have got most of the form data automated with a combo box from a supporting table.
But I now want to automate a username & date, but based on a % and set of conditions.
(At the moment I go in at the end of the week and manually put those 2 usernames next to the new work thats been logged)
I'll try and explain:
user 1... gets 60% of the work
user 2... gets 40% of the work
At the end of that day, when the form is completed with say 100 new records, I want to then run a query, macro (or anythying else that would do it automatically), to assign those usernames 'user1' to 60 of those 100 records... and 'user2' to 40 of the records. and also give it todays date.
Is this even possible?
I have 2 table,
Table1 has fields
docno (text)
title (text)
progress (number)
sample data is like below:
doc-001 test1 90%
doc-002 test2 25%
Table2 has fields
sample data is like below:
id (number)
icon(attachment type)
1 image1
2 image2
Now I would like to make a query when progress of each docno is equal and more than 50% it uses image1 And when progress is less than of 50% it uses image2
Doc-001 test1 90% image1
Doc-002 test2 25% image2
I have a table with the name of a report, the items on the report, and the number of items on the report. I would like to count the instances of the names of the individual reports that appear on another table and then divide that count by the number of items on the report.
View 1 Replies View RelatedIf you have a form that is based on a select query that has selected no records, the form will display as totally blank.
One way to avoid this is to ensure that the query is always able to select at lest one record; but Is there a way a message can be displayed if no records are selected?
Dear All:
Thank you for all you help with the above title. Your suggestions were all implemented.
Code:
Private Sub GPA_AfterUpdate()
If DIVISION = GRADUATE Then 'Honors field is empty
HONORS = ""
End If
If UNDERGRADUATE = True Then 'Apply appropriate honors
End If
If GPA < 3.2 Then
HONORS = ""
ElseIf GPA >= 3.2 And GPA < 3.5 Then
HONORS = "CUM LAUDE"
ElseIf GPA >= 3.5 And GPA < 3.8 Then
HONORS = "MAGNA CUM LAUDE"
ElseIf GPA >= 3.8 Then
HONORS = "SUMMA CUM LAUDE"
End If
End Sub
Here is the issue: Form created with with a combo box called "DIVISION."The choices of the combo box are "GRADUATE" AND "UNDERGRADUATE." There is a text box called "GPA", where a gpa is entered. then, there is an "Honors" field. When a gpa is entered, the honors box returns the appropriate message.
When graduate is selected from the combobox, the honors box should be empty. (That part does not work.) When undergraduate is selected from the combo box, it works.
Please help,
Dion
Hello,
I need to create a query that will seach through a column and only return a result with product codes starting with A.
An example of a product code is AA0000034567.... however there is no realy consistancy. Some start with the AA and some don't. Some have 5 zeros before the numbers start others may only have 1, 2, 3, or 4 zeros before the number.... There are approx 76,000 product codes in total.
My end goal is to create a query to find all the records that have a product code starting with A so that I can eventually delete the AA00000 portion of the code and strip the number back to the point where the 34567 starts.
I figured that by separating out the product codes that start with AA would be a good start.
If any one has any suggestions please let me know.
Peter Vav
I currently have the sql below...
UPDATE tbl_Node_List INNER JOIN qryUpdate_P1 ON tbl_Node_List.Zip = qryUpdate_P1.[Zip Code] SET tbl_Node_List.[Date Sent] = (SELECT [MinOfAudit Date] FROM qryUpdate_P1), tbl_Node_List.[Date Recv'd] = (SELECT [MaxOfAudit Date] FROM qryUpdate_P1)
WHERE (((tbl_Node_List.Zip)="35243"));
but I get an err.msg stating the operation must use an updateable query.
What I am trying to do is update my dates in 'tableA' with the max and min values stored in 'tableB'. I have read some of the posts but still can't quite get it. As always all help is appreciated.
Hello
I have a field called [Hours]
If a user tries to change the [Hours] I need to check if any of these hours have been assigned to staff in another table.
How do I get to run the query and use the value from the result of the query as the Minimum value?
I would like to
run sql and if the value is being entered into the [HOURS] field is less then show a message and do not change allow the value to change.
I am sure this can be done in VB but I am am unsure of the syntax
I hope U can help
I am trying to setup a button that will open a report on click. The catch is that the report name is coming from a query result that is populated into a list box. The way it works the list box will never have more than one result so by default it will always house the name of the report appropriate to a chosen client. I would like to do it this way so that I don't have to clutter up the from with extra buttons all pointing to specific reports.
I found some references to this in this forum, including the example listed below, but I have been unsucessful in making work so I assume that I am entering wrong, entering it the wrong place or maybe the code is incomplete. Unfortunately I am a complete novice to Access VBA so I am still learning how it all works together.
List box name is BillingFormat
The first of 5 reports is named Standard Billing
So in the form I enter the client number and BillingFormat is auto populated with Standard Billing
I tied the button to this code: DoCmd.OpenReport Me.BillingFormat, acPreview
The results I was looking for was for the report named Standard Billing would open. The first way I set it up nothing happened not even any errors. The other way threw an error stating it did not recognize DoCmd as a valid function.
what I am doing wrong?
I would like a query to return dates based upon the input of just the day and month. At the moment I have a parameter query which asks for 'start date' and 'end date' and this works fine, but I want the query to return all the records for all the years in the database and not just the current one (date format is dd/mm/yy)
So if I type <start date> 01/01 and <end date> 02/01 the query will return:
01/01/04
01/01/05
01/01/06
02/01/04
02/01/05
02/01/06
Does anyone know a solution - I have been searching all afternoon!!??
Hi All
I have 2 formulas that work for me in excel. I hope to be able to replicate the result in Access.
1)
I have the following in a field called PROBLEM STORE NAME:
SALE (DAY 1 - HD)
Mt DRUITT (WOWPOS) (E)
THE MALL (WOWPOS)
TOWN HALL (RF)
LIVINGSTON
I want these to be grouped as WOWPOS or ISS460 (if not WOWPOS), so return in field STORE TYPE as:
PROBLEM STORE NAME.............................STORE TYPE
SALE (DAY 1 - HD)............................................... ..ISS
Mt DRUITT (WOWPOS) (E)......................................WOWPOS
THE MALL (WOWPOS).......................................... ...WOWPOS
TOWN HALL (RF).............................................. ......ISS
LIVINGSTON........................................ ..................ISS
I use the following formula in Excel to achieve this when I export query results from Access:
=IF(ISNUMBER(SEARCH("wowpos",K2)),"WOW","ISS460")
2)
I have a field VENDOR ID that may or may not contain ANY detail. If the cell is empty I need it to return N/A. If populated I need to return VENDOR CALL. The following formula achieves that for me in Excel:
=IF(ISBLANK(X2),"NO","VENDOR CALL")
Any suggestions would be greatly appreciated
cheers
sallee
Dear Access Wizards:
I have created a form based on a query with students' gpa in the form. The gpa is in a specific field.
There is another field for honors with is for summa cum laude, magna cum laude, cum laude.
Here is the issue: If the students' gpa is 3.8, return "summa cum laude"
If the students' gpa is 3.5, return "magna cum laude"
if the students' gpa is 3.2, return "cum laude"
This applies to undergraduate students only, which there is a field for.
Any ideas on how to resolve this issue?
Many thanks,
Dion
I have a form with a field within it called ID number, another table contains the ID number with a person's name next to it. I was wondering how I can make it so that in the form, when the ID number is entered, the name of the person also shows next to it and does so automatically for each different record. I am pretty sure that a subform is needed however it doesn't seem to work for me so I must be doing it wrong. How would I do this?
View 1 Replies View Related