"Overflow" error - string to integer // SQL

[PureBluff]

Merry Christmas to all at Mr Excel,
Hope you're working as hard as I am over the festive/holiday season

Trying to write a few ODBC queries into an old IBM AS/400 / iSeries server. It's all gone well using fixed values, but now I'm trying to add in some variables, it's getting a bit more troublesome!

In short, the dates are stored on the AS400 in the format of YYMMDD, so if I code 28-Dec-17 as

"select clntth, trdtth, trf2th, commth, sdsccd, pal#th, dtcdth, trnqth, srb#th, sri#th, IPALPM, PLTPPM from warhist as T01" & Chr(13) & "" & Chr(10) & "left outer join warpall on warpall.pal#pm = T01.pal#th and warpall.commpm = " _ , _
"t01.commth" & Chr(13) & "" & Chr(10) & "INNER JOIN WARCMDT ON WARCMDT.CLNTCD = T01.CLNTTH and WARCMDT.CommCD = T01.commTH" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "where clntth = 'D4' and trdtth = 171228 and trncth in (' 31', ' 51', ' 52', ' 53')" _
)

It works fine, yet if I set it as a variable:

dat3 = format(now() - 2, "YYMMDD")

"select clntth, trdtth, trf2th, commth, sdsccd, pal#th, dtcdth, trnqth, srb#th, sri#th, IPALPM, PLTPPM from warhist as T01" & Chr(13) & "" & Chr(10) & "left outer join warpall on warpall.pal#pm = T01.pal#th and warpall.commpm = " _
, _
"t01.commth" & Chr(13) & "" & Chr(10) & "INNER JOIN WARCMDT ON WARCMDT.CLNTCD = T01.CLNTTH and WARCMDT.CommCD = T01.commTH" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "where clntth = 'D4' and trdtth = dat3 and trncth in (' 31', ' 51', ' 52', ' 53')" _
)

I get an overflow error, so I tried CInt;

Patch = format(now() - 2, "YYMMDD")
dat3 = CInt(patch)

"select clntth, trdtth, trf2th, commth, sdsccd, pal#th, dtcdth, trnqth, srb#th, sri#th, IPALPM, PLTPPM from warhist as T01" & Chr(13) & "" & Chr(10) & "left outer join warpall on warpall.pal#pm = T01.pal#th and warpall.commpm = " _
, _
"t01.commth" & Chr(13) & "" & Chr(10) & "INNER JOIN WARCMDT ON WARCMDT.CLNTCD = T01.CLNTTH and WARCMDT.CommCD = T01.commTH" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "where clntth = 'D4' and trdtth = dat3 and trncth in (' 31', ' 51', ' 52', ' 53')" _
)

Yet I get an overflow error again - which seems to be related to using a string sa integer or vice-versa.

Anyone able to advise?
Thanks,
Steven

 

[PureBluff]

I tried CLng too, but I get an error when trying to referesh the query, which suggests the SQL syntax is wrong.

Dat3 shows as 171228 without "" so integer.

Im really at a loss

 

[PureBluff]

Doh!

Wasn't ending the SQL syntax to use the variable!!!