SQL statement using date range.

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 & "'"

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

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?

I get an ERROR: 543 from NOTES.

The value in our AS400 DB is in YYYYMMDD format.

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 & "'"  

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.

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.

So does that mean it is working?  

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 ????
 

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)

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

THANK YOU!!!!!!!!! HAVE A NICE WEEKEND..............I will definitely have a good one because of your help.

Ummm ... not to complain, but that is exactly what I posted.

Oh well.

Ah no, there is a tiiiiny difference, but I don't know if SQL is that forgiving...