Current/System date in Access

[Miikka Hamalainen]

Hi All,

I'm making library db using SQL.

I have date when book has been loaned (Date loaned) and date to return the
book (Return date), which is 4 weeks.

How can I get current date to show in Date loaned-field?
How do I add 4 weeks to date loaned and can I do it in INSERT
INTO-sentence?

Thanks,

Miikka

[Allen Browne]

Set the Default Value property of your Loaned text box to =Date()

Use the After Update event procedure of the Loaned text box to set the
Return date text box 28 days ahead:

Private Sub Loaded_AfterUpdate()
Me.[Return date] = DateAdd("d", 28, Me.Loaned)
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...

Hi All,

I'm making library db using SQL.

I have date when book has been loaned (Date loaned) and date to return the
book (Return date), which is 4 weeks.

How can I get current date to show in Date loaned-field?
How do I add 4 weeks to date loaned and can I do it in INSERT
INTO-sentence?

Thanks,

Miikka

[Miikka Hamalainen]

Can I do this with SQL?

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti
viestissä:41***********************@per-qv1-newsreader-01.iinet.net.au...

Set the Default Value property of your Loaned text box to =Date()

Use the After Update event procedure of the Loaned text box to set the
Return date text box 28 days ahead:

Private Sub Loaded_AfterUpdate()
Me.[Return date] = DateAdd("d", 28, Me.Loaned)
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...

Hi All,

I'm making library db using SQL.

I have date when book has been loaned (Date loaned) and date to return
the book (Return date), which is 4 weeks.

How can I get current date to show in Date loaned-field?
How do I add 4 weeks to date loaned and can I do it in INSERT
INTO-sentence?

Thanks,

Miikka

[Allen Browne]

No. AFAIK, you cannot set the Default Value of a field in an Access table to
a function with a DDL SQL statement.

If you need to set he Return Date to 28 days after the Loaned date for many
existing records at once, you could use an Update query. (Update on Query
menu in query design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...

Can I do this with SQL?

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti
viestissd:41***********************@per-qv1-newsreader-01.iinet.net.au...

Set the Default Value property of your Loaned text box to =Date()

Use the After Update event procedure of the Loaned text box to set the
Return date text box 28 days ahead:

Private Sub Loaded_AfterUpdate()
Me.[Return date] = DateAdd("d", 28, Me.Loaned)
End Sub
"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...

Hi All,

I'm making library db using SQL.

I have date when book has been loaned (Date loaned) and date to return
the book (Return date), which is 4 weeks.

How can I get current date to show in Date loaned-field?
How do I add 4 weeks to date loaned and can I do it in INSERT
INTO-sentence?

Thanks,

Miikka

[Miikka Hamalainen]

So I have to add the "Date Loaned" by hand.
I have done so and the updated the return date, but it should be automatic
when new book is loaned from db.

Thanks for your answers, I will keep trying :-)

//Miikka

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti
viestissä:41***********************@per-qv1-newsreader-01.iinet.net.au...

No. AFAIK, you cannot set the Default Value of a field in an Access table
to a function with a DDL SQL statement.

If you need to set he Return Date to 28 days after the Loaned date for
many existing records at once, you could use an Update query. (Update on
Query menu in query design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...

Can I do this with SQL?

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti
viestissd:41***********************@per-qv1-newsreader-01.iinet.net.au...

Set the Default Value property of your Loaned text box to =Date()

Use the After Update event procedure of the Loaned text box to set the
Return date text box 28 days ahead:

Private Sub Loaded_AfterUpdate()
Me.[Return date] = DateAdd("d", 28, Me.Loaned)
End Sub
"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...
Hi All,

I'm making library db using SQL.

I have date when book has been loaned (Date loaned) and date to return
the book (Return date), which is 4 weeks.

How can I get current date to show in Date loaned-field?
How do I add 4 weeks to date loaned and can I do it in INSERT
INTO-sentence?

Thanks,

Miikka

[PC Datasheet]

Put the following code in the AfterUpdate event of the textbox where you
enter the borrower's name:
Me!LoanedDate = Date()
Me!ReturnDate = DateAdd("d", 28, Me!LoanedDate)
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...

So I have to add the "Date Loaned" by hand.
I have done so and the updated the return date, but it should be automatic
when new book is loaned from db.

Thanks for your answers, I will keep trying :-)

//Miikka

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti
viestissä:41***********************@per-qv1-newsreader-01.iinet.net.au...

No. AFAIK, you cannot set the Default Value of a field in an Access table to a function with a DDL SQL statement.

