Sql Functions

[bertyball]

We had an exercice for school:
we have to create a function that has this as a result:

name,show:
handel: HA
Handel-talen: HT
Secretariaat-talen: ST
boekhouden-informatica: BI

if there is no "-" then only the first two letters, else the first letter, and the first letter after the "-"

These are the functions we have to use:
Left
UCase
IIF
InStr
Mid

i have no idea how to start xD

 

[Mike375]

These are the functions we have to use:
Left
UCase
IIF
InStr
Mid

i have no idea how to start xD

If you search on Google for each of the functions you listed then you will almost have the answer.

 

[raskew]

Hi. Welcome to the forum.

Since this is homework, I'm not going to give you the complete solution.
However, here are some pointers to get you started:

1) Lookup each of the 5 provided functions in the help file.

2) Since your end result depends on whether there is a "-" in the string, use
an Iif() statement to provide two options as to whether the "-" exists.

3) Use the Instr() function to determine if the "-" is present.

4) Use the Left() function to return the first 1 or 2 characters of the string.

5) Use the Mid() function, if the "-" exists, to determine the location of the
"-". You'll then return the first character following the "-".

6) Use the UCase() function to return the two resulting characters,
converting both to Upper Case.

HTH - Bob

 

[ChrisO]

A few other things you will probably need to do…

The Function will need to be accessible from the query, within scope of the query, so it’s simpler to put it in a standard module, not behind a Form.
The Function should be declared as Public and not Private.
The Function must not have the same name as the Module it is in, it just won’t work.

You will need to pass ‘Name’ to the Function (try using a better name than ‘Name’, it’s a reserved word).

There is the potential for ‘Name’ to be Null therefore the Function should receive ‘Name’ as a Variant.

The Function should check for both Null and zero length string (ZLS) before attempting to process it.
Look up the IsNull or Len Functions for that.

The Function return type should also be of type Variant in order to return the possible Null passed to it.
The Function will default to a Variant return data type if not declared but it’s generally a good idea to declare it anyway.

 

[bertyball]

Thx guys i've found the solution

UCASE(IIF(InStr([Richting],"-"),LEFT([Richting],1)+MED([Richting],InStr([Richting],"-")+1,1),LEFT([Richting],2))) as AFK

 

[raskew]

Hi -

Good job. Couple of comments:

1) Change MED to MID (probably a typo)

2) In the format you've shown, this is a statemeht that would be used in a query, but, in its present state is not a 'function' .

Bob