Access 97 Query returning incorrect Data

[jannecum]

Need help with following Query where User enters a Location Number and Records for that location are returned. Line 1 & line 2 criteria work if the location requested matches the beginning Location or ending location but I can not get line 3 criteria to return records where the requested location is a number between {LOC1) and LOC2 which is calculated as ])<([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)



This is the SQL view of what I have so far.


SELECT DEPT1F.DEPARTMENT, DEPT1F.DeptNo, DEPT1F.LIAISON, RCMGT.ID, RCMGT.TransferNo, RCMGT.ContactPerson, RCMGT.DeptNo, RCMGT.SubDept, RCMGT.BOX1, RCMGT.BOX2, RCMGT.MICRO1, RCMGT.MROLL1, RCMGT.MICRO2, RCMGT.MROLL2, RCMGT.VOLUME, RCMGT.ItemNo, RCMGT.SITE, RCMGT.YEAR1, RCMGT.YEAR2, RCMGT.FORM, RCMGT.ARCH, RCMGT.RYEAR, RCMGT.BOXLABEL, RCMGT.DESCRIBE, RCMGT.RECV, RCMGT.RECV_YR, RCMGT.FILMIN, RCMGT.FILMIN_YR, RCMGT.FILMED, RCMGT.FILMED_YR, RCMGT.RETURN, RCMGT.RETURN_YR, RCMGT.DISPOSE, RCMGT.DISPOSE_YR, RCMGT.COMMENT, RCMGT.INDEXED, RCMGT.INDEX, RCMGT.BOXOUT, RCMGT.SENTTO, RCMGT.DATEOUT, RCMGT.LASTUPDATE, RCMGT.ReturnPriortoDisposal, RCMGT.ScheduleName, RCMGT.DateReceived, RCMGT.STATUS, [RECV]+[FILMIN]-[DISPOSE]-[RETURN] AS STORED, RCMGT.LOC1, [LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1 AS LOC2, [YEAR2]+[RYEAR] AS TARGET
FROM RCMGT LEFT JOIN DEPT1F ON RCMGT.DeptNo = DEPT1F.DeptNo


WHERE (RCMGT.LOC1)=[enter Loc no])

OR([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)=[enter Loc no])

OR ((RCMGT.LOC1)<[enter Loc no]) AND (([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)>[enter Loc no]) AND (([enter Loc no])>[LOC1] And ([enter Loc no])<([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)));

 

[cranebill]

WHERE (RCMGT.LOC1)=[RED]>[/RED][enter Loc no])

OR([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)[RED]<[/RED]=[enter Loc no])

 

[jannecum]

Thanks for the reply. No matter how I put this in it still returns the correct record plus all records where [LOC1] is < [enter Loc no] even though I have the criteria listed that (RCMGT.LOC1)>[enter Loc no]). It is like it is ignoring this piece of criteria ????? I have worked on this for days and can not resolve the problem.
Even rebuilt the query several times.

 

[cranebill]

OOps... use my criteria before but instead of OR, use And

sorry about that.

 

[jannecum]

this is what I have for my third set of criteria but it still brings back too many records. NOTE: I have changed [enter loc no] to [ENTER A LOCATION]


WHERE ((RCMGT.LOC1)>=[ENTER A LOCATION]) AND (([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)<=[ENTER A LOCATION]));

 

[cranebill]

you will not need the third set if you use:

WHERE (RCMGT.LOC1)=[RED]>[/RED][enter Loc no])

[RED]AND[/RED] ([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)[RED]<[/RED]=[enter Loc no])

 

[jannecum]

