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

Dennis McDermott wrote:
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

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:
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 (AT) gmail (DOT) com> wrote

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

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

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 and an error on 2 other PCs?

Dennis

Filter by Form is only an interface for entering filter values, so all the
issues mentioned in my previous reply are applicable. It will be unreliable
and inefficient. But you already know that.

You could go down the path of trying to figure out what is different between
the machine where it works compared to one where it doesn't. You may find a
different service pack of Access or of JET, or you may find the Regional
Settings (in the Windows Control Panel) is different and so the values are
treated differently, or you may find it is to do with other factors (such as
the actual entries the users are making.)

More to the point, is it really worth trying to find out why an unreliable
approach works sometimes and not others?

--
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 (AT) gmail (DOT) com> wrote

Thanks Allen,

Yes I agree that the most important thing to find out is why the PCs
are behaving differently, I know the values being entered are the same
as I am entering them, this database has not been delivered yet.

The PCs are using different OS's Vista 64 Bit (works) XP PRO (does not
work) I have also tried on a 32 Windows 7 PC, this works as well.

I am sure the Access SP's are the same, the regional settings are the
same, I am not sure how to check JET.

Dennis

For Access 2000 - 2003, look at the version of msjet40.dll.
It's usually in windows\system32.

For Access 2007, you nee the acecore.dll, in
Program Files\Common Files\Microsoft Shared\Office12\

The GetJetVersion() function on this page will do it:
http://allenbrowne.com/ser-53code.html#GetJetVersion

Don't forget to check out the regional settings for dates in Windows Control
Panel too.

--
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 (AT) gmail (DOT) com> wrote

Thanks again Allen,

I used your code and this is the result

VISTA HOME PREMUIM JET VERSION 4.0.9511.0 (filter works as expected
here)

XP PRO JET VERSION JET VERSION 4.0.9511.0 (filter does not work as
expected here, same Jet Version as PC above, so it looks like its
something else that is causing the difference)

WINDOWS 7 JET VERSION 4.0.9756.0 (filter works as expected here)

Dennis