UDF on query

How to create any user define function that can be called from query
like Ms Access standart function? If possible give one simple
function.
TIA
Yanto

0
yanto
5/9/2007 9:48:24 AM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
723 Views

Similar Articles

[PageSpeed] 47

If you have a function (has to be in a module) that get a parameter and 
return a value, you can call this function from the query just as you use a 
build in function in Access

e.g
Function FunctionName(MyNumber as Double)
   FunctionName = MyNumber * 100
End Function

Select TableNAme.*, FunctionName([FieldNameToPass]) As NewFieldName From 
TableName

-- 
Good Luck
BS"D


"yanto" wrote:

> How to create any user define function that can be called from query
> like Ms Access standart function? If possible give one simple
> function.
> TIA
> Yanto
> 
> 
0
Utf
5/9/2007 10:12:00 AM
Note that you will not be able to use the query if you're attempting to run 
it from outside of Access (on a web page or from a VB program, for 
instance).

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ofer Cohen" <OferCohen@discussions.microsoft.com> wrote in message 
news:045BB723-0AC9-42C1-8AEB-E8EC3FD64C5A@microsoft.com...
> If you have a function (has to be in a module) that get a parameter and
> return a value, you can call this function from the query just as you use 
> a
> build in function in Access
>
> e.g
> Function FunctionName(MyNumber as Double)
>   FunctionName = MyNumber * 100
> End Function
>
> Select TableNAme.*, FunctionName([FieldNameToPass]) As NewFieldName From
> TableName
>
> -- 
> Good Luck
> BS"D
>
>
> "yanto" wrote:
>
>> How to create any user define function that can be called from query
>> like Ms Access standart function? If possible give one simple
>> function.
>> TIA
>> Yanto
>>
>> 


0
Douglas
5/9/2007 10:51:47 AM
Reply:

Similar Artilces:

Append Query Won't Work
This is silly, I have done this sort of thing many times in the far off past but now cannot get it to work. I have two tables tblSerialNos and tblMain. I want to append serial numbers from field fldStrSerialNumber in tblMain to the same named field in tblSerialNos but only where the field fldStrAssetNumber in tblMain is empty. I have tried umpteen versions of this in queries but every time no records are appended even though there are three that should be. Here is the Query SQL from my latest effort if anyone will take pity on my brick wall that is getting a hammering from my head....

File system: UDF
i have dowloaded the photo from digital camera and burn to a CD. I realised it is in "UDF" File system. The CD can be access from Windows XP PC but not accessable on Windows Vista PC, it shows blank content. Anybody for the Vista to read "UDF" file system? "2Sweet" <chongcmw@hotmail.com> wrote in message news:ukSN8GDaKHA.5608@TK2MSFTNGP05.phx.gbl... > i have dowloaded the photo from digital camera and burn to a CD. I > realised it is in "UDF" File system. The CD can be access from Windows XP > PC but not accessable on ...

Query engine for MS Dynamics CRM 3.0
Hi, exist in MS CRM 3.0 a query engine tools so one user can produce an excel export os some data? For example: I need all account address based on a category? I need all contact contacted for a specific campaign? and so on Thanks and sorry form my bad english Lorenzo Soncini Technoservice S.a.s. 38057 Pergine Valsugana (TN) Italy You can do this using Advanced Find. Click advanced find on the toolbar in the main crm window. Select the entity to search on (Look for field) Edit the query (big white area) Edit the columns to showup (Edit columns button on the top toolbar) Click FInd Click ...

