Parameter query??

[Switchwork]

Hello peeps.

Needing your help again.

The problem I have is I have set up a parameter query to generate an invoice report. The parameters I ask are Start Date, End Date and Customer ID. However when I designed the report I changed the way Weight was written in the label so that it fitted in the design without looking messy.
However when I now run the parameter query to generate the report I get asked for Wgt/kg as one of the parameters. Obviously the Weights are different for each job on the invoice so I can't have a parameter. The weights should automatically be going onto the report along with all the other data.
So when I click OK I don't get the weight data put on to the invoice report.
Please could someone help me with this problem as it is causing me some problems. I just don't know how to change it so that I don't get that parameter coming up.

Thanx in advance.
I appreciate it.
Switch

 

[Mile-O]

What's the SQL?

 

[Switchwork]

Please don't get technical on me

I use this in the criteria in the design query.

In the Date Field I use: Between[Enter Start Date]and[Enter End Date]

In the CustomerID field I use: [Enter CustomerID]

This is what I was told to do to generate the Invoice Report. The query runs fine I get what I want.
But when I want to generate the report from the query it asks me for the extra parameter. The Wgt/Kg one I told you about.

See the actual field name is Weight. But I changed the label in the report design to Wgt/Kg. That is when it changed and I started getting asked for the Wgt/Kg parameter.

I really hope I haven't got to design the whole report again as my report design is really quite good.
I just want to know if there is somehow I can get rid of this other parameter so that the Weight Data goes into the invoice with all the other relevant data that is needed in the invoice.
The weight is really needed as it determines the cost of the job. If the weight of the parcel is not entered on the invoice then my customers can query the amount I have charged them/
Please please please please help me with this if you can.

I don't know anything about SQL but I am looking to learn all about that in the future.

Thanx
Switch

 

[Mile-O]

On the query design grid - at the top left - there is an icon that allows you to switch between the design view, datasheet view, and the SQL view.

Switch to the SQL view, copy whta's there, and paste it here.

If you want to learn SQL then the best method is to create a query and study it in the SQL view. Start with small queries and then build them as you understand them.

 

[Switchwork]

SELECT Customers.CustomerID, Customers.CompanyName, Customers.BillingAddress, Customers.BillingAddress1, Customers.[City/Town], Customers.[County/Region], Customers.PostalCode, [Job Details].[Job No], [Job Details].[REF NO], [Job Details].Date, [Job Details].[Pick up], [Job Details].Destination, [Job Details].Weight, [Job Details].NOP, [Job Details].[Job Cost], [Job Details].[Extra Costs], [Job Details].[TOTAL COST], [Job Details].InvoiceSent, [Job Details].[Invoice Paid]
FROM Customers INNER JOIN [Job Details] ON Customers.CustomerID = [Job Details].CustomerID
WHERE (((Customers.CustomerID)=[Enter CustomerID]) AND (([Job Details].Date) Between [Enter Start Date] And [Enter End Date]));


Here you go. Now this looks proper complicated if you ask me. But here it is for you anyway. Thanx for the advice on learning SQL. I have in the past posted the odd question on here about any websites to learn SQL. But I thought I would try and get the hang of Access first cos from what I have heard SQL is quite difficult. but thanks for the tip learning it from the query int design view I never knew that.

So can you work out what is wrong with it from this SQL??
Please help me??
Thanx again

 

[Mile-O]

Okay, that looks fine.

Now look at the Sorting/Grouping on your report. Is there a reference to the Wgt/kg field there?

 

[Switchwork]

I have looked at the grouping and sorting on my report and there is nothing that I can see with reference to the Weight.

See the field name is Weight and I changed it on the report to Wgt/Kg so that it would fit on the report. Thats when I started getting asked for the Wgt/Kg parameter.

But I can't see anything with relevance to the Wgt/Kg on the sorting and grouping
Any thing else??

 

[Mile-O]

See the field name is Weight and I changed it on the report to Wgt/Kg so that it would fit on the report. Thats when I started getting asked for the Wgt/Kg parameter.

You changed the ControlSource property to Wgt/Kg? If so, that's the problem.

 

[Switchwork]

So do I change the control source back to Weight ? Will that sort it out??

Thanx for the answers and at least I have learnt how to learn about SQL in the future.
So will this fix it??

 

[Mile-O]

Yes. As it's the ControlSource, it just lets Access know which field to show the value for. As your field is called Weight in the query the ControlSource should also be weight.

It's currently looking at the query bound to the report and looking for a field that doesn't exist.

 

[Switchwork]

Thank you so much sorry for taking so long to figure this out but I really so appreciate your help. I have learnt so much from this forum. I have actuall learnt everything that I know about access and now I have got my first step to learning SQL.

Thanx alot. No doubt you will be hearing from me again as I do have a few more questions to ask you.

As I am in the middle of designing another database as I missed things off the first one and am learning from the mistakes that I made in the first application.

Thanx again and I will be here again soon.

 

[Mile-O]

Here's the book I use as an SQL reference. Only £10 in the shops...

 

[Switchwork]

Thanks for the word on the book. I will definetly be getting that book its a bargain for £7.59.

I am just about to ask another question but its about reports now so I will be jumping onto the Reports forum