Queries And Functions Help

Aug 25, 2006

hi,
what im trying to do is use the code below, coupled with a append query, to
make the value of the 'lag' variable go into the feild in 'tblplayer' 'Lowes
t_age_group"

can anyone see what im doing wrong?

thanks
any help would be much apreciated

Nick


Public Function fnlag()


Dim cutof As Date
Dim age As String
Dim Birthdate As Date
Dim age1 As String
Dim lag As String

cutof = DLookup("[cutoff]", "[tblseason]", "[tblseason].[is_current] = -1")
Birthdate = DLookup("[Birthdate]", "[tblplayer]")
lag = 1
lag = DLookup("[Lowest_age_group]", "[tblplayer]")

age = DateDiff("yyyy", Birthdate, cutof, vbMonday, vbFirstJan1)
'MsgBox (age)
lag = age + 1

End Function

View Replies


ADVERTISEMENT

Queries And Functions

Mar 20, 2007

Hi,

I need some help with a function inserted into a query.

I created a function to convert numbers into text. I then created a query to pull some fields from a table. the last field of the query, I inserted the function using the build feature. I was able to see the function in the list of custom functions for the query.

However, when I run the query I get an error message of undefined function - name-. Can I use my function there? or is there some other way to do so.

thanks for any help received.

Regards,

CathyM

View 2 Replies View Related

Functions Work In Code But Not Queries

Feb 14, 2007

I have a problem where I can create queries in code using functions such as Left() and they will work fine on my clients machines with a complied MDE file but if I try to use the same function in a saved querie they get an error: "Function is not available in expressions in query expression..."

The Queries work fine on my machine but not on those using Access Runtime. From my research it appears to be a problem with them not having the correct Reference on their machine. If that is true then which Reference do they need and is there away of installing that Reference by code?

Thanks for your help....

View 1 Replies View Related

Functions In Queries / Expression Builder

Dec 3, 2004

Hi, I need some help using functions in queries. I can calculate simple arithmetic in calculated fields, but fail when using functions. For example I have attached a doc file showing a query. One table displays record ids, the other four have data for consecutive dates for each record. I cannot make the query calculate the standard deviation of the data for each record. What expression would I use, I know it is stdevp but when i try and create the expression i get error messages galore.

thanks

View 2 Replies View Related

Queries :: Aggregate Functions With Conditions?

Mar 22, 2013

I have the following query. I got an error when I ran it.

Code:
SELECT COUNT([encounter_number] WHERE status = 'Death')/ COUNT([encounter_number]) AS Death Ratio, tbl_test.facility_type AS Type,
FROM tbl_test
GROUP BY tbl_test.facility_type;

I think the problem is that first WHERE clause inside.

View 2 Replies View Related

Queries :: IIF Statement With Date Functions

Feb 24, 2014

My goal is if the current date = 1st - 4th of the month to return the failure_date if it is >= 1st of the prior month and <= 4th of the current month (ie, 01/01/14 - 02/04/14)

this works:
>=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4)

But if the current date > 5th of the month to return the failure_date if it is >= 1st of the current month and <= 4th of the next month.(ie 02/01/14 - 03/04/14)

this works
>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4)

But when I put it in the iif statement if will not work:

IIf(Date()>DateSerial(Year(Date()),Month(Date()),4 ),>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4),>=Dat eSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4))

SQL =
SELECT table_testing_dates.Failure_Date, table_testing_dates.Failure_Date, table_testing_dates.FailureGrouping
FROM table_testing_dates
WHERE (((table_testing_dates.Failure_Date)=IIf(Date()>Da teSerial(Year(Date()),Month(Date()),4),(table_test ing_dates.Failure_Date)>=DateSerial(Year(Date()),M onth(Date()),1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date())+1,4),(table_testing_dates. Failure_Date)>=DateSerial(Year(Date()),Month(Date( ))-1,1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date()),4))));

View 2 Replies View Related

Queries :: Semicolons As Parameter Separator In Functions

Sep 8, 2014

