Can't get query result to display in text box 2

[jpl458]

Have the following Query 'Cards Left in Inventory':

SELECT [CardInventoryTbl]![CardsinInventory]-[Cards used So Far]![SumOfCountOfMinOfBusiness_Date] AS CardsLeft
FROM [Cards used So Far], CardInventoryTbl;

Record Source of form is Query "Cards Left in Inventory"
Control Source of text box is "=[Cards Left in Inventory]![CardsLeft]"
But all I get is #Name in text box.
The query runs and give the correct answer.

Office 10
Windows 7

Thanks in advance

jpl

 

[dhookom]

I would first get rid of the table/query name and then the = sign. THen check the name of the text box and possibly change it to txtCardsLeft.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

The query runs", well, it runs, but you have 2 tables ( [Cards used So Far] and CardInventoryTbl) but you never stated in your query how those 2 tables are related. So you do get some results, but they are probably wrong, or will be wrong in the future with more records in them.

Have fun.

---- Andy

 

[jpl458]

CardInventoryTbl is a table with one field, CardsinInventory, which is updated every couple of months with the date and the number of cards now available.
There is a query that I run to get the number of cards used to date:
SELECT Sum([a New Plastic Sales Counts by day].CountOfMinOfBusiness_Date) AS SumOfCountOfMinOfBusiness_Date
FROM CardInventoryTbl, [a New Plastic Sales Counts by day]
WHERE ((([a New Plastic Sales Counts by day].MinOfBusiness_Date)>[CardInventoryTbl]![DateLast]));


Finally In the last query I want to subtract the cards used from the last inventory count:
SELECT [CardInventoryTbl]![CardsinInventory]-[Cards used So Far]![SumOfCountOfMinOfBusiness_Date] AS CardsLeft
FROM [Cards used So Far], CardInventoryTbl;

I don't join the table and the queries because there is nothing to join on. I just want to do the subtraction of the two fields.

I created the queries using the QBE grid.

Sorry for taking so long to get back.

Thanks

jpl

 

[dhookom]

Do you still need assistance or is the issue resolved?

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

The problem is not resolved.

I want to save a date and the number of cards that are available. I want to be able to count the number of cards that have been used since that saved date and subtract that number from the number of cards that we available on that date. Very simple. When I run that last query it always gives me the correct number. I just can't get it to display in a form.
There is no join strategy here because there is nothing to join on. But the query runs and gives the correct answer.
Thanks for keeping in touch.

jpl

 

[dhookom]

You can display the results of a query as:
- the record source of a form or subform
- using DLookup() to grab a single field value (or expression) from a the query
- using VBA code to grab the value and display it in a control

Duane
Hook'D on Access
MS Access MVP

 

[jpl458]

I am sure that I responded earlier, but the post is not in this record of posts. I changed names and basically just tried some stuff and eventually worked, although I can't explain exactly why. But, with the last query having a new name, and using that name as the record source, I was able to get to info to display.

Thanks for the response

jpl.