?
Solved

SQL statement using date range.

Posted on 2005-04-29
16
Medium Priority
?
360 Views
Last Modified: 2013-12-18
I need an SQL statement that would search for date

'date format is converted to 20050429
dateto = uidoc.FieldGetText("fld_dateto")
DT = Year(dateto)*10000 + Month(dateto) * 100 + Day(dateto)
datefrom = uidoc.FieldGetText("fld_datefrom")
DF = Year(datefrom)*10000 + Month(datefrom) * 100 + Day(datefrom)

If (ocon.ConnectTo("AS400DB2", "", ""))  Then
Set oqry.Connection = ocon
SQLStringVar = "select docsrc,amtpd, dteapl from KBM400D100.APPAY where DTEAPL between DT AND DF"  
oqry.SQL = SQLStringVar

gives me a false value (no data), I tried it in query analyzer and there is data.
0
Comment
Question by:cyle
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 13895515
you'll need to build DT and DF as strings and then set your SQLStringVar to the following:

SQLStringVar = "select docsrc,amtpd, dteapl from KBM400D100.APPAY where DTEAPL between '" & DT & "' AND '" & DF & "'"
0
 

Author Comment

by:cyle
ID: 13896555
Happy FunBall,
       Your suggestion did not work, I hard-coded a value into it and it gives me the result but using this variable does not.
Dim datefrom As Variant
Dim dateto As Variant
Dim DF As String
Dim DT As String
Set db = ses.CurrentDatabase
Set uidoc = ws.CurrentDocument
datefrom = uidoc.FieldGetText("fld_datefrom")
DF = Year(datefrom)*10000 + Month(datefrom) * 100 + Day(datefrom)
Msgbox DF
dateto = uidoc.FieldGetText("fld_dateto")
DT = Year(dateto)*10000 + Month(dateto) * 100 + Day(dateto)
Msgbox DT
If (ocon.ConnectTo("AS400DB2", "", ""))  Then
Set oqry.Connection = ocon

SQLStringVar = "select docsrc,payee,dtetrn,amtdsc,amtpd, dteapl from KBM400D100.APPAY_
                        between '" & DF & "' AND '" & DT & "'"  

oqry.SQL = SQLStringVar
Set ores.Query = oqry
ores.FetchBatchSize = 50
0
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 13896640
If you set DF and DT to date strings such as:

DF = "20050401"
DT = "20050430"

(do this just before you set SQLStringVar)

Does that work?

Also, do you get an error message when you try to run this?  Is it an error from Lotusscript or from the SQL server?
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!

 

Author Comment

by:cyle
ID: 13896740
I get an ERROR: 543 from NOTES.
0
 

Author Comment

by:cyle
ID: 13896764
The value in our AS400 DB is in YYYYMMDD format.
0
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 13896832
Hi cyle,

I want to help but I need you to give me as much information as possible.  One line answers are not very helpful.  You say you got an error, but is that the original error or something you received when you tried my suggestion?

Also if you're getting that error from error trapping, can you add the ERROR$ function so you get a description of the error?  I couldn't find the error by its number.  

In the code you sent, there's an underscore in the SQLStringVar and the WHERE clause is missing.  That line should read:

"select docsrc,payee,dtetrn,amtdsc,amtpd, dteapl from KBM400D100.APPAY WHERE DTEAPL between '" & DF & "' AND '" & DT & "'"  


0
 
LVL 3

Expert Comment

by:mssturgeon
ID: 13896875
Try this:

"select docsrc,amtpd, dteapl from KBM400D100.APPAY where DTEAPL between " & DT & " AND " & DF

It's the same query, only removing the quotes from around the dates.  Could be AS400 is misinterpreting them as character values instead of date/integer values.
0
 

Author Comment

by:cyle
ID: 13897248

WHERE DTEAPL between '" & DF & "' AND '" & DT & "'"    -   this gave a result of "FALSE"


where DTEAPL between " & DT & " AND " & DF        -   This gave me no value at all

The error is gone when I moved the DT and DF before the SQLStringVar.

0
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 13897283
So does that mean it is working?  
0
 

Author Comment

by:cyle
ID: 13897377
NO

1. when using the query analyzer I get 355 data.
2. when I hard code the data range on the query I also get the same number of data.

BUT
   if I use this variable ????
 
0
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 13897388
please post the hard coded SQL statement and then post the code again (don't modify anything please, just exactly as it is in the agent)
0
 

Author Comment

by:cyle
ID: 13897656
Hard-Coded date-range
SQLStringVar = "select docsrc,payee,dtetrn,amtdsc,amtpd, dteapl from KBM400D100.APPAY WHERE DTEAPL between 20050401 AND 2005042"


If (ocon.ConnectTo("AS400DB2", "KBM400", "KBM400"))  Then
Set oqry.Connection = ocon
datefrom = uidoc.FieldGetText("fld_datefrom")
DF = Year(datefrom)*10000 + Month(datefrom) * 100 + Day(datefrom)
Msgbox DF
dateto = uidoc.FieldGetText("fld_dateto")
DT = Year(dateto)*10000 + Month(dateto) * 100 + Day(dateto)
Msgbox DT
SQLStringVar = "select docsrc,payee,dtetrn,amtdsc,amtpd, dteapl from KBM400D100.APPAY WHERE DTEAPL between '" & DF & "' AND '" & DT & "'"    
oqry.SQL = SQLStringVar
Set ores.Query = oqry
0
 
LVL 9

Accepted Solution

by:
HappyFunBall earned 2000 total points
ID: 13897705
So to be absolutely clear here:  The Hard-Coded date-range SQL line works, right?  If so, then you can't build the SQLStringVar using single quotes around the dates.  Therefore this should be your SQLStringVar line:

SQLStringVar = "select docsrc,payee,dtetrn,amtdsc,amtpd, dteapl from KBM400D100.APPAY WHERE DTEAPL between " & DF & " AND " & DT
0
 

Author Comment

by:cyle
ID: 13897840
THANK YOU!!!!!!!!! HAVE A NICE WEEKEND..............I will definitely have a good one because of your help.
0
 
LVL 3

Expert Comment

by:mssturgeon
ID: 13898359
Ummm ... not to complain, but that is exactly what I posted.

Oh well.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13898613
Ah no, there is a tiiiiny difference, but I don't know if SQL is that forgiving...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question