Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-13-2019, 03:18 PM   #1
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 261
Thanks: 11
Thanked 1 Time in 1 Post
dynamictiger
Odd and Differing Behaviour in Query on SQL data stores

Thanks to everyone for looking into this. It really has me baffled.

I have two online SQL databases I am linking to in Access in order to produce multiple reports, queries and so on.

Both databases contain dates and typical for SQL the date on the linked table is formatted dd-mm-yyyy hh:nn:ss.

I am running two different queries. The first finds chemical results and limits the records by date (this is from one of the SQL linked tables). The relevant section of the query is:

Code:
Left([LogTime],10)) Between '2019-01-01' And '2019-01-10')

In short I am trimming the time from the date and leaving just the date then query with yyyy-mm-dd. This works and reliably works.


On another query I have a similar requirement. I am wanting to return time records between two specific dates. If I add:
Code:
WHERE (((Left([StartTime],10)) Between '2019-1-1' And '2019-05-01'
This query returns nothing at all. Yet the underlying data looks identical in every way???

If I change the query to:
Code:
WHERE (((Format(Left([StartTime],10),'mm\/dd\/yyyy'))>=#3/7/2019#)
I get nothing

Or
Code:
WHERE (((Format(Left([StartTime],10),'mm\/dd\/yyyy'))>=7/3/2019));
I get no records

Or
Code:
WHERE (((Format(Left([StartTime],10),'mm\/dd\/yyyy'))>='7/3/2019'));
Still nada

And
Code:
WHERE (((Left([StartTime],10))>=3-7-2019));
Appears to be ignoring the criteria regardless of which way I configure the date.


I understand dates can be confusing, but this is past confusing and becoming threatening to my remaining hair follicles. I am really flumoxed and am starting to wonder about alternative approaches. I was hoping someone has come across an equally baffling situation and may give me a better pointer than here is a link to so and so website. I promise I have tried it.

__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 07-13-2019, 04:18 PM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,396
Thanks: 68
Thanked 2,701 Times in 2,586 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Odd and Differing Behaviour in Query on SQL data stores

its not odd at all. you are only comparing the time against the date. if you cut the date part it will be 0 date or dec 30 1899.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-13-2019, 06:22 PM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,354
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Odd and Differing Behaviour in Query on SQL data stores

I looked this up because I wasn't familiar with SQL server internals. But it appears that date/time variables are numeric for SQL server similar to (if not identical to) Access internal date formats. When you say "SQL databases" did you mean SQL Server or some other SQL back end?

So... what is the data type of [StartTime]? You said the BE files contained dates, but I didn't think that you could use the LEFT function on dates because if they are truly date/time variables, they are numeric.

If on the other hand [StartTime] is text-oriented, then Access will do a string comparison because your dates are shown as quoted strings without the appropriate syntax (an octothorpe #) for enclosing literal dates.

Every comparison I saw looked like an apples-to-oranges comparison. Therefore I am not surprised to see that you are getting trouble from that query.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-13-2019, 06:44 PM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,270
Thanks: 0
Thanked 532 Times in 528 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Odd and Differing Behaviour in Query on SQL data stores

Can use string manipulation functions on date values. Access will do implicit conversion. However, result may not be acceptable if not first formatted to a consistent length string. LEFT should be unnecessary as FORMAT function can do the extraction.

Also, review http://allenbrowne.com/ser-36.html
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 07-13-2019 at 06:54 PM.
June7 is online now   Reply With Quote
Old 07-13-2019, 08:39 PM   #5
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 261
Thanks: 11
Thanked 1 Time in 1 Post
dynamictiger
Re: Odd and Differing Behaviour in Query on SQL data stores

Quote:
Originally Posted by June7 View Post
Can use string manipulation functions on date values. Access will do implicit conversion. However, result may not be acceptable if not first formatted to a consistent length string. LEFT should be unnecessary as FORMAT function can do the extraction.

Also, review http://allenbrowne.com/ser-36.html
Used format first. This didn't work.
__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 07-13-2019, 08:40 PM   #6
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 261
Thanks: 11
Thanked 1 Time in 1 Post
dynamictiger
Re: Odd and Differing Behaviour in Query on SQL data stores

Quote:
Originally Posted by arnelgp View Post
its not odd at all. you are only comparing the time against the date. if you cut the date part it will be 0 date or dec 30 1899.
Have converted to date in every conceivable way I can think of
__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 07-13-2019, 08:45 PM   #7
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 261
Thanks: 11
Thanked 1 Time in 1 Post
dynamictiger
Re: Odd and Differing Behaviour in Query on SQL data stores

Quote:
Originally Posted by The_Doc_Man View Post
When you say "SQL databases" did you mean SQL Server or some other SQL back end?
Yes I have two backend SQL servers at the moment. We are upgrading the online system so currently are running part old (online tools) and part new. Eventually it will all transfer to the new system, but the new code is taking longer than anticipated.

Quote:
Originally Posted by The_Doc_Man View Post
So... what is the data type of [StartTime]? You said the BE files contained dates, but I didn't think that you could use the LEFT function on dates because if they are truly date/time variables, they are numeric.

If on the other hand [StartTime] is text-oriented, then Access will do a string comparison because your dates are shown as quoted strings without the appropriate syntax (an octothorpe #) for enclosing literal dates.
The linked table is showing the field as short text. It does suggest it is a string, comparison, and certainly works with the new SQL like that. The old even though stored the same refuses to work with string compare tools. This is my main cause of confusion.


I can literally snip the SQL statement from one query that works, change the field names and drop it in aa query on the different table and it refuses to work.

__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 07-13-2019, 09:13 PM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,396
Thanks: 68
Thanked 2,701 Times in 2,586 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Odd and Differing Behaviour in Query on SQL data stores

how about if you add additional '0' to your criteria, since now you are comparing it to a text:
Code:
WHERE (((Left([StartTime],10)) Between '2019-01-01' And '2019-05-01'
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-13-2019, 11:36 PM   #9
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 261
Thanks: 11
Thanked 1 Time in 1 Post
dynamictiger
Re: Odd and Differing Behaviour in Query on SQL data stores

Quote:
Originally Posted by arnelgp View Post
how about if you add additional '0' to your criteria, since now you are comparing it to a text:
Code:
WHERE (((Left([StartTime],10)) Between '2019-01-01' And '2019-05-01'

I tried that and it trims the leading 0 every time...
__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 07-14-2019, 12:38 AM   #10
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,270
Thanks: 0
Thanked 532 Times in 528 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Odd and Differing Behaviour in Query on SQL data stores

That makes no sense. How can characters be 'trimmed' from within a text string?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
dynamictiger (07-14-2019)
Old 07-14-2019, 01:25 AM   #11
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,396
Thanks: 68
Thanked 2,701 Times in 2,586 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Odd and Differing Behaviour in Query on SQL data stores

you already have leading 0 in your working query.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
dynamictiger (07-14-2019)
Old 07-22-2019, 11:42 AM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Odd and Differing Behaviour in Query on SQL data stores

The Left() function is converting the date to a string so it must be formatted as yyyy-mm-dd to work correctly and that means including leading zeros for 1 digit months and days.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Stores / inventory data base Ltmac70 Tables 4 12-07-2016 10:23 AM
Need help on creating a form that stores data Emil Avramov Forms 14 10-26-2015 07:26 AM
same code - differing dbases - different errors! iankerry Modules & VBA 3 11-15-2011 05:52 AM
Continuously monitors Outlook and stores email data in database cursedeye Modules & VBA 4 11-10-2009 06:34 PM
differing forms... FLAME-oN! Forms 2 09-16-2003 06:41 AM




All times are GMT -8. The time now is 06:38 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World