Modules & VBA :: Return The Field Name From Expression
Mar 13, 2014
I'm looking for a bit of code for a module that will return the field name from an expression (that uses an array) instead of returning the value in that field.
Example:
(Minimum is a working module that finds the smallest number in the array)
Field1 = 34
Field2 = 53
Field3 = 21
Field4 = 62
GetFieldName(Minimum([Field1],[Field2],[Field3],[Field4])) = "Field3"
I've searched but can't find a way to select the field name anywhere...
View Replies
ADVERTISEMENT
Oct 30, 2013
I have a field created in a query expression
BirthMon: Format([DOB],"mmmm")
I would like to create another field in the query using DLookup to return the value of the BrithMon.
BrithMonthID=DLookUp("MonID","tblListMons","BirthM on = Mon")
This works good if used with an unbound text box on a form, but when entered into a query expressions, an error is returned: cannot find the name 'BirthMon'
Can I used DLookUp in a Query expression to refer to another Query created field?
View 2 Replies
View Related
Apr 17, 2014
I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.
Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant
Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer
Dim I As Integer, J As Integer
ReDim varTempArray(UBound(FieldArray))
intArrayValues = 0
[Code] ....
As you can see, this works to find these values while ignoring NULLS. How to return the field name?
View 1 Replies
View Related
Aug 19, 2005
When running a query, I want to hide duplicates records for a given field. PO# will have many duplicates, depending on how many part numbers were order on the same PO#. Other unique fields are not displayed. Can an expression help with this? If not, how do I go about hiding them in a query?
Current Query results:
DATE PO#
08/18/2005 ABCD
08/18/2005 ABCD
08/18/2005 ABCD
08/18/2005 ABCD
Desired Query results:
08/18/2005 ABCD
Thanks in advance with any help!
View 2 Replies
View Related
Nov 14, 2014
I have a small table, that only contains one record (and should only ever contain one record, which simply gets edited to suit once in a while).The table is called OtherRates, and the fields are as follows:
WRD_Sat/Sun/BH
WRD_Mon-Fri
AHDifferentia
SKDifferentia
Sat/Sun/BH_OTRate
Mon-Fri_OTRate
I am trying to use DLookup to return a value from the relevant field in this table, however the field to look in is a variable.The code (below) that I have tried returns an error and highlights the DLookup line,.
Code:
Dim WRD
Dim DayType
[code]....
View 3 Replies
View Related
Apr 6, 2014
I'm trying to create a report that's based on a query, and the query has three fields: [PersonName], [PersonDate], [PersonShift]. This table holds records for people that worked on certain days and certain shifts. What I want to do is create a report that gives a graphical calendar display of each day in a month, and on any day that the person has a record (and sometimes there are more than one), I'd like to see just the PersonShift records showing in that day's box.
tblPersons
PersonName PersonDate PersonShift
Jason 4/10/14 FIRST
Jason 4/13/14 FIRST
Jason 4/13/14 SECOND
So if I were to print this report for Jason, I'd get all the days in April laid out like a calendar, and on 4/10/14 you'd see "FIRST" in the box, and on 4/13/14. you'd see FIRST and SECOND in the box. All the other boxes would be blank.how to display the calendar, how to display the dates. I'm able to return records to those boxes by creating 31 separate queries, one for each day of the month, and each query returns records for that day. The queries are added to the report as subreports. It all works beautifully.
The thing is, I'm running 31 queries every time I pull the report. Is there a way to code a single field on a report that will run a SELECT statement on the table using variables that are located in fields elsewhere in the record?
If I have a PersonName field on the header of the report, and I have a PersonDate field in the detail of the report's record, can I create a new field in the detail of that record that runs a SELECT statement on qryPersons, and filters the tblPersons by the PersonName on the header of the report and on PersonDate in the record?
I want a field on a report that runs a SELECT statement on tblPersons, I want the field to return only the PersonShift records for that person based upon the PersonDate. Each of the fields on my report have a CalendarDate field, and I want the SELECT statement to return records where the PersonDate matches the CalendarDate, and again, it should only display PersonShift records.
View 2 Replies
View Related
Sep 16, 2014
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.
View 3 Replies
View Related
Aug 14, 2014
I have an expression in my query and i'd like to return a value of 0 is the expression yields a null value. Here is the expression i have:
IsNull(Sum(([qryTime].[hours]*[Rate])+([qryTime].[minutes]*[Rate]/60)+([qryTime].[seconds]*[qryTime].[Rate]/3600),0)
I'm getting an error "the expression you entered has a function containing the wrong number of arguments". How can i resolve this?
View 14 Replies
View Related
Jun 28, 2013
I want to return a value from a separate query and have that show up in a field on my main form. The field name from the query is "Six Month Date" and the query name is "DT6Monthqry". I want to return the value displayed in the"Six Month Date" from the query, matching the CustomerID in the query result to the CustomerID that is being displayed on the main form. The CustomerID is a text field.This is the code I placed in the Control Source property for the field I want to be displayed on the main form:
=DLookUp("[Six Month Date]","DT6Monthqry","CustomerID=" & [CustomerID])
The result of this displays a flashing "Error" in the field, even when I go to a Customer that I know has a result in the "DT6Monthqry".
View 8 Replies
View Related
Mar 22, 2014
Expr1 is a field in a report that is bound to this query.
timeline.CompanyName doesn't usually have any data in it, and when it does it needs to be removed and manually input into companies. Company Name.Is it possible to make timeline.CompanyName output as italics? Otherwise we dont know which field is populating.
Code:
SELECT [timeline.CompanyName] & [companies.CompanyName] & " (" & [companies.city] & ", " & [companies.state_id] & ") ~ " & [timeline.Title] AS Expr1, timeline.showitem, timeline.EntryId, timeline.EmployeeId, timeline.CompanyName, timeline.EmpStartDate, timeline.EmpEndDate, timeline.Super, timeline.CoPhone, timeline.Notes, timeline.Title, timeline.company_id, timeline.start_date, timeline.end_date, timeline.assignment, timeline.notes_staff, timeline.showitem
FROM timeline LEFT JOIN companies ON timeline.company_id = companies.company_id
WHERE (((timeline.showitem)="false"))
ORDER BY timeline.end_date DESC
WITH OWNERACCESS OPTION;
View 3 Replies
View Related
Jun 30, 2015
How to write an expression/criteria in a query that will return only ID numbers with alpha numeric values. Example: My Id field contains both numeric 479621, 680530 and alpha numeric 132NAV100, 174NAV26a values. I want to run the query to return only the alpha numeric values.
I do not know SQL nor VBA.
View 11 Replies
View Related
Jun 10, 2013
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
View 14 Replies
View Related
Sep 4, 2013
I have a database which connects to another application (by creating an object) for the purposes of reading/writing data. Part of the code involves creating an instance of this application, and then taking input from the user via an Access form.However, when I load the form, the focus stays with the other application, and the user has to 'click into' the form in order to reset the focus to the form and key the relevant details. Not the end of the world, granted, but frustrating over time and something I'd like to iron out.
I originally had this process in Excel, and used the following code to change the focus back to Excel (so that the user could key directly into the form)
Code:
Dim ExcCap As String
ExcCap = Application.Caption
' ...assorted code, including creating the object to 'house' the other application...
' Return focus to Excel
AppActivate ExcCap
But this doesn't apply to Access as it utilises the Application.Caption / AppActivate method to identify the appropriate window to switch back to. In Access, I don't have a caption to reference.I've tried setting the focus in the Form_Load event but it doesn't bring the form 'to the fore'
Code:
Private Sub Form_Load()
Me.SetFocus
End Sub
View 8 Replies
View Related
Sep 4, 2013
Code:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim SQLstr As String
[Code]....
I created this about 1 hour ago but my laptop crashed and didnt save. So, I open a record set and rs is now loaded with the record I want,
how do I assign the value of "Status" as a vba variable. when I try StatusInt = rs I get the "Type Mismatch" error...
View 4 Replies
View Related
Jul 30, 2015
How i can cancel the a carriage return and replace with a tab or stop it moving down my subform and make it move the the next field instead of going to a new line.
I am using the Keydown but don't know how to cancel it, it just bring an message but it stops it moving to a new record,
Code:
Private Sub Barcode_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
MsgBox "rtn pressed"
End If
End Sub
End If
View 5 Replies
View Related
Feb 19, 2014
I'm new to Access and VB but I managed to write parts of what I want access to do.By tweaking a code I found on the internet, I managed to write a small Subroutine that allows the user to click on one of the fields of a record in a report and then Access opens the form on that specific record.*My code goes something like this:*
Code:
Private Sub Edit_Click()
* Dim strWhere As String
* Dim DocName As String
* DocName = "FormName"
* strWhere = "[Field Name]='" & Field & "'"
* DoCmd.OpenForm DocName, acNormal, , strWhere
End Sub
Now I want to get the name of the Table where the record exists.So, let's say when I click on the "Field" it gets the name of the table where the record with that field exists and sets it in a variable.OR even better would be, get the name of the Form where that record exists but I guess that's a little more complicated since the record is directly linked to the table...*
View 14 Replies
View Related
Jul 26, 2013
what is the best/cleanest way to return the next primary key value which is in the format yyyy-###.These are case files for the current year, ### being 3 digit number incremented by one for each new case.
I could use a function that finds the last one, checks the date, increments by one and returns it, but maybe I could use a public property or a class? I'm keen to use classes more for code maintenance.With a class then: would i create a PrimaryKey variable of the class type/object, so when I need a new key I would just type PrimaryKey.NewKey, for example?
View 5 Replies
View Related
Oct 30, 2013
I have a query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.
I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to
Code:
Function SortStart(StartTime As String) As Integer
then I get zero.
I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.
Code:
Sort_Start: IIf([StartTime]="7:00 AM",1,IIf([StartTime]="8:00 AM",2,IIf([StartTime]="8:45 AM",3,IIf([StartTime]="9:00 AM",4,IIf([StartTime]="9:15 AM",5,IIf([StartTime]="10:00 AM",6,IIf([StartTime]="10:15 AM",7,IIf([StartTime]="10:30 AM",8,IIf([StartTime]="12:00 PM",9,IIf([StartTime]="1:30 PM",10,IIf([StartTime]="1:45 PM",11,IIf([StartTime]="2:00 PM",12,IIf([StartTime]="3:00 PM",13,IIf([StartTime]="4:00 PM",14))))))))))))))
View 3 Replies
View Related
Aug 3, 2015
I'm trying to use a DLookup to get a specific value from a field in a table.
This is what my code currently looks like;
Code:
JOBID = DLookup("[ID]", "MASTER PLANNER", "[JOB NUMBER] = '" & JOB_NUMBER & "'")
Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.
However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.
what I'm doing wrong?
View 9 Replies
View Related
Jan 3, 2014
I would like to select a case depending on the output of a function.
This function tests the syntax of the reporting month.
If the syntax is fine nothing should be done further in the main sub else it should return to the Input window for the reporting month.
Somehow it doesn't work out.
Code:
Public Function RepMonthCheck(rep_date As String) as Boolean
If Len(rep_date) <> 6 Or Left(rep_date, 2) > 12 Or Left(rep_date, 2) < 1 Then
MsgBox ("Reporting rep_date is not in the correct format = mmyyyy")
Return False
ElseIf Right(rep_date, 4) > 9998 Then
MsgBox ("No forecast available for year 9998")
[Code] ....
View 3 Replies
View Related
May 2, 2014
I have a date field in a form fed by an option group. Most of the options return the correct values, but I'm having a hard time with this:
If the inspection date (in another form) is in Jan Feb or March, then return the date 1 Apr of the inspection year. If not, then return 1 Apr of the following year.
Here is the trouble line:
Code:
Case 2
IIf (Month([Forms]![FrmGeneralInspection]![txtInspectionDate]) <= 3), Me![txtReInspectionDate] = DateSerial(Year([Forms]![FrmGeneralInspection]![txtInspectionDate]) + 1, 4, 1), Me![txtReInspectionDate] = DateSerial(Year([Forms]![FrmGeneralInspection]![txtInspectionDate]) + 2, 4, 1)
View 4 Replies
View Related
Sep 23, 2014
Question for Documentation purpose: Should the Public Type be declared in its own module?
Or should it be declared in a standard module where non-public functions use it? It is not for a Form module use.
For a Rule Engine, a function is calling one record on 4 different SQL Views (as linked tables) that have the same field format.
For speed, the recordset should only be opened once. However, there are multiple values that must be returned to the result table multiple fields.
One way to return multiple values is an Array. That has over head too.
Another way is to create multiple public variables. Not my choice for documentation. Another is to create a string.
This is a pure code module with several non public functions / subs. What is the documentation preference? List a Public Type close to the function, or place it in the Global module?
Background: A function can only have one return value.
By creating a public Type, multiple values can be returned.
Code:
Public Type Income
Wages As Currency
Dividends As Currency
Other As Currency
Total As Currency
End Type
Use this structure as the return type for a function. In a real situation, the function would look up your database tables to get the values, but the return values would be assigned like this:
Code:
Function GetIncome() As Income
GetIncome.Wages = 950
GetIncome.Dividends = 570
GetIncome.Other = 52
GetIncome.Total = GetIncome.Wages + GetIncome.Dividends + GetIncome.Other
End Function
To use the function, you could type into the Immediate Window:
GetIncome().Wages
(Note: the use of "Public" in the Type declaration gives it sufficient scope.)
Important Notice The way this function is called will work, but is wrong from the aspect it re-calls the recordset over and over.
See the proper way to use it submitted below.
View 5 Replies
View Related
Feb 25, 2014
I am trying to use a DLookup to search to find what block goes into the production of an item.
In a table called "dbo_vw_MCE_job_with_materials", each item contains a list of the parts which go into its production in a column titled "Expr1". However there are always more than one parts to this, with varying names.
What I am trying to do is to use a Dlookup to only return the first value which starts with B (as the block number is always the only part on the list which begins with B).
At the moment my DLookup would look ike this:
Code:
MixTypeTxt = DLookup("Expr1", "dbo_vw_MCE_job_with_materials", "[item] ='" & Forms![Theta Input]![ItemNumbertxt] & "'")
However I think as the item column contains the same item number in 8 columns, with different values in the Expr1 column, Dlookup only returns the top value "A-CF0057" (which is irrelevant as far as I am concered) - whereas I am hoping it will be able to pull the "B1499" value from the column as it is the only one starting with a B
Is there anyway to specify which value the dlookup function would return, or is there any way to apply some sort of permanent filter on my table?
View 1 Replies
View Related
Feb 11, 2014
I have been trying to find a solution to why I can't get a Dlookup with multiple criteria to return the value I need.
Essentially I am trying to use an Order Number to find the item number which is contained within another table. However the order number has multiple lines (suffixes) which alter the item number. Therefore I am trying to have the item number be populated by the correct 'combination' of Order Number and line ("suffix").
I have managed to use the Dlookup in the after update of each box of the form separately and they retrieve values in the table correctly:
Afterupdate of main order number:
Code:
Private Sub OrderNumbertxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")
End Sub
Afterupdate of suffix:
Code:
Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", "[suffix] = Forms![**INPUT]![SuffixTxt]")
End Sub
However when I combine them as follows in the afterupdate of the Suffix box I receive a "Run-time error '13': Type mismatch"
Code:
Private Sub SuffixTxt_AfterUpdate()
ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'"))
End Sub
I think the reason is something to do with some being numbers and some being a combination of text and numbers (based on the replies of other topics), but have been trying to modify these slightly and can't get it to work still.
Also the Order Number is a combination of letters and numbers (normally in the form of AB12345678), the suffix is just a number between 0-9 and the Item number it finds is a combination of numbers and letters.
View 3 Replies
View Related
Aug 28, 2013
Need to use CAST to return integer value of string (digits as data type string).
Where clause looks like this:
... Where Cast([Price File] as int) > 0
works fine in SQL Server but not sure what syntax is in VBA . Using Paul Baldy's suggestion to set Select statement as string and do the debug.print to verify that SQL has no goofs ... looks good but not to Access. What is proper syntax?
View 12 Replies
View Related
May 28, 2014
I have an Access 2010 application that talks to SQL Server. Part of the application creates tables on the SQl Server and inserts data to it (I know SQL Server has its on data inport/export wizard and bulk insert capabilities but for reasons outside of my control it needs to be done from Access). So I have a connection to the SQl Server and i have VBA that happily executs SQL statements on the SQl Server. The problem is I want to return a recordset from the result of a SQL query issued to the SQL Server. The idea being to check for the presence of tables already created, and if so, append to the already existing table, otherwise if it does not exist, create it and insert data to it.Ive tried the following code but it seems no recordset is being returned:
Set objConnection = New ADODB.Connection
objConnection.Open "DRIVER={SQL Server};SERVER=10.200.3.14;trusted_connection=yes; DATABASE=" & Me.Combo54
strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" & Me.Combo54 & "'"
strSQL = "USE " & Me.Combo54 & " " & strSQL
Set Rst = cmd.Execute("USE " & Me.Combo54 & " " & strSQL)
View 1 Replies
View Related
