What is wrong with this syntax?

  • Thread starter Thread starter What a Ride
  • Start date Start date
W

What a Ride

I have this query with this parameter...on the last name this is inserted
into the criteria field.. like[who do you want?]&"*" or is null...
this works fine except,,that I have two individuals in the data base,, and
their first names are Ryan, and Ray,,,When I run the query,, then usually
Ryan gets all of Rays data,,and Ray isnt even mentioned.....example,,Ryan
has used 24 hours of sick leave, Ray has used 48 hours,,,when I run the
report,, it shows Ryan with 72 hours of sick leave.....Ray is not found in
the report...Please assist...Thanks Bill
 
Lynn, I'll have to wait untill I return to work....tomorrow.
Lynn Trapp said:
Can you post the SQL for the query? It's hard to tell what might be going on
without knowing more about what you are doing and what your data looks like.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp



What a Ride said:
I have this query with this parameter...on the last name this is inserted
into the criteria field.. like[who do you want?]&"*" or is null...
this works fine except,,that I have two individuals in the data base,, and
their first names are Ryan, and Ray,,,When I run the query,, then usually
Ryan gets all of Rays data,,and Ray isnt even mentioned.....example,,Ryan
has used 24 hours of sick leave, Ray has used 48 hours,,,when I run the
report,, it shows Ryan with 72 hours of sick leave.....Ray is not found in
the report...Please assist...Thanks Bill
Click to expand...
Click to expand...
 
PARAMETERS dYEAR Long;

SELECT tPersonnel.LastName, tPersonnel.Fname, tOffDutyHistory.ShiftDate,
tOffDutyHistory.StatusCode, tOffDutyHistory.ShiftID, tOffDutyHistory.Length,
tOffDutyHistory.Note

lynn this is the SQL you requested...Thanks Bill



FROM tPersonnel INNER JOIN (tOffDutyHistory INNER JOIN tStations ON
tOffDutyHistory.StationID = tStations.StationID) ON tPersonnel.ID =
tOffDutyHistory.PersonnelID

WHERE (((tPersonnel.LastName) Like [Who do you want?] & "*" Or
(tPersonnel.LastName) Is Null) AND ((tOffDutyHistory.StatusCode)="s" Or
(tOffDutyHistory.StatusCode)="sf") AND ((tOffDutyHistory.ShiftID)=[What
shift?]) AND ((Year([tOffDutyHistory].[ShiftDate]))=[dYEar]));

What a Ride said:
Lynn, I'll have to wait untill I return to work....tomorrow.
Lynn Trapp said:
Can you post the SQL for the query? It's hard to tell what might be
Click to expand...
going
on
without knowing more about what you are doing and what your data looks like.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp



What a Ride said:
I have this query with this parameter...on the last name this is inserted
into the criteria field.. like[who do you want?]&"*" or is null...
this works fine except,,that I have two individuals in the data base,, and
their first names are Ryan, and Ray,,,When I run the query,, then usually
Ryan gets all of Rays data,,and Ray isnt even mentioned.....example,,Ryan
has used 24 hours of sick leave, Ray has used 48 hours,,,when I run the
report,, it shows Ryan with 72 hours of sick leave.....Ray is not
Click to expand...
Click to expand...
found
in
Click to expand...
Click to expand...
 
Bill,
It's going to be a little tough to tell you what is happening without
knowing your data, but let me make some trouble shooting suggestions.

1. Remove the inside Inner Join -- you will add it back in later, so don't
worry
2. Remove all criteria from the Where clause except for tPersonnel.LastName
Like [Who do you want?]
3. Run the Query.

Does this return the data that you expect?

If so, then start adding the criteria back in one at a time until the data
no longer looks correct. Finally, add your inside Inner Join back in.

Doing this may give you a hint as to where the problem is at. You may need
to try several different combinations of removing criteria and adding them
back in before you find it.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp



What a Ride said:
PARAMETERS dYEAR Long;

SELECT tPersonnel.LastName, tPersonnel.Fname, tOffDutyHistory.ShiftDate,
tOffDutyHistory.StatusCode, tOffDutyHistory.ShiftID, tOffDutyHistory.Length,
tOffDutyHistory.Note

lynn this is the SQL you requested...Thanks Bill



FROM tPersonnel INNER JOIN (tOffDutyHistory INNER JOIN tStations ON
tOffDutyHistory.StationID = tStations.StationID) ON tPersonnel.ID =
tOffDutyHistory.PersonnelID

WHERE (((tPersonnel.LastName) Like [Who do you want?] & "*" Or
(tPersonnel.LastName) Is Null) AND ((tOffDutyHistory.StatusCode)="s" Or
(tOffDutyHistory.StatusCode)="sf") AND ((tOffDutyHistory.ShiftID)=[What
shift?]) AND ((Year([tOffDutyHistory].[ShiftDate]))=[dYEar]));

What a Ride said:
Lynn, I'll have to wait untill I return to work....tomorrow.
Lynn Trapp said:
Can you post the SQL for the query? It's hard to tell what might be
Click to expand...
going
on
without knowing more about what you are doing and what your data looks like.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Security FAQ: http://support.microsoft.com/support/access/content/secfaq.asp



I have this query with this parameter...on the last name this is inserted
into the criteria field.. like[who do you want?]&"*" or is null...
this works fine except,,that I have two individuals in the data
Click to expand...
base,,
and
their first names are Ryan, and Ray,,,When I run the query,, then usually
Ryan gets all of Rays data,,and Ray isnt even mentioned.....example,,Ryan
has used 24 hours of sick leave, Ray has used 48 hours,,,when I run the
report,, it shows Ryan with 72 hours of sick leave.....Ray is not
Click to expand...
found
in
the report...Please assist...Thanks Bill
Click to expand...
Click to expand...
Click to expand...
 
Back
Top