SQL view of messed up action queries

[Kendra]

I really appreciate your kindness in helping me out. Here
is the SQL for the action queries (if it clarifies things):

SQL of append query (AcceptoAppend):
INSERT INTO tbl_New_Report_DB ( Record_ID, [Date],
Jurisdiction, [Zone], LU_Type, Units, ExUnits, [Fees
Paid], Credits, ConCredits, [Obl Fees] )
SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data
Entry].Date, [tbl_Inital Data Entry].Jurisdiction,
[tbl_Inital Data Entry].Zone, [tbl_Inital Data
Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital
Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid],
[tbl_Inital Data Entry].Credits, [tbl_Inital Data
Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees]
FROM [tbl_Inital Data Entry]
WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Accept"
And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept"));

SQL of delete query (DeleteonDecline):
DELETE [tbl_Inital Data Entry].*, [tbl_Inital Data
Entry].AdminStatus, [tbl_Inital Data Entry].Record_ID,
[tbl_Inital Data Entry].Date, [tbl_Inital Data
Entry].Jurisdiction, [tbl_Inital Data Entry].Zone,
[tbl_Inital Data Entry].LU_Type, [tbl_Inital Data
Entry].Units, [tbl_Inital Data Entry].ExUnits, [tbl_Inital
Data Entry].[Fees Paid], [tbl_Inital Data Entry].Credits,
[tbl_Inital Data Entry].ConCredits, [tbl_Inital Data
Entry].[Obl Fees], [tbl_Inital Data Entry]![AdminStatus]
FROM [tbl_Inital Data Entry]
WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Decline"
And ([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline" And
([tbl_Inital Data Entry]![AdminStatus])="Decline"));

I have put these both into a macro
called "AcceptDeclineMac" which only has two actions:
1. Open Query (argument is open AccepttoAppend)
2. Open Query (argumeent in open DeleteonDecline)

AcceptDeclineMac is referenced to in the
properties/event/"on change", as the AdminStatus field is
a pull down menu. I also tried linking the macro to
the "after update field" -- no changes in performance.

I really appreciate your time, I will keep an eye out for
your response.

Thanks,
Kendra

 

[John Spencer (MVP)]

Not sure WHY things were reformatted for you. I would try pasting the following
into the SQL window and then switch to the design window and see what it gives
you. I did surround Date and Zone with [] and changed the ! to a . in the where clause.


INSERT INTO tbl_New_Report_DB
( Record_ID, [Date], Jurisdiction,
[Zone], LU_Type, Units, ExUnits,
[Fees Paid], Credits, ConCredits,
[Obl Fees] )
SELECT [tbl_Inital Data Entry].Record_ID,
[tbl_Inital Data Entry].[Date],
[tbl_Inital Data Entry].Jurisdiction,
[tbl_Inital Data Entry].[Zone],
[tbl_Inital Data Entry].LU_Type,
[tbl_Inital Data Entry].Units,
[tbl_Inital Data Entry].ExUnits,
[tbl_Inital Data Entry].[Fees Paid],
[tbl_Inital Data Entry].Credits,
[tbl_Inital Data Entry].ConCredits,
[tbl_Inital Data Entry].[Obl Fees]
FROM [tbl_Inital Data Entry]
WHERE [tbl_Inital Data Entry].[AdminStatus])="Accept"

IF the query works, then save it, close it, open it in design view and look at
it. Did it still change? Please post back with your results. And if it
changed, post back to show us the new results.

I really appreciate your kindness in helping me out. Here
is the SQL for the action queries (if it clarifies things):

SQL of append query (AcceptoAppend):
INSERT INTO tbl_New_Report_DB ( Record_ID, [Date],
Jurisdiction, [Zone], LU_Type, Units, ExUnits, [Fees
Paid], Credits, ConCredits, [Obl Fees] )
SELECT [tbl_Inital Data Entry].Record_ID, [tbl_Inital Data
Entry].Date, [tbl_Inital Data Entry].Jurisdiction,
[tbl_Inital Data Entry].Zone, [tbl_Inital Data
Entry].LU_Type, [tbl_Inital Data Entry].Units, [tbl_Inital
Data Entry].ExUnits, [tbl_Inital Data Entry].[Fees Paid],
[tbl_Inital Data Entry].Credits, [tbl_Inital Data
Entry].ConCredits, [tbl_Inital Data Entry].[Obl Fees]
FROM [tbl_Inital Data Entry]
WHERE ((([tbl_Inital Data Entry]![AdminStatus])="Accept"
And ([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept" And
([tbl_Inital Data Entry]![AdminStatus])="Accept"));

S N I P

I have put these both into a macro
called "AcceptDeclineMac" which only has two actions:
1. Open Query (argument is open AccepttoAppend)
2. Open Query (argumeent in open DeleteonDecline)

AcceptDeclineMac is referenced to in the
properties/event/"on change", as the AdminStatus field is
a pull down menu. I also tried linking the macro to
the "after update field" -- no changes in performance.

I really appreciate your time, I will keep an eye out for
your response.

Thanks,
Kendra