Related Topics

Using Table Field In Like Function

Storing Functions in a Table
What I am trying to do is to store a function call in a table field (identified as text), and then try to run that function in a query. I need to know how to tell the query to run the function, instead of just returning the text of the function.
Example:
Table 1:
Field Name Field Contents
Fields: Product_ID = 68500
Fields: Products_Function = fp([some date from another table], 1)

So when I use the [Products_Function] field in a query it just returns the text contents. I want it to run the function.
MS Access 2003 SP


Linked table query
I have two tables linked where Table A, field 1, is a look up table for Table B in Field 1.
Table A has 3 fields and I want to query Table B based criteria in Table A, field 2 and 3. SQL as follows

SELECT [Table A].[Field 2], [Table B].[Field 2], Sum([Table B].[Field 2]) AS [SumOfField 2]
FROM [Table A] INNER JOIN [Table B] ON [Table A].[Field 1] = [Table B].[Field 1]
GROUP BY [Table A].[Field 2];

I keep getting the error "You tried to execute a query that does not include the specified expression <Table B Field 2> as part of an aggregate function. (Error


Search function that only looks through one field in a table
I'm having trouble figuring out if it is possible to limit the access search function to only one field in a table.

I am working on a database that with one main table, and several form which have record sets using this table (bound). but there is one rather large problem: The table of records they want to search through has over 130 fields (I know, the DB itself was built before I began trying to improveit) When searching for a record, the only applicable field used is [Address], though the preconstructed Access search feature looks through the whole table.which takes a long time with 130fields X 50,000 records.

Can I build a function that searches through only the Address field in the bound table?


Default values in Access 2000
I am trying to assign a default value to a field in an Access table. The default value is generated in a function which is declared as PUBLIC in a module.

When trying to assign this function as the default value to a field in a table in the same db, Access does not recognize the function.

However in a query or in a form I can reference and call the function without any problem.


How to call a function from aMake table query
I am pretty new at this and have LOTS to learn.

Have a Make table query and don't seem to have correct coding that calls a function and places funtion result into specified table field

VBA coding (simplified):

private function Create_Week_Num () Week_Num as String
do something
end function

The VBA coding is in a lib named: Work1 Module1

