UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> The Difference Different Code Can Make, Access 2016    
 
   
firlandsfarm
post Feb 16 2018, 01:38 PM
Post#21



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Thanks for the vid RAZ, I can now see the SQL views in Access but ... How the h*** do you filter a DateTime field for today's date i.e. I want all events of today. In Access it's a doddle I just filter on >Date() and let the time make the value greater but I've wasted over an hour with Google trying to find the answer and it seems to be a regular complication for people. I've tried every suggested solution they have thrown at me and none on them work for me. Has anyone any idea how to perform this very simple, very basic filter? The Access SQL clause would be ...

Where [DateTimeField] > Date()
This post has been edited by firlandsfarm: Feb 16 2018, 01:39 PM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RAZMaddaz
post Feb 16 2018, 02:51 PM
Post#22


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


Sorry for taking forever in getting back to you!

Try the following for your Criteria: Between Date() And Date()+#11:59:59 PM#

RAZMaddaz
Go to the top of the page
 
AlbertKallal
post Feb 16 2018, 03:00 PM
Post#23


UtterAccess VIP
Posts: 2,633
Joined: 12-April 07
From: Edmonton, Alberta Canada


You really don’t want to place the criteria in the query.

The whole point of using views is that you THEN get to use EXISTING CODE and EXISTING approaches you used in the past.

You never in the past wanted to place dynamic criteria in the SQL. (Because then you can’t re-use that SQL elsewhere).

So just use what you used in the past.

So this would work just fine:

CODE
Docmd.OpenReport "rptInvoice",acViewPreview,,"InvoiceDate = Date()"


Now of course the above is an “evaluated” expression, and we want to avoid the above. So here is what I would use:


CODE
Docmd.OpenReport "rptInvoice",acViewPreview,,"InvoiceDate = " & qu(date())


Of course since you have to OFTEN format strings, or dates then I have these two global functions in all my applications to allow easy coding.

So how you filter that view should be 100% the same as how you would do this in the past. You don’t need a new coding approach or anything else.

The two handily dandy functions use qu() for strings, and qudate() are as follows:

CODE
Function qu(vText As Variant) As String
  
   ' takes a string and surrounds it with double quotes

   qu = Chr$(34) & vText & Chr$(34)
  
End Function

Public Function quDate(dt As Date) As String

   ' return formatted date
  
   quDate = "#" & Format(dt, "mm\/dd\/yyyy HH:NN:SS") & "#"
  
  
End Function



So the qudate() fucntion just simply formats any date var to a correct formatted USA string for date “where” clauses.

Note that for pass-through, hten of course you need to format for SQL server. I use this one:

CODE
Public Function qudateSQL(myDate As Variant) As String

   ' returns ISO date for SQL server
  
   If IsNull(myDate) = True Then
      qudateSQL = ""
   Else
      ' USE iso date format
      qudateSQL = "'" & Format(myDate, "yyyy-mm-dd") & "'"
   End If
  
End Function


As noted, you in “general” do NOT want the criteria in the SQL if it going to change.

Now to be not so inflexible here?

Let’s assume that some great case been made that you want the “today date” criteria in the SQL view?

You can use this:
CODE
raw t-SQL/ view
Select * from tblHotels where BookDate = Convert(Date,getdate())


Now, just like date()/Now() in access (date = date only, now = date + time), the SAME applies to above. So

CODE
T-SQL                              Access SQL/VBA
GetDate()                       =    Now()          ' this includes BOTH date and time parts

ConvertDate(Date,GetDate())     =      Date()        ' this is date part only


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
AlbertKallal
post Feb 16 2018, 03:05 PM
Post#24


UtterAccess VIP
Posts: 2,633
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
Try the following for your Criteria


He is creating a view - so the syntax has to be SQL server syntax, not local access syntax

I explain how you can write a date = today date I SQL server in my above post.

R
Albert
Go to the top of the page
 
