No result in query when run in 2002 as opposed to 2000?

[Guest]

Folks,

I have a problem whereby I can run the following query in Access 2000 and
get results, but when users using 2002 run the same query - they get no
results in the query. I have narrowed down the problem to the data range
identifier in the query apparently not being recognised.

I have another query that works fine in both versions - it contains all the
same tables and criteria, except it doesn't have the additional outer join to
the T_SiteVisits table.

SQL is as follows:

SELECT T_Sites.[Branch Name], T_Sites.SiteBSB, T_Sites.[CommSee PoP Id],
T_Sites.PoPSiteComments, T_Sites.[No of workstations/Laptops] AS [No of Ports
Required], T_Sites.[Ports Assigned], T_Sites.EthPortsPatched AS [Ethernet
Ports Patched], T_Sites.TokenRingPortsRemoved AS [Token Ring Ports Removed],
T_IPAddressing.CommseeServName AS Server, T_IPAddressing.CommSeeSwitchPort AS
[Prim Port], T_IPAddressing.CommseeSwitchILO AS [Prim ILO],
T_SiteVisits.Visit, T_SiteVisits.Date, T_SiteVisits.Arrive,
T_SiteVisits.[Start Patch], T_SiteVisits.[Finish Patch],
T_SiteVisits.Signoff, T_SiteVisits.Comments
FROM (T_RouterID INNER JOIN T_Sites ON T_RouterID.[Router ID] =
T_Sites.[Router ID]) LEFT JOIN (T_IPAddressing LEFT JOIN T_SiteVisits ON
T_IPAddressing.[Project Point of Presence Id] = T_SiteVisits.[POP ID]) ON
T_Sites.[CommSee PoP Id] = T_IPAddressing.[Project Point of Presence Id]
WHERE (((T_SiteVisits.Visit)="3 Comm Cutover" Or (T_SiteVisits.Visit)="5
Call Back") AND ((T_Sites.CoreActivity)="c" Or (T_Sites.CoreActivity)="h" Or
(T_Sites.CoreActivity)="b" Or (T_Sites.CoreActivity)="m") AND
((T_Sites.PoPCutover) Between [forms]![F_GeneralReporting].[txtStart] And
[forms]![F_GeneralReporting].[txtend]))
ORDER BY T_Sites.[Branch Name];

Your help would be appreciated.

Paul

 

[Allen Browne]

The criteria from T_Sites.PoPCutover is read from a form.
It may be that Access is not interpreting it correctly.

If you open the T_Sites table in design view, what data type is PoPCutover?
If Number (size Long Integer), then try this:
1. Open your query in design view.

2. Choose Parameters on the Query menu.
Access opens the Parameters dialog.

3. In the dialog, enter 2 rows:
[forms]![F_GeneralReporting].[txtStart] Long
[forms]![F_GeneralReporting].[txtend] Long

4. Save the query. Close.

5. Open the F_GeneralReporting form in design view.

6. If txtStart and txtEnd are unbound, set their Format property to:
General Number
so Access knows they are numbers. Save the form. Close.

If PoPCutover is a date, declare the paremeters as Date/Time at step 3, and
set the format to General Date at step 6.

It would also be good to check that your computers are using the same
version of JET 4. Locate msjet40.dll (typically in windows\system32), and
examine the file's Properties. On the Version tab, you should see:
4.0.8xxx.0
where the 8 tells you that you have SP8 for JET 4. If not, download it from:
http://support.microsoft.com/gp/sp

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

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

Folks,

I have a problem whereby I can run the following query in Access 2000 and
get results, but when users using 2002 run the same query - they get no
results in the query. I have narrowed down the problem to the data range
identifier in the query apparently not being recognised.

I have another query that works fine in both versions - it contains all
the
same tables and criteria, except it doesn't have the additional outer join
to
the T_SiteVisits table.

SQL is as follows:

SELECT T_Sites.[Branch Name], T_Sites.SiteBSB, T_Sites.[CommSee PoP Id],
T_Sites.PoPSiteComments, T_Sites.[No of workstations/Laptops] AS [No of
Ports
Required], T_Sites.[Ports Assigned], T_Sites.EthPortsPatched AS [Ethernet
Ports Patched], T_Sites.TokenRingPortsRemoved AS [Token Ring Ports
Removed],
T_IPAddressing.CommseeServName AS Server, T_IPAddressing.CommSeeSwitchPort
AS
[Prim Port], T_IPAddressing.CommseeSwitchILO AS [Prim ILO],
T_SiteVisits.Visit, T_SiteVisits.Date, T_SiteVisits.Arrive,
T_SiteVisits.[Start Patch], T_SiteVisits.[Finish Patch],
T_SiteVisits.Signoff, T_SiteVisits.Comments
FROM (T_RouterID INNER JOIN T_Sites ON T_RouterID.[Router ID] =
T_Sites.[Router ID]) LEFT JOIN (T_IPAddressing LEFT JOIN T_SiteVisits ON
T_IPAddressing.[Project Point of Presence Id] = T_SiteVisits.[POP ID]) ON
T_Sites.[CommSee PoP Id] = T_IPAddressing.[Project Point of Presence Id]
WHERE (((T_SiteVisits.Visit)="3 Comm Cutover" Or (T_SiteVisits.Visit)="5
Call Back") AND ((T_Sites.CoreActivity)="c" Or (T_Sites.CoreActivity)="h"
Or
(T_Sites.CoreActivity)="b" Or (T_Sites.CoreActivity)="m") AND
((T_Sites.PoPCutover) Between [forms]![F_GeneralReporting].[txtStart] And
[forms]![F_GeneralReporting].[txtend]))
ORDER BY T_Sites.[Branch Name];

Your help would be appreciated.

Paul

 

[John Spencer (MVP)]

One other thing, should the separator be a ! or a period? I've always used the
! for control references and the period for fields.

The criteria from T_Sites.PoPCutover is read from a form.
It may be that Access is not interpreting it correctly.

If you open the T_Sites table in design view, what data type is PoPCutover?
If Number (size Long Integer), then try this:
1. Open your query in design view.

2. Choose Parameters on the Query menu.
Access opens the Parameters dialog.

3. In the dialog, enter 2 rows:
[forms]![F_GeneralReporting].[txtStart] Long
[forms]![F_GeneralReporting].[txtend] Long

4. Save the query. Close.

5. Open the F_GeneralReporting form in design view.

6. If txtStart and txtEnd are unbound, set their Format property to:
General Number
so Access knows they are numbers. Save the form. Close.

If PoPCutover is a date, declare the paremeters as Date/Time at step 3, and
set the format to General Date at step 6.

It would also be good to check that your computers are using the same
version of JET 4. Locate msjet40.dll (typically in windows\system32), and
examine the file's Properties. On the Version tab, you should see:
4.0.8xxx.0
where the 8 tells you that you have SP8 for JET 4. If not, download it from:
http://support.microsoft.com/gp/sp

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

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

Folks,

I have a problem whereby I can run the following query in Access 2000 and
get results, but when users using 2002 run the same query - they get no
results in the query. I have narrowed down the problem to the data range
identifier in the query apparently not being recognised.

I have another query that works fine in both versions - it contains all
the
same tables and criteria, except it doesn't have the additional outer join
to
the T_SiteVisits table.

SQL is as follows:

