INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel SQL Variable date not working even with Format

Excel SQL Variable date not working even with Format

(OP)
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

CODE --> vba

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.

RE: Excel SQL Variable date not working even with Format

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

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

Just guessing...

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel SQL Variable date not working even with Format

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel SQL Variable date not working even with Format

(OP)
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

RE: Excel SQL Variable date not working even with Format

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

Is dbo.e202_pdc_pln.upd_dm a Date field?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel SQL Variable date not working even with Format

(OP)
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

RE: Excel SQL Variable date not working even with Format

(OP)
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];

RE: Excel SQL Variable date not working even with Format

???

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel SQL Variable date not working even with Format

(OP)
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.

RE: Excel SQL Variable date not working even with Format

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

What if you did this instead...

CODE

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

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel SQL Variable date not working even with Format

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

RE: Excel SQL Variable date not working even with Format

What happens if you use Sybase default date-time syntax YYYY-MM-DD HH.MM.SS.SSSSSS (http://infocenter.sybase.com/help/index.jsp?topic=...)? Date tags # ... # are access specific, try to replace them by ' ... '.

combo

RE: Excel SQL Variable date not working even with Format

(OP)
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.

RE: Excel SQL Variable date not working even with Format

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")

RE: Excel SQL Variable date not working even with Format

(OP)
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

RE: Excel SQL Variable date not working even with Format

Have you tried a pass-through query?

RE: Excel SQL Variable date not working even with Format

(OP)
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.

RE: Excel SQL Variable date not working even with Format

What is this > 0 part for???

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel SQL Variable date not working even with Format

(OP)
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

RE: Excel SQL Variable date not working even with Format

Quote (Xscatolare)

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel SQL Variable date not working even with Format

(OP)
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.... curse

RE: Excel SQL Variable date not working even with Format

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.

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel SQL Variable date not working even with Format

Skip,
Looks like 'REG REPLEN PLAN ' & [pic_zone] is just one field (without an alias) since this is the field to Group By because of Sum
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.

RE: Excel SQL Variable date not working even with Format

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel SQL Variable date not working even with Format

(OP)
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.

RE: Excel SQL Variable date not working even with Format

OK, fair enough.
"I can't find where the problem is" - I would start with a simple version of your SQL:

SELECT *
FROM dbo.itemloc INNER JOIN dbo.item
ON dbo.itemloc.itm_num = dbo.item.itm_num

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close