firlandsfarm
post Feb 16 2018, 03:43 PM
Post#25



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Raz, I have been led to believe in the SQL articles I have been reading that Date() is an Access SQL function but not a Transact SQL function ... I need the transact SQL equivalent of Date(). (This is though subject to what Albert has just said, I'll be reading that next!)

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
firlandsfarm
post Feb 16 2018, 04:19 PM
Post#26



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Albert, I'm sure what you have written is very informative but quite franklly I haven't a clue what you are talking about for most of it! Remember I said earlier in this thread that I have no idea what to do with what I suspect is VBA code.

I simply want a view that shows me what has/is happening today and in my simple mind if SQL is faster to create an unfiltered list why is it not faster to filter the list? You seem to be saying to only use SQL to combine the tables together and let Access filter the list of all records which means you are saying SQL is great for compiling a list but rubbish at filtering it!

In my case I would be asking SQL to form the list of over 2 million records and then ask Access to find the 100 or so that are relevant for today! Is that really more efficient than filtering in SQL?

QUOTE
I explain how you can write a date = today date I SQL server in my above post.

I found "Convert(Date,getdate())" in articles on the Internet but having tried "WHERE [DateTimeField] > Convert(Date,getdate())" I get no return. Maybe I need to explain it more fully ...

Let's assume a record in the [DateTimeField] of the SQL table has a value of 16/02/2018 15:10:00
and that table is linked to my Access database
and I have a local Access query with a clause ... "WHERE [DateTimeField]>Date()"
then today (16/02/18) and today only the query will find the record because 16/02/2018 15:10:00 is greater than 16/02/2018, today's value of Date()

That's what I want to achieve in the SQL view.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RAZMaddaz
post Feb 17 2018, 08:32 PM
Post#27


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


Sorry, for some reason I thought you were trying to do this in Access.

Here is what I used for the Where clause, in order to just return the Current Date.

CODE
Where Convert(Date,(Invoices.InvoiceDate)) = Convert(Date,getDate())


Change the Invoices.InvoiceDate to the Table and Field you are using.

RAZMaddaz
Go to the top of the page
 
firlandsfarm
post Feb 17 2018, 11:48 PM
Post#28



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Well done and thank you Raz ... that worked for me, I wasn't using the Convert on both sides! I can now carry on working on this. Thank you to all contributors, I will now develop some of the issues raised.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RAZMaddaz
post Feb 18 2018, 08:31 AM
Post#29


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


yw.gif


RAZ
Go to the top of the page
 
AlbertKallal
post Feb 20 2018, 03:01 PM
Post#30


UtterAccess VIP
Posts: 2,633
Joined: 12-April 07
From: Edmonton, Alberta Canada


Hum, if you use convert() on both sides, you are likely to loose indexing.

This should have worked
CODE
raw t-SQL/ view
Select * from tblHotels where BookDate = Convert(Date,getdate())


I would double check that the column in question really is a datetime column.

Regardless, you seem to have this working, but in the past I used the above with success.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
firlandsfarm
post Feb 21 2018, 09:10 AM
Post#31



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Albert, of course I can't comment on losing indexes or not, that's a subject too far for me but ... no varient of a Where clause worked for me until I took RAZ's advice. Having seen that work I immediately thought "knowing how fussy SQL is it probably didn't return any records because it doesn't like that my field has the Time as well as the Date". As I have said earlier I didn't have a problem in Access with [DateTimeField]>Date() and cannot see why it's so difficult in SQL. I can't believe it's this difficult to find all records for Today in SQL?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
AlbertKallal
post Feb 21 2018, 06:02 PM
Post#32


UtterAccess VIP
Posts: 2,633
Joined: 12-April 07
From: Edmonton, Alberta Canada


It not difficult in T-SQL, but you just trying all kinds of things and NOT stopping to think about what is going on here.

How you deal with this issue in Access SQL or T-SQL really not much different.

And of course what makes this worse is my example is wrong! But let’s figure out why!!

I mean, either we strip out the time portion, or you build a query that takes the “time” part into account. You can do this either way, and you ALWAYS had to deal with this issue in Access!


Now back to my query:
CODE
Select * from tblHotels where BookDate = Convert(Date,getdate())


Given in above we assume the bookDate has date/time, then of course it will never equal (=) “just” the date. So with our knowledge, then we apply the “rules” that I previous outlined.


So changing the = to >= will find anything starting today and later into the future. (quite sure that was an acceptable solution).

So one ittby bitty “>” and this works!

CODE
Select * from tblHotels where BookDate >= Convert(Date,getdate())


Now, here is a interesting question: In Access, how would you get just today, and NOT future dates?
(after all, that is what you t-SQL does, but not the posted Access solution).

How would we do this say in Access SQL? (and you note that Access SQL is easy and not confusing).


So some learning + knowledge is required for Access SQL, and that of T-SQL. Dealing with “time” as part of date certainly requires some additional knowledge.

Your t-SQL only will return today – so how would you do this now in Access?

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
firlandsfarm
post Feb 22 2018, 01:28 AM
Post#33



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Albert thanks again for your help. I was thinking. That is why I raised the question as to whether SQL was being fickle about the included Time! You will see that I had already tried the same approach as you earlier in this thread and it's basically the same as what I have always used in Access (and I spotted your 'error' and changed the "=" to a ">" (I wasn't bothered about the millisecond at midnight)). That was really the reason why I started this addition to my original post ... because it didn't work and it was for that reason that I was thinking why it didn't work. But, it does now because I found a very simple, silly but 'could happen' error. I was interigating the wrong databas!! How can you be so silly I hear you ask ... simple, I am interogating a 3rd party database that is used and updated daily by proprietary software and what I didn't notice was that a short time ago in an update they seem to have installed a new version of the database so my SSMS was looking at an out of date database and so there was no data for Today! I have now connected to the up to date version and all is fine. I suppose that's what can happen when you interogate someone else's database where you have no control.

I wish to thank you and all contributors for your input, it's not been a waste of time ... not only did I find I was looking at an out of date database but I have furthered my SQL knowledge by learning a few more commands and how to use them. Thanks again.

BTW in answer to your question I put a limiter on the date search of <Date()+1 or I create an Expression column of Datevalue(DateTime) and filter for =Date() or you can have an alternative Expression column of Interger(DateTime) and again filter for =Date() ... there's many ways to skin that cat! smile.gif
This post has been edited by firlandsfarm: Feb 22 2018, 02:02 AM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    18th June 2018 - 06:08 PM