SELECT T_Sites.[Branch Name], T_Sites.SiteBSB, T_Sites.[CommSee PoP Id],
T_Sites.PoPSiteComments, T_Sites.[No of workstations/Laptops] AS [No of
Ports
Required], T_Sites.[Ports Assigned], T_Sites.EthPortsPatched AS [Ethernet
Ports Patched], T_Sites.TokenRingPortsRemoved AS [Token Ring Ports
Removed],
T_IPAddressing.CommseeServName AS Server, T_IPAddressing.CommSeeSwitchPort
AS
[Prim Port], T_IPAddressing.CommseeSwitchILO AS [Prim ILO],
T_SiteVisits.Visit, T_SiteVisits.Date, T_SiteVisits.Arrive,
T_SiteVisits.[Start Patch], T_SiteVisits.[Finish Patch],
T_SiteVisits.Signoff, T_SiteVisits.Comments
FROM (T_RouterID INNER JOIN T_Sites ON T_RouterID.[Router ID] =
T_Sites.[Router ID]) LEFT JOIN (T_IPAddressing LEFT JOIN T_SiteVisits ON
T_IPAddressing.[Project Point of Presence Id] = T_SiteVisits.[POP ID]) ON
T_Sites.[CommSee PoP Id] = T_IPAddressing.[Project Point of Presence Id]
WHERE (((T_SiteVisits.Visit)="3 Comm Cutover" Or (T_SiteVisits.Visit)="5
Call Back") AND ((T_Sites.CoreActivity)="c" Or (T_Sites.CoreActivity)="h"
Or
(T_Sites.CoreActivity)="b" Or (T_Sites.CoreActivity)="m") AND
((T_Sites.PoPCutover) Between [forms]![F_GeneralReporting].[txtStart] And
[forms]![F_GeneralReporting].[txtend]))
ORDER BY T_Sites.[Branch Name];

Your help would be appreciated.

Paul

 

[Guest]

Hey Guy's,

Thanks for your responses. I am still having no luck. It is really
frustrating as it works fine on my Access 2000??

My MSJet.dll = 4.0.8015.0
Their MSjet.dll = 4.08618.0

According to your email, I should only be interested in the Version 8 part,
so I haven't as yet asked them to download the suggested Jet - is that worth
pursuing?

Any other suggestions would be well appreciated!

Many Thanks,

Paul

One other thing, should the separator be a ! or a period? I've always used the
! for control references and the period for fields.

The criteria from T_Sites.PoPCutover is read from a form.
It may be that Access is not interpreting it correctly.

If you open the T_Sites table in design view, what data type is PoPCutover?
If Number (size Long Integer), then try this:
1. Open your query in design view.

2. Choose Parameters on the Query menu.
Access opens the Parameters dialog.

3. In the dialog, enter 2 rows:
[forms]![F_GeneralReporting].[txtStart] Long
[forms]![F_GeneralReporting].[txtend] Long

4. Save the query. Close.

5. Open the F_GeneralReporting form in design view.

6. If txtStart and txtEnd are unbound, set their Format property to:
General Number
so Access knows they are numbers. Save the form. Close.

If PoPCutover is a date, declare the paremeters as Date/Time at step 3, and
set the format to General Date at step 6.

It would also be good to check that your computers are using the same
version of JET 4. Locate msjet40.dll (typically in windows\system32), and
examine the file's Properties. On the Version tab, you should see:
4.0.8xxx.0
where the 8 tells you that you have SP8 for JET 4. If not, download it from:
http://support.microsoft.com/gp/sp

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

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

Folks,

I have a problem whereby I can run the following query in Access 2000 and
get results, but when users using 2002 run the same query - they get no
results in the query. I have narrowed down the problem to the data range
identifier in the query apparently not being recognised.

I have another query that works fine in both versions - it contains all
the
same tables and criteria, except it doesn't have the additional outer join
to
the T_SiteVisits table.

SQL is as follows:

SELECT T_Sites.[Branch Name], T_Sites.SiteBSB, T_Sites.[CommSee PoP Id],
T_Sites.PoPSiteComments, T_Sites.[No of workstations/Laptops] AS [No of
Ports
Required], T_Sites.[Ports Assigned], T_Sites.EthPortsPatched AS [Ethernet
Ports Patched], T_Sites.TokenRingPortsRemoved AS [Token Ring Ports
Removed],
T_IPAddressing.CommseeServName AS Server, T_IPAddressing.CommSeeSwitchPort
AS
[Prim Port], T_IPAddressing.CommseeSwitchILO AS [Prim ILO],
T_SiteVisits.Visit, T_SiteVisits.Date, T_SiteVisits.Arrive,
T_SiteVisits.[Start Patch], T_SiteVisits.[Finish Patch],
T_SiteVisits.Signoff, T_SiteVisits.Comments
FROM (T_RouterID INNER JOIN T_Sites ON T_RouterID.[Router ID] =
T_Sites.[Router ID]) LEFT JOIN (T_IPAddressing LEFT JOIN T_SiteVisits ON
T_IPAddressing.[Project Point of Presence Id] = T_SiteVisits.[POP ID]) ON
T_Sites.[CommSee PoP Id] = T_IPAddressing.[Project Point of Presence Id]
WHERE (((T_SiteVisits.Visit)="3 Comm Cutover" Or (T_SiteVisits.Visit)="5
Call Back") AND ((T_Sites.CoreActivity)="c" Or (T_Sites.CoreActivity)="h"
Or
(T_Sites.CoreActivity)="b" Or (T_Sites.CoreActivity)="m") AND
((T_Sites.PoPCutover) Between [forms]![F_GeneralReporting].[txtStart] And
[forms]![F_GeneralReporting].[txtend]))
ORDER BY T_Sites.[Branch Name];

Your help would be appreciated.

Paul

 

[Allen Browne]

The JET version is fine.

Did you declare the 2 parameters?
Did you set the Format of the text boxes?

What data type is the PoPCutover field?

Since you have criteria on a field from TSites, it will probably behave like
an INNER JOIN anyway.

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

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

Hey Guy's,

Thanks for your responses. I am still having no luck. It is really
frustrating as it works fine on my Access 2000??

My MSJet.dll = 4.0.8015.0
Their MSjet.dll = 4.08618.0

According to your email, I should only be interested in the Version 8
part,
so I haven't as yet asked them to download the suggested Jet - is that
worth
pursuing?

Any other suggestions would be well appreciated!

Many Thanks,

Paul

One other thing, should the separator be a ! or a period? I've always
used the
! for control references and the period for fields.

The criteria from T_Sites.PoPCutover is read from a form.
It may be that Access is not interpreting it correctly.

If you open the T_Sites table in design view, what data type is
PoPCutover?
If Number (size Long Integer), then try this:
1. Open your query in design view.

2. Choose Parameters on the Query menu.
Access opens the Parameters dialog.

3. In the dialog, enter 2 rows:
[forms]![F_GeneralReporting].[txtStart] Long
[forms]![F_GeneralReporting].[txtend] Long

4. Save the query. Close.

5. Open the F_GeneralReporting form in design view.

6. If txtStart and txtEnd are unbound, set their Format property to:
General Number
so Access knows they are numbers. Save the form. Close.

If PoPCutover is a date, declare the paremeters as Date/Time at step 3,
and
set the format to General Date at step 6.

It would also be good to check that your computers are using the same
version of JET 4. Locate msjet40.dll (typically in windows\system32),
and
examine the file's Properties. On the Version tab, you should see:
4.0.8xxx.0
where the 8 tells you that you have SP8 for JET 4. If not, download it
from:
http://support.microsoft.com/gp/sp

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

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

Folks,

I have a problem whereby I can run the following query in Access 2000
and
get results, but when users using 2002 run the same query - they get
no
results in the query. I have narrowed down the problem to the data
range
identifier in the query apparently not being recognised.

I have another query that works fine in both versions - it contains
all
the
same tables and criteria, except it doesn't have the additional outer
join
to
the T_SiteVisits table.

SQL is as follows:

