different results of query from different workstations

[Durian62]

My multi-user database is on a server and I experience different query
results depending on from which workstation the query is run.

The query converts a txt-field into a date format with the following
command: format([left([variable xyz],16,â€General Dateâ€) with a criteria of
“>Now()-26/24â€.

If I remember correctly the problem is linked to differences in the setup of
Access on each single machine (possibly add-ins?) – who can help?

 

[Allen Browne]

Format() generates text, so you have not really converted your text field
into a date.

Try CDate() or CVDate() instead, e.g.:
CVDate(Left([variable xyz],16))
and criteria of:

DateAdd("h", 26, Now())

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My multi-user database is on a server and I experience different query
results depending on from which workstation the query is run.

The query converts a txt-field into a date format with the following
command: format([left([variable xyz],16,â€General Dateâ€) with a criteria of
“>Now()-26/24â€.

If I remember correctly the problem is linked to differences in the setup
of
Access on each single machine (possibly add-ins?) – who can help?

 

[John W. Vinson]

My multi-user database is on a server and I experience different query
results depending on from which workstation the query is run.

The query converts a txt-field into a date format with the following
command: format([left([variable xyz],16,”General Date”) with a criteria of
“>Now()-26/24”.

If I remember correctly the problem is linked to differences in the setup of
Access on each single machine (possibly add-ins?) – who can help?

General Date will have variable appearance depending on the computer's
Regional date/time settings. What are the typical contents of [variable xyz]?
I suspect that a DateSerial and/or TimeSerial function will be a better choice
than Format().

Note that Format will return a text string, not a Date/Time value, and that
the Now() expression might better be

= DateAdd("h", -26, Now())

if your intention is to find all values more recent than 26 hours ago. If
that's not your intention... what is?

 

[Durian62]

Thx for the quick response - will try tomorrow

But one question remains - why does it work on my laptop, but not on the
others ??
--
Durian62

Format() generates text, so you have not really converted your text field
into a date.

Try CDate() or CVDate() instead, e.g.:
CVDate(Left([variable xyz],16))
and criteria of:

DateAdd("h", 26, Now())

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My multi-user database is on a server and I experience different query
results depending on from which workstation the query is run.

The query converts a txt-field into a date format with the following
command: format([left([variable xyz],16,â€General Dateâ€) with a criteria of
“>Now()-26/24â€.

If I remember correctly the problem is linked to differences in the setup
of
Access on each single machine (possibly add-ins?) – who can help?

 

[Allen Browne]

Dodgy things work in one version (or even in one service pack) and not on
another.

It might even depend on the regional settings of the machine.

 

[Durian62]

To both Allen & John

Thanks for your support - I tried the CDate on my home PC - teh acid test
will be tomorrow at work again
Yes, I also thought that teh regional settings might be the root-cause of
the problem, but I haven't found the bug yet.

Again - I'll give it a shot with CDate - and yes, with my criteria I wanted
to eliminate all records older than 26 hrs.

Thanks again
Durian62
--
Durian62

My multi-user database is on a server and I experience different query
results depending on from which workstation the query is run.

The query converts a txt-field into a date format with the following
command: format([left([variable xyz],16,â€General Dateâ€) with a criteria of
“>Now()-26/24â€.

If I remember correctly the problem is linked to differences in the setup of
Access on each single machine (possibly add-ins?) – who can help?

General Date will have variable appearance depending on the computer's
Regional date/time settings. What are the typical contents of [variable xyz]?
I suspect that a DateSerial and/or TimeSerial function will be a better choice
than Format().

Note that Format will return a text string, not a Date/Time value, and that
the Now() expression might better be

= DateAdd("h", -26, Now())

if your intention is to find all values more recent than 26 hours ago. If
that's not your intention... what is?

 

[Rick Brandt]

My multi-user database is on a server and I experience different query
results depending on from which workstation the query is run.

The query converts a txt-field into a date format with the following
command: format([left([variable xyz],16,"General Date") with a
criteria of ">Now()-26/24".

If I remember correctly the problem is linked to differences in the
setup of Access on each single machine (possibly add-ins?) - who can
help?

All of the named formats "General Date", "Short Date", "Currency" etc., depend
on the user's Regional settings in Windows. When you require consistency, don't
use the named formats but use explicit format strings like "mm/dd/yyyy hh:nn
ampm".