I had a problem with my datetime filtering in one of my query. My table is link from our SQL Database Server 2005 and im reading it in Microsoft Access using one Form and Query, my Date From and To from my form is not properly filtering the data from my query. I used the Format("dd/mm/yyy", field) in my criteria but still not working. I also converted the datetime field in directly to my sql field using CONVERT() function but still not working. I need some expert advice. Thanks.
Thanks for your reply but i think i found a better solution that accurately filtering based on my requirement, let me share it to others. In my database SQL, i made an extra field say MyDateInTxt, then used the function CONVERT(varchar(10),(mydatefile),103) to convert datetime to only dd/mm/yyyy format but the result of this is in STRING Data Type, then in my query in Microsoft Access, i converted the variable MyDateInTxt to date using function CDATE(MyDateInTxt), then in my parameter in date evething its back to normal date filtering. Its easy. Now my query is working perfectly now.
MY SCRIPT
SELECT dbo.ttcibd001400.t_citg AS [Item Group], dbo.ttcmcs023400.t_dsca AS [Item Grp Desc], dbo.ttisfc001400.t_apdt AS [Opening Date], CONVERT(varchar(10), dbo.ttisfc001400.t_apdt, 103) AS OpDate, dbo.ttisfc001400.t_cldt AS [Closing Date], dbo.tticst001400.t_sitm AS [Sub Item], dbo.ttcibd001400.t_dsca AS [Item Desc], dbo.tticst001400.t_ccur AS Currency, dbo.tticst001400.t_ques AS [Estimated Qty], dbo.tticst001400.t_qucs AS [Actual Qty], dbo.tticst001400.t_qucs - dbo.tticst001400.t_ques AS Difference1, dbo.tticst001400.t_cpes_1 * dbo.tticst001400.t_ques AS [Estimated SR], dbo.ttisfc001400.t_pdno AS [production order],
dbo.ttisfc001400.t_pcod_c AS Pond, dbo.ttisfc001400.t_crop_c AS Crop, dbo.ttcibd001400.t_cuni AS Unit, dbo.tticst001400.t_aamt_1 AS [Actual SR],
dbo.tticst001400.t_aamt_1 - dbo.tticst001400.t_cpes_1 * dbo.tticst001400.t_ques AS Difference2 FROM dbo.tticst001400 INNER JOIN dbo.ttcibd001400 ON dbo.tticst001400.t_sitm = dbo.ttcibd001400.t_item INNER JOIN
dbo.ttisfc001400 ON dbo.tticst001400.t_pdno = dbo.ttisfc001400.t_pdno INNER JOIN
dbo.ttcmcs023400 ON dbo.ttcibd001400.t_citg = dbo.ttcmcs023400.t_citg
WHERE (LEFT(LTRIM(dbo.ttisfc001400.t_pdno), 3) BETWEEN '101' AND '210') AND (LEFT(LTRIM(dbo.tticst001400.t_sitm), 2) <> 'GR')
ORDER BY [Item Group], [Sub Item]
Thanks for your reply but i think i found a better solution that accurately filtering based on my requirement, let me share it to others. In my database SQL, i made an extra field say MyDateInTxt, then used the function CONVERT(varchar(10),(mydatefile),103) to convert datetime to only dd/mm/yyyy format but the result of this is in STRING Data Type, then in my query in Microsoft Access, i converted the variable MyDateInTxt to date using function CDATE(MyDateInTxt), then in my parameter in date evething its back to normal date filtering. Its easy. Now my query is working perfectly now.
I don't think you will find many who share your enthusiasm for that solution. Remember that the members posting here have considerable experience and it is very unlikely that a newbie is going to come up with a better solution to something as trivial as a date comparison in SQL. Here are some things that are wrong with your technique.
Firstly adding the MyDateInTxt field to the Server table breaches normalization by storing the same information in two fields. This also requires an action by SQL Server to maintain the value.
Secondly your ten character string comparison is far, far, far slower than the date datatype comparison. This string comparison must make ten two byte comparisons and combine their results. With alpha characters the load would be doubled because Access text comparison is case insensitive.
Date in Access is stored as an eight byte numeric value. The SQL query uses a single numeric comparison.
Moreover the string cannot be queried for range so BETWEEN X AND Y will not work on your text string at all.
Indexing a Date datatype field is very efficient. A text index is much larger and slower to process.
Thanks for the reply and added points, I appreciate your help but anyway I tried what you recommended to me but its giving an empty result in my QBE (Format([whatever], "\#mm\/dd\/yyyy\#"),
Here is my Column
Expr1: Format([Closing Date],"\#dd\/mm\/yyyy\#")
Here is the criteria
Between [Forms]![frmMainScreenReport]![txtClosingDateFrom] And [Forms]![frmMainScreenReport]![txtClosingDateTo]
If i used Format([Closing Date],"\#dd\/mm\/yyyy\#") its giving me an empty result.
But when I use Format([Closing Date],"dd/mm/yyyy/"), its giving me wrong set of records that includes dates that are not belong to the parameter i entered in my form. Example im expecting only 219 records but its displaying more than that.
Date queries can be quite confusing and most developers discover this in small pieces. I will try and cover a wide scope here.
The exact syntax of Date queries in Access depends on the context and the Regional Date format of the computer.
In Australia the regional date format is dd/mm/yyyy. Here, inside the Access query designer criteria cell the SQL view shows #dd/mm/yyyy#. Type the date in the criteria cell without the # and Access adds them. I expect other regional formats would behave similarly.
For SQL as applied when using a value from a form's control in VBA, the format needs to be #mm/dd/yyyy#. However even in this situation local date format comes into play. As typed, the "/" character is actually a replacement marker that substitutes the local date seperator character. Used in regions with the / as the date seperator there are no problems but the statement will fail in others.
The backslash "\" indicates the next character is literally as typed so the sequence "\/" means "/" regardless of the regional seperator setting.
Consequently the SQL date formatting has many forms. In the USA where the convention is mm/dd/yyyy the SQL is often seen just like this:
Code:
" ... WHERE [fieldname]=#" & Me.[datecontrol] & "#"
However this fails when the code is used in other regions. Developers where the regional setting is dd/mm/yyyy frequently use:
Code:
" ... WHERE [fieldname]=#" & Format(Me.[datecontrol], "mm/dd/yyyy") & "#"
Although this works in both mm/dd/yyyy and dd/mm/yyyy regions this fails anywhere that uses characters other than the forward slash as the date seperator.
So a more compatible format uses the literal replacement:
Code:
" ... WHERE [fieldname]=#" & Format(Me.[datecontrol], "mm\/dd\/yyyy") & "#"
However some developers (including me) prefer to include the # charater as a literal in the format rather than in the string section of the statement:
Code:
" ... WHERE [fieldname]=" & Format(Me.[datecontrol], "\#mm\/dd\/yyyy\#")
AFAIK, these last two forms that force the literal forward slash can be relied on to work everywhere in the Jet/ACE database engines.
However if the query is directed to SQL Server itself as in a PassThrough or as a Recordset then it must use the single quote in place of the #:
Code:
" ... WHERE [fieldname]=" & Format(Me.[datecontrol], "\'mm\/dd\/yyyy\'")
A further complexity is Access's propensity to "fix" problems itself. When encountering an out-of-region date it will automatically accept it by reversing the month and day.
Type a date that could be either dd/mm/yyyy or mm/dd/yyyy directly into VBA and it will do whatever it is in the mood to do. Consequently sometimes the best solution is to use yyyy/mm/dd which is also accepted by ACE (and I expect JET).
However some other developers have told me that the acceptance of yyyy/mm/dd is actually just another form of automatic translation applied before executing a query in the engine. The engine always processes mm/dd/yyyy so it is best to stick to this format whereever possible.
