0

I would appropriate any help with the following issue.

I got an MS Query for dates. When i am trying to list records using a criteria, it works if I type the value directly, but if I am trying to use =[] as a parameter to type, then I got the popup window, but typing the same date value, no records returns.

This works:

enter image description here

Result:

enter image description here

This does not:

enter image description here

Result:

enter image description here

I have tried many formatting such as 15/08/2013 00:00:00 and so on. Same results.

Thank you for any help!

Edit: If I give a date as a value then i got the following SQL statement:

enter image description here

If i enter the date as a parameter then the SQL statement has a question mark instead of value. Why is that?

enter image description here enter image description here

bontoo
  • 137
  • 1
  • 19
  • Have you tried entering the parameter using the date format that's shown in the result tables, i.e., `YYYY-MM-DD`, e.g., `2013-08-15`? Have you tried entering the parameter using the format that's shown for the query string in the original result screencap, i.e., `#DD/MM/YYYY#`, e.g., `#15/08/2013#`? – TallTed Jun 11 '18 at 15:43
  • Yes, as I mentioned I have tried many different formatting, but none of them works. If i type non date character like # then it says "Syntax error in date". – bontoo Jun 11 '18 at 15:55
  • Handwaves like "many different" doesn't provide useful information. See [how-to-ask](http://stackoverflow.com/help/how-to-ask). What's the back-end DBMS you're querying (and through what ODBC driver)? It might have a preferred format. Also, have you tried `[]` instead of `=[]`? – TallTed Jun 11 '18 at 19:31
  • Thanks for your reply. Sorry for not providing exact information instead of "many different". Back and is Rocket Universe and U2 32 bit ODBC driver. Yes, I have tried //to use [] brackets as well. I have tried to change the date format such as: 2013/08/15 2013-08-15 13-08-15 15-08-13 15-8-13 and as i said i tried using #date# d "date" date "00:00:000" but they all comes back with syntax error. The thing is if i type example 11/1/11 directly to value instead using [] bracket and give as parameter it works with any example above. – bontoo Jun 11 '18 at 21:22
  • Are you using Query atop Access or Excel (or something else)? It's worth checking that all vendor updates have been applied at all levels. Then, I suggest looking into the ODBC Trace files -- creating one set while running the successful path, and another set while running the currently failing path. I'm betting the underlying ODBC app is doing something differently, which will have to be addressed with that app's vendor. – TallTed Jun 12 '18 at 17:10
  • Hi TallTed. Sorry for the late reply. I have been busy. I am quite new with this ODBC stuff so i am not sure where to find the trace file so I need to do some research on that. However, i have edited the post with some images as I found that if a give the date as a parameter then the SQL statement contains a question mark instead of a value which probably the cause of the issue. i just do not know why is that but probably cos of some ODBC settings as you have mentioned. Thanks anyway. – bontoo Jun 18 '18 at 16:32
  • The `?` you see in the query indicates a "parameterized query" -- where the app is "preparing" the query once, for execution one or several times. Each execution, the app only sends the parameter values (which get filled in for the `?` on the other end). We know that in the un-prepared query, the parameter is being treated as a date, because the value is `{d '2013-08-15'}`. Only a trace will reveal whether the "bound parameter" is being treated as a date when the prepared query is executed. – TallTed Jun 19 '18 at 01:59
  • Hi TallTed. It has been a while. I had other priorities so i postponed this issue, but i am back trying to solve this. – bontoo Jul 26 '19 at 09:09
  • I run the trace but i got the same info from there: – bontoo Jul 26 '19 at 09:12
  • working one "SELECT LISTS_DONE.Z_ID, LISTS_DONE.DATE_PRINTED, LISTS_DONE.Z_ID\ d\ aFROM LISTS_DONE LISTS_DONE\ d\ aWHERE (LISTS_DONE.DATE_PRINTED={d '2012-08-22'})" non working "SELECT LISTS_DONE.Z_ID, LISTS_DONE.DATE_PRINTED, LISTS_DONE.Z_ID\ d\ aFROM LISTS_DONE LISTS_DONE\ d\ aWHERE (LISTS_DONE.DATE_PRINTED=?)" MSQRY32 5a0-13e4 EXIT SQLDescribeParam with return code -1 (SQL_ERROR) DIAG [IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function (0) – bontoo Jul 26 '19 at 09:16
  • Again instead of {d '2012-08-22'} it sends ? question mark. – bontoo Jul 26 '19 at 09:17
  • This site is not good for conversational problem solving. A year later... Things to (re-)check are the _versions_ of _Rocket Universe_ and of _U2 32 bit ODBC driver_. The _Driver does not support this function_ error suggests that you're doing things right, but the driver is the failure point. Only solution I know for that is to add an intermediary _bridge driver_ that may support more for the client app (MS Query), and require less from the target driver (U2 driver). My employer makes a few candidates. See and . – TallTed Jul 26 '19 at 13:47
  • Hi TallTed. Thank you for your quick response. Actually I tried the same thing acessing our SQL server database and the same process worked well on that. Obviously it is using different ODBC driver than U2. I tried to install an older version of U2 Driver as I did some research and some forum pointed that older driver might work. It did not though. I will try the bridge driver. Anyway, i know it is weird to return to this after a year, but as I mentioned I had to focus on other things and somehow "forgot" about this. Thank you for the advice. – bontoo Jul 26 '19 at 14:48

1 Answers1

0

I have a postgres database and using MS Query on a column type "date" using 2018/1/29 works fine. I checked and it also works on columns type Timestamp What database are you using?

try using 2 criteria >[date1] and <[date2] with 2018/1/29 & 2018/1/30 or >[date1] with 2018/1/29 sort by date so you can see where the date is being interpreted

and use 2 dates 1 day apart it may at least point you in a direction

Jack DeBear
  • 11
  • 4