• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

Date validations!

Hello!

My name is Assaf Lasry and I'm a junior programmer in VB6. I'm currently working on a software and I have a small problem. I have a database (MS Access) and I have a table called post that has 3 fields (InitialDate, Terms, Late). In my VB form I have a combobox, and a text box that has the current date. In the combobox, I have 3 options (30, 60, 90). Whenever i select an option(i.e. 30) and save the information, in the database, the terms field gets the value 30 and the InitialDate field gets todays date. Terms means the number of days. Now since my terms are 30, I want that when 30 days have passed, I'm suppose to get a 1 in the Late field. How can I do that and what my code suppose to look like. Please I need help. I would really appreciate your answer.

Thank you in advance,
Assaf Lasry!
0
AssafL
Asked:
AssafL
1 Solution
 
sraudaCommented:
What happens when terms are 60 or 90?
0
 
Ryan ChongCommented:
Hi AssafL,

Maybe what you want is like this:

SQL = "Update myTable Set Late = 1 Where InitialDate + Terms < Date"
0
 
Neo78Commented:
First of all, u need to calculate the date when 30, 60 or 90 days are added to the initial date (today's date). Here I'm storing the calculated date in a text box. U can use a variable instead.

For example, TermDate is the date when 30 days are added to the initial date.

TermDate= Format(DateAdd("d", CInt(Term), InitialDate), "dd mmm yyyy ")

Then u assign this code to the Late textbox...

Late = DateDiff("d", TermDate, Now)

Note that if TermDate is still early before Now(today's date), it will display a negative value. However, I'm sure u can easily modify this part to suit your needs.

Hope this is more or less what u r looking for.
Let us know.

Regards.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Neo78Commented:
Note, if the current date is 2 days after the term date, it will display 2, 3 if 3 days late and so on. I'm sure this is what u need. :P All u need to do is add those two lines of code in the appropriate part of your program change the names as needed.

Regards.
0
 
inthedarkCommented:
ryancys is right except that is for SQL server.

For Access:

1) Create a new query called say for example "u Find Late People".  In the design view, select "View - SQL" paste in the following code:

Update myTable Set Late = True Where InitialDate + Terms < Date

Now save the query.

2) Setup a Macro called say "Find Late People".

In the macro add the following lines:
SetWarnings No
OpenQuery "u Find Late People"
SetWarnings Yes

3) You can now run this macro before opening any views or reports for this file or:

4) Create a Macro called "Autoexec", you may already have one.

Add in the line:
RunMacro "Find Late People"

The Autorexec Macro is fired each time your Access database is started.

For VB:

Open your database using DAO or ADO.

Set DB=Workspaces(0).OpenDatabase("c:\MyDB.MDB")

SQL="Update myTable Set Late = True Where InitialDate + Terms < Date"
DB.Execute SQL

Hope this helps.

0
 
Anthony PerkinsCommented:
Please maintain your open questions.  For the record:

Questions Asked 19
Last 10 Grades Given A A A A A A A C D D  
Question Grading Record 11 Answers Graded / 11 Answers Received

Thanks,
Anthony
0
 
inthedarkCommented:
Here is a function that can perform a database backup/restore.
I place all my ADO functions in a class called zADO
 so in my program, after declaring ADO I just type "ADO." and up pop all my tools.

Dim ADO as New zADO
Dim OK as Boolean

' open your connection
CN.Open ' normally I do this in a function too.

' backup database
OK = ADO.BackatabaseOK(CN, "MyDatabase", "d:\MyDestination\File.bak")
If Not Ok Then
   Msgbox "Panic " + ADO.GetLastError(CN)
   Exit Sub
End If

' Restore database
OK = ADO.BackatabaseOK(CN, "MyDatabase", "d:\MyDestination\File.bak")
If Not Ok Then
   Msgbox "Panic " + ADO.GetLastError(CN)
   Exit Sub
End If


' here is an extract of the zADO Class
==================class module zADO

Public Function BackupDatabaseOK(CN As ADODB.Connection, DatabaseName As String, DestinationFile As String) As Boolean

' Backup a database

Dim SQL As String
Dim OK As Boolean
Dim RS As ADODB.Recordset

   
    SQL = "USE master" + vbCrLf
    SQL = SQL + "EXEC sp_addumpdevice 'disk', 'TMP_Backup', '" + DestinationFile + "'" + vbCrLf
    SQL = SQL + "BACKUP DATABASE " + DatabaseName + " TO TMP_Backup" + vbCrLf
    SQL = SQL + "EXEC sp_dropdevice 'TMP_Backup'" + vbCrLf
    SQL = SQL + "USE " + DatabaseName + vbCrLf
    On Error Resume Next
    Err.Clear
    CN.Execute SQL
    If Err.Number <> 0 Then
        OK = False
    Else
        OK = True
    End If
     
    ' reset current database for this connection
    SQL = "USE " + DatabaseName + vbCrLf ' re-issue incase last command did not read the en.d
    CN.Execute SQL
    BackupDatabaseOK = OK


End Function

Public Function RestoreDatabaseOK(CN As ADODB.Connection, DatabaseName As String, SourceFile As String) As Boolean

' Restore a database don't try master

Dim SQL As String
Dim OK As Boolean

SQL = "USE master" + vbCrLf
SQL = SQL + "EXEC sp_dboption '" + DatabaseName + "', 'offline', 'TRUE'" + vbCrLf
SQL = SQL + "RESTORE DATABASE " + DatabaseName + " FROM DISK = '" + SourceFile + "'" + vbCrLf
SQL = SQL + "EXEC sp_dboption '" + DatabaseName + "', 'offline', 'FALSE'" + vbCrLf

On Error Resume Next
Err.Clear
CN.Execute SQL
If Err.Number <> 0 Then
    OK = False
Else
    OK = True
End If
On Error GoTo 0
RestoreDatabaseOK = OK

End Function

Public Function GetLastError(CN As ADODB.Connection) As String

' Returns the last error on a connection

'Example:
' OK = ADO.ConnectOK(CN)
' If Not OK Then
'      MsgBox ADO.GetLastError(CN)

If CN Is Nothing Then
    GetLastError = "Connection is invalid"
    Exit Function
End If

Dim m$

Dim E As ADODB.Error
Dim Elist As ADODB.Errors
Set Elist = CN.Errors
For Each E In Elist
    m$ = m$ + CStr(E.Number) + " " + E.Description + " " + E.Source + " " + E.SQLState + vbCrLf
Next
   
GetLastError = m$

End Function
0
 
inthedarkCommented:
Woops, sorry I pasted the above code for a different question in error.

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now