SELECT T_Sites.[Branch Name],
T_Sites.SiteBSB,
T_Sites.[CommSee PoP Id],
T_Sites.PoPSiteComments,
T_Sites.[No of workstations/Laptops] AS [No of Ports Required],
T_Sites.[Ports Assigned],
T_Sites.EthPortsPatched AS [Ethernet Ports Patched],
T_Sites.TokenRingPortsRemoved AS [Token Ring Ports Removed],
T_IPAddressing.CommseeServName AS Server,
T_IPAddressing.CommSeeSwitchPort AS [Prim Port],
T_IPAddressing.CommseeSwitchILO AS [Prim ILO],
T_SiteVisits.Visit,
T_SiteVisits.Date,
T_SiteVisits.Arrive,
T_SiteVisits.[Start Patch],
T_SiteVisits.[Finish Patch],
T_SiteVisits.Signoff,
T_SiteVisits.Comments
FROM (T_RouterID INNER JOIN T_Sites
ON T_RouterID.[Router ID] = T_Sites.[Router ID])
LEFT JOIN (T_IPAddressing LEFT JOIN T_SiteVisits
ON T_IPAddressing.[Project Point of Presence Id] = T_SiteVisits.[POP ID])
ON T_Sites.[CommSee PoP Id] = T_IPAddressing.[Project Point of Presence Id]
WHERE ((T_SiteVisits.Visit = "3 Comm Cutover"
Or T_SiteVisits.Visit = "5 Call Back")
AND (T_Sites.CoreActivity = "c"
Or T_Sites.CoreActivity = "h"
Or T_Sites.CoreActivity = "b"
Or T_Sites.CoreActivity = "m")
AND (T_Sites.PoPCutover Between [forms]![F_GeneralReporting].[txtStart]
And [forms]![F_GeneralReporting].[txtend]))
ORDER BY T_Sites.[Branch Name];

 

[Guest]

Hey Allen,

I did indeed declare the 2 parameters and format the text boxes - no joy.
The PoPcutover field is a Date type.

Any other ideas?

pb

The JET version is fine.

Did you declare the 2 parameters?
Did you set the Format of the text boxes?

What data type is the PoPCutover field?

Since you have criteria on a field from TSites, it will probably behave like
an INNER JOIN anyway.

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

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

Hey Guy's,

Thanks for your responses. I am still having no luck. It is really
frustrating as it works fine on my Access 2000??

My MSJet.dll = 4.0.8015.0
Their MSjet.dll = 4.08618.0

According to your email, I should only be interested in the Version 8
part,
so I haven't as yet asked them to download the suggested Jet - is that
worth
pursuing?

Any other suggestions would be well appreciated!

Many Thanks,

Paul

One other thing, should the separator be a ! or a period? I've always
used the
! for control references and the period for fields.

Allen Browne wrote:

The criteria from T_Sites.PoPCutover is read from a form.
It may be that Access is not interpreting it correctly.

If you open the T_Sites table in design view, what data type is
PoPCutover?
If Number (size Long Integer), then try this:
1. Open your query in design view.

2. Choose Parameters on the Query menu.
Access opens the Parameters dialog.

3. In the dialog, enter 2 rows:
[forms]![F_GeneralReporting].[txtStart] Long
[forms]![F_GeneralReporting].[txtend] Long

4. Save the query. Close.

5. Open the F_GeneralReporting form in design view.

6. If txtStart and txtEnd are unbound, set their Format property to:
General Number
so Access knows they are numbers. Save the form. Close.

If PoPCutover is a date, declare the paremeters as Date/Time at step 3,
and
set the format to General Date at step 6.

It would also be good to check that your computers are using the same
version of JET 4. Locate msjet40.dll (typically in windows\system32),
and
examine the file's Properties. On the Version tab, you should see:
4.0.8xxx.0
where the 8 tells you that you have SP8 for JET 4. If not, download it
from:
http://support.microsoft.com/gp/sp

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

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

Folks,