If you need to set he Return Date to 28 days after the Loaned date for
many existing records at once, you could use an Update query. (Update on
Query menu in query design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...

Can I do this with SQL?

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti
viestissd:41***********************@per-qv1-newsreader-01.iinet.net.au... Set the Default Value property of your Loaned text box to =Date()

Use the After Update event procedure of the Loaned text box to set the
Return date text box 28 days ahead:

Private Sub Loaded_AfterUpdate()
Me.[Return date] = DateAdd("d", 28, Me.Loaned)
End Sub
"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...
> Hi All,
>
> I'm making library db using SQL.
>
> I have date when book has been loaned (Date loaned) and date to return> the book (Return date), which is 4 weeks.
>
> How can I get current date to show in Date loaned-field?
> How do I add 4 weeks to date loaned and can I do it in INSERT
> INTO-sentence?
>
> Thanks,
>
> Miikka

[Billie Kennedy Jr]

If you look in the properties for Date Loaned field you will see a
default value. All you need to do is add Date() to that value. Then
it is pretty much automatic.

I use a double click function to load the date on one of my forms. it
updates the second date once you leave the field this way.

Private Sub Loaned_Date_DblClick(Cancel As Integer)

If IsNull(Me.Loaned_Date) Then
Exit Sub
End If

Me.Loaned_Date = Date

End Sub

Private Sub Loaned_Date_Exit(Cancel As Integer)

Me.[Return date] = DateAdd("d", 28, Me.Loaned_Date)

End Sub

Of course you could just put it all in a button to make it do this.
Then you just have to click the button to add all of it automatically
and still have the option of changing it manually if you want.

[Miikka Hamalainen]

Thank you very much for all info, but is it possible to do these commands in
SQL in Access?

//Miikka

"Miikka Hamalainen" <mi***************@haminetti.net> kirjoitti
viestissä:co**********@phys-news1.kolumbus.fi...

So I have to add the "Date Loaned" by hand.
I have done so and the updated the return date, but it should be automatic
when new book is loaned from db.

Thanks for your answers, I will keep trying :-)

//Miikka

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti
viestissä:41***********************@per-qv1-newsreader-01.iinet.net.au...

No. AFAIK, you cannot set the Default Value of a field in an Access table
to a function with a DDL SQL statement.

If you need to set he Return Date to 28 days after the Loaned date for
many existing records at once, you could use an Update query. (Update on
Query menu in query design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...

Can I do this with SQL?

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti
viestissd:41***********************@per-qv1-newsreader-01.iinet.net.au...
Set the Default Value property of your Loaned text box to =Date()

Use the After Update event procedure of the Loaned text box to set the
Return date text box 28 days ahead:

Private Sub Loaded_AfterUpdate()
Me.[Return date] = DateAdd("d", 28, Me.Loaned)
End Sub
"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...
> Hi All,
>
> I'm making library db using SQL.
>
> I have date when book has been loaned (Date loaned) and date to return
> the book (Return date), which is 4 weeks.
>
> How can I get current date to show in Date loaned-field?
> How do I add 4 weeks to date loaned and can I do it in INSERT
> INTO-sentence?
>
> Thanks,
>
> Miikka

[PC Datasheet]

No! SQL is NOT for adding data. It can only limit data output and modify
data.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...

Thank you very much for all info, but is it possible to do these commands in SQL in Access?

//Miikka

"Miikka Hamalainen" <mi***************@haminetti.net> kirjoitti
viestissä:co**********@phys-news1.kolumbus.fi...

So I have to add the "Date Loaned" by hand.
I have done so and the updated the return date, but it should be automatic

when new book is loaned from db.

Thanks for your answers, I will keep trying :-)

//Miikka

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti

viestissä:41***********************@per-qv1-newsreader-01.iinet.net.au...

No. AFAIK, you cannot set the Default Value of a field in an Access table to a function with a DDL SQL statement.

If you need to set he Return Date to 28 days after the Loaned date for
many existing records at once, you could use an Update query. (Update on Query menu in query design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Miikka Hamalainen" <mi***************@haminetti.net> wrote in message
news:co**********@phys-news1.kolumbus.fi...
Can I do this with SQL?

"Allen Browne" <Al*********@SeeSig.Invalid> kirjoitti
viestissd:41***********************@per-qv1-newsreader-01.iinet.net.au...> Set the Default Value property of your Loaned text box to =Date()
>
> Use the After Update event procedure of the Loaned text box to set the> Return date text box 28 days ahead:
>
> Private Sub Loaded_AfterUpdate()
> Me.[Return date] = DateAdd("d", 28, Me.Loaned)
> End Sub
>
>
> "Miikka Hamalainen" <mi***************@haminetti.net> wrote in message> news:co**********@phys-news1.kolumbus.fi...
>> Hi All,
>>
>> I'm making library db using SQL.
>>
>> I have date when book has been loaned (Date loaned) and date to return>> the book (Return date), which is 4 weeks.
>>
>> How can I get current date to show in Date loaned-field?
>> How do I add 4 weeks to date loaned and can I do it in INSERT
>> INTO-sentence?
>>
>> Thanks,
>>
>> Miikka

[Bas Cost Budde]

PC Datasheet wrote:

No! SQL is NOT for adding data. It can only limit data output and modify
data.

Beg your pardon? What is the SQL INSERT statement doing, then?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Miikka Hamalainen wrote:

Hi All,

I'm making library db using SQL.

I have date when book has been loaned (Date loaned) and date to return the
book (Return date), which is 4 weeks.

How can I get current date to show in Date loaned-field?
How do I add 4 weeks to date loaned and can I do it in INSERT
INTO-sentence?

If Date() [the current date] is not recognized as a valid function when
you execute the INSERT INTO, you have to pass it as a parameter. You can
use its value+28 for [return date] then.

Could it be that simple?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Billie Kennedy Jr]

Why is it imperative that you must us an SQL command to add this to
your fields?

I would think that having a form with the option to change the value
would be better.

If you really must have a SQL Statement you could just do something
like:

Private Sub Loaned_Date_DblClick(Cancel As Integer)

If IsNull(Me.Loaned_Date) Then
Exit Sub
End If

Me.Loaned_Date = Date
Me.Refresh

$returnDate = = DateAdd("d", 28, Me.Loaned_Date)

DoCmd.RunSQL "UPDATE myTable SET returnDate = '" & $returnDate & "'
WHERE LoanId = '" & $loanId & "';"

End Sub