Excel SQL Variable date not working even with Format

[Xscatolare]

I have hit a wall here and trying to figure this out. At one point I did have it working and lost the changes due to system malfunction. Anywho, I thought i could recreate it and I can't seem to be able to do this. I have tested the base SQL string in PowerQuery and it works until I add the date criteria.

Here is the sub building the string out.

This is the value (formatted as date) of cell C3 = 5/22/17 6:00 AM

Sub PickPlan()

Dim strSQL As String
Dim target As Range
Dim MyVal As String

Set target = Worksheets("Imports").Range("Tblimport")
MyVal = Format(Sheets("Dashboard").Cells(3, "C"), "mm/dd/yyyy hh:mm:ss AM/PM")


strSQL = "SELECT [pic_zone], Sum([num_pic]-[pic_comp])" & _
    " FROM dbo.pickwkahdr" & _
    " INNER JOIN dbo.e202_pdc_pln" & _
    " ON dbo.pickwkahdr.wave_num = dbo.e202_pdc_pln.wav_nbr" & _
    " WHERE (dbo.pickwkahdr.num_pic) > 0 And (dbo.e202_pdc_pln.upd_dm) < #" & MyVal & "# And (dbo.pickwkahdr.pic_zone) > 0" & _
    " GROUP BY [pic_zone];"

Call ImportSQLtoRange(constring, strSQL, target)

When I verify in the immediate window I do get a valid date (appears to be)
?strsql
SELECT [pic_zone], Sum([num_pic]-[pic_comp]) FROM dbo.pickwkahdr INNER JOIN dbo.e202_pdc_pln ON dbo.pickwkahdr.wave_num = dbo.e202_pdc_pln.wav_nbr WHERE (dbo.pickwkahdr.num_pic) > 0 And (dbo.e202_pdc_pln.upd_dm) <#05/22/2017 6:0:00 AM# And (dbo.pickwkahdr.pic_zone) > 0 GROUP BY [pic_zone];

What is frustrating is that if i do an insert query (Data Tab, New Query, Other, ODBC) for a cell and use this SQL it works.

SELECT [pic_zone], Sum([num_pic]-[pic_comp])
FROM dbo.pickwkahdr
INNER JOIN dbo.e202_pdc_pln
ON dbo.pickwkahdr.wave_num = dbo.e202_pdc_pln.wav_nbr
WHERE (dbo.pickwkahdr.num_pic) > 0 And (dbo.pickwkahdr.pic_zone) > 0
GROUP BY [pic_zone];

If I hard set the date time it does not work errors on "DataSource.Error: ODBC: ERROR [42000] [Sybase][ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near '6'.
"

SELECT [pic_zone], Sum([num_pic]-[pic_comp])
FROM dbo.pickwkahdr
INNER JOIN dbo.e202_pdc_pln
ON dbo.pickwkahdr.wave_num = dbo.e202_pdc_pln.wav_nbr
WHERE (dbo.pickwkahdr.num_pic) > 0 And (dbo.e202_pdc_pln.upd_dm) < #05/22/2017 6:00:00 AM# And (dbo.pickwkahdr.pic_zone) > 0
GROUP BY [pic_zone];

I have tried numerous rearrangements of the date using #, ', " and just can't get it to work.

Any help would be greatly appreciated.

 

[Xscatolare]

Skip, I would love to stay in Excel with my queries however I feel I have maxed on my knowledge base here. Since i was having issues with this date problem I thought i would jump to another query and come back to it as the knowledge on this site helped out with ideas.

So I moved to this query with no dates and I can't even get this to run in VBA nor in Power Query. I get errors about '=' and dbo and I can't find where the problem is.

SELECT 'REG REPLEN PLAN ' & [pic_zone], Sum(IIf([dbo.itemloc]![qty_on_hand]-[dbo.itemloc]![qty_alloc]=0,1,0))" & _
" FROM dbo.itemloc INNER JOIN dbo.item ON dbo.itemloc.itm_num = dbo.item.itm_num" & _
" WHERE (((dbo.itemloc.qty_mvin_reg) = 0) And ((dbo.itemloc.qty_mvin_emg) = 0) And ((dbo.itemloc.qty_mvot_reg) = 0) And ((dbo.itemloc.qty_mvot_emg) = 0) And" & _
" ((dbo.itemloc.stg_fnc_code) = 10) And ((([dbo.item]![fwd_on_hand] + [dbo.item]![res_on_hand]) - ([dbo.item]![fwd_alloc] + [dbo.item]![res_alloc])) > 0))" & _
" GROUP BY dbo.itemloc.pic_zone" & _
" HAVING (((dbo.itemloc.pic_zone) Not In (222,251,252,253)));"

I don't want to quit with it but really thing I am above my skill here....

 

[SkipVought]

I found that reorganizing the SQL in my VBA helped me better manage my code. You may find a better technique. But its gotta be better than what you pasted. Mind the trailing spaces after each clause.

DIM sSQL As String

sSQL = sSQL & "SELECT"
sSQL = sSQL & "  [REG REPLEN PLAN]"
sSQL = sSQL & ", itl.pic_zone"
sSQL = sSQL & ", Sum(IIf(itl.qty_on_hand-itl.qty_alloc=0,1,0)) " 
sSQL = sSQL & "FROM dbo.itemloc itl"
sSQL = sSQL & "INNER JOIN dbo.item itm"
sSQL = sSQL & "   ON itl.itm_num = itm.itm_num "
sSQL = sSQL & "WHERE itl.qty_mvin_reg = 0"
sSQL = sSQL & "  And itl.qty_mvin_emg = 0"
sSQL = sSQL & "  And itl.qty_mvot_reg = 0"
sSQL = sSQL & "  And itl.qty_mvot_emg = 0"
sSQL = sSQL & "  And itl.stg_fnc_code = 10"
sSQL = sSQL & "  And itm.fwd_on_hand + itm.res_on_hand - itm.fwd_alloc + itm.res_alloc > 0 "
sSQL = sSQL & "GROUP BY itl.pic_zone "
sSQL = sSQL & "HAVING itl.pic_zone Not In (222,251,252,253);"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Skip,
Looks like [tt]'REG REPLEN PLAN ' & [pic_zone][/tt] is just one field (without an alias) since this is the field to Group By because of [tt]Sum[/tt]
The Sum itself is a little funky - it Sums either 0's or 1's as an outcome of IIF part.

And yes - getting rid of all those unnecessary ( and ) makes the statement a lot easier to read.

BTW - I usually add a carriage return at the end of the lines, it makes outcome in the immediate window a lot nicer to read.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Skould make no difference to the ultimate result as REG REPLEN PLAN seems to be a string.

I'd agree with inserting vbLf. I usually do.

Just wanted the OP to get past an error.

In the event of an error, I copy the resulting SQL, paste it into the SQL window in MS Query and discover why the error.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Xscatolare]

Thank you both for the feedback and points to improve my query building. I will still tinker with this and apply it because i need to get it beat. However, with the project at hand i have a short deadline and i need to get the data moving so for now it is best to move over to access and get it going then go back and work on this for a future update of the tool.

 

[Andrzejek]

OK, fair enough.
"I can't find where the problem is" - I would start with a simple version of your SQL:
[tt]
SELECT *
FROM dbo.itemloc INNER JOIN dbo.item
ON dbo.itemloc.itm_num = dbo.item.itm_num
[/tt]
Make sure it works, and if it does - add a piece at the time and test it until you either get what you want or you get an error. If error, you know what you added last and concentrate on this issue.

Have fun.

---- Andy

There is a great need for a sarcasm font.