I have a problem whereby I can run the following query in Access 2000
and
get results, but when users using 2002 run the same query - they get
no
results in the query. I have narrowed down the problem to the data
range
identifier in the query apparently not being recognised.

I have another query that works fine in both versions - it contains
all
the
same tables and criteria, except it doesn't have the additional outer
join
to
the T_SiteVisits table.

SQL is as follows:

SELECT T_Sites.[Branch Name],
T_Sites.SiteBSB,
T_Sites.[CommSee PoP Id],
T_Sites.PoPSiteComments,
T_Sites.[No of workstations/Laptops] AS [No of Ports Required],
T_Sites.[Ports Assigned],
T_Sites.EthPortsPatched AS [Ethernet Ports Patched],
T_Sites.TokenRingPortsRemoved AS [Token Ring Ports Removed],
T_IPAddressing.CommseeServName AS Server,
T_IPAddressing.CommSeeSwitchPort AS [Prim Port],
T_IPAddressing.CommseeSwitchILO AS [Prim ILO],
T_SiteVisits.Visit,
T_SiteVisits.Date,
T_SiteVisits.Arrive,
T_SiteVisits.[Start Patch],
T_SiteVisits.[Finish Patch],
T_SiteVisits.Signoff,
T_SiteVisits.Comments
FROM (T_RouterID INNER JOIN T_Sites
ON T_RouterID.[Router ID] = T_Sites.[Router ID])
LEFT JOIN (T_IPAddressing LEFT JOIN T_SiteVisits
ON T_IPAddressing.[Project Point of Presence Id] = T_SiteVisits.[POP ID])
ON T_Sites.[CommSee PoP Id] = T_IPAddressing.[Project Point of Presence Id]
WHERE ((T_SiteVisits.Visit = "3 Comm Cutover"
Or T_SiteVisits.Visit = "5 Call Back")
AND (T_Sites.CoreActivity = "c"
Or T_Sites.CoreActivity = "h"
Or T_Sites.CoreActivity = "b"
Or T_Sites.CoreActivity = "m")
AND (T_Sites.PoPCutover Between [forms]![F_GeneralReporting].[txtStart]
And [forms]![F_GeneralReporting].[txtend]))
ORDER BY T_Sites.[Branch Name];

 

[Guest]

Here is another query that is similar, but without the left join to the
T_SiteVisits table. This works fine in both versions of Access. Thought this
might help eliminate some things?

SELECT T_Sites.[Branch Name] AS [Site Name], T_IPAddressing.[Router Name],
T_IPAddressing.CommseeUserSwitch, T_IPAddressing.CommSeeSwitch,
T_Sites.SiteBSB AS BSB, T_Sites.[CommSee PoP Id] AS [PoP ID],
T_RouterID.CommSeeOrderedAccess AS Access, T_Sites.PoPSiteComments AS
Engineer, T_Sites.[No of workstations/Laptops] AS [No Ports Require],
T_Sites.[Ports Assigned], T_IPAddressing.CommseeServName AS Server,
T_IPAddressing.CommSeeSwitchPort AS [Prim Port],
T_IPAddressing.CommseeSwitchILO AS [ILO Port], "" AS [Arrival Time], "" AS
[Start Patch], "" AS [Finish Patch], "" AS [Sign-off], "" AS [Issues/Comments]
FROM (T_RouterID INNER JOIN T_Sites ON T_RouterID.[Router ID] =
T_Sites.[Router ID]) LEFT JOIN T_IPAddressing ON T_Sites.[CommSee PoP Id] =
T_IPAddressing.[Project Point of Presence Id]
GROUP BY T_Sites.[Branch Name], T_IPAddressing.[Router Name],
T_IPAddressing.CommseeUserSwitch, T_IPAddressing.CommSeeSwitch,
T_Sites.SiteBSB, T_Sites.[CommSee PoP Id], T_RouterID.CommSeeOrderedAccess,
T_Sites.PoPSiteComments, T_Sites.[No of workstations/Laptops], T_Sites.[Ports
Assigned], T_IPAddressing.CommseeServName, T_IPAddressing.CommSeeSwitchPort,
T_IPAddressing.CommseeSwitchILO, "", "", "", "", "", T_Sites.CoreActivity,
T_Sites.PoPCutover
HAVING (((T_Sites.CoreActivity)="c" Or (T_Sites.CoreActivity)="h" Or
(T_Sites.CoreActivity)="b" Or (T_Sites.CoreActivity)="m") AND
((T_Sites.PoPCutover) Between [forms]![F_GeneralReporting].[txtStart] And
[forms]![F_GeneralReporting].[txtend]));


