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: 270
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,553
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,569
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,569
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: 270
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: 270
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,553
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: 270
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 Yesterday, 08:31 AM
Post#29


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


yw.gif


RAZ
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    19th February 2018 - 02:28 PM