Extracting Database based on Dates

4 posts / 0 new
Log in or register to post comments
Last post
Tue, 08/11/2015 - 18:35
#1
mponter
Offline
Joined: 07/14/2015 - 10:53
Extracting Database based on Dates

Hi,I am relatively new to IDEAScripting and am having trouble extracting databases using "Start" and "End" dates which the user has input through a Dialog box. The user inputs MM/DD/YYYY as a string in a textbox and then I wanted to perform a database extraction with the "Transaction Date" being greater than the starting date and less than the ending dates.
Eventually I'll switch the input to drop down boxes for MM, DD, and YYYY since most of the queries we will be running are only over the last few years (and it will reduce the chance that the user enters an irregular format), but for curiosity, I am getting stuck on the syntax for comparing a TRANS_DATE field (imported from a CSV file originally in MM/DD/YY DATE format) to a user input string. I know IDEA converted the TRANS_DATE field to YYYYMMDD but what type of conversion should I do on the user input Strings?
I can do the extraction correctly in IDEA itself using @ctod but cannot get my head around the correct format when scripting.The script I have written is getting quite large and seems to be working well but I keep adding to it. Let me know if you would like a snippet to help explain.
Thanks!Mark
 
 
 
 
 
 

Top
Tue, 08/11/2015 - 19:07
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Mark, the trick is to rearrange the date is the IDEA date format which is YYYYMMDD.  The sample code will take what is entered in the inputbox and rearrange it so it can be used in the equation.  If you haven't already seen it I also have some code for a date picker that you might want to try out (http://ideascripting.com/snippet/date-picker).

Sub Main
	Call DirectExtraction()	'Sales transactions-Database.IMD
End Sub


' Data: Direct Extraction
Function DirectExtraction
	Dim db As database
	Dim task As task
	Dim inputDate As String
	
	inputDate = InputBox("Input a start date", "Input a date", "MM/DD/YYYY")
	
	'rearrange the date to IDEA date formal "YYYYMMDD"
	inputDate = Mid(inputDate, 7, 4) & Mid(inputDate, 1, 2) & Mid(inputDate, 4, 2) 
	
	Set db = Client.OpenDatabase("Sales transactions-Database.IMD")
	Set task = db.Extraction
	task.IncludeAllFields
	dbName = client.uniqueFilename("EXTRACTION1")
	task.AddExtraction dbName, "", "INV_DATE > """ & inputDate & """"
	task.CreateVirtualDatabase = False
	task.PerformTask 1, db.Count
	Set task = Nothing
	Set db = Nothing
	Client.OpenDatabase (dbName)
End Function

 

Top
Thu, 08/13/2015 - 12:23
mponter
Offline
Joined: 07/14/2015 - 10:53

Took me a moment to get the break I needed to test this out and I had that "AH HA!" moment when you find the missing puzzle piece. I really appreciate the help!   I ended up rewriting everything to incorporate dropdown lists to reduce user error and seems to be working well. Thanks!!!

Top
Thu, 08/13/2015 - 13:18
(Reply to #3)
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

That is probably the best way as you need to control input if you can and dates can be a pain.  Having drop downs is a good way to go.

Top