kbkhoo5053

I am VB user who needs assistance from expert ppl in Visual Basic. My question is:

- How to compare year and month by using visual basic? Do I need to use For Loops statement and If Else Statement?
Indeed, i am writting a visual basic system for "Room Booking System". The staff need to delete the shift report. The shift report needs to delete after 1 month. For instance, now is February, then the staff can only delete the shift report on last year, December 2004. This year January 2005, he or she cannot delete it. It must be after or over 1 month from now. Hence, I do not know how to write it. Can anyone please help me by guide me.

Thanks you.

Rory

VB6 makes it delightfully easy to work with dates (that is until some-time in 2030 when the dates will do another millennium bug thing) - use the built in Date type (actually holds time and date, not just date), and you can manipulate it like so:
Most other methods should be obvious, though have a look at the Object Inspector or MSDN if you get stuck.

kbkhoo5053

Below are my coding. However, it can't work at all. Can you help me rectify.

Dim intYear As Integer

Dim strMonth As String

strMonth = Array ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")


For strMonth = January To December Step 1


if strMonth=31 November 2004 And strMonth= 31 January 2004 Then

MsgBox DateDiff("m", "25-11-2004", "25-02-2005")

MsgBox("You can clear all shift report")

MsgBox("Do you really want to clear all the shift records for last month?", vbExclamation + vbYesNo, "DELETE ALERT") =

Elseif (datCheckin.Recordset.RecordCount = January And datCheckout.Recordset.RecordCount = January And datDaily.Recordset.RecordCount = January And datRoom.Recordset.RecordCount = January) Then

MsgBox("Do you really want to clear all the shift records for last month?", vbExclamation + vbYesNo, "DELETE ALERT") = vbYes


'deleting record from database

datCheckout.Recordset.delete
datCheckout.Refresh
a
datCheckin.Recordset.delete
datCheckin.Refresh

datRoom.Recordset.delete
datRoom.Refresh

datDaily.Recordset.delete
datDaily.Refresh

'refreshing from database

Next strMonth

End If

Else

MsgBox "You are not allow to delete all shift records for last month", vbOk

End If

End Sub

kbkhoo5053

Dear Rory, thanks for ur help. Thanks you.
Can u clarify for me about your coding? I am not comprehend it. Thanks.

Dim MyDate as Date
MyDate = "January 17, 1988" ' String date
MsgBox MyDate
MyDate = Vba.Now ' Current Time/Date
MsgBox "This is the " & Vba.Day(MyDate) & " of the month" ' Example of built in date function
MyDate = DateAdd("d",12,MyDate)
MsgBox "Twelve Days from now will be " & MyDate

What is means by Vba.Now? Wat do u mean by Vba? Ur coding can solve my problems? I need to "delete shift report" for every month. I am doing "Apartment Room Booking System". For instance, now is February 2005. I need to delete last 1 month shift report that is I need to delete last year December 2004 shift report. Only 1 month over then I can delete the shift report. I don knw how to write the coding for this problem. Can u give me some ideas (how to write the coding)? Thanks.

Regards Steven.

Rory

Steven,
I think I understand what you're trying to achieve, though your posted code doesn't seem to make sense. For instance, why are you comparing an array of strings with a single date? Also date literals are either represented by cdate("1 March 2005") or #03/01/05# [no quotes] - typing in 1 March 2005 on its own would produce a compile error. Secondly, I would iterate through the database recordset rather than through the months.
As I said, I don't fully understand what you're doing, though below is a function I hope will help:

Rory

Oh and VBA is just the base library that most VB functions and methods (e.g. val(), cstr(), beep() etc) belong to - type in vba. and see what comes up in the autocomplete!

kbkhoo5053

To Rory,

Rory, thanks for ur assistance. My coding is wrong? Izit can't work? Ur coding, can I use as my references? I need to submit my project to my supervisor on 14/2/05. Until now, I still cannot solve my problems. Hence, need ur help. Thanks for ur help. I copy ur coding into my program. Do I need to make any changes? I only need to ensure tht the coding tht u provide can perform and meet my requirements, then it is ok.

Rory, the program tht u provide is sufficient for me to solve my problems?

Thanks for ur help again, Rory.

Rory

Well the fuction should work on its own without modification...
Basically you need to replace the bit that you were using before to check the date with that new function. It should be ok for your project.

kbkhoo5053

Thanks Rory. How am I going to check the date whether it is fully automatically be deleted? Can I check it in my database record?
I just copy and paste the coding that u gav me. Then, try to run it. There is no errors. It can perform well. Now I don know how to check the date whether it is being deleted or not. Can you guide me?
Thanks.

STEVEN.

kbkhoo5053

Rory, below are the latest coding tht modify. Does this coding correct? Can u check for me? Can it work? Anything else that need to add?

Private Sub cmdDelete_Click()
datCheckin.DatabaseName = App.Path & "\db.mdb"
sSql = "select * from checkin"
datCheckin.RecordSource = sSql
datCheckin.Refresh

datCheckout.DatabaseName = App.Path & "\db.mdb"
sSql1 = "select * from checkout"
datCheckout.RecordSource = sSql1
datCheckout.Refresh

datRoom.DatabaseName = App.Path & "\db.mdb"
sSql2 = "select * from room"
datRoom.RecordSource = sSql2
datRoom.Refresh

datDaily.DatabaseName = App.Path & "\db.mdb"
sSql3 = "select * from daily"
datDaily.RecordSource = sSql3
datDaily.Refresh

End Sub

Public Function CanRecordBeDeleted(ByVal strRecordDate As String) As Boolean
' Inputs: A string date
' Outputs: True or false
' True: Date occurs in last month or prior
' False: Date occurs this month or later
'
Dim datRecordDate As Date
' Used to hold the date in "VB" form
Dim datStartOfMonth As Date
' Date rolled back to the start of the month
Dim TodayStartOfMonth As Date
' Today rolled back to the start of the month

datRecordDate = CDate(strRecordDate)

datStartOfMonth = VBA.DatePart("m", datRecordDate)
TodayStartOfMonth = VBA.DatePart("m", VBA.Now())
' This built in VB function rounds down to the start of the month

If datStartOfMonth < TodayStartOfMonth Then
CanRecordBeDeleted = True
Else
CanRecordBeDeleted = False
End If

MsgBox("Do you really want to clear all the shift records ?", vbExclamation + vbYesNo, "DELETE ALERT") = vbYes

End Function