Default field on a Form

[Astronomyman]

I want to fill a default value on a form field using a function that displays the membership fee based on the current month (which is pro-rata)

I have a table with the month number and fee [tblSubscriptionTable]:
SubscriptionID SubscriptionMonth SubscriptionAmmount DateNumber
2 Jul £20.00 01/07/1900 7
3 Aug £20.00 01/08/1900 8
4 Sep £20.00 01/09/1900 9
5 Oct £18.00 01/10/1900 10
6 Nov £16.00 01/11/1900 11
7 Dec £14.00 01/12/1900 12
8 Jan £12.00 01/01/1900 1
9 Feb £10.00 01/02/1900 2
10 Mar £8.00 01/03/1900 3
11 Apr £6.00 01/04/1900 4
12 May £4.00 01/05/1900 5
13 Jun £3.50 01/06/1900 6

The function reads like this:
Function GetDefaultFEE() As Long
GetDefaultFEE = Nz(DLookup([DateNumber],[tblSubscriptionTable]), 20)
End Function

Not sure how to best select Month(date) to equate to the [DateNumber] field

should I use the IIF, SWITCH or CASE statements to achieve this?

 

[Ranman256]

Still not quite sure why your table is build this way, but,

GetDefaultFEE = Nz(DLookup([DateNumber],[tblSubscriptionTable]), month(date))

 

[sneuberg]

If you want the function to return a fee I'd think you'd want something where the amount is returned and the DateNumber is criteria. Something like:

Function GetDefaultFEE() As Double
    GetDefaultFEE = Nz(DLookup("[SubscriptionAmmount]", "[tblSubscriptionTable]", "[DateNumber] = " & Month(Date)), 20)
End Function

I copied and pasted SubscriptionAmmount into this but the spelling looks suspect so I suggest checking that.

I wonder about this table too. Why do you need any more than the Month number and fee.

 

[Astronomyman]

"I wonder about this table too. Why do you need any more than the Month number and fee."
The table is used elsewhere to populate Queries

 

[Astronomyman]

Still not quite sure why your table is build this way, but,

GetDefaultFEE = Nz(DLookup([DateNumber],[tblSubscriptionTable]), month(date))

Function:
Public Function GetDefaultFEE() As Currency
GetDefaultFEE = Nz(DLookup([DateNumber], [tblSubscriptionTable]), Month(Date))
End Function

Field on form:
Default Value =GetDefaultFEE

Response: #Name?

Should the field be enclosed in parentheses?

I don't understand what I am doing wrong

Tried this:
Function GetDefaultFEE() As Double
GetDefaultFEE = Nz(DLookup([SubscriptionAmmount], [tblSubscriptionTable], "[DateNumber] = " & Month(Date)), 20)
End Function

Same response

 

[pbaldy]

The arguments must be in quotes:

http://access.mvps.org/access/general/gen0018.htm