Filling date entered by user to 2 fields in table

[DJTI via AccessMonster.com]

I have a entry form that will enter first transaction data for new account.
The user will enter PurchaseDate from the form, I would like that data to be
entered in PurchaseDate and DataTransaction.

Can this be done, if so how?
Thanks for your help.

 

[Ron2006]

I have a entry form that will enter first transaction data for new account.
The user will enter PurchaseDate from the form, I would like that data to be
entered in PurchaseDate and DataTransaction.

Can this be done, if so how?
Thanks for your help.

If "DataTransaction" is in the same record then in the afterupdate
event of the "PurchaseDate "

me.DataTransaction = me.PurchaseDate

If you do not want the DataTransaction to change once it has been
set then

if isnull(me.DataTransaction ) then me.DataTransaction =
me.PurchaseDate

will only load that date field if it does not already have information
in it.

Ron

 

[DJTI via AccessMonster.com]

Ron,
Thank you for responding! These 2 dates [Investment.PurchaseDate] and
[InvestmentDetails.DateTransaction] are for the same record. I will need the
Purchase Date to remain unchanged throughout the history of the record. The
transaction date in this instance will be the first entry in the Transaction
Ledger (read only). Data is calculated (DivEarned, DivPdOut, Redemption) for
the transaction ledger in a query.

Can the Date entry you are helping me with be accomplished in a query? if so,
how can this be done.

Thanks again,
Deb

I have a entry form that will enter first transaction data for new account.
The user will enter PurchaseDate from the form, I would like that data to be

[quoted text clipped - 5 lines]
If "DataTransaction" is in the same record then in the afterupdate
event of the "PurchaseDate "

me.DataTransaction = me.PurchaseDate

If you do not want the DataTransaction to change once it has been
set then

if isnull(me.DataTransaction ) then me.DataTransaction =
me.PurchaseDate

will only load that date field if it does not already have information
in it.

Ron

 

[Ron2006]

Ron,
Thank you for responding! These 2 dates [Investment.PurchaseDate] and
[InvestmentDetails.DateTransaction] are for the same record. I will need the
Purchase Date to remain unchanged throughout the history of the record. The
transaction date in this instance will be the first entry in the Transaction
Ledger (read only). Data is calculated (DivEarned, DivPdOut, Redemption) for
the transaction ledger in a query.

Can the Date entry you are helping me with be accomplished in a query? if so,
how can this be done.

Thanks again,
Deb

I have a entry form that will enter first transaction data for new account.
The user will enter PurchaseDate from the form, I would like that data to be

[quoted text clipped - 5 lines]

If "DataTransaction" is in the same record then in the afterupdate
event of the "PurchaseDate "

me.DataTransaction = me.PurchaseDate

If you do not want the DataTransaction to change once it has been
set then

if isnull(me.DataTransaction ) then me.DataTransaction =
me.PurchaseDate

will only load that date field if it does not already have information
in it.

Ron

On the form in which they are adding the transaction, in the
afterupdate event of the "PurchaseDate " place the following code.

if isnull(me.DataTransaction ) then me.DataTransaction =
me.PurchaseDate

You should not need a separate query as long as all transactions are
being added through this form. The above line of code will put the
PurchaseDate into the DataTransaction IF there is not already a date
in the DataTransaction. The DataTransaction field does not have to be
visible on the form or even on the form, as long as it is in the
underlying query.

Ron

 

[DJTI via AccessMonster.com]

Ron,
I placed the code in the afterupdate event of the 'PurchaseDate' as you
suggested. I am getting an error code:

