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.

 

[Andrzejek]

Is the field [tt]upd_dm[/tt] in the table [tt]e202_pdc_pln[/tt] defined as a Date? Date/Time?

Did you try to skip the time portion to see if that will work:
[tt]... And (dbo.e202_pdc_pln.upd_dm) < #05/22/2017# And ...[/tt]

Just guessing...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Can you run this query? Does it return what you expect?

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 06:00:00 AM#
  And (dbo.pickwkahdr.pic_zone) > 0 GROUP BY [pic_zone];

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

 

[Xscatolare]

Andrzejek - yes the field is a date/time field. As mentioned i had it working at one point. Also, without the date criteria I pull records.

Skip, Ran your query in PowerQuery and received error - DataSource.Error: ODBC: ERROR [42000] [Sybase][ODBC Driver][Adaptive Server Enterprise]Incorrect syntax near '06'

Note a correction to on the value of C3. It is formatted to no show the seconds however the true value is 5/22/2017 6:00:00 AM

 

[SkipVought]

So with your correction to the SQL, did it run?

Is dbo.e202_pdc_pln.upd_dm a Date field?

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

 

[Xscatolare]

Skip, there was no change to value of C3 however i typed it incorrectly at the top when i was sharing this post. SQL was not changed. Yes the field upd_dm is a Date/Time field. Although it may not matter to much but here is value of a record straight out of the table - 5/22/2017 6:38:50 PM

 

[Xscatolare]

To add a layer of interest to this. When i put the query in Access it runs. Here is the SQL from Access 2016 as is Excel 2016. I did have to remove the "'PZ' & " from the string as that was giving me fits in excel.
Running this query in Access with the date field having <#5/22/2017 6:00:00 AM# in the query design produces this in SQL view.


SELECT 'PZ ' & [pic_zone] AS Expr1, Sum([num_pic]-[pic_comp]) AS Expr2
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)<#5/22/2017 6:0:0#) AND ((dbo_pickwkahdr.pic_zone)>0))
GROUP BY dbo_pickwkahdr.[pic_zone];

 

[SkipVought]

???

I'm trying to determine if your SQL string actually executes and returns what you expect.

BTW 5/22/2017 6:38:50 PM is not less than 05/22/2017 06:00:00 AM, so no record would result.

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

 

[Xscatolare]

SOrry Skip, i realize that date/time is not less. I pulled that from the source table of where i am trying to filter back results.

In Access it returns what i am looking for and data is correct. In Excel it pulls back data so long as the date/time is not in the string.

 

[SkipVought]

In Excel it pulls back data so long as the date/time is not in the string."

What if you did this instead...

MyVal = Format(Sheets("Dashboard").Cells(3, "C"), "mm/dd/yyyy hh:mm:ss")

...or this...

MyVal = Format(Sheets("Dashboard").Cells(3, "C"), "yyyy/mm/dd hh:mm:ss")

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

 

[strongm]

>In Access

Do you mean that you have the data in Access as well as the code? Or are you going through the same ODBC driver as used in Excel to get the ASE data source?

 

[combo]

What happens if you use Sybase default date-time syntax YYYY-MM-DD HH.MM.SS.SSSSSS (

http://infocenter.sybase.com/help/i...enter.dc38151.1510/html/iqrefbb/X315942.htm)?

Date tags # ... # are access specific, try to replace them by ' ... '.

combo

 

[Xscatolare]

Skip, Tried both and still no data return.

Strongm, The data is living in Sybase. I use Access to verify string and test/compare results. Yes I am using the same ODBC connection for both access and Excel. As mentioned if the date criteria is removed in Excel I get data returned. There is valid data with the date criteria as I can verify it by visibly seeing and also using Access to match SQL.

Combo, I did try that and still no change.

 

[strongm]

And what happens if you don't put a time into the query,
e.g

MyVal = Format(Sheets("Dashboard").Cells(3, "C"), "mm/dd/yyyy")

 

[Xscatolare]

Strongm, Still no data. Here is the immediate window display for the resulting SQL.

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) And ((dbo.e202_pdc_pln.upd_dm) <= #05/22/2017# > 0 And (dbo.pickwkahdr.pic_zone) > 0 GROUP BY [pic_zone];

If i take the time out the resulting sql looks like this.

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];

and i receive results of this (small sample of about 32 records)
220 312
253 790
301 80
190 2630
170 6339
950 53
296 463
275 157

 

[strongm]

Have you tried a pass-through query?

 

[Xscatolare]

Hey Guys, I think I am going to call the dogs off on this one. I moved on to some other queries and they are getting to complicated for me to convert to run from Excel. I think I am going to resort back to Access to crunch the data and then have Excel link to the Output table in Access and go from there. I appreciate everyone's help on this but I think at this point I am better off to stay with Access until I get more knowledge in Excel query building.

 

[Andrzejek]

What is this [tt]> 0[/tt] part for???

[tt]And ((dbo.e202_pdc_pln.upd_dm) <= #05/22/2017# [red]> 0[/red] And (dbo.pickwkahdr.pic_zone) > 0 GROUP BY [pic_zone[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Xscatolare]

Andrzejek, That was a typing error when i was cutting the date criteria in and out. The >0 should have been with the (dbo.pickwkahdr.num_pic) > 0 And (dbo.pickwkahdr.pic_zone) > 0 GROUP BY [pic_zone].
My apologies for that

 

[SkipVought]

to complicated for me to convert to run from Excel

Really? I've run some pretty complex queries from Excel to get data from Oracle, BD2, SQL Server Access, multiple sheets/tables in Excel, including multiple embedded queries. Often used Excel/MS Query as a breadboard for testing SQL.

This may not mean anything, but our IT rehosted SAP tables in Oracle. We often needed to join 5 to 10 tables. No simple queries. Did a bunch or exploration and testing from Excel.

Ran it all in VBA.

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