Microsoft Access question: date fields?

Question

I am a teacher, constructing a database for a hypothetical video store. I would like to create two fields in the Rentals table: one for rental date, and one for due date. Rental date would be "today", and Due date would be 7 days later (7 days plus the rental date). How can I do this? Thank you!

Answer 1

In your INSERT statement, you can use some functions like this:

INSERT INTO test (date1, date2)
SELECT Date(), dateAdd("d",7,Date())

That will add a record with the first date as the current date and the second record with the date 7 days later. You can make that an Append type query and pass parameters to it for additional data.


Optionally, you can use the Date and DateAdd functions as the default values in the two columns of your table - then when a rental record is added, the dates would be automatically filled in.

Answer 2

Hi again Crazy :)

For detailed requests like this, I'd again really recommend using VB 2005.

Let's assume that in VB 2005 EE, you've set up a form to insert an entry (such as by double-clicking on a video entry in a DGV, Data Grid View)

Let's than assume that a confirmation message box appears that will confirm the users wish to "check out" the video (I'm not quite sure how you're doing user authentication, but I'm going with a Windows-based user assumption at the moment)

"Are you sure you would like to check out this video?" Yes-No-Cancel

When a user clicks Yes, you would update the specific video (specified by the double-clicked entry's key), and specify that it is equal to today and that the due date is equal to today plus seven days:

'Example Code:
Dim checkOut As String

checkedOut = Today

Dim todaysDate() as String = Splite(checkedOut, "/")
Dim tM As String = todaysDate(0) 'Today's Month
Dim tD As String = todaysDate(1) 'Today's Day
Dim tY As String = todaysDate(2) 'Today's Year

Dim dueDate As String = todaysDate(0) & (todaysDate(1) + 7) _
& todaysDate(2)

'End Code

The above code makes several assumptions and will not work without quite a bit more refinement (such as logic to determine that December 28, 2006 + 7 days does not equal December 35, 2006) and implementing your Ole DB connection strings and update, but that will hopefully (hopefully hopefully ;)) give you an idea of where to start. I'm not sure how familiar you are with Visual Basic, so it may not help at all.

If you'd like more direction, shoot me an email at:

[e-mail]

Answer 3

Use Microsoft Excel. More power for what you're doing. And if not use MySQL. Microsoft Access is to much work for what you want to do.

Answer 4

load the csv document into Excel, exchange the date format to what you require then keep it as csv back. of path if it may no longer be a valid csv document you've gotten greater of a subject.

Answer 5

check the dateAdd function.