Error from Delete query with inner join

[Rick Beach]

Error From Delete Query with Inner Join

I cannot seem to attach a txt file or a zip file. I receive an error stating a security token was missing.

[Rabbit]

Try logging out and then logging back in.

[Rick Beach]

Error from Delete Query with Inner Join

I have logged off and back on. I even closed the browser and reopened. I still receive the same error stating "Your submission could not be processed because a security token was missing".

[Rabbit]

Alright, we'll have to make do with descriptions.

Can you give us your relevant table definitions, i.e. column names and column types. And your relevant query definitions.

[Rick Beach]

Below are the two tables used for the queries:

Equipment
EquipJ-tag# Text
StatusID Number
CabJ-tag# Text
EquipLoc Text
Manufacturer Text
Model Text
SerNum Text
NumCords Number
Floor Text
Notes Text
Updated Date/Time
PlanID Number
HLAmps Number

EquipmentReques ted
RequestedBy Text
WorkPhone Text
MocRoc Text
HPCostCtr Text
WorkType Text
Building Text
Location Number
CHSRLOC Number
PanelID Text
SINumber Text
RIDNumber Text
CHSRNumber Number
CHSRLetter Text
Model Number
Manufacturer Number
Customer Text
AssocSystem Text
ProjectName Text
InitiatedDate Date/Time
RequestedStartD ate Date/Time
RequestedComple teDate Date/Time
ActualCompleteD ate Date/Time
ConnectorType Text
NumberOfCords Number
BreakerAmps Number
NumberOfPoles Number
Voltage Number
Notes Text
Notes2 Text
Pre-InstallNotes Text
Status Text
CompletedBy Text
OrigCHSRNumber Number
Overtime Yes/No
AuditDate Date/Time
AuditComplete Yes/No
InstallRDCLoc Text
Phase Text
UPS Text
BreakerType Text
HLAmps Number

Below is the query to select the cancelled EquipmentReques ted records:

SELECT "CHSR" & [EquipmentReques ted].[CHSRNumber] & "-" & [EquipmentReques ted].[InitiatedDate] AS JTag
FROM EquipmentReques ted
WHERE (((EquipmentReq uested.Status)= "CANC"));

Below is the query to delete the records from the EquipmentTable:

DELETE Equipment.*
FROM CHSRRequestInPr ogressB INNER JOIN Equipment ON CHSRRequestInPr ogressB.JTag = Equipment.[EquipJ-tag#];

Below is a sample of an EquipmentReques ted record in a comma delimited format:

RequestedBy,Wor kPhone,MocRoc,H PCostCtr,WorkTy pe,Building,Loc ation,CHSRLOC,P anelID,SINumber ,RIDNumber,CHSR Number,CHSRLett er,Model,Manufa cturer,Customer ,AssocSystem,Pr ojectName,Initi atedDate,Reques tedStartDate,Re questedComplete Date,ActualComp leteDate,Connec torType,NumberO fCords,BreakerA mps,NumberOfPol es,Voltage,Note s,Notes2,Pre-InstallNotes,St atus,CompletedB y,OrigCHSRNumbe r,Overtime,Audi tDate,AuditComp lete,InstallRDC Loc,Phase,UPS,B reakerType,HLAm ps
Glenn Goodale,939-5662,,US7045895 ,Electric Install,C2,1954 2,19542,4233,,, 45949,Z,1104,12 8,,SABRE,,1/3/2011,12/30/2010,1/14/2011,1/5/2011,H2720,,30, 3,208,30A/250V 3 PHASE UPS 6,,,CANC,Unk,,F ALSE,,FALSE,C2. M.46,ABC,6,GE,6

Below is a sample of an Equipment record in a comma delimited format:

EquipJ-tag#,StatusID,C abJ-tag#,EquipLoc,M anufacturer,Mod el,SerNum,NumCo rds,Floor,Notes ,Updated,PlanID ,HLAmps,s_GUID, s_ColLineage,s_ Generation,s_Li neage
CHSR45948-1/4/2011,9,,C2.J41. 2,HP/COMPAQ,C7000,,0 ,C2,,1/4/2011,,6,,,17791,

Please let me know if I can supply any other information that would help.

[Rick Beach]

On the above sample of an Equipment Record, change the CHSR45948-1/4/2011 to CHSR45949-1/5/2011 so both records will match. I accidentally send one record off from the Equipment table.

[Rick Beach]

I made an error in the date for the above Equipment record, it should read CHSR45949-1/3/2011. I think I need to pay closer attention.

Thanks

[Rabbit]

Your EquipJ-tag# is CHSR45949-1/3/2011 but when you rebuild your JTag using "CHSR" & [EquipmentReques ted].[CHSRNumber] & "-" & [EquipmentReques ted].[InitiatedDate], you're going to get CHSR45949-01/03/2011 because the date is returned in the format 01/03/2011 and not 1/3/2011 and so they don't match.

[Rick Beach]

I have the query result on the screen and it is:

CHSR45949-1/3/2011

This was copied from the query. There are no leading zero's in the date format.

[Rick Beach]

Also note that if the delete query is run in Datasheet view, the record is found and listed.

[Rabbit]

What version of Access are you using? Mine shows leading zeroes.

[Rick Beach]

I forced the date to be 01032011 in the query and changed the table to reflect CHSR45949-01032011 and I still am not able to delete the record. The record is still not updateable. I will change this back to the original format of CHSR45949-1/3/2011.

[Rick Beach]

I have Microsoft Access 2010.

[Rabbit]

Are you sure this doesn't work?

DELETE *
FROM Equipment
WHERE (((Equipment.[EquipJ-tag#]) In (SELECT JTag FROM CHSRRequestInProgressB)));

Because it works when I try it.

[Rick Beach]

The query listed above is different than any others that were sent. This one works. I really appreciate all of your time and assistance.