By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

How to enter dates from vba / query in report description

P: 62
Hello friends

I want to make a report which display data as below
Column 1 Jan to till today.
Column 2 Last 30 days like 16 Jul to 15 Aug.

This report is completed and all data is being displayed... see attachment

But I have to mention 16 Jul to 15 Aug manually.
How can computer write itself this sentence automatically from query or vba.

Kindly guide me ...

Thanks
irsmalik

Attached Images
File Type: jpg REPORT.jpg (126.8 KB, 71 views)
1 Week Ago #1

✓ answered by twinnyfo

Then make the report header a Text Box with the following as itís control source:

Expand|Select|Wrap|Line Numbers
  1. = Format(Date() - 30, "dd Mmm") & " to " & Format(Date(), "dd Mmm")
You will have to fix the curly quotes to straight quotes, as I am typing on my iPad.

Hope this hepps.

Share this Question
Share on Google+
9 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,257
irsmalik,

I think you need to modify your Query to handle that. You seek data that covers those dates, so this is not a report issue, but a query issue. If you could post the query that you use for your reportís record source, that would be helpful.

thanks
1 Week Ago #2

P: 62
Here is my query:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     DM.Class, 
  3.     DM.ClassName, 
  4.     DM.Size, 
  5.     DM.SizeName, 
  6.     DM.TDate, 
  7.     Sum(DM.BG) AS SumOfBG, 
  8.     Sum(DM.Demo) AS SumOfDemo, 
  9.     Sum(DM.DTP) AS SumOfDTP, 
  10.     Sum(DM.FAS) AS SumOfFAS, 
  11.     Sum(DM.FM) AS SumOfFM, 
  12.     Sum(DM.HPM) AS SumOfHPM, 
  13.     DM.Division
  14. FROM 
  15.     DM
  16. GROUP BY 
  17.     DM.Class, 
  18.     DM.ClassName, 
  19.     DM.Size, 
  20.     DM.SizeName, 
  21.     DM.TDate, 
  22.     DM.Division
  23. HAVING 
  24.     (((DM.TDate) Between Date() And Date()-30))
  25. ORDER BY 
  26.     DM.ClassName, DM.TDate;
When I run my query, my required data display.... it is OK... Now I want to print this part of my query on my report.
HAVING (((DM.TDate) Between Date() And Date()-30)).

How to do it.... plz guide.
thanks
irsmalik
1 Week Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,257
Exactly what is it that you want to do with this query? What results does your query produce and what results do you want? This is our starting point. Do you just want a sun of your totals that covers the most recent 30 days? Do you want something else?

I guess Iím just not following exactly what the issue is. Iíd love to help, but I need to understand better.

Thanks.
1 Week Ago #4

Rabbit
Expert Mod 10K+
P: 12,359
Sounds like they want to print the query filter text on the report so they know how it's filtered.

You'll need to grab the SQL from the query def, parse it, and put it into a control.
1 Week Ago #5

P: 62
Yes.. Mr Rabit.... you got my point. Query Data is not problem. It is fine.

Basically I want to print in Report Title / Description
"16 July to 15 Aug" and it will go on for further period.

At present, I do it manual... but when the user will run this report, he will not be allowed to go into Design Mod.

If any one could help me.... thanks
irsmalik
1 Week Ago #6

twinnyfo
Expert Mod 2.5K+
P: 3,257
Then make the report header a Text Box with the following as itís control source:

Expand|Select|Wrap|Line Numbers
  1. = Format(Date() - 30, "dd Mmm") & " to " & Format(Date(), "dd Mmm")
You will have to fix the curly quotes to straight quotes, as I am typing on my iPad.

Hope this hepps.
1 Week Ago #7

NeoPa
Expert Mod 15k+
P: 31,433
An alternative is to do it in your SQL (Query) instead but Twinny's approach works perfectly too.
Expand|Select|Wrap|Line Numbers
  1. ...
  2. Format(DateAdd('d',-30,Date()),'d mmm') & ' to ' & Format(Date(),'d mmm') AS [MonthTitle]
  3. ...
4 Days Ago #8

P: 62
Thanks to all friends

I have changed in the query and make a form to enter the required dates which not only give the my required dates data but it is also printed in the header / description of the report.

Regards
irsmalik
3 Days Ago #9

NeoPa
Expert Mod 15k+
P: 31,433
Very pleased to hear it. These are exactly the types of questions we can help with most easily.
2 Days Ago #10

Post your reply

Sign in to post your reply or Sign up for a free account.