pb

Folks,

I have a problem whereby I can run the following query in Access 2000 and
get results, but when users using 2002 run the same query - they get no
results in the query. I have narrowed down the problem to the data range
identifier in the query apparently not being recognised.

I have another query that works fine in both versions - it contains all the
same tables and criteria, except it doesn't have the additional outer join to
the T_SiteVisits table.

SQL is as follows:

SELECT T_Sites.[Branch Name], T_Sites.SiteBSB, T_Sites.[CommSee PoP Id],
T_Sites.PoPSiteComments, T_Sites.[No of workstations/Laptops] AS [No of Ports
Required], T_Sites.[Ports Assigned], T_Sites.EthPortsPatched AS [Ethernet
Ports Patched], T_Sites.TokenRingPortsRemoved AS [Token Ring Ports Removed],
T_IPAddressing.CommseeServName AS Server, T_IPAddressing.CommSeeSwitchPort AS
[Prim Port], T_IPAddressing.CommseeSwitchILO AS [Prim ILO],
T_SiteVisits.Visit, T_SiteVisits.Date, T_SiteVisits.Arrive,
T_SiteVisits.[Start Patch], T_SiteVisits.[Finish Patch],
T_SiteVisits.Signoff, T_SiteVisits.Comments
FROM (T_RouterID INNER JOIN T_Sites ON T_RouterID.[Router ID] =
T_Sites.[Router ID]) LEFT JOIN (T_IPAddressing LEFT JOIN T_SiteVisits ON
T_IPAddressing.[Project Point of Presence Id] = T_SiteVisits.[POP ID]) ON
T_Sites.[CommSee PoP Id] = T_IPAddressing.[Project Point of Presence Id]
WHERE (((T_SiteVisits.Visit)="3 Comm Cutover" Or (T_SiteVisits.Visit)="5
Call Back") AND ((T_Sites.CoreActivity)="c" Or (T_Sites.CoreActivity)="h" Or
(T_Sites.CoreActivity)="b" Or (T_Sites.CoreActivity)="m") AND
((T_Sites.PoPCutover) Between [forms]![F_GeneralReporting].[txtStart] And
[forms]![F_GeneralReporting].[txtend]))
ORDER BY T_Sites.[Branch Name];

Your help would be appreciated.

Paul

 

[Allen Browne]

Okay, you presumably you declared the paramters as Date/Time:
[forms]![F_GeneralReporting].[txtStart] Date/Time
[forms]![F_GeneralReporting].[txtend] Date/Time

If the 2 text boxes are unbound, set the Format property to Short Date. This
helps Access understand the data type, ensures consistency, and prevents the
user from entering bad dates.

These steps should help prevent issues related to the different users having
different Regional Settings in the Windows Control Panel for their dates.
For more info on that, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

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

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

I did indeed declare the 2 parameters and format the text boxes - no joy.
The PoPcutover field is a Date type.

Any other ideas?

pb

The JET version is fine.

Did you declare the 2 parameters?
Did you set the Format of the text boxes?

What data type is the PoPCutover field?

Since you have criteria on a field from TSites, it will probably behave
like
an INNER JOIN anyway.

Hey Guy's,