[ENTER A LOCATION] is greater than [LOC1] AND Less than ([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1) so have entered your one criteria: WHERE (((RCMGT.LOC1)<=[ENTER A LOCATION]) AND (([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)>=[ENTER A LOCATION]));

& it still results in locations being returned where ([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1) is < [ENTER A LOCATION] when it should be greater than [ENTER A LOCATION]


This is what I currently have:

SELECT DEPT1F.DEPARTMENT, DEPT1F.LIAISON, DEPT1F.DeptNo, RCMGT.ID, RCMGT.TransferNo, RCMGT.ContactPerson, RCMGT.DeptNo, RCMGT.SubDept, RCMGT.BOX1, RCMGT.BOX2, RCMGT.MICRO1, RCMGT.MROLL1, RCMGT.MICRO2, RCMGT.MROLL2, RCMGT.VOLUME, RCMGT.ItemNo, [ENTER A LOCATION] AS Expr1, RCMGT.SITE, RCMGT.STATUS, RCMGT.LOC1, [LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1 AS LOC2, RCMGT.YEAR1, RCMGT.YEAR2, RCMGT.FORM, RCMGT.ARCH, RCMGT.RYEAR, RCMGT.BOXLABEL, RCMGT.DESCRIBE, RCMGT.RECV, RCMGT.RECV_YR, RCMGT.FILMIN, RCMGT.FILMIN_YR, RCMGT.FILMED, RCMGT.FILMED_YR, RCMGT.RETURN, RCMGT.RETURN_YR, RCMGT.DISPOSE, RCMGT.DISPOSE_YR, RCMGT.COMMENT, RCMGT.INDEXED, RCMGT.INDEX, RCMGT.BOXOUT, RCMGT.SENTTO, RCMGT.DATEOUT, RCMGT.LASTUPDATE, RCMGT.ReturnPriortoDisposal, RCMGT.ScheduleName, RCMGT.DateReceived, [RECV]+[FILMIN]-[DISPOSE]-[RETURN] AS STORED, [YEAR2]+[RYEAR] AS TARGET
FROM RCMGT LEFT JOIN DEPT1F ON RCMGT.DeptNo = DEPT1F.DeptNo

WHERE (((RCMGT.LOC1)<=[ENTER A LOCATION]) AND (([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)>=[ENTER A LOCATION]));

 

[jannecum]

The records that are returned that are incorrect are all 2-3 digit numbers compared to a four digit number I am looking for???? Is my formating a problem here?

 

[jannecum]

I just realized the problem is with the number of digits in the locations. If I am looking for a 4 digit location it finds the correct location but also all 3 digit locations. If I look for a 2 digit location it returns the correct location plus 1 digit locations. If I look for a 1 digit location it returns only the correct 1 digit location.

 

[cranebill]

is loc1 a number field or a text field... sounds like it may be interpreting some numbers as text so you may want to be sure your dealing with all numbers....

 

[jannecum]

It is a general number field (Double) I have changed the number format with leading zeros so all numbers were the same length but still no change in the records returned....

 

[jannecum]

The fields sort like number fields should so I do not understand why they are having a problem with the number of digits in the criteria section of the query.

 

[jannecum]

Bill,


Thanks for all your help..... This was a real stinker for me and I do queries all the time. For some unknown reason ##$@(*&^ I had to add -0 to the [ENTER A LOCATION] like this:

WHERE (((RCMGT.LOC1)<=[ENTER A LOCATION-0) AND (([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)>=[ENTER A LOCATION]-0));



I have no idea what this accomplished but it made it work........ I am always learning new and weird things when I work with MS Access. Thanks again.

 

[lameid]

Based on your workaround... likely Access is doing something weird with the datatype of your parameter. If you enter yor paramters from the parameter submenu under the query menu and specify the datatype or convert the parmater to the correct datatype using a function like cdbl or cint, it would also likely work.

I almost never explicitly specify parameters myself. You have to use the parmaters collection to specify paramters in vba or build the whole SQL in code.

 

[cranebill]

Sorry couldnt get back sooner, dang meetings... glad you got it to work.

 

[jannecum]

Thank you for all the help to figur this out.
Lameid - even thoough I did get the guery to work with the -0's, I will also try working with the parameters. I also do not usually mess with these.

Thanks Janne

 

[jannecum]

Yes adding the following parameter at the beginning of Query also works. Thanks a million!

Julie C

PARAMETERS [ENTER A LOCATION] IEEEDouble;
SELECT DEPT1F.DEPARTMENT, DEPT1F.DeptNo, DEPT1F.HEAD, DEPT1F.LIAISON, DEPT1F.PHONE, DEPT1F.EXT, DEPT1F.NOTE, DEPT1F.LastUpdate, DEPT1F.UserName, DEPT1F.Password, DEPT1F.FormToOpen, DEPT1F.RecmanAccount, DEPT1F.QuarterlyReport, DEPT1F.SendReportTo, DEPT1F.RecmanAccessDate, DEPT1F.RecmanUsers, DEPT1F.AccessUsed, DEPT1F.IndexName, RCMGT.ID, RCMGT.TransferNo, RCMGT.ContactPerson, RCMGT.DeptNo, RCMGT.SubDept, RCMGT.BOX1, RCMGT.BOX2, RCMGT.MICRO1, RCMGT.MROLL1, RCMGT.MICRO2, RCMGT.MROLL2, RCMGT.VOLUME, RCMGT.ItemNo, RCMGT.LOC1, RCMGT.SITE, RCMGT.YEAR1, RCMGT.YEAR2, RCMGT.FORM, RCMGT.ARCH, RCMGT.RYEAR, RCMGT.BOXLABEL, RCMGT.DESCRIBE, RCMGT.RECV, RCMGT.RECV_YR, RCMGT.FILMIN, RCMGT.FILMIN_YR, RCMGT.FILMED, RCMGT.FILMED_YR, RCMGT.RETURN, RCMGT.RETURN_YR, RCMGT.DISPOSE, RCMGT.DISPOSE_YR, RCMGT.STATUS, RCMGT.COMMENT, RCMGT.INDEXED, RCMGT.INDEX, RCMGT.BOXOUT, RCMGT.SENTTO, RCMGT.DATEOUT, RCMGT.LASTUPDATE, RCMGT.ReturnPriortoDisposal, RCMGT.ScheduleName, RCMGT.DateReceived, [ENTER A LOCATION] AS Expr1, [LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1 AS Expr2, RCMGT.[RECV]+[FILMIN]-[DISPOSE]-[RETURN] AS STORED, RCMGT.[YEAR2]+[RYEAR] AS TARGET
FROM RCMGT LEFT JOIN DEPT1F ON RCMGT.DeptNo = DEPT1F.DeptNo
WHERE (((RCMGT.LOC1)<=[ENTER A LOCATION]) AND (([LOC1]+[RECV]+[FILMIN]-[DISPOSE]-[RETURN]-1)>=[ENTER A LOCATION]));