worksheet functions

[StevenS]

Hi
I am new to this forum and an inexperienced access user, and i ran up to a problem building a query for my database.
I am trying to use some functions in expressions, and i found the functions i need in the access help file. They are described there as "worksheet function". However, when i enter the function in the expression in my query i get an answer like "undefined function"
Can anyone help me?

Regards
Steven

 

[Mile-O]

Post the SQL of your query so we can see where you are going wrong without having to guess.

 

[StevenS]

SELECT [Town Data].X, [Town Data].Y, [Town Data].Country, [Town Data].Place, [Town Data].Pop2004, If([town data].[pop2004]<[town viability table].[priority 3],"notviable") AS ["Not Viable"]
FROM [Town Data] INNER JOIN [town viability table] ON [Town Data].Country = [town viability table].Country;

 

[StevenS]

the message given is:
undefined function 'if' in expression

 

[Mile-O]

IF is a keyword in VBA - it can't be used in a query. It's not the IF expression in Excel.

What you want to use is the immediate If in an expression. This is equivalent to the IF function in Excel - its syntax is:

[b]IIf([/b][i]test[/i][b],[/b][i]if_true[/i][b],[/b][i]if_false[/i][b])[/b]

 

[StevenS]

Thanks.

does this mean that i can't use any of the functions that ms access help describes as worksheet-functions? (for example the IRR_worksheetfunction)

Steven

 

[Mile-O]

The IRR() function will work fine. As will most. Just the IF() won't.

 

[StevenS]

Thanks very much for your time
i'm very grateful
Steven

 

[StevenS]

With the ISNUMBER worksheet function i am getting the same message again:

undefined function 'isnumber' in expression

Is this also a vba keyword? or have i got another problem now?

Steven

 

[Mile-O]

What help filess are you referring to?

ISNUMBER is not used in Access unless you have a reference to Excel set with your database application. You probably won't so, the equivalent is:

IsNumeric()

 

[StevenS]

I just use the help files that are connected with the F1 key.
If i am searching for the functions that i need, i only seem to find worksheet functions, and can't find the access (or sql?) equivalent