Parameter query runs on one pc but not on another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following parameter query which returns data on my pc (XP) and did
at one time on another (2K) until that one was re-imaged. Now it returns
nothing on that pc. They both are on the same version of Access. Is there
something wrong with the setup of the second pc?

SELECT qryPatcomSuspensePatch.NewLine, qryPatcomSuspensePatch.NewBatch,
qryPatcomSuspensePatch.AcctNum, qryPatcomSuspensePatch.CDMNum,
qryPatcomSuspensePatch.SVCDate, qryPatcomSuspensePatch.Qty,
qryPatcomSuspensePatch.Price, qryPatcomSuspensePatch.DEPT,
qryPatcomSuspensePatch.Expl, qryPatcomSuspensePatch.Order,
qryChargelog2.patient_name, qryChargelog2.ord_num, qryChargelog2.charge_num,
qryChargelog2.drug_name, IIf([final_qty]=0,IIf([bill_type]='PCR','- ',' ') &
[disp_unit_qty] & ' x ' & [bill_unit_qty],' ') AS Disp, qryChargelog2.ID1,
qryChargelog2.bill_timestamp AS Expr1, qryPatcomSuspensePatch.PostDate
FROM qryChargelog2 INNER JOIN qryPatcomSuspensePatch ON qryChargelog2.Order
= qryPatcomSuspensePatch.Order
WHERE (((qryChargelog2.bill_timestamp) Like [Billed Date: ] & "*"))
ORDER BY qryChargelog2.ID1;

Thanks,
 
Suggestions:

1 Untyped parameter can be misinterpreted
=================================
Assuming that bill_timestamp is a Date/Time Field, choose Parameters on the
Query menu (in query design.)
Access opens a dialog.
Enter this:
[Billed Date: ] Date/Time

2 Wildcard with dates
================
The Like operator performs a string comparison, which is inefficient and
likely to cause the kind of problem you are experiencing. Additionally the
criteria does not return all records: records where the date is null are
excluded.

Change the criteria to:
WHERE ([Billed Date: ] Is Null)
OR (qryChargelog2.bill_timestamp = [Billed Date: ])



3. Patch differences in JET
=====================
Also, check JET - the query engine in Access.
Locate the file msjet40.dll on both computers (typically in
windows\system32.)
Right-click it, and choose Properties.
On the Version tab, you will see:
4.0.8xxx.0
The xxx digits don't matter, but if you do not see at least the 8 starting
the minor version number, go to:
http://support.microsoft.com/kb/239114
and get the latest service pack for JET 4.
(The minor version might start with 9 on a Windows 2000 machine, but 8 on a
Windows XP machine. Both machines are up to date if you see this.)

4. Patch differences in Access
=======================
Where a query works on one PC and not on another, you are trying to identify
the differences between the 2 machines.

Do they both have the same service pack for you version of Office?
(See Help | About.)
If they are different, go to:
http://support.microsoft.com/gp/sp

5. Regional Settings
===============
On each machine, open the Windows Control Panel, and choose Regional
Options. See if the settings are different for dates.

More info on misinterpreted field values:
http://allenbrowne.com/ser-45.html

More info on non-US date formats:
http://allenbrowne.com/ser-36.html

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

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

melissas said:
I have the following parameter query which returns data on my pc (XP) and
did
at one time on another (2K) until that one was re-imaged. Now it returns
nothing on that pc. They both are on the same version of Access. Is
there
something wrong with the setup of the second pc?