Microsoft Access can't find the macro 'if isnull(me.'

I did not specify a macro, I followed your instructions only. Do you know why
this is happening?

Thanks for your help,
Deb

Ron,
Thank you for responding! These 2 dates [Investment.PurchaseDate] and

[quoted text clipped - 36 lines]

- Show quoted text -

On the form in which they are adding the transaction, in the
afterupdate event of the "PurchaseDate " place the following code.

if isnull(me.DataTransaction ) then me.DataTransaction =
me.PurchaseDate

You should not need a separate query as long as all transactions are
being added through this form. The above line of code will put the
PurchaseDate into the DataTransaction IF there is not already a date
in the DataTransaction. The DataTransaction field does not have to be
visible on the form or even on the form, as long as it is in the
underlying query.

Ron

 

[DJTI via AccessMonster.com]

I forgot to mention in previous post that the error message happens when the
PurchaseDate gets onfocus.
Deb

Ron,
Thank you for responding! These 2 dates [Investment.PurchaseDate] and

[quoted text clipped - 36 lines]

- Show quoted text -

On the form in which they are adding the transaction, in the
afterupdate event of the "PurchaseDate " place the following code.

if isnull(me.DataTransaction ) then me.DataTransaction =
me.PurchaseDate

You should not need a separate query as long as all transactions are
being added through this form. The above line of code will put the
PurchaseDate into the DataTransaction IF there is not already a date
in the DataTransaction. The DataTransaction field does not have to be
visible on the form or even on the form, as long as it is in the
underlying query.

Ron

 

[Ron2006]

When you are in design mode and have selected that date field
and are looking at the properties window
etc.
on the line of the after update event you need to mouse the little box
over on the right hand side of the tab.
If it asks for what you want to make you want to select code.
Then that is where you place the code.

Ron

 

[DJTI via AccessMonster.com]

Ron,
I still can't get this to work. The error messages ceased, DateTransaction is
just not getting filled. Does this AfterUpdate work with subforms? Maybe that
is what I am having problems with it.
Debra

 

[Ron2006]

Yes it works with all forms

As a test also put a

msgbox "I am at the after update"

into the same section of code as you have the update of the field.

That should make it clear if the event is being triggered.

This should be the afterupdate event of the field that the person is
actually changing.

Also, please post the code that you put there just incase something
there is stopping it.

Ron

 

[DJTI via AccessMonster.com]

Ron,
It is working now. Thanks for your help. I hope you can also help me with my
next question. I read and copied from a earlier post on this site, but I
don't quite understand the logic of the DateSerial function.

I have a report showing the
1. Last quarters' confirmed shares
2. Then the current quarters' increase of dividends or withdrawals = total
confirmed shares for the current quarter.
I am trying to use the DateSerial Function

DateSerial(Year(Date()),3*DatePart("q",Date())-2,0)

This is giving me the previous quarters date and data. If I were to query for
semi-annual how would the DateSerial function read?

I really appreciate your help.
Deb

 

[Ron2006]

I am not sure how you are using this to get a range or specify a range
but

DateSerial(Year(Date), 3 * DatePart("q", Date) - 8, 0) run today
will get you June
and
DateSerial(Year(Date()),3*DatePart("q",Date())-2,0) run today
will get you December.

You can start to play with the numbers there to see how you can get
the ranges you want.

Ron

 

[DJTI via AccessMonster.com]

Thanks, I will play with the numbers this will come in handy.
Deb

 

[DJTI via AccessMonster.com]

Ron,
I sure hope you can help me. I am stuck on a report for the db. I thought the
DateSerial function would work but now I see that the last quarters dates
will not be useful for my report.

I really need the previous date and total shares on each account. The dates
can be different for each account, depending on the activity. What I am
trying to do is run a report that displays
1. Last Date the shares were confirmed (usually end of quarter) and the
"TotalShares" amount.
2. then take the current Date and TotalShares and combine for TotalValue of
investment.

Here is the sql for the query:
SELECT [TransLedgersub].[InvDetailID], [TransLedgersub].[DateTransaction],
[TransLedgersub].[CurShareValue], [TransLedgersub].[QDivValue],
[TransLedgersub].[QCashPdValue], [TransLedgersub].[RedeembyShare],
[TransLedgersub].[TotalShares], [TransLedgersub].[CashPdTotal],
[TransLedgersub].[TotalValue], [TransLedgersub].[DivEarned], [TransLedgersub].
[RedeemedAmt], [ShareholderAccount].[ShareholderID], [ShareholderAccount].
[SLName], [ShareholderAccount].[ShareholderName], [ShareholderAccount].
[Address], [ShareholderAccount].[Address2], [ShareholderAccount].[City],
[ShareholderAccount].[State], [ShareholderAccount].[Country],
[ShareholderAccount].[PostalCode], [ShareholderAccount].[BrokerID],
[Investment].[InvestmentID], [Investment].[PurchaseDate], [Investment].
[InvestmentAmt], DateSerial(Year(Date()),3*DatePart("q",Date())-2,0) AS
DateTransactionLast
FROM [ShareholderAccount] INNER JOIN (Investment INNER JOIN TransLedgersub ON
[Investment].[InvestmentID]=[TransLedgersub].[InvestmentID]) ON
[ShareholderAccount].[ShareholderID]=[Investment].[ShareholderID]
ORDER BY [TransLedgersub].[DateTransaction] DESC , [ShareholderAccount].
[SLName];

If I have not provided you with enough information to help me, let me know
what else is needed.

Thanks again,
Deb

 

[DJTI via AccessMonster.com]

Ron,
I think I need to post this question in new topic, since it is different from
my first question. Thanks.

Ron,
I sure hope you can help me. I am stuck on a report for the db. I thought the
DateSerial function would work but now I see that the last quarters dates
will not be useful for my report.

I really need the previous date and total shares on each account. The dates
can be different for each account, depending on the activity. What I am
trying to do is run a report that displays
1. Last Date the shares were confirmed (usually end of quarter) and the
"TotalShares" amount.
2. then take the current Date and TotalShares and combine for TotalValue of
investment.

Here is the sql for the query:
SELECT [TransLedgersub].[InvDetailID], [TransLedgersub].[DateTransaction],
[TransLedgersub].[CurShareValue], [TransLedgersub].[QDivValue],
[TransLedgersub].[QCashPdValue], [TransLedgersub].[RedeembyShare],
[TransLedgersub].[TotalShares], [TransLedgersub].[CashPdTotal],
[TransLedgersub].[TotalValue], [TransLedgersub].[DivEarned], [TransLedgersub].
[RedeemedAmt], [ShareholderAccount].[ShareholderID], [ShareholderAccount].
[SLName], [ShareholderAccount].[ShareholderName], [ShareholderAccount].
[Address], [ShareholderAccount].[Address2], [ShareholderAccount].[City],
[ShareholderAccount].[State], [ShareholderAccount].[Country],
[ShareholderAccount].[PostalCode], [ShareholderAccount].[BrokerID],
[Investment].[InvestmentID], [Investment].[PurchaseDate], [Investment].
[InvestmentAmt], DateSerial(Year(Date()),3*DatePart("q",Date())-2,0) AS
DateTransactionLast
FROM [ShareholderAccount] INNER JOIN (Investment INNER JOIN TransLedgersub ON
[Investment].[InvestmentID]=[TransLedgersub].[InvestmentID]) ON
[ShareholderAccount].[ShareholderID]=[Investment].[ShareholderID]
ORDER BY [TransLedgersub].[DateTransaction] DESC , [ShareholderAccount].
[SLName];

If I have not provided you with enough information to help me, let me know
what else is needed.

Thanks again,
Deb

I am not sure how you are using this to get a range or specify a range
but

[quoted text clipped - 9 lines]

Ron