General Date in query
Can you not set an open criteria in a query for a field set to a General Date? When I run my query with no criteria, entries are returned. When I put [Enter Date] in the Criteria nothing is returned. I have checked the date to make sure it was just not my error, but the date is correct. I have also tried Like "*" &[Enter Date]& "*" This did not work either. Any thoughts of what I am doing wrong or if I cannot search this way? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200711/1 POST your SQL. (View: S...

query question
I want to be able to run a query i guess to populate the additional information fields in the customer properties. I sell certain items that have warranties and serial numbers and would like the product name (or discription) to display on the first field the date of purchase on the second feild, and the serial # on the third field. I would like to be able to run this query i guess on a weekly bases and be able to run t for all of my past sales as well. Victor ...

Textbox based query returns blank
I have a textbox which displays data (Only numbers). Then I have a button that calls a query thats supposed to return all the info from the recordsource that matches the textbox. This is my query. SELECT Draw_TBL.[Scope_#], Draw_TBL.[DWG_#], Draw_TBL.DWG_Name, Draw_TBL.[Arc_DWG_#], Draw_TBL.[Structural_DWG_#], Draw_TBL.[Grid/ Loc], Draw_TBL.[Baker-submittal_#], Draw_TBL.[Baker-submittal_status], Draw_TBL.[Baker-submittal_date], Draw_TBL.[POJV-submittal_#], Draw_TBL. [POJV-submittal_status], Draw_TBL.[POJV-submittal_date], Draw_TBL.[BnA- approval_status], Draw_TBL.[BnA-approval_date], Draw_TBL...

Animation query
I'm building a program that animates a ball moving around the screen. The ball is going to be a simple object, probably not much more than a small white circle. I haven't tried it yet, but am just trying to guage opinion from people who have done this sort of thing on what would be the best method to start investigating. I am planning on kicking off a thread to handle the repainting of the ball in a different position, and then letting that thread Sleep for a certain amount of time between frames, the time it would Sleep for would be guaged by how fast the ball is moving. I ju...

Update Query & Yes/No Field
Hi - I am working with Access 2003. I have a field with the "Yes/No" format. Is there a statment I can time in an Update query which will place a check mark for Yes? I tried the following statement: UPDATE [Purchased] SET [Lotus Tealight] = Is Not Null; And received the following error message: Syntax error (missing operator) in query expression 'Is Not Null'. I have a query with close to 1200 records (the database itself has 5000 records) which need to be checked Yes as to purchasing a specific item and I really do not want manually check each record. Thanks! I ...

access query reports to excel formatting help
hi. i am really very new in excel programming. i did not even knew that we can do some programming in Excel until last week when i came up with a problem. i hope you guys can help me out. but first let me tell you i dont have no clue about programming in excel. so treat me like a newbie and i will really appreciate that. i have created a database in Msaccess. i have 3 buttons for my reports based on my query in my access form. 1) Problems resolved[Field Names(Problems(text), Short description(text), Long description(Memo), DateOpen(text), Date Closed(Text)]. 2) Problems not resolve(Same as A...

Select query from transaction table
For some reason I can't run a select query on the transaction table. Other tables work fine eg. select * from transaction returns Incorrect syntax near the keyword 'transaction' Any ideas? Cheers The rms database was originally designed in MS Access. It uses some SQL Server reserved words as table names. Transaction and Order spring to mind, but there may be a couple of others. To query these tables you must delimit the tablenames: select * from [transaction] Same thing anywhere that you use the transaction table such as the select list of joins. See the .qrp file for th...

Strange query results/Wild characters???
Hello. My query is showing me strange values from whem loading a value from a form. It's very strange because if you have a form named FormA and inside a field named Field1 and if the field has a default value of 2 and if you run it. Then if you'll make a query with any table or query and put this in the column: Test:([forms]![FormA]![Field1]) your result will be 2, tha same as the the Form Field. My problem is that my query instead of showing me the value of 2 is showing another thing very strange, such as wild characters or value that has nothing to do with it. If I use t...

When queries fail in VBA code, then work manually, then work in code?
I have written a function that executes action parameter queries given a name and a list of parameters. It splits a string into an array, passes the parameters to the (already saved) query name, then runs the query using the Execute method, etc., then writes a record to a table that logs the name of the table and whether the query works or not. The function has worked flawlessly for over a year; I have used it in ACC2000 and ACC2002, in multiple implementations, all without error. I used it with ACC2003 a few months ago and implemented the app as an MDE. No issues. However, in the last ...

Crosstab Query
I have some data that I need to flatten by a particular field, but it is duping up to 7 times due to one field. Unfortunately, the value of these fields is different for each unique record, so a simple crosstab will not work. Here's an example Field to Flatten Duping Field XXX ABC XXX BCA XXX CAB YYY JDK YYY KJD If there were only two dupes per "field to flatten" i could use "FIRST" and "LAST" or "MI...

Year query based on text
Hi Everyone, I’m creating a database that has a table called History. History contains 7 fields; ID, Year, Schedule, Hold, LType, Painted, Approved. The Year field is a combo box with the following values: Every, Even, Odd, Varies, No. The rest of the fields don’t matter for this query (except ID). I’ve been trying to create a query that will return the results in the Year field based on the current year. For example 2010 is an “Even” year, so it would return those records, as well as “Every” and “Varies”. If possible I don’t want to have to go into the query next year and...

append query with visual basic
I'm trying to execute an append query (qapdCopy&PasteProperties) with visual basic, but I get an error. Run-time error '' Syntex error in INSERT INTO statement. ================================================ Private Function Duplicate() Dim strInsert As String, strFrom As String Dim sstrWhere As String, Dim strSQL As String Dim db As DAO.Database Set db = CurrentDb strInsert = "INSERT qapdCopy&PasteProperties.* " strFrom = "FROM qapdCopy&PasteProperties " strWhere = "WHERE qapdCopy&PasteProperties.ChemicalID = " & [Forms]! [frmM...

Change the default format of the query design view
When I use the query design view, I have to increase the size of the table window (from which I'm selecting fields) sideways and vertically to see the field names more clearly and that also means moving the criteria grid further down the page to make room. Is there a way to change the default table window size and default grid position so that I dont' have to do this every time? Thanks, Pat Pat When you find it, let the newsgroup know! You are (unfortunately) not the first person to wish there was a setting...<g> Regards Jeff Boyce Microsoft Office/Access MVP "...

Filter a query in a date range
I have a query in Access2007 which contains reansactions of several years. I work in design mode and I want my query to return only transactions of a custom defined date range that will be defined by the user at execution time. I tried to use criteria (>StartDate <EndDate) which where defined in other columns but it doesn't work. How should I do this? Should look like: Between [Start Date] and [End Date] That will prompt the user to type in both the Start and End dates. I don't have A07 here with me, so I don't know the exact steps, but it's a very, very good i...

Importing Excel named ranges using MS Query
I want to use multiple ranges (named) as the data source for a pivot table using MS Query. When I import the workbook my options are only to select the "tables" (which are my sheets referenced as sheetname$). I don't want to use the entire sheet, just my named ranges in multiple sheets. Thanks, Kathy H Names ranges should appear in the list of tables, unless they're dynamic ranges. But if there's nothing else on the sheet, you can use the sheetname$ tables. KHanna wrote: > I want to use multiple ranges (named) as the data source for a pivot table > usi...

Query for previous week, Sun thru Sat, from date field
I run a query for distribution via macro. I could use the 'between dates' criteria, but is there a better way? I have code 'Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)' that will pull data for the previous month without using 'between dates'. Can it be modified for previous week? -- LMR Between DateAdd("d",1-WeekDay(Date())-7,Date()) And DateAdd("d",1-WeekDay(Date())-1,Date()) For today's date (April 9, 2010 that should return Between #3/28/2010# and #4/3/2010# On Ap...

Query set-up with two tables
Good Morning Everyone, I’m learning and creating a database at the same time. I first added the table Trans_Mstr_Lbr that is an import from Excel that contains all my data related to labor. I built queries and reports based on this table. But then I wanted to add ODC which is another import from Excel. I can’t really establish a unique relationship between the two tables, though I thought possibly about the date field. They both have the same month/year in common. My goal is to create a query and report that displays the Labor Cost,ODC Cost, and then the sum of the two, ...

Query to find the sum of fields...
Hello all,I have an orders table that stores all the orders from variouscustomers sorted by order date descending (I guess that part doesn'treally make a difference). Anyhow, I want a query that gives me a"grand total" of every order from a particular customer. I'm reallynot sure how to go about it... with a Select Query or CrossTab Queryor even a different way completely.What I have is a table called [ORDERS] with the following fields:OrderIDOrderDateCustomerIDOrderTotaletc.Obviously there are orders placed from the same customer throughoutthe year but would have different...

Emailing a form created from a query
I am using Microsft Access 2007 to email a form to individual email addresses so they can update their details and email it back to me. The form was created from a query as the information is in more than one table. However when the recipient gets the email the form is blank and no longer contains the information which was in the form and query. It seems to work when I have created the form from a table but not a query??? ...

CRM Query
Hi I would really appreciate some help with the following: I want to fo a report which is run from the campaign, and then shows all the responses by their response code and channel. I also want to see the number of leads qualified for each response code and channel. I would really appreciate some help on this please. Cheers Snow White ...

Calculate MTD and YTD in a query
I need to calculate sales order MTD totals and YTD totals per month in a query. The query will show the following fields: Part#, Order Period (YYYYMM format), MTD Orders, YTD Orders I am getting the information from our transaction-based information system which stores all orders by date. I will need to group the orders by Part# and period, then sum them by month and by year-to- date. I need to see this information for the current month and year as well as for all previous months and years. It's easy enough to run totals per month, but the YTD is another story. Please help! Thanks Per...

Transferring Queries Question
I am running an Access 2K database in Access 2003. I use the TransferDatabase command inside an Autoexec Macro to transfer / update various Objects in the same database on other company Laptops. The database has some 100 queries I had to update and now I need to update them in the other databases. Is there a way or a single command to use so I can get Access to copy ALL of the queries in one database to another database? Can wildcards work inside the TransferDatabase command or do I need some VB Code to do the trick? TIA! -- Tom You could do it with VB code, but have you tried Tony To...