Writing a function and using it in a query.

[Aleashia]

Hello everybody,

I am taking a module called 'Database and SQL' which was compulsory. I have had no problems up to now, sql was fine etc. However, I have never done visual basic of any description and so I have come unstuck.

I have read manuals and this forum but cannot find anything specific. To be honest I know where I have to write the code in Access and the basics (basic code format such as Function ()as integer, End function) but that is about it!

I have been trying to create a few VBA functions. One is this:

There is an attribute called orders which has different order amounts (i.e. different customers orders are different prices). I am wanting to create a function called Total which receives an amount as an argument and depending on the height of a different category gives the following:

Up to 500 'Small order'
From 500-1000 'Normal order'
>1000 'big order'

The database is for a plant company which has different categories of plants which are all different heights (hence the category and height part).

I hope this is clear and I can post the relationship schema if further clarification is needed.

Thankyou =)

 

[KenHigg]

So are you needing help writing your first function or do you need help figuring out how to get this specific one to work?

 

[Aleashia]

My first function

If I had this function as an example I could proceed with the rest- I can usually figure it all out and understand it better with an example geared toward what I am wanting to do

 

[KenHigg]

My first function

If I had this function as an example I could proceed with the rest- I can usually figure it all out and understand it better with an example geared toward what I am wanting to do

I did a simple one here:

http://www.access-programmers.co.uk/forums/showthread.php?t=99777

Take a look and see if it makes sense.

Welcome to the forum...

 

[aflack]

My first function

If I had this function as an example I could proceed with the rest- I can usually figure it all out and understand it better with an example geared toward what I am wanting to do

Public Function Total(ByVal amount As Integer) As String
If (amount < 500) Then
Total = "small order"
ElseIf (amount < 1000) Then
Total = "Normal order"
Else
Total = "Big Order"
End If

Return
End Function

hope the above helps. think you can just use the query builder to select the function in access from there.
(not tested as quick knock up and im not a VBA developer ) just borred

 

[Aleashia]

Thanks, I will try a few of my own and hopefully they will work.

 

[KenHigg]

Also, since custome functions can sometimes slow down a query, in the example you posted above you could simply use an embedded iif() to return the results you need (If I'm reading your example correctly)...

Something like the following in your query:

MyNewFldName: iif(myNumberfld < 500, "Small", iif(myNumberfld < 1000, "Medium", "Large"))

 

[Aleashia]

Thanks to you both I will attempt the other couple of vba functions I have to make and hopefully they will work