Validate a Currency Form field for a minus entry

[thedrumdoctor]

I have been experimenting with a simple cash sheet database with the
following fields:

BillPaymentsTable

PaymentID (AutoNumber & PK)
PaymentDate (Date/Time)
PaymentMethod (Number – has relationship with a lookup table)
Recipient (Number – has relationship with a lookup table)
ReasonForPayment (Number – has relationship with a lookup table)
PaymentAmount (Currency)

From an AutoForm, the database works as it should. In the PaymenAmount field
I enter a prefixed minus number for money going out (i.e., -50.00) and a
non-prefixed number for money going in (i.e., 50.00). However, I would like
to add a validation rule on the Form for the PaymentAmount field to catch out
any transactions entered that are missing the minus prefix where they match
certain ReasonForPayment entries. Each ReasonForPayment entry carries its own
unique ID number from its parent table which has a one-to-many relationship
with the BillsPaymentsTable.

Certain ID’s will refer to money being deducted whilst other ID’s refer to
money being added.

My initial thoughts are something along the lines of:

Test: Is the ReasonForPayment entry number in the record equal to numbers
4,5,6,7,8 (these would be the ID's where a minus entry is mandatory) and is
the entry in the PaymentAmountField equal to a minus number?

If True then validation test is passed.

If False then display and error message advising to correct the number to a
minus value.

I don’t know how to approach this at all so any help would be fantastic!

 

[Ryan]

Try adding this field to your query.

Test:IIf([ReasonForPaymentEntryNumber]
In(4,5,6,7,8),[PaymentAmountField]*-1,[PaymentAmountField])

This states that if the reson is IN the values listed to multiply the
payment amount by -1, and if not to just use the number. This will eliminate
the need to prefixed -.

 

[thedrumdoctor]

Yes, now that's a good way to approach it.

However, I don't have any queries running on the Form that the data is
entered on, it's just a simple auto-form generated from the table.

I've tried generating the entry form via a saved query and inserting the
code you suggested and it is treating it as a parameter query which isn't
what's really needed on the form. The 'ReasonForPayment' field is handled by
a drop-down list and I was hoping I could use a validation rule of some sort
in there to check the 'ReasonForPayment' against the value entered in the
'PaymentAmount' field.

Try adding this field to your query.

Test:IIf([ReasonForPaymentEntryNumber]
In(4,5,6,7,8),[PaymentAmountField]*-1,[PaymentAmountField])

This states that if the reson is IN the values listed to multiply the
payment amount by -1, and if not to just use the number. This will eliminate
the need to prefixed -.
--
Please remember to mark this post as answered if this solves your problem.

I have been experimenting with a simple cash sheet database with the
following fields:

BillPaymentsTable

PaymentID (AutoNumber & PK)
PaymentDate (Date/Time)
PaymentMethod (Number – has relationship with a lookup table)
Recipient (Number – has relationship with a lookup table)
ReasonForPayment (Number – has relationship with a lookup table)
PaymentAmount (Currency)

From an AutoForm, the database works as it should. In the PaymenAmount field
I enter a prefixed minus number for money going out (i.e., -50.00) and a
non-prefixed number for money going in (i.e., 50.00). However, I would like
to add a validation rule on the Form for the PaymentAmount field to catch out
any transactions entered that are missing the minus prefix where they match
certain ReasonForPayment entries. Each ReasonForPayment entry carries its own
unique ID number from its parent table which has a one-to-many relationship
with the BillsPaymentsTable.

Certain ID’s will refer to money being deducted whilst other ID’s refer to
money being added.

My initial thoughts are something along the lines of:

Test: Is the ReasonForPayment entry number in the record equal to numbers
4,5,6,7,8 (these would be the ID's where a minus entry is mandatory) and is
the entry in the PaymentAmountField equal to a minus number?

If True then validation test is passed.

If False then display and error message advising to correct the number to a
minus value.

I don’t know how to approach this at all so any help would be fantastic!

 

[Ryan]

The very best way to handle this is to create a one to many relationship
where the one side of the relationship is on the ReasonForPaymentTable, and
the many is on the BillPaymentsTable. In the ReasonForPaymentTable, add a
field called ReasonType. For each Reason give it a type of Plus or Minus.
Then build a query with both tables. In a blank field in the query add this
code and .

TotalAmount:IIf([ReasonType]="Plus",[PaymentAmount],[PaymentAmount]*-1)

Be sure not to enter nagatives anymore because this will handle that for
you. All you would need to do is type the Payment Amount.
--
Please remember to mark this post as answered if this solves your problem.

Yes, now that's a good way to approach it.

However, I don't have any queries running on the Form that the data is
entered on, it's just a simple auto-form generated from the table.

I've tried generating the entry form via a saved query and inserting the
code you suggested and it is treating it as a parameter query which isn't
what's really needed on the form. The 'ReasonForPayment' field is handled by
a drop-down list and I was hoping I could use a validation rule of some sort
in there to check the 'ReasonForPayment' against the value entered in the
'PaymentAmount' field.

Try adding this field to your query.

Test:IIf([ReasonForPaymentEntryNumber]
In(4,5,6,7,8),[PaymentAmountField]*-1,[PaymentAmountField])

This states that if the reson is IN the values listed to multiply the
payment amount by -1, and if not to just use the number. This will eliminate
the need to prefixed -.
--
Please remember to mark this post as answered if this solves your problem.

I have been experimenting with a simple cash sheet database with the
following fields:

BillPaymentsTable

PaymentID (AutoNumber & PK)
PaymentDate (Date/Time)
PaymentMethod (Number – has relationship with a lookup table)
Recipient (Number – has relationship with a lookup table)
ReasonForPayment (Number – has relationship with a lookup table)
PaymentAmount (Currency)

From an AutoForm, the database works as it should. In the PaymenAmount field
I enter a prefixed minus number for money going out (i.e., -50.00) and a
non-prefixed number for money going in (i.e., 50.00). However, I would like
to add a validation rule on the Form for the PaymentAmount field to catch out
any transactions entered that are missing the minus prefix where they match
certain ReasonForPayment entries. Each ReasonForPayment entry carries its own
unique ID number from its parent table which has a one-to-many relationship
with the BillsPaymentsTable.

Certain ID’s will refer to money being deducted whilst other ID’s refer to
money being added.

My initial thoughts are something along the lines of:

Test: Is the ReasonForPayment entry number in the record equal to numbers
4,5,6,7,8 (these would be the ID's where a minus entry is mandatory) and is
the entry in the PaymentAmountField equal to a minus number?

If True then validation test is passed.

If False then display and error message advising to correct the number to a
minus value.

I don’t know how to approach this at all so any help would be fantastic!

 

[thedrumdoctor]

Many thanks for your help, that works a treat! I'm new to Access only having
done 3 days of training courses which only really hit the tip of the iceberg.
I can see that a mix of database design and clever Querying are at the
backbone of this application and I have a LONG way to go.

The very best way to handle this is to create a one to many relationship
where the one side of the relationship is on the ReasonForPaymentTable, and
the many is on the BillPaymentsTable. In the ReasonForPaymentTable, add a
field called ReasonType. For each Reason give it a type of Plus or Minus.
Then build a query with both tables. In a blank field in the query add this
code and .

TotalAmount:IIf([ReasonType]="Plus",[PaymentAmount],[PaymentAmount]*-1)

Be sure not to enter nagatives anymore because this will handle that for
you. All you would need to do is type the Payment Amount.
--
Please remember to mark this post as answered if this solves your problem.

Yes, now that's a good way to approach it.

However, I don't have any queries running on the Form that the data is
entered on, it's just a simple auto-form generated from the table.

I've tried generating the entry form via a saved query and inserting the
code you suggested and it is treating it as a parameter query which isn't
what's really needed on the form. The 'ReasonForPayment' field is handled by
a drop-down list and I was hoping I could use a validation rule of some sort
in there to check the 'ReasonForPayment' against the value entered in the
'PaymentAmount' field.

Try adding this field to your query.

Test:IIf([ReasonForPaymentEntryNumber]
In(4,5,6,7,8),[PaymentAmountField]*-1,[PaymentAmountField])

This states that if the reson is IN the values listed to multiply the
payment amount by -1, and if not to just use the number. This will eliminate
the need to prefixed -.
--
Please remember to mark this post as answered if this solves your problem.


:

I have been experimenting with a simple cash sheet database with the
following fields:

BillPaymentsTable

PaymentID (AutoNumber & PK)
PaymentDate (Date/Time)
PaymentMethod (Number – has relationship with a lookup table)
Recipient (Number – has relationship with a lookup table)
ReasonForPayment (Number – has relationship with a lookup table)
PaymentAmount (Currency)

From an AutoForm, the database works as it should. In the PaymenAmount field
I enter a prefixed minus number for money going out (i.e., -50.00) and a
non-prefixed number for money going in (i.e., 50.00). However, I would like
to add a validation rule on the Form for the PaymentAmount field to catch out
any transactions entered that are missing the minus prefix where they match
certain ReasonForPayment entries. Each ReasonForPayment entry carries its own
unique ID number from its parent table which has a one-to-many relationship
with the BillsPaymentsTable.

Certain ID’s will refer to money being deducted whilst other ID’s refer to
money being added.

My initial thoughts are something along the lines of:

Test: Is the ReasonForPayment entry number in the record equal to numbers
4,5,6,7,8 (these would be the ID's where a minus entry is mandatory) and is
the entry in the PaymentAmountField equal to a minus number?

If True then validation test is passed.

If False then display and error message advising to correct the number to a
minus value.

I don’t know how to approach this at all so any help would be fantastic!