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
UcaihcAsked:
Who is Participating?
 
DarthModCommented:
PAQed with points refunded (500)

DarthMod
Community Support Moderator
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.