I used to use commas as a parameter separator, but for some reason it is now a semicolon. How can I change it back to a comma?

Currently this works:

Code:
SELECT IIF(MyExpression; TrueThing; FalseThing)
FROM ...

Want to use (and have used before):

Code:
SELECT IIF(MyExpression, TrueThing, FalseThing)
FROM ...

View 3 Replies View Related

Troubles With Aggregate Functions In Queries And Null Values

Jun 26, 2007

I'm having a problem with queries, and I can't seem to find a solution in books - I looked through about ten of them and none of them addressed the problem. This may be because it has a painfully obvious solution...

A little background:
I am designing a database for a debt-collection law firm. One of the functions it must have is to keep track of various different sorts of financial transactions which can pertain to a given debtor (ie, a received payment, a cost expended, and a few other things).

The problem is that, in generating reports, I need to use queries to find several sums of only those transactions which fall into specific categories (for instance, to calculate the amount a debtor has paid against his balance, it needs to sum only those entries which are both linked to that debtor's ID number and whose type field reads "payment", and then subtract from that those entries whose type field reads "cost"). The problem is this: not all debtors may have "costs" entries, and when there are none, the report comes up blank with a single "#Error" written in the name field and nothing else present.

I believe the problem is that the Sum aggregate is returning a null value when the query finds nothing that meets the criteria. I have been unable to find a way around this; the Nz() and IIf() with IsNull() functions don't seem to be helping.

The query runs as intended when there are entries for every relevant type; however, it is undesired to have to enter a "payment" of $0, "cost" of $0 etc for every entry just so that this function works.

Is there anything I can do about this? Any input would be appreciated, as I'm fairly inexperienced with the use of Office Access. (If it matters, I am using Office 2003).

View 2 Replies View Related

Queries :: IIF Functions - Control Source Of A Field In Form Data

May 8, 2014

I am new to access, after learning basics I am trying to build my first DB.

Having some troubles with the IIF syntax. I am placing the formula

=IIf([Total]<=(200),[Total]*(0.7)) & IIf([Total]>(201)&[Total]<=(500),[Total]-(200)*(1)+(140)) & IIf([Total]>(501),[Total]-(500)*(1.2)+(300)+(140))

In the form data control source of a field. The first 2 statement seems to work, but the last statement results in some crazzy figures.

View 3 Replies View Related

Queries :: Applying Multiple Count Functions With WHERE Criteria Without Affecting Others

Dec 11, 2013

Any way to use multiple count functions in a query with their own individual filters without affect the others?

For example:

SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2]
FROM [E&I Table]
WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV"))
GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;

I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query...

View 6 Replies View Related

Functions

Jun 15, 2005

i have the following line:

Me.Text2 = Right(Me.Text0, InStr(1, Me.Text0, ".") - 1)

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 ?

Thanks

View 4 Replies View Related

Help With Functions

Jan 4, 2005

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

Private Sub HEAVY_AfterUpdate()

Total_Sheets = SECOND + HEAVY + LIGHT + SCRAP

Total_Sheets = Total_Sheets * YIELD

Total_Sheets = Total_Sheets + D_Sheets

Total_Sheets.Text = Val(Total_Sheets)



End Sub

View 2 Replies View Related

IF Functions?

Aug 18, 2004

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.

Nathan

View 6 Replies View Related

Missing Functions

Aug 17, 2005

Hi,

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?

Thanks

View 2 Replies View Related

Search Functions

Jan 12, 2006

Hi everyone,

Thanks for taking the time to read this.

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.

How do I get this to happen?

Thankyou for your help

Jess:confused:

View 2 Replies View Related

MS Access And Functions

Nov 3, 2006

Hello,

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.

View 2 Replies View Related

MS Access And Functions

Nov 3, 2006

Hello,

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.

View 3 Replies View Related

Built In Functions?

Aug 8, 2007

Does anyone know of a good resource to mull through all of the built in functions and learn their syntax, uses, etc?

Thanks

bluke

View 2 Replies View Related

Functions List

Dec 7, 2007

