Date Function in Idea

2 posts / 0 new
Last post
Joined: 11/29/2018 - 10:55
Date Function in Idea

Hi I am trying to script a dailog box where the user can select a table, field name and enter a date. The details entered will perform an extract on the selected table to include all fields that are greater than the date entered. I am having trouble running the extract due to the date field.

Option Explicit
Dim sFilename As String
Dim sFieldName As String
Dim sDate As String
Dim bExitScript As Boolean

Sub Main
Call mainMenu()
If Not bExitScript Then
Call DirectExtraction() 'Sample-Detailed Sales.IMD
MsgBox "Script Cancelled by User"
End If
End Sub

Function mainMenu()
Dim dlg As NewDialog
Dim button As Integer
On Error Resume Next
sFilename = Client.CurrentDatabase.Name
button = Dialog(dlg)
NewDialog.TextBox1 = "YYYY/MM/DD"
End Function
Function DisplayIt(ControlID$, Action%, SuppValue%)
Dim bExitFunction As Boolean
Select Case Action%
Case 1
If sFilename <> "" Then
Call getDateFields()
DlgListBoxArray"DropListBox1", listBox1$()
End If
Case 2
Select Case ControlID$
Case "PushButton1"
Call getFile()
If sFilename <> "" Then
Call getDateFields()
DlgListBoxArray"DropListBox1", listBox1$()
End If
sFieldName = listBox1$(NewDialog.DropListBox1)
sDate = NewDialog.TextBox1
If sFileName = "" Then
MsgBox "Please select a file"
ElseIf Not IsDate(sDate) Then
MsgBox "Please enter a proper Date"
ElseIf sDate = "" Then
MsgBox "Please enter a proper Date"
bExitFunction = True
End If
bExitScript = True
bExitFunction = True
End Select
End Select
If SFilename = "" Then
DlgText"Text2", " Please Select a File"
DlgText"Text2", iSplit(sFileName,"","\",1,1)
End If
If sDate = "" Then
DlgText"Text2", "Please Enter a Date"
IsDate(sDate) = iRemove(SDate, "/")
End If
If bExitFunction Then
DisplayIt = 0
Displayit = 1
End If
End Function
Function getFile()
Dim obj As Object
Set obj = Client.CommonDialogs
sFilename = obj.FileExplorer()
Set obj = Nothing
End Function
Function getDateFields()
Dim db As dataabse
Dim table As tableDef
Dim field As field
Dim i As Integer
Dim bFirstTime As Boolean
ReDim listBox1$(0)
bFirstTime = True
Set db = Client.OpenDatabase(sFilename)
Set table = db.TableDef
For i = 1 To table.count
Set field = table.GetFieldAt(i)
If field.IsDate Then
If bFirstTime Then
bFirstTime = False
listBox1$(0) =
ReDim preserve listBox1$(UBound(listBox1$) + 1)
listBox1$(UBound(listBox1$)) =
End If
End If
Next i
Set field = Nothing
Set table = Nothing
Set db = Nothing
End Function
'Data: Direct Extraction
Function DirectExtraction
Dim db As database
Dim task As task
Dim dbName As String
Set db = Client.OpenDatabase(sFilename)
Set task = db.Extraction
dbName = client.uniqueFilename("RollForward New Hire Testing" & sFieldname & sDate)
task.AddExtraction dbName, "", sFieldName & ">" & sDate
task.CreateVirtualDatabase = False
task.PerformTask 1,
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function

I reformatted your code. Brian

Brian Element's picture
Brian Element
Joined: 07/11/2012 - 19:57

Hello Maria and welcome to the site.

I hope you don't mind I have reformatted your code for easier reading.

In the mainMenu you have the NewDialog.TextBox1 after you call the dialog, it should be before it.  Also you an use dlg instead of NewDialog, so your code would look something like this:

dlg.TextBox1 = "YYYYMMDD"

button = Dialog(dlg)

Notice I took out the "/" in the mask, the equation editor expects the following date format in order to use it "YYYYMMDD", if it is in another format you have to change it first or use the @CtoD() function to change it to a date.

For your actual equation I believe it should be:

task.AddExtraction dbName, "", sFieldName & ">""" & sDate & """" 

Dates have to be within double quotes so these need to be added or else you will probably be getting sometype of error.

Let me know if this fixes your problems.