Jack-are you there? (1 Viewer)

[smelly]

Hi,
Yet another problem, big surprise. I am trying to do a parameter query and I am getting this SQL error when I run the report off the query. The query works by itself.

Here is the error...

The specified field '[LAB ID]' could refer to more than one table listed in the FROM clause of your SQL statement. (Error 3079)

And here is my SQL code...

SELECT Table1a.*, Table1a.[LAB ID]
FROM Table1a
WHERE (((Table1a.[LAB ID]) Between [ENTER BEGINNING LAB ID] And [ENTER ENDING BBC ID]));

I don't see the problem here. Do you?
Thanks!!!!!!!!

 

[Jack Cowley]

Create a small form with two unbound text boxes and a command button that will open your report. Name the unbound text boxes BeginID and EndID. In your query put this in the criteria field of the LabID field:

Between [Forms]![NameOfNewForm]![BeginID] and [Forms]![NameOfNewForm]![EndID]

Open the form, enter the ID's and click the command button to print the report...

hth,
Jack

 

[smelly]

I did just that but it has the exact same error message. My new criteria is this...
Between [Forms]![PrintILS]![BeginID] And [Forms]![PrintILS]![EndID]

but it still spits out the error about the ....'[Lab ID]'.....

Do you know what the problem could be?
Thanks!

 

[Jack Cowley]

Hard to know where this error is coming from. You could have an control on your report that was bound to a field that is now no longer in the field list of the report. You can try deleting the Record Source, saving the report, reopening and then adding the Record Source back in. You can also try making a quick report using the Wizard and see what that produces. It is hard to know what is causing this error without seeing the db, but give those to things a try and one of them has a good chance of solving the problem...

Good luck...

Jack

 

[smelly]

Ok, I found the error! The query works by itself and the report works by itself but when I use the form with the print report button I get a blank report. Do I have to send the beginID and endID in?
Thanks again for your help, i am nearing the end of this mess so hopefully I won't bug you too much more.

 

[Jack Cowley]

If your query has this line of code:

Between [Forms]![PrintILS]![BeginID] And [Forms]![PrintILS]![EndID]

in the criteria line of the query then opening the report by clicking the button on the PrintILS form should work. You cannot close the PrintILS form when you open the Report....

I am not sure that I follow your last post as you say you found the error, but what follows leads me to believe that it still is not working correctly. Am I missing anything?

Jack

 

[smelly]

The error I found was in the control source key on the reports LAB ID. It was just LAB ID so I changed it to Table1a.LAB ID and now it works. But that button on the form doesn't seem to be working properly. I made the button using the wizard and the form doesn't close so I don't know what could be wrong. It brings up the right table but none of the LAB ID's are filled in.
Here is the code from the button, is it missing something?

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String

stDocName = "InternalLab1a"
DoCmd.OpenReport stDocName, acNormal

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub
THanks a million.

 

[Jack Cowley]

Would it be possible to import the table, form, query and report into a database, test it to be sure it is still not working and then email it to me? I cannot guarantee that I can solve the problem but I am willing to take a shot at it. You can email me at jpcowley@aaahawk.com and I will be online for about another 20 minutes or so.

Jack

 

[Pat Hartman]

The problem is here:
SELECT Table1a.*, Table1a.[LAB ID]
[LAB ID] will appear twice, once because it was specifically selected and again because it was selected with all the columns of Table1a. Get rid of the specific reference by unchecking the "show" box for that field in the QBE grid. You can still specify the criteria.