Filter on a date working on one PC not not another
in [Help]
|
From: Dennis McDermott on 27 May 2010 09:26 Access 2003 SP3 I have a form with a couple of date/time fields On one PC I can do a filter by form and enter *17/03/2010* and the filter will display records where the date is 17/03/2010 (regardles of the time part) which is what I want. However, on two other PCs if I try and filter on the same textbox with the same filter I get an error. "The expression you entered contain invalid syntax" You may have entered a comma without a preceeding value or identifier The PC it works on is running Vista 64 Bit The other PCs where it does not work are Windows XP PRO SP3 The version of Access is the same on all 3 PCs. Another thing, on the PCs that will not work, if I highlight the date with my mouse and then use 'filter by selection' it works fine, so I thought I would look at the forms filter at that point and this is it ((Format$(qryShowAllEquipment.dtmLoanStartDateTime,"dd/mm/yyyy hh:nn") Like "17/03/2010*")) So it seems Access wraps a format around the filter expression? Can anybody suggest a solution to how I get this to work on all 3 PCs? Many Thanks Dennis
From: Salad on 27 May 2010 10:28 Dennis McDermott wrote: > Access 2003 SP3 > > I have a form with a couple of date/time fields > > On one PC I can do a filter by form and enter *17/03/2010* and the > filter will display records where the date is 17/03/2010 (regardles of > the time part) which is what I want. > > However, on two other PCs if I try and filter on the same textbox with > the same filter I get an error. > > "The expression you entered contain invalid syntax" > You may have entered a comma without a preceeding value or identifier > > The PC it works on is running Vista 64 Bit > The other PCs where it does not work are Windows XP PRO SP3 > > The version of Access is the same on all 3 PCs. > > Another thing, on the PCs that will not work, if I highlight the date > with my mouse and then use 'filter by selection' it works fine, so I > thought I would look at the forms filter at that point and this is it > > ((Format$(qryShowAllEquipment.dtmLoanStartDateTime,"dd/mm/yyyy hh:nn") > Like "17/03/2010*")) > > So it seems Access wraps a format around the filter expression? > > Can anybody suggest a solution to how I get this to work on all 3 PCs? > > Many Thanks > > Dennis x = now() ? x 5/27/2010 7:18:24 AM ? cdate(x) 5/27/2010 7:18:24 AM ? cdate(format(x,"dd/mm/yyyy")) 5/27/2010 'set a filter Dim d as date d = #3/10/2010# Me.filter = _ Cdate(Format([dtmLoanStartDateTime],"dd/mm/yyyy")) = #17/3/2010# Me.filteron = True
From: Allen Browne on 27 May 2010 11:24 I presume that you actually enter the wildcards, so you are relying on them to get any records that contain that date regardless of any time component. That means you are performing a string comparison, which is both inefficient and unreliable. A far better idea would be to use criteria like this in your query: >= [Forms].[Form1].[Text0] AND < ([Forms].[Form1].[Text0] + 1) Then, to be absolutely certain Access understands the date correctly: 1. Set the Format property of Text0 to General Date. 2. Declare the parameter in the query, by clicking Parameters on query menu/ribbon, and entering a row like this in the Parameters dialog: [Forms].[Form1].[Text0] Date/Time Explanation: a) Access will understand the data type of the unbound text box correctly. b) Access (JET) will treat the value correctly in the query. c) It will perform a date/time comparison (not a string comparison), and so get the results right regardless of regional setting. d) It will be able to use any index on the date/time field, so will be much more efficient to execute if there are many records in the table. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Dennis McDermott" <dennis.mcdermott(a)gmail.com> wrote in message news:40d903f0-3701-415e-bac7-3eb66935d811(a)f13g2000vbm.googlegroups.com... > Access 2003 SP3 > > I have a form with a couple of date/time fields > > On one PC I can do a filter by form and enter *17/03/2010* and the > filter will display records where the date is 17/03/2010 (regardles of > the time part) which is what I want. > > However, on two other PCs if I try and filter on the same textbox with > the same filter I get an error. > > "The expression you entered contain invalid syntax" > You may have entered a comma without a preceeding value or identifier > > The PC it works on is running Vista 64 Bit > The other PCs where it does not work are Windows XP PRO SP3 > > The version of Access is the same on all 3 PCs. > > Another thing, on the PCs that will not work, if I highlight the date > with my mouse and then use 'filter by selection' it works fine, so I > thought I would look at the forms filter at that point and this is it > > ((Format$(qryShowAllEquipment.dtmLoanStartDateTime,"dd/mm/yyyy hh:nn") > Like "17/03/2010*")) > > So it seems Access wraps a format around the filter expression? > > Can anybody suggest a solution to how I get this to work on all 3 PCs? > > Many Thanks > > Dennis
From: Dennis McDermott on 28 May 2010 02:13 Thank you for your answers, I believe I may not have made myself clear in my question, sorry This problem is happening when a user uses the 'Filter By Form' button on the menubar. I am not writing code at this point. So on one PC the user clicks the 'Filter By Form' button, enters *17/03/2010* into the date textbox, then clicks the 'Apply Filter' button on the menubar. One PC shows the form with all the records containing 17/03/2010 and the other two show this error "The expression you entered contain invalid syntax" You may have entered a comma without a preceding value or identifier So, the real question is why does the ser get the expected result on one PC nd an error on 2 other PCs? Dennis
From: Dennis McDermott on 28 May 2010 02:15
Thank you for your answers, I believe I may not have made myself clear in my question, sorry This problem is happening when a user uses the 'Filter By Form' button on the menubar. I am not writing code at this point. So on one PC the user clicks the 'Filter By Form' button, enters *17/03/2010* into the date textbox, then clicks the 'Apply Filter' button on the menubar. One PC shows the form with all the records containing 17/03/2010 and the other two show this error "The expression you entered contain invalid syntax" You may have entered a comma without a preceding value or identifier So, the real question is why does the user get the expected result on one PC nd an error on 2 other PCs? Dennis |