SELECT qryPatcomSuspensePatch.NewLine, qryPatcomSuspensePatch.NewBatch,
qryPatcomSuspensePatch.AcctNum, qryPatcomSuspensePatch.CDMNum,
qryPatcomSuspensePatch.SVCDate, qryPatcomSuspensePatch.Qty,
qryPatcomSuspensePatch.Price, qryPatcomSuspensePatch.DEPT,
qryPatcomSuspensePatch.Expl, qryPatcomSuspensePatch.Order,
qryChargelog2.patient_name, qryChargelog2.ord_num,
qryChargelog2.charge_num,
qryChargelog2.drug_name, IIf([final_qty]=0,IIf([bill_type]='PCR','- ',' ')
&
[disp_unit_qty] & ' x ' & [bill_unit_qty],' ') AS Disp, qryChargelog2.ID1,
qryChargelog2.bill_timestamp AS Expr1, qryPatcomSuspensePatch.PostDate
FROM qryChargelog2 INNER JOIN qryPatcomSuspensePatch ON
qryChargelog2.Order
= qryPatcomSuspensePatch.Order
WHERE (((qryChargelog2.bill_timestamp) Like [Billed Date: ] & "*"))
ORDER BY qryChargelog2.ID1;
Click to expand...
 
Thanks, Allen. The tips are all good. I thought I had previously checked
the regional date/time settings. I matched those up and changed one from
English-United Kingdom to English-United States and it worked. Thanks so
much.

Allen Browne said:
Suggestions:

1 Untyped parameter can be misinterpreted
=================================
Assuming that bill_timestamp is a Date/Time Field, choose Parameters on the
Query menu (in query design.)
Access opens a dialog.
Enter this:
[Billed Date: ] Date/Time

2 Wildcard with dates
================
The Like operator performs a string comparison, which is inefficient and
likely to cause the kind of problem you are experiencing. Additionally the
criteria does not return all records: records where the date is null are
excluded.

Change the criteria to:
WHERE ([Billed Date: ] Is Null)
OR (qryChargelog2.bill_timestamp = [Billed Date: ])



3. Patch differences in JET
=====================
Also, check JET - the query engine in Access.
Locate the file msjet40.dll on both computers (typically in
windows\system32.)
Right-click it, and choose Properties.
On the Version tab, you will see:
4.0.8xxx.0
The xxx digits don't matter, but if you do not see at least the 8 starting
the minor version number, go to:
http://support.microsoft.com/kb/239114
and get the latest service pack for JET 4.
(The minor version might start with 9 on a Windows 2000 machine, but 8 on a
Windows XP machine. Both machines are up to date if you see this.)

4. Patch differences in Access
=======================
Where a query works on one PC and not on another, you are trying to identify
the differences between the 2 machines.

Do they both have the same service pack for you version of Office?
(See Help | About.)
If they are different, go to:
http://support.microsoft.com/gp/sp

5. Regional Settings
===============
On each machine, open the Windows Control Panel, and choose Regional
Options. See if the settings are different for dates.

More info on misinterpreted field values:
http://allenbrowne.com/ser-45.html

More info on non-US date formats:
http://allenbrowne.com/ser-36.html

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

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

melissas said:
I have the following parameter query which returns data on my pc (XP) and
did
at one time on another (2K) until that one was re-imaged. Now it returns
nothing on that pc. They both are on the same version of Access. Is
there
something wrong with the setup of the second pc?

SELECT qryPatcomSuspensePatch.NewLine, qryPatcomSuspensePatch.NewBatch,
qryPatcomSuspensePatch.AcctNum, qryPatcomSuspensePatch.CDMNum,
qryPatcomSuspensePatch.SVCDate, qryPatcomSuspensePatch.Qty,
qryPatcomSuspensePatch.Price, qryPatcomSuspensePatch.DEPT,
qryPatcomSuspensePatch.Expl, qryPatcomSuspensePatch.Order,
qryChargelog2.patient_name, qryChargelog2.ord_num,
qryChargelog2.charge_num,
qryChargelog2.drug_name, IIf([final_qty]=0,IIf([bill_type]='PCR','- ',' ')
&
[disp_unit_qty] & ' x ' & [bill_unit_qty],' ') AS Disp, qryChargelog2.ID1,
qryChargelog2.bill_timestamp AS Expr1, qryPatcomSuspensePatch.PostDate
FROM qryChargelog2 INNER JOIN qryPatcomSuspensePatch ON
qryChargelog2.Order
= qryPatcomSuspensePatch.Order
WHERE (((qryChargelog2.bill_timestamp) Like [Billed Date: ] & "*"))
ORDER BY qryChargelog2.ID1;
Click to expand...
Click to expand...
 
Back
Top