Query field:
Week_Num:[Create_Week_Num


Function to select field from a table using several criteria
I am new to VBA and need help creating a function to select data from a table in the current database. I would like the function to accept parameters (fieldname, tablename, one or more criteria). I would call the function from any other procedure or from a query field. I am using Access 2007 in the XP.

I would call the function as in this example:

MyFunction("fieldname","tblTableName","criteria1 = 5 and criteria2 = 45")

It would be nice if it accepted numeric as well as strings in the criteria


VBA Access function
I need a function to do:

- Loop through all fields in a table (use tabledef) (use any table tablename should be a parameter for the function)

- There needs to be a timestamp-field which is set on some records and not on others

- For each textfield (only textfields ignore all others!) check if there are new values (compared between the ones with time-stamp and without ) without time stamp will be first default entry and with time stamps will be updated entry

- If there are new values add the name of the field and the new values to a log-table

- After looping through all textfields and putting the name of the field and the values into the log-table export the log table to Excel and show it in Excel. (alternatively you can log directly to Excel)

The end result in Excel should look something like this:

Fieldname1
NewValue1
NewValue2
NewValue3

Fieldname2


search for key word in field
I have a table (t_Marketing List). In this table I have a field for "Item Title". I want to be able to do similar to what I did with this database in Excel with the ctrl+f function where Ican type in a word or series of words and search the Item Title field for anything that matches.

I have created a form with an unbound text box (txtSearch) and linked that to the Item Title field in my subform from my table (t_Marketing List).

I can't seem to get it to function properly. Is there any way to get this search box to function as I had hoped? (similar to the ctrl+f or like a google search)


Populating a field from another table using DLookup
I'm trying to populate a field (we'll call it field#2) on a standard form (Customer Record Card) automatically after I've selected the contents of another field on the same form (we'll call it field#1)

The content of field#1 is a pick-list lookup from a separate table. The other table also holds the look-up list I want to use to populate field#2. The relationship between fileds 1 & 2 is direct. Sounds simple ? It's driving me nuts.

I've tried using DLookup and I'm stuggling.

Can it be done using DLookup or another Built-in function or do I need more complex code.

Can anyone tell me what the syntax for the Dlookup function in the above scenario would be . and where should I put it ? "on click" "after update


fOSUsername
I want a field in my table to list the name of the person who created the record. I've been trying to do this using the fOSUsername function, by copying and pasting that code into a module and then setting the default value of the field to =fOSUsername(). But, this isn't working because I get an error that the function is unknown when I try to save the table. I've spent ages trawling forums looking for an answer but can't make it work.

Can anyone help me, possibly by posting a sample database with the module and a table that has a default value based upon calling the function? I think from that I'd be able to make it work in my own database.


Extended DAvg()
The DAvg() function built into Access lets you get the average of a field in a table, and optionally specify criteria.
This EAvg() function extends that functionality, so you can get the average of just the TOP values (or percentage) from the field. You can even specify a different field for sorting, e.g. to get the average of the 4 most recent values.
Code to average a field in a table/query in Microsoft Access, with the option to count the TOP values. A replacement for DAvg().


Non Editable Textbox Problem
I have a simple form with a textbox whose controlsource I get from function: < Public ControlSourceName as String
Public Function getControlSourceName()
getControlSourceName = ControlSourceName
End Function
>
I set the controlsource of the textbox to the result of this function having given the name of the field in the current table to the ControlSourceName variable.

ControlSourceName = "1(1)ProblemMemo"

On load the textbox displays 1(1)ProblemMemo !

If I put text into the field at table datasheet, the text is displayed but I cannot edit it!
If I put another textbox on the form and put it's controlsource directly to 1(1)ProblemMemo field then all is well.


Getting Rid of Preceding CR/LF
I have an application that looks up an address and then takes the address found and puts it in to a string field in the main table. Not quite sure what's been happening (user error the most likely) but sometimes I'm finding a CR/LF at the beginning of the field (in the database, not the lookup table). I need to leave the field editable so can't lock it down. I've tried adding a Trim function and a Ltrim function to the AfterUpdate for the field but it will only remove leading spaces, trailing spaces or trailing CR/LF. It will not remove a leading CR/LF


Unknown Date Function
I have added the following date function to a Default Value property of a field named PURGEDATE in my table:

=DateSerial (Year(Date()), Month(Date()) + 24,1)

I use this function to add automatically at new entry a date wich is greater of 24 months from current date.

THe error I am getting is:
Unknown function 'Date" in validation expression or default value


Whst is wrong with this DLookUP function???
I create a report and I try to use the DlookUp function;
I have a table (Values_T) [ID], [Accepted], [Reject], [VL100], [Vl600], [E-Date] in my reports I have field txtCalc I try to brinf the value of [VL100] to my report and make some calculations, and it is the function

=DLookUp("[VL100]","Value_t, [E-Date] = & #04/06/2012#")

but I got a #Error in the field what is in my report, What is wrong n with this function


Update in seperate table
I have two tables

one is ClientDetails
the other is ClientContactDetails

in the form for ClientContactDetails I have a last edited field called UpdateDate. I have a button on the form which has a save form function. it also has an on click function "me.UpdateDate = Date()" which updates the UpdateDate field to todays date.

The problem is, when I click the save button I also want it to update an UpdateDate field in the ClientDetails table, how to I change the on click function to update in the other table aswell


Using a function in a report to substitute a string
I'm building a report in Access where I want the value of a field to be substituted by a string.

To be more specific, I have one table text field called client_capability, which can have 4 numeric values (0, 2, 4, 8). Depending on this, I want a given string to be displayed on thereport (0 = Consumer, 2 = Contributor.).

So, I wrote the following function:


need a formula function Name to Initials
I have names in one field and I want it to be initials in a new field. Can someone gave me a push.

Field Name = Tom Smith

Field Initials = TS

I have Name. So, I am thinking it would be:

In the query it would be a new field

Initials: ?

Some Function or Iff statements


VBA Access function
- Loop through all fields in a table (use tabledef) (use any table – tablename should be a parameter for the function)

- There needs to be a timestamp-field which is set on some records and not on others

- For each textfield (only textfields – ignore all others!) check if there are new values (compared between the ones with time-stamp and without ) without time stamp will be firstdefault entry and with time stamps will be updated entry

- If there are new values add the name of the field and the new values to a log-table

- After looping through all textfields and putting the name of the field and the values into the log-table – export the log table to Excel and show it in Excel. (alternatively you can log directly to Excel)


Dmax in a calculated field
I have a query with a field called TotalToDate

I have placed the following DMax function in that field

DMax("nz([TotalToDate])","tbl_ProgramInvoiceLineItem","[Service]=?")+[ServiceCost])

The question Marks represent the criteria part I can not figure out

Basically I want the function to return the max value of the TotalToDate field where the Service field in the query is the same value as the service field in the table.

I hope I am making sense. I have searched and can not find anything here like this though I am certain that it is here. I just can't find it