Posting certain rows to a form

[ashley0578]

I've created a database that accessess tables from a program we use which is constantly updating with customer information. My database is set up to automatically run 5 times a day to basically take a snapshot of certain financial items at that time. During this automation a query is updating a table. I have named this table daily_updates, although it runs several times a day. What I need to do is format a report to pull info from 1 day ago, 1 week ago and 1 month ago. Since there are multiple rows with daily info, I just want one of those rows. I know I can count back, but I'm really unsure of how to write the code to pull a specific amount of rows back. I hope I'm not sounding too confused or confusing!

 

[raskew]

Hi –

Here are query examples for filtering for:
- yesterday
- a week ago today
- a month ago today

Change the table / field names to fit your needs.

Yesterday:

SELECT OrdersTemp.*
FROM OrdersTemp
WHERE (((OrdersTemp.OrderDate)=Date()-1));

A week ago today

SELECT OrdersTemp.*
FROM OrdersTemp
WHERE (((OrdersTemp.OrderDate)=Date()-7));

A month ago today

SELECT OrdersTemp.*
FROM OrdersTemp
WHERE (((OrdersTemp.OrderDate)=DateAdd("m",-1,Date())));

HTH - Bob

 

[ashley0578]

Thanks for your help, Bob!

I think I understand where you're going with this...

I changed out OrdersTemp.* for Daily Totals.All Active where Daily Totals is my table and All Active is the field I want to pull from. I changed OrdersTemp.OrderDate for Daily Totals.Date where Date is the time stamp.

When I run the report, I get this error.

The Microsoft Jet Database engine does not recognize [SELECT Daily Totals].[All Active FROM Daily Totals WHERE (((Daily Totals].[Date)=Date()-1));] as a valid field name or expression.

I think it is my wonderful naming convention but I'm not sure.

Any suggestions?

 

[ashley0578]

I changed the table name to DailyTotals. Now I get this error.

Syntax error (missing operator) in query expression '[SELECT [DailyTotals].[All Active] FROM [DailyTotals] WHERE ((([DailyTotals].[Date])=Date()-1));]'.

The report is pulling from a query called totals, not this table. Does that matter?

 

[richary]

Your using spaces in your table/field names which is bad parctice. Use square brackets, or better still, rename your tables/fields to remove the spaces.

 

[ashley0578]

I have updated my tables to use the proper naming conventions and I also used square brackets. I'm still getting this error.

Syntax error (missing operator) in query expression '[SELECT [DailyTotals].[AllActive] FROM [DailyTotals] WHERE ((([DailyTotals].[Date])=Date()-1));]'.

 

[ashley0578]

I decided to make another report I've named test. The control source is my table DailyTotals. I've added one text field just to try out the sql. The sql I've used is
SELECT AllActive FROM DailyTotals WHERE PrevDate=Date()-1;

AllActive is the amount of active accounts we have at that given time. DailyTotals is the table and PrevDate is the date/time of that particular update. When I click to view the report, I am now asked for a Parameter Value. I truly do not know what I am doing wrong.

 

[richary]

When you say

I've added one text field just to try out the sql. The sql I've used is
SELECT AllActive FROM DailyTotals WHERE PrevDate=Date()-1;

what is it that you are doing? The above SQL should be used as your reports record source, not as the record source for a text field. Paste the above SQL into the record source property for your report. You have one field (AllActive) to place on your report. If that is what you're doing already, check that there isn't a spelling mistake in the field names.