Query from report that runs a report

[AlfredNor]

I need a query based on a search form that runs a report.

Basically, I have a form that records a person's name and has a subform with entries that records a date and a name on each line.

What I need is to run a query that takes its information from a search form Ive made and automatically puts it into a report.

Im guessing that I can make a command button that runs the report....and the report is based on the query...but how do I set up the query to get its information straight from the search form?

What language do I use in the query to say the criteria for Field1 should = Field2 (from a separate table) ??

 

[Hayley Baxter]

I have a sample that might help you, the query gets it's information from the form. The report is based on the query and produces the results as per your selection(s) in the form. If you want it send me an email as it's too big to attach to the forum and remember to tell me what version of access you are using.

Hay

 

[AlfredNor]

thank you!

Thanks I could use it!

Please send to both apothicsouls@yahoo.com and Jnorris@ms-ainc.com. Yahoo wont take anything over 1.5 mgs so depending on the size, I may not get it there.

Thanks.

 

[Hayley Baxter]

They have now been sent to both your emails.

Hope this helps
Best Regards
Hay

 

[AlfredNor]

This is exactly what I need. However,im not the greatest SQL programmer.

I looked at the properties for the command button on the form and found the event proceedure. Unfortunately its not as simple as merely cutting and pasting field/object names into the command lines. Can you offer some guidance?

I printed out the Summary from the Microsoft Knowledge Base on QBF but its written in Japanese! Greek! Russian! (wait I speak Russian).

This really will be great if I can get it to work...save a lot of time and such. Id appreciate any and all guidance I could get.


Thanks - Alfred (Alfie if you're from Britain)

 

[Hayley Baxter]

Hi Alfie

Glad those samples were of some help to you. Tell me what you need and I'll help you with this. You can send me the db if you want along with some instructions and If I get the time I'll do this for you. If there is real data in it just make a copy and take the data out and I'll use some dummy data to work with.

Let me know
Hay

 

[AlfredNor]

Files sent!

Let me first of all say THANK YOU. I promise I wont take advantage of your help over and over, but this one part is kickin my arse.

Ive sent the file (with the data - its neither sensitive or large and will help with understanding the subforms).

In case you're reading this before the e-mail the quick and dirty of it is that I need a query by form that pulls a date out of a subform and displays a report. Every master record that has a date in the subform which matches the date typed in the form gets

I explained it much better in the letter. Tell me if you didnt get it.

Much gratitude,

Alfie

(I used to live in Exeter (a bit removed from you) and still have a taste for spotted dick which I cant get here)

 

[Hayley Baxter]

Hi Alfie

Your most welcome. I haven't had time to look at this yet but I will probably get a chance sometime this afternoon but if not then definately tomorrow. I received the file ok and I'm sure I will be ok following your explanation.

Exeter? Have no idea where abouts exeter is down south, down south somewhere (as they used to say on strike it lucky top, middle or bottom?) Haven't a clue!

I'll be in touch
Hay

 

[Hayley Baxter]

Hi Alfie

I've had a quick look at this is the Application Date the only field you want to pull a search on? So say you search on date1 you would get all records for date1 yeah? or do you want to pull records on the other fields in the subfrm as well?

Hay

PS Your database is very nice - like the design!!

 

[Rich]

Exeter is one of our ancient Roman towns Haley, just up the road from Plymouth, I suspect you've never heard of spotted dick either

 

[AlfredNor]

Just the one.

Thanks. Yes - just the application date.

I will use this to say..."Who should get their credits applied on (this date) ?" Then the report spits out all the master records who have subfields with application dates fitting the query.

QueryByForm : What date ? (user enters) 10/12/02

Report: 10/12/02
Adkins, Lisa RefAmt:35$
Smith, Jason RefAmt:34$
etc....

I promised I wouldnt take advantage...and I wont, but would it be possible for the user to put in a range of dates as well as just one date? If its too hard then dont worry about that part.

Sorry if I overexplain...its just my way. I appreciate the help.

Alfie

P.S. Thanks for complimenting my DB. I worked really hard on it.

 

[Hayley Baxter]

Hi Alfie

I've got this working for you, at least to what I think you're after. What I have done is allowed you to have a date range to type in date from - Date To and then bring up the report from this. I have lost your email address, can you give me this again and I'll send you what I have done. The report is nothing fancy just done this for quickness to get it working..will leave the rest up to you since you're quite the designer!!

Best wishes
Hay

 

[AlfredNor]

Thanks!

Super! my email address is apothicsouls@yahoo.com

I'll design the heck out of it...and Ill get a lot of use out of your addition!

 

[Hayley Baxter]

File has been sent.

Regards
Hay

 

[AlfredNor]

Great got it!

PERFECT!! You are a certified Genius.

Since new info has been entered into the "real" db over the weekend...I can just copy the parts you made in the "sample" one...and they should work correctly since the field names are the same right?

Im also going to be a bit daring and try to add [CustomerName] to the search. I'll look at what you did and try to follow in your footsteps. (Its a bit unneccessary since there's a list box that can be typed into...but one of the users keeps bugging me about it.)

 

[Hayley Baxter]

Yes that should be fine, Sorry about before I didn't realise your email addresses were already on this thread.

Best of luck
Hay

 

[AlfredNor]

CRAP!@#$@#

I tried just copying the new items over....but im having trouble.

I had to make a new command button...which was easy enough..but now when I run the search form I get a compilation error in the SQL "User type not defined." Im guessing this has something to do with the button/click event on the SQL search form.

What should I change?

I await your instructions.


Also Im not sure why the the same records show up multiple times -sometimes three, other times six times - on the same Search Report ? Like the same search is copied several times on the same report. I guess im not hip to the use of sub reports. Educate me.

Thanks

P.S Depending on how much data was entered into the "real" database I may use the working DB and re-enter the new entries.

 

[Hayley Baxter]

Check the library references in your 'real' db and compare to the sample I sent you it sounds as though you might be missing one.

The way your dates are set up they are currently US. I've had this problem before and it was pointed out in a previous post the # symbol represents US dates. Harry ammended my code to see if this would work for uk dates but I didn't use it, instead I entered a format of dd mmm yyyy which seems to work ok. If you want to keep the short date format I'll find you my post and you can try out Harry's ammendment.

Let me know if you have any further problems
Hay

 

[AlfredNor]

Im not sure how to do that

 

[Hayley Baxter]

- Highlight your form in the database window
- View menu, code
- Tools menu, References (Do this with both dbs check they all have the same references)
- If they don't add the ones that are missing