Access 2003 query - form link

Dr. Chill

Board Regular
Joined
Jan 28, 2005
Messages
83
I just was upgraded to Office 2003 this weekend. I am running a query in access that uses a start data and an end date from a form. I have it linked in to my query so I can have one column just give me start data and one give me end date. But now when I run it they are blank even when the form shows they are 12/1/05 (start date) and 12/10/05 (end date). Is this something in 2003 or is it a different problem.

My formula is this in the query field

StartDate: [Forms]![Cash Flow]![Start Date] :devilish:
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Dr. Chill

Board Regular
Joined
Jan 28, 2005
Messages
83

ADVERTISEMENT

SELECT [xRef - Account List for Cash Flow Daily].ProdType, [xRef - Account List for Cash Flow Daily].Type, [xRef - Account List for Cash Flow Daily].RF, [xRef - Account List for Cash Flow Daily].Description, Sum(tbl_Cash_Flow_Daily.DP) AS SumOfDP, Sum(tbl_Cash_Flow_Daily.EP) AS SumOfEP, Sum([DP]+[EP]) AS [Gross Purchases], Sum(tbl_Cash_Flow_Daily.DR) AS SumOfDR, Sum(tbl_Cash_Flow_Daily.ER) AS SumOfER, Sum([DR]+[ER]) AS [Gross Redemptions], Sum([DP]+[EP]+[DR]+[ER]) AS NetCashFlow, IIf([MoneyMktAdj] Is Null,0,[MoneyMktAdj]) AS [Money Market Adj], Sum(tbl_Cash_Flow_Daily.[GL Activity]) AS [SumOfGL Activity], Sum(([DP]+[DR])) AS NetDirect, Sum(([EP]+[ER])) AS NetExchange, [Forms]![Cash Flow]![Start Date] AS StartDate, [Forms]![Cash Flow]![End Date] AS EndDate, [xRef - Account List for Cash Flow Daily].Sort INTO tbl_CashFlow_Daily_by_Fund
FROM ([xRef - Account List for Cash Flow Daily] INNER JOIN tbl_Cash_Flow_Daily ON [xRef - Account List for Cash Flow Daily].RF = tbl_Cash_Flow_Daily.MfactShortDescription) LEFT JOIN qry_CashFlow_MMAdj_step2 ON tbl_Cash_Flow_Daily.MfactShortDescription = qry_CashFlow_MMAdj_step2.RF
GROUP BY [xRef - Account List for Cash Flow Daily].ProdType, [xRef - Account List for Cash Flow Daily].Type, [xRef - Account List for Cash Flow Daily].RF, [xRef - Account List for Cash Flow Daily].Description, IIf([MoneyMktAdj] Is Null,0,[MoneyMktAdj]), [Forms]![Cash Flow]![Start Date], [Forms]![Cash Flow]![End Date], [xRef - Account List for Cash Flow Daily].Sort
ORDER BY [xRef - Account List for Cash Flow Daily].RF;
 

Dr. Chill

Board Regular
Joined
Jan 28, 2005
Messages
83
Let me ask this a different way and see if anybody knows what I am trying to do. I want to enter a date on a form and then use it in my query as a field. So for each record it gets that date in a field.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432

ADVERTISEMENT

This looks like a totals query and my testing shows that you can pull through the value from a form exactly as you have done. Have you tried creating a parameter for the query? You can get to this when in the query design screen by clicking on Query -> Parameters -> insert your field name from the form with the full syntax (i.e. Forms!....). Does it make any difference?

I take it the field names you assigned to the 2 date fields do not exist in the underlying tables - correct?

How about saving the query as another name and removing one field / table at a time to see when this field starts or stops working? (you will need a 2nd button on your form to open this new query) If we can identify the point at which it doesn't work then we should be able to solve the problem.

Also, is there any reason you want a column of start and end dates? This technique is most often used to pull criteria into the query and / or to get header information into a report.

Andrew :)
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Late post : I just saw you have a 'left join' in your query - maybe this is the issue? Try removing qry_CashFlow_MMAdj_step2 from version 2 of the query and see what happens.
A
 

Dr. Chill

Board Regular
Joined
Jan 28, 2005
Messages
83
What I want to do is enter a date and be able to use it in my query so I can use it in my report so I can show the date range entered. Is there a better way to do that?
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
If you want to show the entered date on the report, create an unbound text box and use a control source of =[Forms]![Cash Flow].[Start Date]

Provided the form is still open the date _should_ show.

Andrew
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,088
Members
412,567
Latest member
mm1
Top