What am I doing wrong?

[Fred Bloggs]

Help a newbie, please!

I've created a query using a membership database. One field is currency
(Subs2006) and contains various subscription values (e.g. £45.00, £65.00,
etc) one of which is £0.00. I'm using this query in a Word mailmerge letter,
and I want to include a place where it shows what payment has been made. I
want it to say 'No payment required' if there was no subscription paid (i.e
£0.00), or the value as shown in the record if there was.

I've created a column in the query called Paytest and coded it like so:

Paytest: IIf([Subs2006]="£0.00","No payment required"," ")

All this does is return 'Error' in the results.

What am I doing wrong? Can you not use an IIf function in a currency field?
I've tried removing the quote marks, and various combinations of brackets,
etc. to no avail. I've searched the Help file and spent ages on the net but
nothing seems to cover it.

As you can see, I don't know functions very well, but I'm hoping you guys
can tell me the solution.

Cheers

James

 

[Guest]

Fred,

Change your formula to the following:

iif([Subs2006]<>0, [PaymentFieldNumber],"No payment required")

Rename this column something more useful, such as "PayResult" then use THIS
field in your reports. The field in the code I named "PaymentFieldNumber"
should refer to the record where the numbers are actually stored.

So, the line basically asks: If the subs for 2006 don't equal zero, then use
whatever they're entered as, otherwise just say "No payment required".

Just FYI, unless you're trying to filter out a text line where your currency
character is actually written in the line, you should leave it out, since the
record is actually only the number behind it.

HTH

Aaron G
Philadelphia, PA

 

[Fred Bloggs]

That works brilliantly! Thanks a bunch.
Incidentally, bearing in mind there's nothing in the help files, or on MS
website, where's the best place to go to find examples like these? Or is it
just down to experience with VBA?

Cheers
James

Fred,

Change your formula to the following:

iif([Subs2006]<>0, [PaymentFieldNumber],"No payment required")

Rename this column something more useful, such as "PayResult" then use
THIS
field in your reports. The field in the code I named "PaymentFieldNumber"
should refer to the record where the numbers are actually stored.

So, the line basically asks: If the subs for 2006 don't equal zero, then
use
whatever they're entered as, otherwise just say "No payment required".

Just FYI, unless you're trying to filter out a text line where your
currency
character is actually written in the line, you should leave it out, since
the
record is actually only the number behind it.

HTH

Aaron G
Philadelphia, PA

Help a newbie, please!

I've created a query using a membership database. One field is currency
(Subs2006) and contains various subscription values (e.g. £45.00, £65.00,
etc) one of which is £0.00. I'm using this query in a Word mailmerge
letter,
and I want to include a place where it shows what payment has been made.
I
want it to say 'No payment required' if there was no subscription paid
(i.e
£0.00), or the value as shown in the record if there was.

I've created a column in the query called Paytest and coded it like so:

Paytest: IIf([Subs2006]="£0.00","No payment required"," ")

All this does is return 'Error' in the results.

What am I doing wrong? Can you not use an IIf function in a currency
field?
I've tried removing the quote marks, and various combinations of
brackets,
etc. to no avail. I've searched the Help file and spent ages on the net
but
nothing seems to cover it.

As you can see, I don't know functions very well, but I'm hoping you guys
can tell me the solution.

Cheers

James

 

[Guest]

Fred,

There are a boatload of sites with examples, including downloads for you to
view. One of my favorites to get me started was datapig at

http://www.datapigtechnologies.com/AccessMain.htm

He has actual videos where he walks you through some basics. You can also
find great stuff at the following sites

http://allenbrowne.com/tips.html

http://www.lebans.com/products.htm

Plus the newsgroup you're on now has really been a great help to me.

Also, just so you know if you're looking for future help, when you're
creating and working with queries, you're typically writing in SQL, not VBA.
Also, you can write SQL within VBA. This becomes extreeeeemly helpful as
things start getting more sophisticated.

Anyway, I hope this helps. Have a great weekend.

Aaron G
Philadelphia, PA

That works brilliantly! Thanks a bunch.
Incidentally, bearing in mind there's nothing in the help files, or on MS
website, where's the best place to go to find examples like these? Or is it
just down to experience with VBA?

Cheers
James

Fred,

Change your formula to the following:

iif([Subs2006]<>0, [PaymentFieldNumber],"No payment required")

Rename this column something more useful, such as "PayResult" then use
THIS
field in your reports. The field in the code I named "PaymentFieldNumber"
should refer to the record where the numbers are actually stored.

So, the line basically asks: If the subs for 2006 don't equal zero, then
use
whatever they're entered as, otherwise just say "No payment required".

Just FYI, unless you're trying to filter out a text line where your
currency
character is actually written in the line, you should leave it out, since
the
record is actually only the number behind it.

HTH

Aaron G
Philadelphia, PA

Help a newbie, please!

I've created a query using a membership database. One field is currency
(Subs2006) and contains various subscription values (e.g. £45.00, £65.00,
etc) one of which is £0.00. I'm using this query in a Word mailmerge
letter,
and I want to include a place where it shows what payment has been made.
I
want it to say 'No payment required' if there was no subscription paid
(i.e
£0.00), or the value as shown in the record if there was.

I've created a column in the query called Paytest and coded it like so:

Paytest: IIf([Subs2006]="£0.00","No payment required"," ")

All this does is return 'Error' in the results.

What am I doing wrong? Can you not use an IIf function in a currency
field?
I've tried removing the quote marks, and various combinations of
brackets,
etc. to no avail. I've searched the Help file and spent ages on the net
but
nothing seems to cover it.

As you can see, I don't know functions very well, but I'm hoping you guys
can tell me the solution.

Cheers

James