[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

What is wrong with this DateDiff function in an Access VB query

sqlMax50percent1 = "SELECT MAX(DATEX) AS Median1 FROM " & _
        "(SELECT TOP 50 PERCENT " & DateDiff("d", tblFOIAdata.Date_Completed, tblFOIAdata.Date_Rcvd) & " AS DATEX " & _
        "FROM tblFOIAdata " & _
        "WHERE (((tblFOIAdata.Date_Rcvd) Between " & strWhereClause & ")) AND tblFOIAdata.Date_Completed Is Not Null " & _
        "AND tblFOIAdata.Simple= 'Simple' " & _
        "ORDER BY 1) AS H1"
           

I need to find the median of an number of days and I get an error with the DateDiff function
0
Ucaihc
Asked:
Ucaihc
1 Solution
 
dancebertCommented:
One problem: use single quotes instead of double quotes around the first DateDiff parameter.


0
 
dancebertCommented:
I don't think Access supports FROM ( <select expression> ), only FROM <table expression> or FROM <query>.

Here is a knowledgeBase article on how to get a median in Access SQL

http://support.microsoft.com/?id=210581

0
 
patingsadagatCommented:
Is Date_Completed and Date_Rcvd a datepicker control? If so, add .value
Ex: tblFOIAdata.Date_Completed.Value
      tblFOIAdata.Date_Rcvd.Value

Another thing, you AS H1 in your query.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
aikimarkCommented:
@dancebert
<<use single quotes >>
DateDiff() format requires quotes, so SQL string must accomodate with doubled-up quote characters or Chr(34).

<<I don't think Access supports FROM ( <select expression> ),>>
That restriction was losened with MSAccess version2000 and later.  I too used to think this was a restriction.

========================
@patingsadagat
<<Is Date_Completed and Date_Rcvd a datepicker control?>>
Doubtful, since they were qualified with the table name.

========================
Depending on the back-end database, DateDiff() might not even be supported.  If it is then try:
sqlMax50percent1 = "SELECT MAX(H1.DATEX) AS Median1 FROM " & _
        "(SELECT TOP 50 PERCENT " & DateDiff(""d"", tblFOIAdata.Date_Completed, tblFOIAdata.Date_Rcvd) & " AS DATEX " & _
        "FROM tblFOIAdata " & _
        "WHERE (((tblFOIAdata.Date_Rcvd) Between " & strWhereClause & ")) AND tblFOIAdata.Date_Completed Is Not Null " & _
        "AND tblFOIAdata.Simple= 'Simple' " & _
        "ORDER BY 1) AS H1"

Notes:
1. Beware of how strWhereClause formats its dates relative to the back-end database.
2. Are you use you want the oldest 50% dates?

If DateDiff() not supported by the back-end database, try
sqlMax50percent1 = "SELECT MAX(H1.DATEX) AS Median1 FROM " & _
        "(SELECT TOP 50 PERCENT " & Int(tblFOIAdata.Date_Completed - tblFOIAdata.Date_Rcvd) & " AS DATEX " & _
        "FROM tblFOIAdata " & _
        "WHERE (((tblFOIAdata.Date_Rcvd) Between " & strWhereClause & ")) AND tblFOIAdata.Date_Completed Is Not Null " & _
        "AND tblFOIAdata.Simple= 'Simple' " & _
        "ORDER BY 1) AS H1"
0
 
UcaihcAuthor Commented:
The single quotes around the, 'd', or the two double quotes. " " d" " gave me errors. I tried using the Int function, but again I got the error 'object required'

Everything worked when I was building my query and used (tblFOIAdata.Date_Completed - tblFOIAdata.Date_Rcvd) as a placeholder. When I used Int or DateDiff, I get the object required error. Can you help?
0
 
aikimarkCommented:
I recommend PAQ with no refund
0
 
DarthModCommented:
PAQed with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now