tempVar not recognized by query

[Bobohhhhhh]

I currently have two tables; Total & Out Proccessed Personnel

I have created a macro in design view which starts by setting a temporary varible via user input. It requests a SSN from the users and saves it under the name SSN.

The macro then executes three queries, using the tempVar as criteria for the WHERE expression.

The three queries are set up to append a record from one table to the other, update the record int he new table, then delete the old record from the original table.

My problem is that it seems the tempVar isnt recognized by any of the macros. When I run it it states you are making changes to 0 records. Below is my code for the three queries:

APPEND MACRO

INSERT INTO [Out Processed Personnel] ( SSAN, Category, Rank, [Last], [First] )
SELECT Total.SSAN, Total.Category, Total.Rank, Total.Last, Total.First
FROM Total
WHERE (((Total.SSAN)=TempVars![SSN]));

UPDATE MACRO

UPDATE [Out Processed Personnel] SET [Out Processed Personnel].[Archived Date] = Date()
WHERE ((([Out Processed Personnel].SSAN)=TempVars![SSN]));

DELETE MACRO

DELETE Total.SSAN, Total.Category, Total.Rank, Total.Last, Total.First
FROM Total
WHERE (((Total.SSAN)=TempVars![SSN]));

 

[DCrake]

For a start using field names such as Total, first, Last, etc is a big no no these are most likely Access reserved words and may lead to all sorts of issues in your application.

 

[boblarson]

I would also make sure that you do a test to see if the TempVar![SSN] is returning anything. Go to the VBA Code area and in the Immediate Window and type:

?TempVars![SSN}

and hit enter. You should see a value in the immediate window.

 

[Bobohhhhhh]

CDrake - I understand that may be an issue, but I don't think it is the issue here.

B0b - Thanks for the quick response, I took your advice and it is returning -1 instead of the full SSN the user inputs. I am following the design view's expresssion template, so I am not sure why it isnt accepting the full string.

 

[boblarson]

B0b - Thanks for the quick response, I took your advice and it is returning -1 instead of the full SSN the user inputs. I am following the design view's expresssion template, so I am not sure why it isnt accepting the full string.

Post how you are creating and populating the TempVar.