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:
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Hi

Try changing the second exclamation mark (!) for a period (.).

HTH, Andrew :)
 

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,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows

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,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,022
Messages
5,834,994
Members
430,330
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top