Query

[gianina]

Hi All,

I created a form which pull-up data (fields) for a certain NCR (non-conformance report) selected. There is a designated field called NCR Number. There are several NCR Numbers in the table.

On this form I created a button which which e-mails a report to users selected.
This report contains all fields from the form including he NCR Number.

My problem is that I don't know how to create the query behind the report in order to create the report with data related to the certain NCR Number selected.

So, basically, let's say we have 20 NCR's in the table. That means 20 NCR Numbers with related data (fields).
The user selects let's say NCR 15 and my form pop-up with all data related to NCR 15. Now, the user wants to send all this data to other users. We will send this information through a report.
How should I create the query behind the report that will generate the report only for the certain NCR number, in our case NCR 15; with all related fields ????

Thanks.

 

[randy700]

You can add a WHERE clause to your query...

WHERE NCR = [i][COLOR=red]forms!YourFormName!YourTextBoxName[/color][/i]

Or you can add the WHERE clause when you open the report...

docmd.OpenReport "[NCR Report]",acViewNormal,,"NCR = " & [i][COLOR=red]forms!YourFormName!YourTextBoxName[/color][/i]

Randy

 

[gianina]

Thanks randy700,

I entered the code behind the button which opens the report:

docmd.OpenReport "[ReportNCR]",acViewNormal,,"NCR = " & forms!NCR formx!NCR #

and an error message pops-up: "Compile error: Syntax error"
and th entire code is highlighted in red.

Where's the problem ?

Also, can you tell me where exactly to enter the code for the query. I'm not very familiar with WHERE conditions.

Thanks.

 

[randy700]

How about...
docmd.OpenReport "[ReportNCR]",acViewNormal,,"NCR = " & forms![NCR formx]![NCR #]

In a query...
SELECT FieldName1, FieldName2
FROM TableName
WHERE NCR = forms![NCR formx]![NCR #]

Randy

 

[lespaul]

as Randy has shown, you have to surround field names with spaces and unusual character (and keywords like Date incorrectly used as field names) with brackets.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gianina]

randy700,

Your suggestion with the brackets was good. It works.

However, I'm still confused with the query:

" In a query...
SELECT FieldName1, FieldName2
FROM TableName
WHERE NCR = forms![NCR formx]![NCR #] "

I'm keep looking at my query and I don't know where to enter the above !

Please help...

 

[gianina]

I got it guys...

But, when I press the button for sending the report via e-mail a small window pops-up asking me the NCR #. Even after I enter the selected NCR # the report will show the entire database (ALL NCR #'s...all 20 of them). ANd I want to show only the one NCR # selected.

Is it possible to do this without the small window poping up asking for the NCR # ?

How can I fix the report issue ?

Thanks a million...

 

[randy700]

The example I gave about the query can be used if you are creating your query in SQL. If you are using the query grid, put the = forms![NCR formx]![NCR #] on the Criteria line in the NCR column.

Randy

 

[gianina]

Thanks Randy...it works fine.

I appreciate your help.