Thanks for your responses. I am still having no luck. It is really
frustrating as it works fine on my Access 2000??

My MSJet.dll = 4.0.8015.0
Their MSjet.dll = 4.08618.0

According to your email, I should only be interested in the Version 8
part,
so I haven't as yet asked them to download the suggested Jet - is that
worth
pursuing?

Any other suggestions would be well appreciated!

Many Thanks,

Paul
:

One other thing, should the separator be a ! or a period? I've always
used the
! for control references and the period for fields.

Allen Browne wrote:

The criteria from T_Sites.PoPCutover is read from a form.
It may be that Access is not interpreting it correctly.

If you open the T_Sites table in design view, what data type is
PoPCutover?
If Number (size Long Integer), then try this:
1. Open your query in design view.

2. Choose Parameters on the Query menu.
Access opens the Parameters dialog.

3. In the dialog, enter 2 rows:
[forms]![F_GeneralReporting].[txtStart] Long
[forms]![F_GeneralReporting].[txtend] Long

4. Save the query. Close.

5. Open the F_GeneralReporting form in design view.

6. If txtStart and txtEnd are unbound, set their Format property to:
General Number
so Access knows they are numbers. Save the form. Close.

If PoPCutover is a date, declare the paremeters as Date/Time at step
3,
and
set the format to General Date at step 6.

It would also be good to check that your computers are using the
same
version of JET 4. Locate msjet40.dll (typically in
windows\system32),
and
examine the file's Properties. On the Version tab, you should see:
4.0.8xxx.0
where the 8 tells you that you have SP8 for JET 4. If not, download
it
from:
http://support.microsoft.com/gp/sp

Folks,

I have a problem whereby I can run the following query in Access
2000
and
get results, but when users using 2002 run the same query - they
get
no
results in the query. I have narrowed down the problem to the data
range
identifier in the query apparently not being recognised.

I have another query that works fine in both versions - it
contains
all
the
same tables and criteria, except it doesn't have the additional
outer
join
to
the T_SiteVisits table.

SQL is as follows:

SELECT T_Sites.[Branch Name],
T_Sites.SiteBSB,
T_Sites.[CommSee PoP Id],
T_Sites.PoPSiteComments,
T_Sites.[No of workstations/Laptops] AS [No of Ports Required],
T_Sites.[Ports Assigned],
T_Sites.EthPortsPatched AS [Ethernet Ports Patched],
T_Sites.TokenRingPortsRemoved AS [Token Ring Ports Removed],
T_IPAddressing.CommseeServName AS Server,
T_IPAddressing.CommSeeSwitchPort AS [Prim Port],
T_IPAddressing.CommseeSwitchILO AS [Prim ILO],
T_SiteVisits.Visit,
T_SiteVisits.Date,
T_SiteVisits.Arrive,
T_SiteVisits.[Start Patch],
T_SiteVisits.[Finish Patch],
T_SiteVisits.Signoff,
T_SiteVisits.Comments
FROM (T_RouterID INNER JOIN T_Sites
ON T_RouterID.[Router ID] = T_Sites.[Router ID])
LEFT JOIN (T_IPAddressing LEFT JOIN T_SiteVisits
ON T_IPAddressing.[Project Point of Presence Id] = T_SiteVisits.[POP
ID])
ON T_Sites.[CommSee PoP Id] = T_IPAddressing.[Project Point of Presence
Id]
WHERE ((T_SiteVisits.Visit = "3 Comm Cutover"
Or T_SiteVisits.Visit = "5 Call Back")
AND (T_Sites.CoreActivity = "c"
Or T_Sites.CoreActivity = "h"
Or T_Sites.CoreActivity = "b"
Or T_Sites.CoreActivity = "m")
AND (T_Sites.PoPCutover Between [forms]![F_GeneralReporting].[txtStart]
And [forms]![F_GeneralReporting].[txtend]))
ORDER BY T_Sites.[Branch Name];