Is there a list anywhere that tells what all the functions do in access?
I mean functions such as:

RTrim
RTrim$
SaveSetting
Seek

There is a big long list, and some of them.. I have no idea what they do.

View 7 Replies View Related

Multiple Functions

Oct 12, 2005

I have a query that contains a field with 4 codes that I need to replace with values.

Example:
Replace([CE020]![LNCH_FLG],"0","None") Replace([CE020]![LNCH_FLG],"1","Free") Replace([CE020]![LNCH_FLG],"2","Reduced")
Replace([CE020]![LNCH_FLG],"3","State Free")
Replace([CE020]![LNCH_FLG],"4"," Paid")


I get an invalid syntax error, I know if I was to have one Replace function it will works fine, but I’ll still have 3 code to convert.

Any Ideas?

Thanks
--pete

View 7 Replies View Related

Two Count Functions

Feb 6, 2008

Hi
Im trying to run two count functions on one column in sql (access 2003). I need to return two new column, pass and fail. something like this; select count(grade) as pass, where grade>=40 and select count (grade) as fail, where grade<= 39.

my table is student_ID, module and grade:

student_ID Module Grade
0012 history 59
0034 history 34
0045 maths 78
0031 maths 45
0046 spanish 66

what i need is:

Module Pass Fail
History 1 1
Maths 2 0
Spanish 1 0

I have achieved it by running two seperate querys, one for pass and one for fail and then merging them with another query but is there any other way?

View 1 Replies View Related

Functions Error

Mar 31, 2008

I have looked and looked at this expression line and cannot find what is wrong.

Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31])),1) And DateSerial(Year(date(forms![enterprise report card].[combo31])),Month(date(forms![enterprise report card].[combo31]))+1,0),1,0))

This gives me an error "Expression entered has a function containing the wrong number of arguements"

However this one:
Repaired: Sum(IIf(work!status=4 And work!code=11 And work![tin date] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0))
works fine... The only difference is I inserted forms![enterprise report card].[combo31] inside of the Date() function.

Can anyone see what I might be missing??

View 4 Replies View Related

Financial Functions

Sep 23, 2005

I am doing some work on a loan calculator which I have done successfully in Excel. I now wish to do this with an Access form (A2K). Can anyone please confirm that the Financial Functions (ie CUMPRINC etc) that are available in Excel can be used in Access. The Access help menu calls them Worksheet Functions and I can't see any reference to the above function in this forum. I have entered it in a control and get a #name? error. I dont know if I have made a syntax error or that Access doesn't recognise it.The following is the formula I have used =CUMPrinc([rate]/[freq],[nper],[amount],1,[nper],0).

I have not had the same problems with the following formula =PPmt([rate]/[freq],1,[nper],[amount],0)

Cheers

View 2 Replies View Related

How To Use Min And Max Functions In Rows?

Sep 24, 2004

Hi there,

I`ve got a problem with access. I Have data in several columns like:

Date Value 1 Value 2 Value 3 Value 4
1-1-01 12 10 11 9
2-1.01 14 7 16 11
etc.
Now I would like to add two columns that determine the min and max value of a row. Like below:

Date Value 1 Value 2 Value 3 Value 4 Min_value Max_value
1-1-01 12 10 11 9 9 12
2-1.01 14 7 16 11 7 16

Is this possible with the standaard min function? or how else do i do this?

View 12 Replies View Related

DateAdd Functions

Mar 10, 2005

I enter this statement on my control source in a text box
of a report for a date field.

=DateAdd("d",1,[startdate]

I get error when I preview the report. I trying to add 1 day
to the date.

Thanks in Advance

View 2 Replies View Related

Worksheet Functions

May 23, 2006

I need to find the latest of several date fields in a SINGLE record of a query. The Max function seems only to work for a single field across several records in a query. However the worksheet function Max suggests I can use it for in-record fields but it does not work in queries. It is called a Worksheet Function but what is an Access worksheet? I know what it is in Excel but not Access. Neither Help nor two thick reference books even mention worksheets.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved