Functions That Should Return Same Result But Do Not
May 8, 2015
I 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
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)
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 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
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 ?
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?
All 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 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.
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.
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField] "kick the ball" "return the pass"
my query result should looks like this
[Orig] [new] "kick the ball" "kick" "kick the ball" "the" "kick the ball" "ball" "return the pass" "return" "return the pass" "the" "return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
All: I'd like to make the result of my text box flash (in my form) if the its value is less than 30..For example. If A - B < 30, then the result whatever it is as long as it is less than 30..Is this possible in Access? thanks in advance.. :
and i would like that if Text0 is "test.exe" i want to return "exe", however, with the above i am getting ".exe" ... can someone tell me what i am doing wrong please ?
I currently have a text box that is calculated off of 6 other text boxes. I want the one field that is calculated to update any time I change any of the other fields. I can do this by putting the same calculation in each ones afterupdate event. Below is one section of my code for the arithmetic, and I want to perform that code many times. Can I use a function to do this, and if so some help would be nice? thanks
In access, I'm trying to create a database to track material that has an expiration date of one year from the appointment letter date. I'm not sure what I need to do here. Here's what I have: I have a table that has fields for the individual (UNIT), the appointment letter date (Appointment Letter Date), and a True/False field (Out of Date) that I want to make True when the current date is greater than the (Appointment letter date + 365 days). Is this possible through Access tables and/or queries or will I have to incorporate VB? I am an extreme novice when it comes to expressions and query building in Access.
Maybe I've been looking at the monitor for too long but... I need to have a query return data based on a date field or return all dates greater than 1/1/2001 if left blank.
criteria: [Enter date - mm/dd] Or >#1/1/2001#
When I leave it blank it gives me all dates as it's supposed to but when I type a date it does not filter by that date, I still get everything.
I also tried [Enter date - mm/dd] Or Like "*" but it behaves the same way. I know it has got to be easy but my skull may be too thick to let me see it.
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
I will firstly give you the low down on the business that I am doing the database for. It is a Car Restoration Business, where we deal with vintage/classic/muscle cars and restore them to the former glory.
I do not know Microsoft Access very well, I have attached a file with what I think is a databae :) I could be wrong. The main function of this database is to search for suppliers for a specific car part for a specific car.
So if I want to find a doorhandle for a 1970 Ford Mustang, I need the suppliers pertaining to those criterias to come up.
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue but I cannot be sure.
Both computers run the same version of Office. Does anyone have any ideas.
I have developed a MS Access database which uses several functions such as DateDiff and inStr. When I use these function on my machine the database runs fines and the values are calculated.
However the database I have developed is not for my computer and when run on this one computer produce some errors (The date function is mentioned). I believe this is some sort of security issue stopping her running the functions.
Both computers run the same version of Access. Does anyone have any ideas.
