LINK A FORM FORMULA TO A TABLE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to show the formula that I created in a form in my table, so I can
then import the report into Excel. What are the steps?

Thanks.
nazzoli
 
Tables do not store formulas. You can use the formula in a query and then
either make a table, append records, or update records.
 
I need to show the formula that I created in a form in my table, so I can
then import the report into Excel. What are the steps?
Click to expand...

Put the formula into a vacant Field cell in a Query (rather than
calculating on the form), and export the Query itself. You cannot put
a formula into a table, and there's no need to do so in any case;
queries export just fine.

John W. Vinson[MVP]
 
When I input the expression into the query and I change it back to a database
all I recieve is a -1????? I am using this formula
FORMAT([SUIT DTE],"MM YY").

Any ideas?
 
When I input the expression into the query and I change it back to a database
all I recieve is a -1????? I am using this formula
FORMAT([SUIT DTE],"MM YY").
Click to expand...

I have no idea what you mean by "change it back to a database". A
query is NOT a database; a database is a .mdb file, a container for
multiple objects.

Perhaps you could open the Query in SQL view and post the entire query
here.

John W. Vinson[MVP]
 
SELECT Bjerep.[ACCT #], Bjerep.[LAST NAME], Bjerep.[SUIT DTE], Bjerep.[MTH YR
REF], Bjerep.[FWD ID], Bjerep.[CLIENT #], Bjerep.[TYPE OF DEBT],
Bjerep.DISPO, Bjerep.STATUS, Bjerep.REASON, Bjerep.WAGE, Bjerep.BANK,
Bjerep.LIEN, Bjerep.ADD, Bjerep.[PURCHASE BAL]
FROM Bjerep
WHERE (((Bjerep.[MTH YR REF])=Format([SUIT DTE],"mm yy")));


John Vinson said:
When I input the expression into the query and I change it back to a database
all I recieve is a -1????? I am using this formula
FORMAT([SUIT DTE],"MM YY").
Click to expand...

I have no idea what you mean by "change it back to a database". A
query is NOT a database; a database is a .mdb file, a container for
multiple objects.

Perhaps you could open the Query in SQL view and post the entire query
here.

John W. Vinson[MVP]
Click to expand...
 
SELECT Bjerep.[ACCT #], Bjerep.[LAST NAME], Bjerep.[SUIT DTE], Bjerep.[MTH YR
REF], Bjerep.[FWD ID], Bjerep.[CLIENT #], Bjerep.[TYPE OF DEBT],
Bjerep.DISPO, Bjerep.STATUS, Bjerep.REASON, Bjerep.WAGE, Bjerep.BANK,
Bjerep.LIEN, Bjerep.ADD, Bjerep.[PURCHASE BAL]
FROM Bjerep
WHERE (((Bjerep.[MTH YR REF])=Format([SUIT DTE],"mm yy")));
Click to expand...

It appears that you put the Format expression on the Criteria line
under [MTH YR REF] rather than doing what I suggested - putting it in
a vacant Field cell.

It's not at all clear what you're trying to accomplish, but if you
want a text field such as "06 06" for a date stored in the Date/Time
field [MTH YR REF], try

SELECT Bjerep.[ACCT #], Bjerep.[LAST NAME], Bjerep.[SUIT DTE],
Bjerep.[MTH YR REF], Format([SUIT DTE],"mm yy") AS ShowDate,
Bjerep.[FWD ID], Bjerep.[CLIENT #], Bjerep.[TYPE OF DEBT],
Bjerep.DISPO, Bjerep.STATUS, Bjerep.REASON, Bjerep.WAGE, Bjerep.BANK,
Bjerep.LIEN, Bjerep.ADD, Bjerep.[PURCHASE BAL]
FROM Bjerep;

If that's not what you're trying to do, please explain what you ARE
trying to do!

John W. Vinson[MVP]
 
Works great thanks for the help.

John Vinson said:
SELECT Bjerep.[ACCT #], Bjerep.[LAST NAME], Bjerep.[SUIT DTE], Bjerep.[MTH YR
REF], Bjerep.[FWD ID], Bjerep.[CLIENT #], Bjerep.[TYPE OF DEBT],
Bjerep.DISPO, Bjerep.STATUS, Bjerep.REASON, Bjerep.WAGE, Bjerep.BANK,
Bjerep.LIEN, Bjerep.ADD, Bjerep.[PURCHASE BAL]
FROM Bjerep
WHERE (((Bjerep.[MTH YR REF])=Format([SUIT DTE],"mm yy")));
Click to expand...

It appears that you put the Format expression on the Criteria line
under [MTH YR REF] rather than doing what I suggested - putting it in
a vacant Field cell.

It's not at all clear what you're trying to accomplish, but if you
want a text field such as "06 06" for a date stored in the Date/Time
field [MTH YR REF], try

SELECT Bjerep.[ACCT #], Bjerep.[LAST NAME], Bjerep.[SUIT DTE],
Bjerep.[MTH YR REF], Format([SUIT DTE],"mm yy") AS ShowDate,
Bjerep.[FWD ID], Bjerep.[CLIENT #], Bjerep.[TYPE OF DEBT],
Bjerep.DISPO, Bjerep.STATUS, Bjerep.REASON, Bjerep.WAGE, Bjerep.BANK,
Bjerep.LIEN, Bjerep.ADD, Bjerep.[PURCHASE BAL]
FROM Bjerep;

If that's not what you're trying to do, please explain what you ARE
trying to do!

John W. Vinson[MVP]
Click to expand...
 
Back
Top